SP Error

[me]

I'm trying to pass a filename and path into an sp and I'm getting an error.
I must be missing something easy. (Both UNC path and Drive letter path give
an error)
anyone know what might be the prob? (chars like : or \\ ????)

TIA

CBL
error is:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'J:'.

call to the sp is:
declare @ImportFile varchar(255)
--set @ImportFile='\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
set @ImportFile='J:\JOEL\ITEMTEST.txt'

EXEC shipping.dbo.pts_ImportItems @ImportFile

[Simon Hayes]

"me" <[e-mail]> wrote in message
news:10*************@corp.supernews.com...

I'm trying to pass a filename and path into an sp and I'm getting an error. I must be missing something easy. (Both UNC path and Drive letter path give an error)
anyone know what might be the prob? (chars like : or \\ ????)

TIA

CBL
error is:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'J:'.

call to the sp is:
declare @ImportFile varchar(255)
--set @ImportFile='\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
set @ImportFile='J:\JOEL\ITEMTEST.txt'

EXEC shipping.dbo.pts_ImportItems @ImportFile

You'll have to show the procedure code where the variable @ImportFile is
used - as a complete guess, you're building a string dynamically, and the
execution of that string is giving the error. If this is the case, you may
want to SELECT the string just before you execute it, to make sure it's
doing what you think it is - it's convenient to add a @Debug parameter to
the procedure to do this, so you can easily troubleshoot in future. But I
might be completely wrong, since you haven't posted any detailed code.

Simon

[me]

Here is probably where the problem is per your suggestion the actual sp code
follows:
exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '+ @ImportFile +' WITH
(FORMATFILE ='+ @FormatFile +')')

here is the sp code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE pts_ImportItems(@ImportFile varchar(255))
AS
SET NOCOUNT ON

DECLARE @ErrorSave int
declare @Barcode int
declare @FormatFile varchar(255)

--set @ImportFile='\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
set @FormatFile='\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'

BEGIN TRANSACTION

if exists (select * from dbo.sysobjects where id = object_id(N'[ItemTest]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ItemTest]

/*change to a temp table when working and delete the drop table*/
CREATE TABLE [ItemTest] (
[BarCode] [int] IDENTITY(1,1) NOT NULL ,
[FileNumber] [nvarchar] (20) NULL CONSTRAINT [DF_ItemTest_FileNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_ItemNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Description]
DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNumber] DEFAULT
(''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT ('0'),
[ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_ImportDate] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]

exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '+ @ImportFile +' WITH
(FORMATFILE ='+ @FormatFile +')')

set @Barcode=dbo.pts_GetNextBarcode2()

insert Item2 ([BarCode Part#],[File Number],[Item
Number],[Description],[Room Number],[Quantity])
select
[BarCode]+@Barcode,
[FileNumber],
[ItemNumber],
[Description],
[RoomNumber],
[Quantity]
from
dbo.ItemTest

SELECT @ErrorSave = @@ERROR

IF @ErrorSave <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

RETURN @ErrorSave
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40********@news.bluewin.ch...

"me" <[e-mail]> wrote in message
news:10*************@corp.supernews.com...

I'm trying to pass a filename and path into an sp and I'm getting an

error.

I must be missing something easy. (Both UNC path and Drive letter path

give

an error)
anyone know what might be the prob? (chars like : or \\ ????)

TIA

CBL
error is:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'J:'.

call to the sp is:
declare @ImportFile varchar(255)
--set @ImportFile='\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
set @ImportFile='J:\JOEL\ITEMTEST.txt'

EXEC shipping.dbo.pts_ImportItems @ImportFile

You'll have to show the procedure code where the variable @ImportFile is
used - as a complete guess, you're building a string dynamically, and the
execution of that string is giving the error. If this is the case, you may
want to SELECT the string just before you execute it, to make sure it's
doing what you think it is - it's convenient to add a @Debug parameter to
the procedure to do this, so you can easily troubleshoot in future. But I
might be completely wrong, since you haven't posted any detailed code.

Simon

[Simon Hayes]

"me" <[e-mail]> wrote in message
news:10*************@corp.supernews.com...

Here is probably where the problem is per your suggestion the actual sp code follows:
exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '+ @ImportFile +' WITH
(FORMATFILE ='+ @FormatFile +')')

here is the sp code:

<snip>

If you check the examples in Books Online, you'll see that the filename has
single quotes around it:

.... FROM 'f:\orders\lineitem.tbl' ...

But your variable substitution will not have any quotes:

.... FROM \\ACCT_NT\SHARED\CBL\ITEMTEST.txt ...

So you need to add them:

exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '''+ @ImportFile +''' WITH
(FORMATFILE ='''+ @FormatFile +''')')

(That's 3 single quotes in each case, in case your font isn't quite clear).
An easier way to troubleshoot this is:

declare @sql nvarchar(1000)
set @sql = 'BULK INSERT SHIPPING.DBO.ItemTest FROM '''+ @ImportFile +'''
WITH (FORMATFILE ='''+ @FormatFile +''')'
if @debug = 1 print @sql
exec(@sql)

Now you can add a parameter to your procedure called @debug:

@debug bit default 0

If you get strange syntax errors, you can execute the procedure like this,
and you will see the SQL string, which will hopefully make the problem more
obvious:

EXEC shipping.dbo.pts_ImportItems @ImportFile = 'c:\temp\in.txt', @debug = 1

Simon

[me]

Thanks! I should have looked at the quotes closer...the @debug var is a
good idea too I've had a lot of trouble trying to debug in the past.

Carter

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40**********@news.bluewin.ch...

"me" <[e-mail]> wrote in message
news:10*************@corp.supernews.com...

Here is probably where the problem is per your suggestion the actual sp code

follows:
exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '+ @ImportFile +' WITH
(FORMATFILE ='+ @FormatFile +')')

here is the sp code:

<snip>

If you check the examples in Books Online, you'll see that the filename

has single quotes around it:

... FROM 'f:\orders\lineitem.tbl' ...

But your variable substitution will not have any quotes:

... FROM \\ACCT_NT\SHARED\CBL\ITEMTEST.txt ...

So you need to add them:

exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '''+ @ImportFile +''' WITH
(FORMATFILE ='''+ @FormatFile +''')')

(That's 3 single quotes in each case, in case your font isn't quite clear). An easier way to troubleshoot this is:

declare @sql nvarchar(1000)
set @sql = 'BULK INSERT SHIPPING.DBO.ItemTest FROM '''+ @ImportFile +'''
WITH (FORMATFILE ='''+ @FormatFile +''')'
if @debug = 1 print @sql
exec(@sql)

Now you can add a parameter to your procedure called @debug:

@debug bit default 0

If you get strange syntax errors, you can execute the procedure like this,
and you will see the SQL string, which will hopefully make the problem more obvious:

EXEC shipping.dbo.pts_ImportItems @ImportFile = 'c:\temp\in.txt', @debug = 1
Simon