Author Archives: John Perry

Easy linked server setup for a one hop query.

PC (A) to SERVER (B) will
work.
PC (A) client tools to SERVER (B) and four-part queries from
SERVER (B) connection to SERVER (C) will not work without some kind of
delegation.

See "How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers" and this "Allow a computer to be trusted for delegation for specific services"

-- Add a linked server
EXEC sp_addlinkedserver 
@server = N'SQLDEV',
@srvproduct  = N'SQL Server'
GO
-- And some options
exec sp_serveroption 
@server = N'SQLDEV', 
@optname = N'data access', 
@optvalue = N'true' 
exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
GO
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'SQLDEV'
,@useself = 'TRUE'
--     ,@locallogin = 'sa'    -- this doesn't seem to be required.
GO
EXEC sp_helpserver 'SQLDEV'
EXEC sp_helplinkedsrvlogin 'SQLDEV'
GO
-- This query will work from one hop. e.g. If you are querying directly from one local server to another.
-- You can't do this by Query Analyser on PC A, logging into server B, and running the 4 part query agains
-- Server C.
select * from sqldev.master.dbo.sysservers
GO
-- Some options
-- exec sp_serveroption N'SQLDEV', N'query timeout', 0 
-- exec sp_serveroption N'SQLDEV', N'collation name', N'null' 
-- exec sp_serveroption N'SQLDEV', N'use remote collation', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc out', N'true' 
-- exec sp_serveroption N'SQLDEV', N'data access', N'true' 
-- exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
-- exec sp_helplinkedsrvlogin N'SQLDEV'
-- exec sp_addlinkedserver N'SQLDEV' 
-- Clean up our test
--  Exec Sp_dropserver 'SQLDEV', droplogins

Sometimes before I actually run dynamic SQL statement I want to know if there is any data, making it worthwhile do run it.

Here's some code that will test the T-SQL for data.

DECLARE @vcSQL VARCHAR(8000)
DECLARE @nvcSQL NVARCHAR(4000),
   @nvcParamDef NVARCHAR(128),
   @bEXISTS bit 
 -- Change the IF statement to test having data vs. not having data.
IF 'EMPTY' = 'EMPTYx'
   SELECT  @vcSQL = 'SELECT * FROM pubs.dbo.dtproperties'
ELSE
   SELECT  @vcSQL = 'SELECT * FROM pubs.dbo.authors'

IF @bEXISTS = 1
   PRINT 'Dynamic SQL [' + @vcSQL + '] data exists'
ELSE
   PRINT 'Dynamic SQL [' + @vcSQL + '] has no data'


I still don't know what is causing SSMS to output rowcount differently from QA, but to be safe one should use @@ROWCOUNT to output counts for dynamic SQL statements.

SET NOCOUNT ON
DECLARE @vcSQL NVARCHAR(4000)
SET @vcSQL = 'SELECT * FROM pubs..authors;'
PRINT @vcSQL
SET NOCOUNT OFF
PRINT 'Test1: sp_executesql'
EXEC sp_executesql @vcSQL
PRINT 'Test2: EXEC ()'
EXEC (
@vcSQL
)
PRINT '(There were ' + cast(@@ROWCOUNT as varchar) + ' row(s) affected)'
/*
----------------------------------------
Test1 **SSMS output
SELECT * FROM pubs..authors;
Test1: sp_executesql
(23 row(s) affected)
Test2: EXEC ()
(23 row(s) affected)
(There were 23 row(s) affected)
----------------------------------------
Test2 **QueryAnalyzer output
SELECT * FROM pubs..authors;
Test1: sp_executesql
(23 row(s) affected)
Test2: EXEC ()
(There were 23 row(s) affected)
----------------------------------------
*/

Sometimes I want to start with clean information. I can always add back in the owner and identifiers.

DECLARE @vcTableName varchar(128)
SET @vcTableName = 'dbo.test table'
SET @vcTableName = 'mytable.test'
SET @vcTableName = QUOTENAME('mytable.test table')
IF CHARINDEX('[', @vcTableName) > 0
BEGIN
SET @vcTableName = REPLACE(REPLACE(@vcTableName, '[', ''), ']', '')
END
IF CHARINDEX('.', @vcTableName) > 0
BEGIN
SET @vcTableName = SUBSTRING(@vcTableName, CHARINDEX('.', @vcTableName) + 1, LEN(@vcTableName) - CHARINDEX('.', @vcTableName))
END
PRINT '***' + @vcTableName + '***'
PRINT 'dbo.' + QUOTENAME(@vcTableName)