Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BryanBurroughs
Starting Member
8 Posts |
 Posted - 2010-04-07 : 14:31:42
|
| When I do the following, sp_executesql never replaces @dbname. What am I doing wrong here?SET @params = N'@dbname NVARCHAR(max)'SET @sql = N'CREATE SYNONYM MainServer_FacilityParameters FOR MainServer.[@dbname].dbo.FacilityParameters'EXEC sp_executesql @sql, @params, @d |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-07 : 15:00:41
|
| That object (i.e. any of server / database / schema / table) can not be executed with a parameterSET @sql = N'CREATE SYNONYM MainServer_FacilityParameters FOR MainServer.[' + @dbname + '].dbo.FacilityParameters'You ought not to be treating the dbname as an NVARCAHR(MAX) - best not to use NVARCAHR(MAX) for things that have finite length. |
 |
|
|
BryanBurroughs
Starting Member
8 Posts |
Posted - 2010-04-07 : 15:14:10
|
| so then I guess I should just create that string using quotename(), instead? or is quotename() even necessary? I'm trying to avoid SQL injection |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 03:20:19
|
"I'm trying to avoid SQL injection"Ah, OK. Yup, QUOTENAME() would prevent injection. For SQL injection safety you need to get it to provide the outer square-brackets too.SET @sql = N'CREATE SYNONYM MainServer_FacilityParameters FOR MainServer.' + QUOTENAME(@dbname, '[') + '.dbo.FacilityParameters' |
|
|
|
BryanBurroughs
Starting Member
8 Posts |
Posted - 2010-04-08 : 13:37:06
|
| Alright then. Thanks! |
|
|
|
|
|
|
sp_executesql problems