Bcp utility with stored procedure

Guys,

I have stored proc sp_generate_insert which will generate insert scripts for the tables. When I run the stored Proc
from the management studio it runs fine. But when I run through stored proc as part of BCP utility I get this error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Execute dev.dbo.sp_generate_inserts 'auth' runs fine from management studio and generates inserts for auth table.

When I run the same proc as part of the following stored proc with bcp utility I get the error.

alter PROCEDURE INSERTTEST2 ( @.FILEPATH NVARCHAR(50))
AS
DECLARE @.cmd varchar(2000)
BEGIN
set @.cmd = 'bcp.exe "EXEC dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@.filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3' + ' -q ' + ' -c -T -e' + @.filePath+'.log -o '
+ @.filePath+ '_out.log'
select @.cmd -- + '...'
EXEC master.dbo.xp_cmdShell @.cmd
END

Any suggestions or inputs would help.

Thanks

The problem lies within the proc, so we need to see that code.

Though usually, this error comes from statements where the length parameter in SUBSTRING becomes negative.

If you're dynamically trying to set how large chunk substring should take, and that variable becomes negative, then this error happens.

Since the problem seems to occur or not depending on method of connecting, it may suggest that there are different settings that may be the root cause.. (ie ANSI DEFAULTS etc)

Could this be it perhaps?

/Kenneth

|||kenneth,

Thank you for you reply.

I dont know if the problem is setting defaults on the database or the connection, more so since the stored proc - sp_generate_scripts runs fine from the managment studio.

Anyways the code for stored proc is available at the following link

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Any suggestions/inputs would help

Thanks

|||

I played around a bit with the proc and found some 'interesting' stuff...

I think your problem may be that you don't use the -d parameter in your bcp command, so you're not ending up in the right db.

The reason this matters may be the same that I found, but didn't notice at first...

(I tried it on SQL Server 2000).
First when compiled, there was a msg about not finding sys.sp_MS_marksystemobject, but the proc compiled anyway, so I tried it out.

Got the same message as you a couple of times, but found that only if I was in a db other than master. Made a usertable in master, then it worked. =/

So, fixed the 'sys.sp_MS_marksystemobject' to 'sp_MS_marksystemobject' and recompiled (since the former doesn't exist in 2000, only in 2005) and tried again. Now all is smooth, and it works like it's supposed to.

Apparently, the proc needs to be marked as a systemobject, else you may get these 'db-scope' issues, so check out if this is the problem.

/Kenneth

BCP unility and SQL Server Management Studio Express

I ahve a utility which uses the bulk copy untility (or program) bcp.exe
which doesn't seem to be part of SQL Server Management Studio Express. I
don't need a database server on that machine but I do want the bcp.exe
program. How can I get this?
Many thanks for any help in advance!
Peter (peteATkapiti.co.nz) writes:
> I ahve a utility which uses the bulk copy untility (or program) bcp.exe
> which doesn't seem to be part of SQL Server Management Studio Express. I
> don't need a database server on that machine but I do want the bcp.exe
> program. How can I get this?
BCP comes with SQL Express. I don't remember off-hand if SQL Express
offers a client-only install, but you could try running the install and
see if you get that offer. Just make sure press all Advanced buttons
you see.
I guess you also could cull it from a an existing installation. You
would have to install SQL Native Client (which is available as a separate
install), but if you are lucky that maybe all. (Or have I seen a
BCP.rll somewhere?)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I had to install the SQL Express Toolkit to get bcp.exe.
But I now have a really strange situation I wonder if anyone can answer...
Just installed on Vista Ultimate.
At first I couldn't find BCP in what I thought should be an expected
directory (A bin directory in the SQL Express folder tree).
Instead, after doing a Vista "Search" on the entire SQL Server tree, it's
supposed to be in
...\Program Files\Microsoft SQL Server\90\Tools
But, when I browsed to that location, there are no files, just folders.
Re-launched Explorer to run "As Administrator" (which itself is an irritant)
in case files were hidden, but not so.
Started manually opening up folders and I found it in the same folder as
bcp.rll
...\Program Files\Microsoft SQL Server\90\Tools\Binn
Why the weirdness? This is the first time I've run a Microsoft OS Search for
over 15 years on anything which returned bad results like this.
Tony Su
www.su-networking.com
ISA
SBS
Enterprise Mobile Solutions Architect
"Erland Sommarskog" wrote:

> Peter (peteATkapiti.co.nz) writes:
> BCP comes with SQL Express. I don't remember off-hand if SQL Express
> offers a client-only install, but you could try running the install and
> see if you get that offer. Just make sure press all Advanced buttons
> you see.
> I guess you also could cull it from a an existing installation. You
> would have to install SQL Native Client (which is available as a separate
> install), but if you are lucky that maybe all. (Or have I seen a
> BCP.rll somewhere?)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Tony Su (TonySu@.discussions.microsoft.com) writes:
> I had to install the SQL Express Toolkit to get bcp.exe.
> But I now have a really strange situation I wonder if anyone can answer...
> Just installed on Vista Ultimate.
> At first I couldn't find BCP in what I thought should be an expected
> directory (A bin directory in the SQL Express folder tree).
> Instead, after doing a Vista "Search" on the entire SQL Server tree, it's
> supposed to be in
> ..\Program Files\Microsoft SQL Server\90\Tools
> But, when I browsed to that location, there are no files, just folders.
> Re-launched Explorer to run "As Administrator" (which itself is an
> irritant) in case files were hidden, but not so.
> Started manually opening up folders and I found it in the same folder as
> bcp.rll
> ..\Program Files\Microsoft SQL Server\90\Tools\Binn
> Why the weirdness? This is the first time I've run a Microsoft OS Search
> for over 15 years on anything which returned bad results like this.
It appears that your question is more about the search in Vista, and
I'm afraid I can't help with that. The search in Vista is very mysterious,
and nothing use unless I'm in desperation.
Tools\Binn is indeed the place..
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

BCP in SQL SERVER 2005

Hello folks!
I have installed SQL Server 2005 Express and SQL Server Management Studio Express. Everything looks ok and works fine, but I can't find the Data Import/Export Wizard. Can anyone tell me how to Import/Export data?

Thank you!!!

SQL Server 2005 Express Manager does not offer Import/Export functionality as it is dependent on SQL Server Integration Services (SSIS) and SQL Server Agent. You will need to optain the full version of SQL Server Management Studio.

Peter

|||You can you other means to move data in and out of SQL Express that don't rely on SSIS. BCP is one such tool, you can find more information about BCP at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

If you are working with data in Access, you can use the functionality of Access to directly export the data into SQL Server. Most other programs can generate data in a format that BCP can accept.

Mike|||

I have to say, I have been developing for many years. This time I am truely disappointed in MS. I avoid say this kind of thing, and come to MS's defense almost always. Never bit into the hand that feeds you (karma-ish).

How am I supposed to learn this great technology between jobs, or when I work for a cheap start up company.

We (your users) can not easily import data into this new version of SQL Server. If you are going to put it out there as education; at least make it useful.

I just don't get it. I appologize for sounding disenchanted; but I have become accustom to being able to depend on MS products atleast being usable. For completeness sake maybe I am missing something. And do not say bcp, command lines went out years ago, that iss what Microsoft is founded on.

Enlighten me please; I fear I have just taken the first step to becoming one of them, the na sayers. Prove me wrong. After all that expression of opinion, I hope you find it heartfelt and consructive.

I look forward to your response. Please tell me there is a quick windows orienent approach, not some prehistoric utility.

MKruz

|||

Michael,

Agree with you - what a big disappointment. I'm sure that there are many of us who have data that we would like to bring into a SQL Server Express table and work with - it's natural form being a text or Excel base that comes from a bank or some other source. All the functionality in the world is of little use if the basic data import is not there.

Bill Sullivan

|||I totally agree, SQL 2005 Express is useless if you can't import/export data from a GUI. I'll move back to SQL2000 and DTS until it is fixed.|||

I really want to find out what the missing feature is, there was no GUI for importing excel files in MSDE and there is not one in Express, which is the equivalent SKU.

You can use the eval version for free to eval features, or if it is just for development then the dev edition is < 50 USD.

|||

Yeah, after all the hoopla surrounding the Visual Studio release man I'm bummed out about this. Guess there always has to be a catch. I was excited, I had a program idea mulling in my head for a few years and with Visual Studio I saw a chance to bring it to life.

But for my idea to come to life I need access to Access databases! (Isn't that why it's called "Access"?..) Oh well, as this n00by knows, if there is a will there is a way! Hope I don't bump into my much of this 'fine print' on my travels...

Luke

|||I disagree that command lines went out years ago. They are still the most expedient way to accomplish a whole host of tasks, and I use them daily.

That said, my 30-second review of the bcp link does suggest that importing data into a table for SQL Express may be more complicated than just getting it into CSV format. I'll reserve judgment until I've actually given it a try.
|||There is a company that makes a whole bunch of db converters. I just used one of them to convert from Access to MS SQL Express:
http://www.convert-in.com/acc2mss.htm

They have a demo that will convert the first five records from each of your tables if you'd like to try it out before you buy.
|||

If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

Before you start exporting make sure you have a data base in SQL where you want to export to.

Open your data base in access

Right click on the table you want to export

Select export

A dialog box opens...

at the bottom of this dialog box there is a "Save as Type" drop down box

select it and scroll to the bottom you will find "ODBC Databases () "

select it

Give your table a new name, or leave as is, if it that suits you.... Click OK

A Select Data Source dialog box opens..... Click the "New" button

A create new data source dialog opens

Scroll to the bottom of that box and select SQL Server

click NEXT

give your data source a name...... what ever you like.

click NEXT.... click FINISH

a 'Create New Data Source to SQL Sever' dialog box opens

Give your connection a description

select or type the name of your server in the Server drop down box

Click NEXT...... Click NEXT......

check the 'change the default data base to' check box

select the data base where you want the export your table to.

Click NEXT.... click Finish.

That's all. Now you can select each table from you access data base then select

Export.......

ODBC databases ()

now you can select the data source you created then click OK

return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

Hope this is helpful

|||

Hello Everyone,

Just after reading this form, I tried out some import/ export operation like below

SQL 2000 - SQL 2005 Express (Import/Export)

Excel - SQL 2005 Express (Import/Export)

It worked out very fine. I just used the DTS in SQL 2000 to do this. Any comments on this.

Thanks

Swami

|||Hello,
Try:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
Worked for me!
|||

Wolfey305 wrote:

If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

Before you start exporting make sure you have a data base in SQL where you want to export to.

Open your data base in access

Right click on the table you want to export

Select export

A dialog box opens...

at the bottom of this dialog box there is a "Save as Type" drop down box

select it and scroll to the bottom you will find "ODBC Databases () "

select it

Give your table a new name, or leave as is, if it that suits you.... Click OK

A Select Data Source dialog box opens..... Click the "New" button

A create new data source dialog opens

Scroll to the bottom of that box and select SQL Server

click NEXT

give your data source a name...... what ever you like.

click NEXT.... click FINISH

a 'Create New Data Source to SQL Sever' dialog box opens

Give your connection a description

select or type the name of your server in the Server drop down box

Click NEXT...... Click NEXT......

check the 'change the default data base to' check box

select the data base where you want the export your table to.

Click NEXT.... click Finish.

That's all. Now you can select each table from you access data base then select

Export.......

ODBC databases ()

now you can select the data source you created then click OK

return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

Hope this is helpful

If COnnection Failed..what I want to do?

|||Hi swami,

Could you pls give me more info as to how to run DTS.

I have installed Sql server 2005 Ex Ed.
Is it possible to install only the DTS from SQL 2000.

Tnx

Basic: Starting SQL Server

Hello,

I am having trouble just starting SQL Server 2005. When I try to use Management Studio, I do not see any server name. I just need to be able to start it up!

Thanks,

pagates

Hi pagates,

One of the easiest ways to start SQL Server 2005 is go to Start -> Control Panel -> Administrative Tools -> Services. Look up for a service by the name SQL Server or SQL Server (your instance name), right click -> Start. If you do not see this service listed, it is likely that you have installed only the client tools for SQL Server 2005 and not the server.

Hope that helps,

Amol.