July 10, 2018 at 8:14 am
What am I doing wrong in this statement or one like it?
DECLARE @SQLString nvarchar(500);
SET @SQLString =
N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM @DbName.HumanResources.Employee ';
EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
July 10, 2018 at 8:38 am
DECLARE @SQLString nvarchar(500);
SET @SQLString =
'declare @String nvarchar(500) = ''SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM '' + @DbName + ''.HumanResources.Employee '' EXECUTE sp_executesql @String'
EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 10, 2018 at 8:41 am
Budd - Tuesday, July 10, 2018 8:14 AMWhat am I doing wrong in this statement or one like it?
DECLARE @SQLString nvarchar(500);
SET @SQLString =
N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM @DbName.HumanResources.Employee ';EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
That's just not going to work. The inherent syntax is going to be a problem. You can't normally use a variable that way, so just making it dynamic SQL isn't going to solve that problem. If you want to do the SQL that way, then just use the REPLACE function on your string to solve your parameter problem. It's always handy to be able to use sp_executesql, but not always practical. You would still need to validate your input to avoid SQL injection, so there's no value to using sp_executesql just to have a database name for a parameter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2018 at 8:54 am
Mike01 - Tuesday, July 10, 2018 8:38 AMDECLARE @SQLString nvarchar(500);
SET @SQLString =
'declare @String nvarchar(500) = ''SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM '' + @DbName + ''.HumanResources.Employee '' EXECUTE sp_executesql @String'EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
AWESOME !!!
Thanks So Much. I don't know how I would have ever come up with that.
July 10, 2018 at 9:04 am
Budd - Tuesday, July 10, 2018 8:54 AMMike01 - Tuesday, July 10, 2018 8:38 AMDECLARE @SQLString nvarchar(500);
SET @SQLString =
'declare @String nvarchar(500) = ''SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM '' + @DbName + ''.HumanResources.Employee '' EXECUTE sp_executesql @String'EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
AWESOME !!!
Thanks So Much. I don't know how I would have ever come up with that.
Actually, I would just do this:
DECLARE @SQLString nvarchar(max)
, @DbName nvarchar(100);
SET @DBName = 'AdventureWorks2012';
SET @SQLString =
N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM ' + QUOTENAME(@DbName) + '.[HumanResources].[Employee];';
EXECUTE sp_executesql @stmt = @SQLString;
July 10, 2018 at 9:54 am
Lynn Pettis - Tuesday, July 10, 2018 9:04 AMBudd - Tuesday, July 10, 2018 8:54 AMMike01 - Tuesday, July 10, 2018 8:38 AMDECLARE @SQLString nvarchar(500);
SET @SQLString =
'declare @String nvarchar(500) = ''SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM '' + @DbName + ''.HumanResources.Employee '' EXECUTE sp_executesql @String'EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
AWESOME !!!
Thanks So Much. I don't know how I would have ever come up with that.Actually, I would just do this:
DECLARE @SQLString nvarchar(max)
, @DbName nvarchar(100);
SET @DBName = 'AdventureWorks2012';SET @SQLString =
N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM ' + QUOTENAME(@DbName) + '.[HumanResources].[Employee];';EXECUTE sp_executesql @stmt = @SQLString;
Thanks Lynn, I appreciate all suggestions.
That is how I am more accustom to do it and probably would do something like that if I were building this as a stored Procedure.
But, in this case all I want is to be able to run the same adhoc query on an error logging table in 3 different databases (D,T,P), on 2 different servers using linked servers, in order to review the most recent errors, and compare then.
So once I've set the query I execute it 3 different times with 3 different parameters on 2 different servers.
EXEC [<ServerName>].msdb.dbo.sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='<databasenam>.'
July 10, 2018 at 10:02 am
Budd - Tuesday, July 10, 2018 9:54 AMLynn Pettis - Tuesday, July 10, 2018 9:04 AMBudd - Tuesday, July 10, 2018 8:54 AMMike01 - Tuesday, July 10, 2018 8:38 AMDECLARE @SQLString nvarchar(500);
SET @SQLString =
'declare @String nvarchar(500) = ''SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM '' + @DbName + ''.HumanResources.Employee '' EXECUTE sp_executesql @String'EXECUTE sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='AdventureWorks2012'
AWESOME !!!
Thanks So Much. I don't know how I would have ever come up with that.Actually, I would just do this:
DECLARE @SQLString nvarchar(max)
, @DbName nvarchar(100);
SET @DBName = 'AdventureWorks2012';SET @SQLString =
N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM ' + QUOTENAME(@DbName) + '.[HumanResources].[Employee];';EXECUTE sp_executesql @stmt = @SQLString;
Thanks Lynn, I appreciate all suggestions.
That is how I am more accustom to do it and probably would do something like that if I were building this as a stored Procedure.
But, in this case all I want is to be able to run the same adhoc query on an error logging table in 3 different databases (D,T,P), on 2 different servers using linked servers, in order to review the most recent errors, and compare then.
So once I've set the query I execute it 3 different times with 3 different parameters on 2 different servers.
EXEC [<ServerName>].msdb.dbo.sp_executesql @SQLString, N'@DbName nvarchar(100)', @DbName='<databasenam>.'
That is called not providing complete details of what you are doing.
July 10, 2018 at 11:33 am
Another option is to create a proc in master named sp_<whatever> (the name must start with sp_), mark it as a system proc, then you can execute from any db and it executes in the context of that db.
EXEC db1.dbo.sp_query_employees
EXEC db2.dbo.sp_query_employees
USE db3;
EXEC dbo.sp_query_employees
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply