I have this task where I need to use OpenRowset to insert data into a table and then use that table further for specific calculation (using views).
This is the code for OpenRowSet that I am using:
SELECT * INTO dbo.table1 FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
'EXEC DB.[dbo].Procedure1')
The problem I have is that the database is in one collation and the result from the OpenRowSet is in another and when it comes down to using views I receive errors because the collation of table fields are different.
Is there a way that I could make the OpenRowSet return results in the collation that I would need?
Trying to change the collation of the table after the rows were imported doesn't seem to work (maybe because the end users don't have enough rights).
Any advice would be great I am ready to change the "OpenRowSet" to something else if needed, but to my knowledge this is the only way to use select * into with a stored procedure.
SQLNCLI, aka. SQL Native Client, in new development work and try to migrate existing applications that use it to something else, such as ODBC Driver 17 (or 18) for SQL Server. SQLNCLI has not had any feature updates since SQL Server 2012, does not work with any of the new protocol features in SQL Server 2017 and later, and Microsoft have finally stopped distributing it as of SQL Server 2022.charandvarcharcolumns is that they control which characters are mapped to octets 128-255, so trying to change collations will result in loss of information because not all characters can be converted (and so usually appear as?characters in the destination).EXEC DB.[dbo].Procedure1 WITH RESULT SETS ((colX varchar(123) COLLATE Lithuanian_CI_AS..))or UNION the resultset of openrowset with values of the collation..SELECT * INTO dbo.table1 FROM (select * from OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;','EXEC DB.[dbo].Procedure1') union all select '' collate database_default, '' collate database_default, 0, 0, getdate() where 1=2 ) as t