July 9, 2021 at 1:56 pm
Hello.
I need to execute a select into statement from one SQL server to another.
I try to execute it with both ways above
SELECT * INTO [SecondServer].[SecondDatabase].[dbo].[object_name]
FROM FirstDatabase.dbo.object_name
SELECT * INTO [SecondServer].[SecondDatabase].[dbo].[object_name]
FROM OPENQUERY (SecondServer, 'SELECT * from FirstDatabase.dbo.object_name')
but I get the same error message:
"The object name '[SecondServer].SecondDatabase.dbo.object_name' contains more than the maximum number of prefixes. The maximum is 2."
Is there a way to execute the command through the linked server and bypass this error?
I don't want first to create the object to the second server, and I don't want to run the process through SSIS package.
Any ideas?
Thanks.
July 9, 2021 at 2:50 pm
If you don't want to create the object on the secondary server first (which is the method I would HIGHLY recommend doing as then you can properly define the datatypes and indexes and keys and such), I think you would likely need to do this FROM the secondary server and do it as a data pull instead of a data push.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 9, 2021 at 2:58 pm
EXEC('SELECT * INTO [SecondDatabase].[dbo].[object_name] FROM [FirstServer].FirstDatabase.dbo.object_name') AT [SecondServer]
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 9, 2021 at 3:10 pm
Thank you for your answers.
The EXEC command returns the same error, I had already tried this.
I don't want to create the object on the second database, cause my query is dynamic and the object changes it's schema occassionally, so this would not help either.
I finally managed to do this with the above statement:
declare @sql nvarchar (4000)
set @sql = 'select * into SecondDatabase.dbo.object_name from FirstServer.FirstDatabase.dbo.object_name'
execute [SecondServer].master.dbo.sp_executesql @sql
and it is being executed as needed.
Thanks again.
July 9, 2021 at 3:18 pm
Thank you for your answers.
The EXEC command returns the same error, I had already tried this.
I don't want to create the object on the second database, cause my query is dynamic and the object changes it's schema occassionally, so this would not help either.
I finally managed to do this with the above statement:
declare @sql nvarchar (4000) set @sql = 'select * into SecondDatabase.dbo.object_name from FirstServer.FirstDatabase.dbo.object_name' execute [SecondServer].master.dbo.sp_executesql @sql
and it is being executed as needed.
Thanks again.
I ran the EXEC myself, it works fine.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 9, 2021 at 3:42 pm
Thank you, it works fine for me too.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply