patternsqlMinor
Run "USE" with linked server alternatives
Viewed 0 times
withalternativesserveruselinkedrun
Problem
I am using SQL server linked servers and find some difficulties while running queries.
Below is the actual query.
To run it to a linked server I used
But when I run this I get error as shown below
Database 'mylinkedserver' does not exist. Make sure that the name is entered correctly.
Where as I can query the table by using
So how to run the above query?(I think i cannot make use of "USE" with linked server.So what is the alternative
EDIT:
These things i have tried after getting nice replies.
Then i used OPENQUERY option
It returned NULL. But it should have returned 1 as column1 is a identity column.
Then I checked again running my original query directly in the linked server without use command and NULL is returned . If I run same query after I mention
Below is the actual query.
USe testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0To run it to a linked server I used
Use mylinkedserver.testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0But when I run this I get error as shown below
Database 'mylinkedserver' does not exist. Make sure that the name is entered correctly.
Where as I can query the table by using
select * from pc91sql.testdb.dbo.Table_1 successfully.So how to run the above query?(I think i cannot make use of "USE" with linked server.So what is the alternative
EDIT:
These things i have tried after getting nice replies.
Then i used OPENQUERY option
SELECT * FROM OPENQUERY(pc91sql,'SELECT COLUMNPROPERTY( OBJECT_ID(''testdb.dbo.Table_1''),''column1'',''IsIdentity'')');It returned NULL. But it should have returned 1 as column1 is a identity column.
Then I checked again running my original query directly in the linked server without use command and NULL is returned . If I run same query after I mention
use testdb or in SSMS after choosing database from list then it returns 1 .So it means OBJECT_ID is not able to use database name along with table name(i.e like ''testdb.dbo.Table_1'').Solution
You can use
Even better, parameterize it:
EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions.DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
WHERE c.is_identity = 0
AND t.name = N''Table_1''
AND c.name = N''column1'';';
EXEC mylinkedserver.testdb.sys.sp_executesql @sql;Even better, parameterize it:
DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';
DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
WHERE c.is_identity = 0
AND t.name = @t
AND c.name = @c;';
EXEC mylinkedserver.testdb.sys.sp_executesql
@stmt = @sql,
@params = N'@t SYSNAME, @c SYSNAME',
@t = @t,
@c = @c;Code Snippets
DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
WHERE c.is_identity = 0
AND t.name = N''Table_1''
AND c.name = N''column1'';';
EXEC mylinkedserver.testdb.sys.sp_executesql @sql;DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';
DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
WHERE c.is_identity = 0
AND t.name = @t
AND c.name = @c;';
EXEC mylinkedserver.testdb.sys.sp_executesql
@stmt = @sql,
@params = N'@t SYSNAME, @c SYSNAME',
@t = @t,
@c = @c;Context
StackExchange Database Administrators Q#67935, answer score: 4
Revisions (0)
No revisions yet.