SIMPLE UPDATE NO WORKING

[comtec]

By no means am I SQL efficient!
But this should be easy ( shouldn't it?)
I created this query to update a new database structure from my old one with the query wizard in enterprise manager and modified it accordingly.
We are trying to change to a new ecommerce solution if we can get the data over!
It runs without errors in query manager but nothing gets transferred.
both databases are on the same server and security has been disabled for now.



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 2:45:38 PM ******/

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 2:26:39 PM ******/
ALTER PROCEDURE insert_sfManufacturers_1
(@mfgID_1 [int],
@mfgName_2 [nvarchar](30),
@mfgLogo_3 [nvarchar](50))

AS INSERT INTO [storefront5SQL].[dbo].[sfManufacturers]
( [mfgID],
[mfgName],
[mfgLogo])


SELECT [mfgID], [mfgName], [mfgLogo]
FROM [dontneednew].[dbo].[EXPORTManufacturers]


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if I try to create the same thing in query analyser i get



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 2:45:38 PM ******/

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 2:26:39 PM ******/
ALTER PROCEDURE insert_sfManufacturers_1
(@mfgID_1 [nvarchar](50),
@mfgName_2 [nvarchar](30),
@mfgLogo_3 [nvarchar](50))

AS INSERT INTO [storefront5SQL].[dbo].[sfManufacturers]
( [mfgID],[mfgName],[mfgLogo])


SELECT ( [mfgID],[mfgName],[mfgLogo])
FROM [dontneednew].[dbo].[EXPORTManufacturers]


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Server: Msg 170, Level 15, State 1, Procedure insert_sfManufacturers_1, Line 14
Line 14: Incorrect syntax near ','.


So after 2 days, I give up
Please help

Paul D
dontneednew.com

 

[SQLBill]

Why are you creating variables when you aren't using them?

This is all you should really need:

ALTER PROCEDURE insert_sfManufacturers_1
AS

INSERT INTO [storefront5SQL].[dbo].[sfManufacturers]
([mfgID],[mfgName],[mfgLogo])

SELECT ([mfgID],[mfgName],[mfgLogo])
FROM [dontneednew].[dbo].[EXPORTManufacturers]
GO

EXEC insert_sfManufacturers_1
GO

BTW-This is the NEW database and table, correct:
[storefront5SQL].[dbo].[sfManufacturers]

-SQLBill

 

[comtec]

-SQLBill

I am hoping to be a "wizard"
You know nothing up this sleeve.
tried waht you gave me and same thing

Server: Msg 170, Level 15, State 1, Procedure insert_sfManufacturers_1, Line 8
Line 8: Incorrect syntax near ','.

-SQLBill


Paul D
dontneednew.com

 

[comtec]

BTW I wad all kinds of things going on tryingto make this work.
this why there are unsued variables in the original calls.
I had originally tried coding it my self but I really am not a coder

Paul D
dontneednew.com

 

[MDXer]

The parens in your select statement are the offenders

SELECT ([mfgID],[mfgName],[mfgLogo])

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[comtec]

If these are the problem, why when you use the wizardfs does it continue to place them there?

Paul D
dontneednew.com

 

[comtec]

here is where we are now
no errors but no update either

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 3:41:11 PM ******/

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 2:45:38 PM ******/

/****** Object: Stored Procedure dbo.insert_sfManufacturers_1 Script Date: 8/4/2003 2:26:39 PM ******/
ALTER PROCEDURE insert_sfManufacturers_1

AS INSERT INTO storefront5SQL.dbo.sfManufacturers ( mfgID , mfgName , mfgLogo)

SELECT [mfgID], [mfgName], [mfgLogo]
FROM [wsrv2].[dontneednew].[dbo].[exportManufacturers]



GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Paul D
dontneednew.com

 

[MDXer]

not sure. I know know I ran a basic query selecting 3 columns from a table it worked fine I added parens as in your query and it bombed with the same message. I tend not to use the wizards for much of anything so I'm not 100% on how they do things.

with parens:
Select
(Years, AgeDescription,AgerangeDescription)
From dbo.dimAge

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.

without
Select
Years, AgeDescription,AgerangeDescription
From dbo.dimAge

Years AgeDescription AgerangeDescription
----------- ------------------------- ---------------------
0 0 - Years 0 - 10 Years
1 1 - Years 0 - 10 Years
2 2 - Years 0 - 10 Years
etc etc

(101 row(s) affected)



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[MDXer]

does the select statement ran alone return rows?

SELECT [mfgID], [mfgName], [mfgLogo]
FROM [wsrv2].[dontneednew].[dbo].[exportManufacturers]

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[JayKusch]

Just a quick one on my part ... Do you have data ...

in the table "exportManufacturers"
in the DB "dontneednew"
on the server "wsrv2"

Or is the server/database assignment wrong?

Thanks

J. Kusch

 

[comtec]

Everything is as you would expect

SELECT [mfgID], [mfgName], [mfgLogo]
FROM [wsrv2].[dontneednew].[dbo].[exportManufacturers]

does work on its own but not in the sp

I did get it to work
I just replaced

SELECT [mfgID], [mfgName], [mfgLogo]
FROM [wsrv2].[dontneednew].[dbo].[exportManufacturers]

with

SELECT *
FROM [wsrv2].[dontneednew].[dbo].[exportManufacturers]

and away it went
not bad for 2 days work
anyone have any idea why?

Paul D
dontneednew.com

 

[SQLBill]

When you ran this:

SELECT *
FROM [wsrv2].[dontneednew].[dbo].[exportManufacturers]

What were the column headers in your results?

I have a feeling there is something wrong with these names:
[mfgID], [mfgName], [mfgLogo]

What results do you get when you run this:

USE dontneednew
GO
EXEC SP_HELP 'exportManufacturers'
GO

-SQLBill