| Author |
 Topic  |
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 08/21/2006 : 14:35:34
|
-- drop proc usp_a
create proc usp_a @tableName varchar(30) as
begin
declare @sql varchar(20)
set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo."@tableName"'
exec @sql
end
exec usp_a 00519
after running SP, QAnalyzer shows the error.
" Could not find stored procedure 'insert into address '. "
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 08/21/2006 : 14:43:03
|
Does this variable appear to have the correct length?
declare @sql varchar(20)
CODO ERGO SUM |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/21/2006 : 14:43:15
|
1) declare @sql varchar(200)
2) set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo.' + @tableName
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/21/2006 14:44:44 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 14:43:52
|
Run PRINT @SQL after the SET statement. Verify that the string is in the correct format.
Tara Kizer |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/21/2006 : 14:47:15
|
-- drop proc usp_a
create proc usp_a @tableName varchar(300) as
begin
declare @sql varchar(200)
set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo."@tableName"'
exec @sql
PRINT @SQL
end
exec usp_a 00519
Server: Msg 911, Level 16, State 1, Line 8
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 14:49:05
|
quote:
Originally posted by gongxia649
-- drop proc usp_a
create proc usp_a @tableName varchar(300) as
begin
declare @sql varchar(200)
set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo."@tableName"'
exec @sql
PRINT @SQL
end
exec usp_a 00519
Server: Msg 911, Level 16, State 1, Line 8
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.
So what does the PRINT statement show you?
You need to use the + sign to concatenate your variable to the rest of the string. See Peso's post.
Tara Kizer |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/21/2006 : 14:49:05
|
You must concatenate the string properly. Add @TableName last with + operator, as described in my previous posting.
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/21/2006 : 14:49:44
|
Where's the "Shoot the Yak" icon? 
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/21/2006 14:49:56 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/21/2006 : 14:55:28
|
-- drop proc usp_a
create proc usp_a @tableName varchar(300) as
begin
declare @sql varchar(200)
--declare @tableName varchar(300)
--set @tableName = '00519'
set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo.' + @tableName
PRINT @SQL
exec @sql
end
exec usp_a "00519"
insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo.00519
Server: Msg 911, Level 16, State 1, Line 10
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 08/21/2006 : 14:59:46
|
quote:
Originally posted by Peso
Where's the "Shoot the Yak" icon?
Peter Larsson
Helsingborg, Sweden
He appears unwilling to put any effort at all into solving even simple problems like this.
CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/21/2006 : 15:00:38
|
drop the parathesis around select id_voter!
set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo.' + @tableName
Peter Larsson
Helsingborg, Sweden |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/21/2006 : 15:04:54
|
create proc usp_a @tableName varchar(300) as
declare @sql varchar(200)
--declare @tableName varchar(300)
--set @tableName = '00519'
set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo.' + @tableName
exec @sql
--PRINT @SQL
exec usp_a "00519"
Server: Msg 911, Level 16, State 1, Line 10
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 15:07:31
|
gongxia, please post what the PRINT statement is showing. It will show exactly what the problem is.
Tara Kizer |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/21/2006 : 15:08:11
|
What does the PRINT @SQL tell you?
Post only the result of PRINT here...
Peter Larsson
Helsingborg, Sweden |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 15:25:31
|
So did you notice that there are double quotes around your table name? Do you think that's the problem?
EDIT: He erased his reply.
Tara Kizer |
Edited by - tkizer on 08/21/2006 15:27:10 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/21/2006 : 15:28:17
|
-- drop proc usp_a
create proc usp_a @tableName varchar(300) as
declare @sql varchar(200)
--declare @tableName varchar(300)
--set @tableName = "00519"
set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo.' + '"'+ @tableName +'"'
--set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo."00519"'
PRINT @SQL
exec @sql
exec usp_a "00519"
the print shows:
insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo."00519" which is right. run it and it inserts records.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 15:32:04
|
You need exec (@sql).
Tara Kizer |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/21/2006 : 15:33:20
|
oh thanks it worked... why do i need brakets?
gongxia649 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 15:35:18
|
You need parenthesis because otherwise it thinks you are trying to run a stored procedure. See EXEC in BOL.
Tara Kizer |
|
|
|
msantosa
Starting Member
16 Posts |
Posted - 08/21/2006 : 15:37:40
|
ALTER proc usp_a
@tableName varchar(30)
AS
BEGIN
Declare @Sql varchar(200)
set @Sql = 'insert into address (id_voter) SELECT(id_voter) from
SS_Import_voter_Registration.dbo.00'+@tablename
print @sql
exec @sql
END
exec usp_a '00519' -->give single quote on the parameter
Make sure you're on the same database.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/21/2006 : 15:38:59
|
quote:
Originally posted by msantosa
ALTER proc usp_a
@tableName varchar(30)
AS
BEGIN
Declare @Sql varchar(200)
set @Sql = 'insert into address (id_voter) SELECT(id_voter) from
SS_Import_voter_Registration.dbo.00'+@tablename
print @sql
exec @sql
END
exec usp_a '00519' -->give single quote on the parameter
Make sure you're on the same database.
He still needs exec (@sql).
Tara Kizer |
|
|
|
Topic |
|
insert into in SP won't run
