bcp importing data from fewer fields to more table columns

Hello, please help me with this urgent problem,

I am trying to migrate data from one database to another using the bcp utility. Nowhere in the net I was able to find documentation on how to perform data importation when the destination table has more columns than the fields in my data file. I have a origin table which has 5 columns, and I need to migrate this data to a table that has 20 columns. How to do this? of course I need that the remaining 15 columns of the destination table be filled with NULL data.

From what I have seen, it seems that my xml format file must always have the same amount of <COLUMN> elements than the number of columns in the destination table, is this always so?. If it is, this is a real problem, since I lack 15 <COLUMN> elements in my format file.
I really don't know what can I do about this, pelase help!!

Thanks in advance,
Diego V.

Create a VIEW with the table.columns that match the data file, and BCP into the VIEW. (Be sure the other columns allow NULL values and/or have DEFAULT values.

bcp api and error handling

I have implemented a VB.NET application which uses the ODBC BCP api to bulk
insert data into a Microsoft SQL 2000 database. I have included error
handling as it is important to trap and log any errors when they occur. The
import works fine, and the error handling works fine for any ODBC errors
(server not found, login problems etc are all logged properly).
The problem is that ANY bcp error results in SQLGetDiagRec returning
SQL_NO_DATA. The same code works for the ODBC errors from the SQL* functions
,
but if any bcp_* function fails I always get SQL_NO_DATA. I am passing in th
e
connection handle with SQL_HANDLE_DBC as the handle type like the docs say,
but I have never managed to get a BCP error from this function.
Sometimes I can run the input files (I am using data and format files for
simplicity) with the command line bcp utility, and then I will see error
messages I can use. Sometimes the bcp utility works however, so I need the
error handling to find out what is going wrong. (The application is running
under COM+ as a special domain user for security reasons).
How should I use VB.NET to get the error messages when the ODBC BCP
functions fail? Why would my error handling code work for some errors but
return SQL_NO_DATA for others?Hi
I have never used this but, from your description it is hard to tell what is
failing, or even if you are using the correct error handling mechanism.
Posting sample code that can re-create your problem will help.
You may want to look at the example
http://msdn.microsoft.com/library/d...>
mp_4pm8.asp, although this is in C++.
John
"Stephen Davies" wrote:

> I have implemented a VB.NET application which uses the ODBC BCP api to bul
k
> insert data into a Microsoft SQL 2000 database. I have included error
> handling as it is important to trap and log any errors when they occur. Th
e
> import works fine, and the error handling works fine for any ODBC errors
> (server not found, login problems etc are all logged properly).
> The problem is that ANY bcp error results in SQLGetDiagRec returning
> SQL_NO_DATA. The same code works for the ODBC errors from the SQL* functio
ns,
> but if any bcp_* function fails I always get SQL_NO_DATA. I am passing in
the
> connection handle with SQL_HANDLE_DBC as the handle type like the docs say
,
> but I have never managed to get a BCP error from this function.
> Sometimes I can run the input files (I am using data and format files for
> simplicity) with the command line bcp utility, and then I will see error
> messages I can use. Sometimes the bcp utility works however, so I need the
> error handling to find out what is going wrong. (The application is runnin
g
> under COM+ as a special domain user for security reasons).
> How should I use VB.NET to get the error messages when the ODBC BCP
> functions fail? Why would my error handling code work for some errors but
> return SQL_NO_DATA for others?|||Hi,
Did you try the sample code included in SQL server setup CD? Please refer
to odbcerr.cpp for details.
http://msdn.microsoft.com/library/d...-us/odbcsql/od_
odbcsamp_4pm8.asp
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: bcp api and error handling
| thread-index: AcVAmXFPVsZNNvWPT5azzwfawDXxLg==
| X-WBNR-Posting-Host: 203.57.240.95
| From: "examnotes" <chalky@.newsgroup.nospam>
| Subject: bcp api and error handling
| Date: Wed, 13 Apr 2005 19:27:02 -0700
| Lines: 21
| Message-ID: <B608ADB0-3DFC-438D-89A4-45D4582D87C1@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.programming
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:85683
| NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have implemented a VB.NET application which uses the ODBC BCP api to
bulk
| insert data into a Microsoft SQL 2000 database. I have included error
| handling as it is important to trap and log any errors when they occur.
The
| import works fine, and the error handling works fine for any ODBC errors
| (server not found, login problems etc are all logged properly).
|
| The problem is that ANY bcp error results in SQLGetDiagRec returning
| SQL_NO_DATA. The same code works for the ODBC errors from the SQL*
functions,
| but if any bcp_* function fails I always get SQL_NO_DATA. I am passing in
the
| connection handle with SQL_HANDLE_DBC as the handle type like the docs
say,
| but I have never managed to get a BCP error from this function.
|
| Sometimes I can run the input files (I am using data and format files for
| simplicity) with the command line bcp utility, and then I will see error
| messages I can use. Sometimes the bcp utility works however, so I need
the
| error handling to find out what is going wrong. (The application is
running
| under COM+ as a special domain user for security reasons).
|
| How should I use VB.NET to get the error messages when the ODBC BCP
| functions fail? Why would my error handling code work for some errors but
| return SQL_NO_DATA for others?
||||Thanks John and Peter for your responses.
I have created a sample and in doing so found an interesting phenomenon:
The code works in a "normal" app, but when run as a COM+ component it fails
to return BCP error messages. Commenting out the "Inherits ServicedComponent
"
line causes the error messages to work. Leave it in and I get SQL_NO_DATA.
I can't see how to add attachments with Microsoft's web based newsgroup
reader, so I signed up to geocities:
http://www.geocities.com/stephenchalkydavies/VbBcp.zip
How can I get BCP error messages when running in a COM+ application?
Thanks again,
Stephen|||Hi
This may be interesting
http://support.microsoft.com/defaul...kb;en-us;319243 but
nothing to do with COM+
John|||It doesn't mention COM+, and is talking about other methods (SQLGetData)
returning SQL_NO_DATA. I am getting bcp failures and the SQLGetDiagRec call
returns SQL_NO_DATA - but only when running in COM+.
I tried installing the MDAC patch anyway just in case - but it wouldn't
install, I think because I already upgraded to MDAC 2.8 and it is for 2.7.
Any more ideas? I really need to get the error handling working.
Thanks,
Stephen
"John Bell" wrote:

> Hi
> This may be interesting
> http://support.microsoft.com/defaul...kb;en-us;319243 but
> nothing to do with COM+
> John|||Hello Stephen,
Did you try SQLGetDiagRecW? The issue might be related to Unicode?
Also, you may want to contact our Develop Support Services by telephone so
that a dedicated Support Professional can assist you on this issue.To
obtain the phone numbers for specific technology request please take a look
at the web site listed below:
http://support.microsoft.com/defaul...S;PHONENUMBERS.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: bcp api and error handling
| thread-index: AcVDsxEOzHtU0b+iSc6szI5qAdg/Kw==
| X-WBNR-Posting-Host: 203.57.240.95
| From: "examnotes" <chalky@.newsgroup.nospam>
| References: <B608ADB0-3DFC-438D-89A4-45D4582D87C1@.microsoft.com>
<EA1606BF-08E9-45B2-A71A-ED1FCD739434@.microsoft.com>
<A3B4D1EE-DD56-4290-919C-0ED9ECE806F8@.microsoft.com>
<1113548068.421350.42470@.o13g2000cwo.googlegroups.com>
| Subject: Re: bcp api and error handling
| Date: Sun, 17 Apr 2005 18:08:01 -0700
| Lines: 21
| Message-ID: <98CECA7A-7E8A-43AD-9426-D2AA20A1821B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:517122
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| It doesn't mention COM+, and is talking about other methods (SQLGetData)
| returning SQL_NO_DATA. I am getting bcp failures and the SQLGetDiagRec
call
| returns SQL_NO_DATA - but only when running in COM+.
|
| I tried installing the MDAC patch anyway just in case - but it wouldn't
| install, I think because I already upgraded to MDAC 2.8 and it is for 2.7.
|
| Any more ideas? I really need to get the error handling working.
|
| Thanks,
| Stephen
|
| "John Bell" wrote:
|
| > Hi
| >
| > This may be interesting
| > http://support.microsoft.com/defaul...kb;en-us;319243 but
| > nothing to do with COM+
| >
| > John
||||Hi Peter,
I just tried SQLGetDiagRecW, but when I enabled COM+ (by un-commenting the
"Inherits ServicedComponent" line) I got the same SQL_NO_DATA return code
that I got with SQLGetDiagRec.
What could COM+ be doing that causes bcp to lose its errors?
Yesterday I tried running an ODBC trace. Outside of COM+, I see a [DIAG]
message in the trace followed by a successful call to SQLGetDiagRec. When ru
n
in COM+ the only difference is that the [DIAG] message is not in the trace
and hence the call to SQLGetDiagRec returns SQL_NO_DATA.
Is the error message getting stored somewhere else?
Thanks,
Stephen
"Peter Yang [MSFT]" wrote:

> Hello Stephen,
> Did you try SQLGetDiagRecW? The issue might be related to Unicode?|||Hello Stephen,
I haven't found a known issue on this behavior. Please contact our Develop
Support Services by telephone on debugging/dump analysis to further
troubleshoot this issue.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: bcp api and error handling
| thread-index: AcVEdfIhZtnSq5dWQby7kIwLKqtnvA==
| X-WBNR-Posting-Host: 203.57.240.95
| From: "examnotes" <chalky@.newsgroup.nospam>
| References: <B608ADB0-3DFC-438D-89A4-45D4582D87C1@.microsoft.com>
<EA1606BF-08E9-45B2-A71A-ED1FCD739434@.microsoft.com>
<A3B4D1EE-DD56-4290-919C-0ED9ECE806F8@.microsoft.com>
<1113548068.421350.42470@.o13g2000cwo.googlegroups.com>
<98CECA7A-7E8A-43AD-9426-D2AA20A1821B@.microsoft.com>
<VLbWZOARFHA.2316@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: bcp api and error handling
| Date: Mon, 18 Apr 2005 17:23:01 -0700
| Lines: 24
| Message-ID: <90CF8356-0BCF-4740-91A6-F29889FC0A24@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:517422
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Peter,
|
| I just tried SQLGetDiagRecW, but when I enabled COM+ (by un-commenting
the
| "Inherits ServicedComponent" line) I got the same SQL_NO_DATA return code
| that I got with SQLGetDiagRec.
|
| What could COM+ be doing that causes bcp to lose its errors?
|
| Yesterday I tried running an ODBC trace. Outside of COM+, I see a [DIAG]
| message in the trace followed by a successful call to SQLGetDiagRec. When
run
| in COM+ the only difference is that the [DIAG] message is not in the
trace
| and hence the call to SQLGetDiagRec returns SQL_NO_DATA.
|
| Is the error message getting stored somewhere else?
|
| Thanks,
| Stephen
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Stephen,
| >
| > Did you try SQLGetDiagRecW? The issue might be related to Unicode?
|
|

BCP - between 2 SQL Server Tables

Any syntax where i will copy an sql server table to another table using BCP.im using sql server 2000 on vb.net, DTS isn't working for me because i want to edit some conditions on the package and it just dont work well.thanks in advance!are the 2 tables in the same database, or the same server or on the same network? you may be able to do this with a simple

INSERT INTO MyTable(MyField)
SELECT

using fully qualified names and maybe a linked server.|||if you want to use bcp, you have to do it in 2 steps:

1. use bcp with "out" or "queryout" keywords to go from source table to flat file
2. use bcp with "in" keyword to go from flat file to dest table.

if you have many millions of rows, bcp is faster than using INSERT...SELECT.

read about bcp syntax here:

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Another option, since you are using .net, is to use the sqlbulkcopy class, which has the advantage of not needing to write the intermediate file. It's quite fast. Read up here: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx. I recommend you to use the overload of WriteToServer that takes IDataReader, not the DataTable overloads. much more memory efficient.|||Thanks..maybe i forgot that one..workin on it now..hope it will be much faster|||thats what im thinking..i have to do two steps..good if im transferring it to other database or web.will it still be much faster than 'Insert...Select' statement in two steps?...|||it depends on how many rows you are moving. if it's millions, bcp/bulk insert/sqlbulkcopy will be faster than INSERT...SELECT.

If you have lots of rows, I would try sqlbulkcopy. it's only one step, and uses the same underlying method as bcp, namely, bulk insert.

bcp

How implements bcp.exe in visual basic.net project.
hi Viktor,
"Viktor Zadro" <viktor.zadro@.tel.net.ba> ha scritto nel messaggio
news:O1IHIcPtEHA.2192@.TK2MSFTNGP14.phx.gbl
> How implements bcp.exe in visual basic.net project.
how would you implement BCP?
you can "shell" to it, or you could "import" the COM object definition of
bcp (via INTEROP) and access it's properties and methods...
http://msdn.microsoft.com/library/de..._ob_b_8o8k.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Batch updates with SQL CLR

Unfortunately batch updates (i.e. setting SqlDataAdapter.UpdateBatchSize to >1) are not possible with ADO.NET using a context connection. Does anyone know why it's not possible? Will it be implemented or allowed in a future version of .NET?

Here's what I'm currently doing:
I have a C# stored procedure, using a context connection, that updates all the records in a table (typically a million records) by repeatedly:
- sequentially reading a group of records (1000 at a time) into a SqlDataAdapter (using a 'select top row_number' -type statement that prevents re-querying processed records);
- performing complex processing on each record and writing the results back to the adapter (several fields of each record are updated);
- updating the table when each group has been processed.

This works, but it's not quite as fast as I'd have hoped for and is only slightly slower if using a non-context connection from an external application. I'd like to enable batch updating to increase performance (I actually get much faster performance from an external application that batch updates using a non-context connection, say 20% faster!)... but of course I can't with SQL CLR.

Any ideas on how I can improve performance of my updating?

Thanks in advance,

Graham

If I understand correctly, you're doing record by record calculation. So all required info is present within the record. If I would try to solve this I would have started with a CLR function, not a SP. So my statement would look something like:

Update BigTable Set CalcField = CLRFunc.ToughCalc(fieldX, fieldY, fieldZ)

I'm not sure if this would be faster, but it would be set based (not all functions will produce set bases solutions)

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
Dan
Are you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your
problem....
Brad Feaker
Database Administrator

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could
be slowing down as the transaction log grows. Try breaking the updates into
smaller logical batches (say 1000 inserts/updates at a time) and issue a co
mmit. This may solve your
problem....
Brad Feaker
Database Administrator

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your problem...
Brad Feake
Database Administrator

Batch insert

I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
MarkI am confused:confused: What is it that you need?

If you're talking about being able to do BULK INSERT from a stored procedure, you can do that, but you'll have to grant the User ID that will execute it Bulk Insert Administrator server role (bulkadmin.)|||Sorry
I have 2 tables
table 1 - remote, cant use an SP
table 2 - local, can use a SP

I do a select on Table1 and it returns the results,

I want to insert all these results into table 2

whats the best way to do that? (most efficient)

Cheers
Mark|||The problem is that the first table is really slow to access, I have a string of booking Id's i need to pass it and it keeps timing out...its the DB! its on rubbish HW and is badly designed but I cant do anything about that so I have to use the most efficent method of getting the data out, or simply copying the required records between the 2 tables

remember the rubbish DB is not local and I only have read access to it!

whats the most efficent method I should use?

thanks
Mark|||Create a stored procedure on the remote server that retrieves rows via SELECT, and call it instead of doing SELECT from your local server.

Batch execute of SQL script from ADO.Net

I have a VB.Net application that needs to create about 5 stored
procedures and a couple of functions on SQL Server 2005 Express.
Currently I can execute all of them in one window of Sql Server
Management Studio, just separate each of them with a "GO" statement. Is
there a way I can accomplish this "one shot" approach via ADO.Net in my
application? If so, I can just put all my ddl SQL code in a text file
as an embedded resource, and then execute it in a couple of lines of
code. However, I suspect that I need to execute each ddl statement
separately and thus will need to parse the text file to break it up, or
break the sql code into multiple files, one for each stored proc.
Thanks for your thoughts,
Marcus[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Marcus (holysmokes99@.hotmail.com) writes:
> I have a VB.Net application that needs to create about 5 stored
> procedures and a couple of functions on SQL Server 2005 Express.
> Currently I can execute all of them in one window of Sql Server
> Management Studio, just separate each of them with a "GO" statement. Is
> there a way I can accomplish this "one shot" approach via ADO.Net in my
> application? If so, I can just put all my ddl SQL code in a text file
> as an embedded resource, and then execute it in a couple of lines of
> code. However, I suspect that I need to execute each ddl statement
> separately and thus will need to parse the text file to break it up, or
> break the sql code into multiple files, one for each stored proc.
Yes, if you read this file from your own application, you will need to
parse the file for "go" and send down batch by batch with ExcecuteNonQuery.
Parsing the file for "go" is a trivial matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

batch (.bat) file from sql server?

Is that possible to run batch file (.bat) from sql server?
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***DECLARE @.v_cmd VARCHAR(2000)
SET @.v_cmd = 'CALL "d:\myfolder\mybatch.bat"'
EXEC master..xp_cmdshell @.v_cmd ,NO_OUTPUT
-- OR
-- EXEC master..xp_cmdshell @.v_cmd|||I guess what I am asking is to call the bat file from a stored proc?
*** Sent via Developersdex http://www.examnotes.net ***|||Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||Test Test (farooqhs_2000@.yahoo.com) writes:
> I guess what I am asking is to call the bat file from a stored proc?
You can invoke a bat file with help of xp_cmdshell. Note that xp_cmdshell
is fairly dangerous feature, if opened to unprivileged users, since
it permits you run commands on Windows level on the SQL Server machine.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

basics with stored procedures.....please

Hi there, I'm still fairly new to asp.net / c#

I've setup my stored procedure, but now what?
I want to display the information (only one record it will return) on the page / NOT using a datagrid or list, just simple....I guess using an asp:label???
Could someone tell me what I need to do!!!

Also, are there any proper resources out there that just show you right out of the box howto insert / update / delete with sp's?, show / view, just proper simple source code?

THANKS!

my code:
// Creating the database connection
SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_conn_main"]);

// Creating the stored procedure 4 the webpage
// Instantiate a Sql Command
SqlCommand webpage_view = new SqlCommand("stpr_webpage_view", objConn);
// Declare that the T-SQL statement that will be executed is a stored procedure
// Use a CommandType.Text to execute a T-SQL Query
webpage_view.CommandType = CommandType.StoredProcedure;
SqlParameter webpage_id_parm = webpage_view.Parameters.Add("@.webpage_id", SqlDbType.Int);
webpage_id_parm.Value = "1";
// Create a SqlDataAdapter to manage the connection and retrieve the data
SqlDataAdapter da_1 = new SqlDataAdapter();
// Instantiate a Dataset that will hold the data retrieved from the database using the select command
DataSet ds_webpage = new DataSet();
da_1.SelectCommand = webpage_view;
// Retrieve the data into the dataset using the SqlDataAdapter.Fill command
da_1.Fill( ds_webpage, "webpage_title" );
DataView dv2 = ds_webpage.Tables[0].DefaultView;
if ( !this.IsPostBack ) {
this.DataBind ( );
}

// C#
// To place a single value from a recordset into a label
// drag an ASP:Label onto the Web form and give it an ID of myLabel
myLabel.Text = "No data";
// Be compulsive and make sure there is a datatable in the dataset and that it has at least one row
if( ds_webpage.Tables.Count > 0 && ds_webpage.Tables[0].Rows.Count > ) ){
 myLabel.Text = ds_webpage.Tables[0].Rows[0].ToString();
}

The simplest way to see how stored procedures can be used to select and update data is to let VS.Net create it for you.

1. Go to Design View for a web form.
2. Drag an SqlDataAdapter onto the form. A DataAdapter Configuration Wizard will open
3. Select your database connection and click Next.
4. On the Choose a Query Type screen select the second option "Create a new Stored Procedure". Click Next
5. On the Generate a Stored Procedure screen enter a select statement for the fields you want to use. For example, just something like Select * from SomeTable. Click Next.
6. On the Create the Stored Procedures screen enter appropriate stored procedure names. Be sure to click the "Yes, create them in the database for me" radio button.

Look at the code behind generated for this data adapter to see how it all works. Use it like the one you created above for the select. For the update, after you changed data in the data set just call:
da_1.Update( ds_webpage, "webpage_title" );

Basic, perhaps silly SQLServer 2005 question

I've previously used version 7 and 2000.

I've recently started to learn ASP.net. I've installed
Visual Web Developer 2005 Express and this includes
SQLServer 2005 in the installation. The task manager
and and the list of services definitely show I've got
SQLServer running on my computer.

Here's the problem: how do I actually use it? I can't
find any tools to create and access databases.

I've tried installing the Enterprise Manager from 2000
but this gives me an error message saying I must use the 2005
tools to connect.

So, is there an Enterprise Manager (or similar) for 2005?
Where do I find it?Download Microsoft SQL Server Management Studio Express from
http://www.microsoft.com/downloads/...&DisplayLang=en

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145465194.532705.252780@.u72g2000cwu.googlegr oups.com...
> Download Microsoft SQL Server Management Studio Express from
> http://www.microsoft.com/downloads/...&DisplayLang=en

Thanks, thats great