Syntax error? - 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?

Author

Topic

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 13:33:45

Hello everyone. I'm just trying to fix a syntax error. My code is as follows and I'm receiving an error for the first part of the code:

SELECT @Body = SELECT DISTINCT a.inp_SHORT_ITEM_NUMBER, d.Family_Name, e.Category_ID, f.Category_Name INTO NotYetAvailable FROM
OPGINORDERPLAY.InOrder_play.dbo.tblinventoryprimary a JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Specs b
ON a.inp_SHORT_ITEM_NUMBER = b.String_Value
JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Family c ON b.product_id = c.product_id
JOIN OPG7SQL.Catalogtest.dbo.Tb_Family d ON c.Family_ID = d.Family_ID
JOIN OPG7SQL.Catalogtest.dbo.Tb_Catalog_Family e ON d.Family_ID = e.Family_ID
JOIN OPG7SQL.Catalogtest.dbo.Tb_Category f ON e.Category_ID = f.Category_ID
WHERE a.ist_inventory_status_code = 'N' AND b.attribute_ID = '1'

Will Leonard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:37:03

the query doesnt make any sense. you cant store a resultset onto a variable. you should be declaring a table variable instead and using like below


INSERT INTO @Body 
SELECT DISTINCT a.inp_SHORT_ITEM_NUMBER, d.Family_Name, e.Category_ID, f.Category_Name INTO NotYetAvailable FROM
OPGINORDERPLAY.InOrder_play.dbo.tblinventoryprimary a JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Specs b
ON a.inp_SHORT_ITEM_NUMBER = b.String_Value 
JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Family c ON b.product_id = c.product_id
JOIN OPG7SQL.Catalogtest.dbo.Tb_Family d ON c.Family_ID = d.Family_ID
JOIN OPG7SQL.Catalogtest.dbo.Tb_Catalog_Family e ON d.Family_ID = e.Family_ID
JOIN OPG7SQL.Catalogtest.dbo.Tb_Category f ON e.Category_ID = f.Category_ID
WHERE a.ist_inventory_status_code = 'N' AND b.attribute_ID = '1'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 13:41:39

ummm what is this?

SELECT @Body = SELECT DISTINCT a.inp_SHORT_ITEM_NUMBER, d.Family_Name, e.Category_ID, f.Category_Name
INTO NotYetAvailable

If you want to Insert into a table


INSERT INTO NotYetAvailable (<Col List>)
     SELECT DISTINCT a.inp_SHORT_ITEM_NUMBER, d.Family_Name, e.Category_ID, f.Category_Name 
       FROM OPGINORDERPLAY.InOrder_play.dbo.tblinventoryprimary a 
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Specs b
	 ON a.inp_SHORT_ITEM_NUMBER = b.String_Value 
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Family c 
	 ON b.product_id = c.product_id
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Family d 
	 ON c.Family_ID = d.Family_ID
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Catalog_Family e 
	 ON d.Family_ID = e.Family_ID
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Category f 
	 ON e.Category_ID = f.Category_ID
      WHERE a.ist_inventory_status_code = 'N' 
	AND b.attribute_ID = '1'

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 13:45:58

I'm doing a stored procedure. The procedure's method is:

CREATE TABLE using developed JOIN statement.

Then after the table is created, the results are sent out in an email using EXEC msdb....sp_send_dbmail into a csv file.

Afterwards, the table is dropped.

Will Leonard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:47:53

then whats the purpose of variable if you're already creating table with SELECT...INTO?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 13:52:58

I guess the real question is:

How do I create a table, take the results from "SELECT * FROM table," bcp export out to csv file, and then drop the table all in the same stored procedure? That will help me.

Will Leonard

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 13:53:36

is @body suppose to be a table variable?

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 13:56:10

Yes. Exactly. I want to SELECT....INTO (create the table) and then set @Body = the results of that table.

Will Leonard

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 13:57:48

Do you care if it's a table veriable or a temp table?


     SELECT DISTINCT a.inp_SHORT_ITEM_NUMBER, d.Family_Name, e.Category_ID, f.Category_Name
INTO #Body
       FROM OPGINORDERPLAY.InOrder_play.dbo.tblinventoryprimary a 
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Specs b
	 ON a.inp_SHORT_ITEM_NUMBER = b.String_Value 
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Prod_Family c 
	 ON b.product_id = c.product_id
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Family d 
	 ON c.Family_ID = d.Family_ID
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Catalog_Family e 
	 ON d.Family_ID = e.Family_ID
 INNER JOIN OPG7SQL.Catalogtest.dbo.Tb_Category f 
	 ON e.Category_ID = f.Category_ID
      WHERE a.ist_inventory_status_code = 'N' 
	AND b.attribute_ID = '1'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 14:00:18

quote:
Originally posted by wleonard

Yes. Exactly. I want to SELECT....INTO (create the table) and then set @Body = the results of that table.

Will Leonard

and you use @body for the mail? so how do you expect the contents of table to be displayed in mail?
as single long text?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 14:03:02

We haven't gotten to "temp tables" yet in the class (I'm a comp. sci. student). Maybe he won't mind but I'd like to play it safe and just follow the directions on the assignment.

Will Leonard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 14:05:28

quote:
Originally posted by wleonard

We haven't gotten to "temp tables" yet in the class (I'm a comp. sci. student). Maybe he won't mind but I'd like to play it safe and just follow the directions on the assignment.

Will Leonard

then use a table variable

declare @body table
(
....
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 14:21:58

Okay my problem was solved. Thank you for the help. One last question though....does anyone know the command for attachments in emails?

exec msdb..sp_send_dbmail @profile_name='opg', @recipients='wleonard@opgi.com',
@subject=@Subject, @body=@Body, @body_format ='HTML'

Need to include the CSV file attachment.

Will Leonard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 14:34:28

there's a parameter @attachments you can specify attachments in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wleonard
Starting Member

30 Posts

Posted - 2012-02-07 : 15:10:07

quote:
Originally posted by visakh16

there's a parameter @attachments you can specify attachments in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It's actually @file_attachments, but thanks anyway :).

Will Leonard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 15:16:05

quote:
Originally posted by wleonard

quote:
Originally posted by visakh16

there's a parameter @attachments you can specify attachments in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It's actually @file_attachments, but thanks anyway :).

Will Leonard

you can refer to MSDN documentation to get the exact name

I'm not near SQL box so just knew its something related to attachments

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources