SEO

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

"me" <me@work.com> wrote in message
news:10d14v7bo31q911@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

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" <sql@hayes.ch> wrote in message
news:40d09ad1_3@news.bluewin.ch...
>
> "me" <me@work.com> wrote in message
> news:10d14v7bo31q911@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" <me@work.com> wrote in message
news:10d1737o5hjfd50@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

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" <sql@hayes.ch> wrote in message
news:40d09fda$1_2@news.bluewin.ch...
>
> "me" <me@work.com> wrote in message
> news:10d1737o5hjfd50@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
>
>