syntax error, Whats Wrong?? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

syntax error, Whats Wrong??

Author

Topic

Shrews14
Starting Member

37 Posts

Posted - 2007-03-21 : 09:00:13

Hi heres my script, why does it not work?

CREATE PROC [dbo].[sp_append_CemexSQLDB_to_EQuIS5_V2]
@DBIN nvarchar(50),
@DBOUT nvarchar(50)

AS

DECLARE @SQLStr Varchar(1000)

/* Start Transaction, incase of error */
BEGIN TRAN

/* Appending Ref tables */
SET @SQLStr = 'INSERT INTO ' + QUOTENAME(@DBOUT) '.dbo.rt_action_level_type'
SET @SQLStr = 'SELECT *'
SET @SQLStr = 'FROM ' + QUOTENAME(@DBIN) '.dbo.rt_action_level_type;' + ' OPTION (KEEP PLAN)'
EXEC (@SQLStr)
IF @@ERROR <> 0 GOTO ERRORHANDLER
COMMIT TRAN

RETURN 0

ERRORHANDLER:
PRINT "Unexpected Error Ocurred!"
ROLLBACK TRAN

RETURN 1
GO

The error is 'Server: Msg 170, Level 15, State 1, Procedure sp_append_SQLtables_to_NewDB_V2, Line 13
Line 13: Incorrect syntax near '.dbo.rt_action_level_type'.'

can anybody help

Kristen
Test

22859 Posts

Posted - 2007-03-21 : 09:02:28

SET @SQLStr = 'INSERT INTO ' + QUOTENAME(@DBOUT) + '.dbo.rt_action_level_type'

and on the other one too.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-21 : 09:10:05

Now I need to take another shower......

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Shrews14
Starting Member

37 Posts

Posted - 2007-03-21 : 09:54:46

Many thanks Kristen, i couldn't see the wood for the trees.

I thought my script was floored, just a missing '+'

I pieced this together from some internet sites and i understand it all but the RETURN 0/1 what does RETURN mean and what does it do.

Thanks
Shrew

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-21 : 10:04:27

quote:
Originally posted by Shrews14

Many thanks Kristen, i couldn't see the wood for the trees.

I thought my script was floored, just a missing '+'

I pieced this together from some internet sites and i understand it all but the RETURN 0/1 what does RETURN mean and what does it do.

Thanks
Shrew

Return tells the calling code the status of the sproc....and you should never set it....and what's with all the dynamic sql?

quote:

Yes, yes. To Obi-Wan you listen.
The cave. Remember your failure
at the cave!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Shrews14
Starting Member

37 Posts

Posted - 2007-03-21 : 10:24:55

i'm new to SQL server and this is the only way i know copy data from one table to another, i understand that the DTS is for this purpose, but in my case i'm copying a number of tables into a highly structured DB and they need to be copied in a certain order because of references and table relationships.

hope this makes sense! I'm looking to become more skilled in the ways of the SQL server. but i'm at the start of a large learning curve>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources