Duplicated Rows

Hi,

I have just started developing in SQL Express in the last 2 months so still learning. The problem I’m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I’m asking.

A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.

ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS

SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],

CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],

MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'

WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'

WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'

WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'

WHEN DATEPART(MM,SessionDate)=5 THEN 'May'

WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'

WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'

WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'

WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'

WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'

WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'

WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'

FROM Sessions

GROUP BY [Job No], Sessions.SessionDate

ORDER BY [Job No]

Results in SQL Server Express

'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month'

1113-001 0 08/16/2001 08/16/2001 Aug
1113-002 0.5 07/11/2000 07/11/2000 Jul
1113-002 0.5 02/09/2000 02/09/2000 Feb
1116-001 1 07/07/1999 07/07/1999 Jul
1116-001 1 07/06/1999 07/06/1999 Jul
1118-001 1 01/12/1999 01/12/1999 Jan
1118-001 0.5 03/17/1999 03/17/1999 Mar
1118-001 1 02/23/1999 02/23/1999 Feb
1118-001 1 01/26/1999 01/26/1999 Jan
1118-001 0.5 03/09/1999 03/09/1999 Mar
1118-001 1 12/15/1998 12/15/1998 Dec
1118-001 1 02/09/1999 02/09/1999 Feb

Results in Ms Access

Days Done per Job

JobNo

Days to Date

Start Date

End Date

End Month

1113-001

0.00

16/08/2001

16/08/2001

Aug01

1113-002

1.00

09/02/2000

11/07/2000

Jul00

1116-001

2.00

06/07/1999

07/07/1999

Jul99

1118-001

6.00

15/12/1998

17/03/1999

Mar99

Not being an expert by any means, my hunch is that you should set up the first column as a primary key. It will never allow duplicate values after that. It will take you a second to do it in the SQL database table. Right click on the column name in designer. It may not eliminate the problem completely in a sense that there might be a bug in your code that does it. At least you will get an error message from the server protesting the fact that somebody is sending duplicates to it. It will be a cue for you where to look.|||Cheers Alex will give it a go.

Duplicate Reference Numbers using MAX()+1

Hi

Within a stored procedure I'm getting the next value of a reference
number using (simplified):

BEGIN TRANSACTION
@.next_ref=select max(ref) from table
insert into table (ref) values (@.next_ref+1)

create related records in other tables.

COMMIT TRANSACTION

I'm getting duplicate values in a multi-user network, presumably
because the new record is not commited until the transaction is
complete and another user starts another transaction and reads the same
max value.

Can anyone suggest a way of ensuring unique values? Perhaps by locking
the table for the duration.
There is already a separate identity column that increments ok.If you have an IDENTITY column, why do you want an incrementing "ref"
as well? Sure, you can lock the table each time but then you'll block
other inserts and turn your multi user system into a single user
system. The IDENTITY feature exists precisely to solve that problem.

--
David Portas
SQL Server MVP
--|||JohnSouth (jsouth@.cix.co.uk) writes:
> Within a stored procedure I'm getting the next value of a reference
> number using (simplified):
> BEGIN TRANSACTION
> @.next_ref=select max(ref) from table
> insert into table (ref) values (@.next_ref+1)
> create related records in other tables.
> COMMIT TRANSACTION
> I'm getting duplicate values in a multi-user network, presumably
> because the new record is not commited until the transaction is
> complete and another user starts another transaction and reads the same
> max value.

Add "WITH (UPDLOCK)" after the table name in the first query.

If you have a requirement that these values should be unique, you should
also add a UNIQUE constraint on this column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> JohnSouth (jsouth@.cix.co.uk) writes:
> > Within a stored procedure I'm getting the next value of a reference
> > number using (simplified):
> > BEGIN TRANSACTION
> > @.next_ref=select max(ref) from table
> > insert into table (ref) values (@.next_ref+1)
> > create related records in other tables.
> > COMMIT TRANSACTION
> > I'm getting duplicate values in a multi-user network, presumably
> > because the new record is not commited until the transaction is
> > complete and another user starts another transaction and reads the
same
> > max value.
> Add "WITH (UPDLOCK)" after the table name in the first query.
> If you have a requirement that these values should be unique, you
should
> also add a UNIQUE constraint on this column.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks Erland
As I understand it, the UPDLOCK hint will still allow other users to
read the table but will stop another transaction from doing the same
select max()until the first transaction has done the update and is
complete.
Hopefully it won't have too much impact on performance.|||JohnSouth (jsouth@.cix.co.uk) writes:
> As I understand it, the UPDLOCK hint will still allow other users to
> read the table but will stop another transaction from doing the same
> select max()until the first transaction has done the update and is
> complete.

Correct. UPDLOCK is a shared lock, other processe can still read the
value. But if they use UPDLOCK they get stuck.

What you really do is to upgrade the transaction isolation level to
Serializable instead of the default READ COMMITTED. UPDLOCK is a
special tweak to prevent deadlocks. Regular serializable would have meant
that two processes could have read the max value, and then they
would have deadlocked on the INSERT statements. Thanks to the UPDLOCK
this does not happen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Duplicate Records

Hi Folks,

I am new to SQl so need help in developing Stored procedure. What I am trying to do is, I have a big table of demographic information(more than 500000 records) for our customers. the table has lots of duplicates. It is because of typos. The unique key in the table is contactid. I have to write a code which reads through the records in the table and look for duplicates on different matchng criteria like firtsname, last name or phone no match etc. and assign a new id in a diffrent table with that contactid.
Like I have to open a recordset which reads through the first record and put that contactid and assign a new id(Let's call it personid) in another table(This table will have only two fields) then it will read the next record and see if we already have found this person if we have it will pick up that person id and will enter a record with that personid and its contactid and then it will go to the next record until done with all. So, by the end we will have one more table where we will have all the conatctids with their corresponding personid.

Plaese help me write some kind of store procedure in which I can do this.

Thank for the help.There are much faster and better ways to do this than by stepping through your records one at a time, but which is best depends on some specifics of your task.

One solution would be:

Select Max(PrimaryKey), Column1, Column2, Column3...
From YourOldTable
Into YourNewTable
Group By Colum1, Column2, Column3...

Other solutions involve select statements that join two instances of your table and match them on selected columns.

Is your goal to eliminate duplicates after updating related tables that have obsolete key values, or are you just creating a lookup table?

blindman|||I am not trying to eliminate duplicates. I want to create a lookup table. I want to run the several kinds of matches like first I want it on firstname, last name and zipcode, the next query will be on just the phone nos and next query should be on email. My loookup table will have only contactid and personid(one to many relationship) which means for several conatctids we have one personid. My ultimte gaol is to get the unique counts of contacts in the table.

Azra

Duplicate Process ID

I have a stored procedure that uses cursers. This stored
procedure executed by a user from MTS server. At times,
when I refresh the 'Current Activity' and look
into 'Process Info' window in EM, I see two process ID (ID
54) for the same stored proc. Shouldn't the ID's be unique
and assigned only 1 for this SP ? Would this be a SP
issue ?
It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
Thanks.
That means, the query inside your sp is doing parallel processing. SQL
Server can make use of more than one processor, if available - based on the
query.
More information on parallelism can be found in SQL Sever 2000 Books Online.
You can restrict parallelism by using the MAXDOP hint.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:73ba01c47647$1c37efc0$a401280a@.phx.gbl...
I have a stored procedure that uses cursers. This stored
procedure executed by a user from MTS server. At times,
when I refresh the 'Current Activity' and look
into 'Process Info' window in EM, I see two process ID (ID
54) for the same stored proc. Shouldn't the ID's be unique
and assigned only 1 for this SP ? Would this be a SP
issue ?
It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
Thanks.
|||Thanks.

>--Original Message--
>That means, the query inside your sp is doing parallel
processing. SQL
>Server can make use of more than one processor, if
available - based on the
>query.
>More information on parallelism can be found in SQL Sever
2000 Books Online.
>You can restrict parallelism by using the MAXDOP hint.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:73ba01c47647$1c37efc0$a401280a@.phx.gbl...
>I have a stored procedure that uses cursers. This stored
>procedure executed by a user from MTS server. At times,
>when I refresh the 'Current Activity' and look
>into 'Process Info' window in EM, I see two process ID (ID
>54) for the same stored proc. Shouldn't the ID's be unique
>and assigned only 1 for this SP ? Would this be a SP
>issue ?
>It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
>Thanks.
>
>.
>

Duplicate Process ID

I have a stored procedure that uses cursers. This stored
procedure executed by a user from MTS server. At times,
when I refresh the 'Current Activity' and look
into 'Process Info' window in EM, I see two process ID (ID
54) for the same stored proc. Shouldn't the ID's be unique
and assigned only 1 for this SP ' Would this be a SP
issue ?
It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
Thanks.That means, the query inside your sp is doing parallel processing. SQL
Server can make use of more than one processor, if available - based on the
query.
More information on parallelism can be found in SQL Sever 2000 Books Online.
You can restrict parallelism by using the MAXDOP hint.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:73ba01c47647$1c37efc0$a401280a@.phx.gbl...
I have a stored procedure that uses cursers. This stored
procedure executed by a user from MTS server. At times,
when I refresh the 'Current Activity' and look
into 'Process Info' window in EM, I see two process ID (ID
54) for the same stored proc. Shouldn't the ID's be unique
and assigned only 1 for this SP ' Would this be a SP
issue ?
It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
Thanks.|||Thanks.

>--Original Message--
>That means, the query inside your sp is doing parallel
processing. SQL
>Server can make use of more than one processor, if
available - based on the
>query.
>More information on parallelism can be found in SQL Sever
2000 Books Online.
>You can restrict parallelism by using the MAXDOP hint.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:73ba01c47647$1c37efc0$a401280a@.phx.gbl...
>I have a stored procedure that uses cursers. This stored
>procedure executed by a user from MTS server. At times,
>when I refresh the 'Current Activity' and look
>into 'Process Info' window in EM, I see two process ID (ID
>54) for the same stored proc. Shouldn't the ID's be unique
>and assigned only 1 for this SP ' Would this be a SP
>issue ?
>It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
>Thanks.
>
>.
>

Duplicate Process ID

I have a stored procedure that uses cursers. This stored
procedure executed by a user from MTS server. At times,
when I refresh the 'Current Activity' and look
into 'Process Info' window in EM, I see two process ID (ID
54) for the same stored proc. Shouldn't the ID's be unique
and assigned only 1 for this SP ' Would this be a SP
issue ?
It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
Thanks.That means, the query inside your sp is doing parallel processing. SQL
Server can make use of more than one processor, if available - based on the
query.
More information on parallelism can be found in SQL Sever 2000 Books Online.
You can restrict parallelism by using the MAXDOP hint.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:73ba01c47647$1c37efc0$a401280a@.phx.gbl...
I have a stored procedure that uses cursers. This stored
procedure executed by a user from MTS server. At times,
when I refresh the 'Current Activity' and look
into 'Process Info' window in EM, I see two process ID (ID
54) for the same stored proc. Shouldn't the ID's be unique
and assigned only 1 for this SP ' Would this be a SP
issue ?
It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
Thanks.|||Thanks.
>--Original Message--
>That means, the query inside your sp is doing parallel
processing. SQL
>Server can make use of more than one processor, if
available - based on the
>query.
>More information on parallelism can be found in SQL Sever
2000 Books Online.
>You can restrict parallelism by using the MAXDOP hint.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:73ba01c47647$1c37efc0$a401280a@.phx.gbl...
>I have a stored procedure that uses cursers. This stored
>procedure executed by a user from MTS server. At times,
>when I refresh the 'Current Activity' and look
>into 'Process Info' window in EM, I see two process ID (ID
>54) for the same stored proc. Shouldn't the ID's be unique
>and assigned only 1 for this SP ' Would this be a SP
>issue ?
>It is on Win 2K SQL 2K SP2 (With latest patch before SP3)
>Thanks.
>
>.
>

Duplicate Key Ignored

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

Since you want to avoid inserting rows from the source already existing in the destination, have you tried inserting based on a select that excludes those existing rows?

One way to construct it, is by a LEFT JOIN query.

INSERT destination
( col1, col2 ..... )
SELECT col1, col2 ....
FROM source s
LEFT JOIN
destination d
ON s.pk = d.pk
WHERE d.pk IS NULL

/Kenneth

|||A better way in that case is to use a subselect:

INSERT INTO destination
(col1, col2, ...)
SELECT col1, col2, ...
FROM source
WHERE pk NOT IN (
SELECT pk
FROM destination
)|||

Rick Brown wrote:

I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.

I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.

When I try to execute this in a DTS package, it stops the package execution because an error message was returned.

I have tried setting the fail on errors to OFF, but this has no effect.

I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.

I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.

I have verified the service pack, re-booted, etc.....

I am trying this in MSDE 2000a.

Thoughts or comments? Thanks!

I have the same problem, when my app runs the stored procedure, the error appears and the data is not retrieved. I don't think I can use the subquery solution, so I would like to get it working with Ignore_Dup_Key.

Isn't the purpose of Ignore_Dup_Key to allow the data to be returned without inserting the duplicated data?

Thanks for your time

Duplicate entries in the resulting table

Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.

Here is my stored procedure:

PROCEDURE[dbo].[spGetCompBulletins]

@.Useriduniqueidentifier OUTPUT,

@.DisplayNamevarchar(200)

AS

SELECT*

FROMdbo.UserProfileINNER JOIN

dbo.bulletinsONdbo.UserProfile.UserId = dbo.bulletins.UseridINNER JOIN

dbo.AssociationsONdbo.Associations.BusinessID = dbo.bulletins.UseridWHEREUserProfile.DisplayName=@.DisplayName

andUserprofile.Userid = @.UseridORDER BYBulletins.Bulletin_Date

Return

can you post some sample results and expected output?

You can use GROUP BY to avoid duplicates.

|||

The results are kind large. But the stored procedure works fine..Is there a way to name the stored procedure as a Table then remove the rows that has the same bulletin? I trie AS myNewTable at the end of the storedprocedure but I don't think it's the right syntax. I was thinking of doing something like this:

delete from (storedprocedure here) where bulletin is the same , but I don't know the syntax for this. Thanks for the help!

|||

I dont need to see 100 rows. just 4-5 rows with the data should give me an idea of what you are asking for..

|||

I have the following:

keyid name bulletin

143 mark bulletin 1

143 mark bulletin 1

143 mark bulletin 1

The output should be :

keyid name bulletin

143 mark bulletin 1

Thanks!!

|||

I forgot to mention that the same keyid can appear multiple times because I am joining a table that has information about the user(userprofile) with a table that has information about bulletin and a table (associations) that has 2 fields userid and businessid (both userid and businessid can be the userid in userprofile) this is to know what user is associated with who. And I want the user to view only the bulletins of other users who are associated with them in the association table.

Cheers!

|||

For the given data :

SELECT KeyId, Name, Bulletin

FROM YourTable

GROUP BY KeyId, Name, Bulletin

|||

Thanks! It works great!

Cheers!

Duplicate a row in SP code

I'm sure there's a simple way to do it, I just haven't run into it yet:
I just want to duplicate a table record (row) using a stored procedure.
lqThe solution depends on exactly what you mean by 'duplicate'. Hopefully,
the following will get you started:

CREATE TABLE MyTable1
(
Col1 INT NOT NULL
CONSTRAINT PK_MyTable1
PRIMARY KEY
)

CREATE TABLE MyTable2
(
Col1 INT NOT NULL
CONSTRAINT PK_MyTable2
PRIMARY KEY
)

INSERT INTO MyTable1 VALUES(1)
INSERT INTO MyTable1 VALUES(2)
GO

CREATE PROCEDURE CopyRow
@.Col1 int
AS
INSERT INTO MyTable2
SELECT Col1 FROM MyTable1
WHERE Col1 = @.Col1
GO

EXEC CopyRow 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0402180610.60f76e50@.posting.google.c om...
> I'm sure there's a simple way to do it, I just haven't run into it yet:
> I just want to duplicate a table record (row) using a stored procedure.
> lq|||What do you mean by "duplicate a row"? Normally we aim to avoid duplicating
data in a database but here are two examples taken from Pubs.

Iinsert a duplicate of a row into a table:

INSERT INTO Authors
(au_id, au_lname, au_fname, phone, address, city, state,
zip, contract, total_sales, ytd_sales)
SELECT <new PK value>, au_lname, au_fname, phone, address, city, state,
zip, contract, total_sales, ytd_sales
FROM Authors
WHERE au_id = '172-32-1176'

Return duplicates of a single row:

SELECT au_id, au_lname, au_fname, phone, address, city, state,
zip, contract, total_sales, ytd_sales
FROM Authors
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
WHERE au_id = '172-32-1176'

Neither of these examples serves much of a useful purpose but HTH.

--
David Portas
SQL Server MVP
--|||You can use the INSERT COMMAND for two times to get the duplicate
records in your table, but be sure that there is no unique contrauint
on that table.

Regards

Prashant Thakwani

laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0402180610.60f76e50@.posting.google.com>...
> I'm sure there's a simple way to do it, I just haven't run into it yet:
> I just want to duplicate a table record (row) using a stored procedure.
> lq

Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where value = 1

Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.

The function of this, I believe was to make the select work but not retrieve any actual values.

I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.

What I am thinking may not even apply to what I need to do here.

If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.

Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table

but I was hoping this would work.

Example:

Insert Into table

Select

1 as RecId,

' ' as field1,

field2

From test1

Where field2 = 'CA'

Union

Select

2 as RecId,

' ' as field1,

field2

From test1

Where field2 = 'NJ'

Union

/*Final Select */

Select

6 as RecId,

' ' as field1,

field2

From test1

Where 'some value' = 1'

Thanks much for your assistance!!!

TADEG

If you want to create a dummy select that never returns rows, just add WHERE 0=1 to the end.

Code Snippet

SELECT x,y,x FROM Table WHERE 0=1

dumbest question of the day: run a simple query and save the results into a text file

Can someone demonstrate a SIMPLE way to do this that does not require
additional functions or stored procedures to be created? Lets say I
want to execute the following simple query - "select * from clients" -
and save the results to a text file, we will assume c:\results.txt

How can I do this in one step?Here is one way, from the command line:

isql -S<yourserver>-U<username>-P<password> -Q "select * from clients" -o
output.log

hth

"sumGirl" <emebohw@.netscape.net> wrote in message
news:a5e13cff.0411301337.69b2ff44@.posting.google.c om...
> Can someone demonstrate a SIMPLE way to do this that does not require
> additional functions or stored procedures to be created? Lets say I
> want to execute the following simple query - "select * from clients" -
> and save the results to a text file, we will assume c:\results.txt
> How can I do this in one step?|||The BCP command line works as well, and you get a little more control over
how the data is saved.

Also there are a boat load of XML functions, if you need data saved in that
format.

"JD" <joeydba@.yahoo.com> wrote in message news:41acf2d6$1@.news.qgraph.com...
> Here is one way, from the command line:
> isql -S<yourserver>-U<username>-P<password> -Q "select * from clients" -o
> output.log
> hth
>
> "sumGirl" <emebohw@.netscape.net> wrote in message
> news:a5e13cff.0411301337.69b2ff44@.posting.google.c om...
>> Can someone demonstrate a SIMPLE way to do this that does not require
>> additional functions or stored procedures to be created? Lets say I
>> want to execute the following simple query - "select * from clients" -
>> and save the results to a text file, we will assume c:\results.txt
>>
>> How can I do this in one step?|||I don't know if you want it to be ad-hoc, but if you do, you can run
the query from Query Analyzer and from the query menu pick "results to
file"

Dumb Stored Procedure Question

I've designed a very basic SQL Server Stored Procedure that I'm using
via a Visual Basic 6.0 front-end file in retrieving records into a
data entry form.

I can't for the life of me get the records retrieved via the Stored
Procedure to be edited.

I can't even run the stored procedure in MSDE's T-SQL utility and then
edit any of the records shown to me.

Any ideas?

Thanks!

Brad McCollum
bmccoll1@.midsouth.rr.comBrad H McCollum (bmccoll1@.midsouth.rr.com) writes:
> I've designed a very basic SQL Server Stored Procedure that I'm using
> via a Visual Basic 6.0 front-end file in retrieving records into a
> data entry form.
> I can't for the life of me get the records retrieved via the Stored
> Procedure to be edited.
> I can't even run the stored procedure in MSDE's T-SQL utility and then
> edit any of the records shown to me.

Since I don't know how your procedure looks like, I have to guess.

The problem is likelyl to be that ADO is to smart for its own good.
It thinks that each column must be associated with a table, so if you
say:

SELECT a, b + 'j' FROM tbl

The first column will be editable, the second not.

If you get the result set into a temp table and return from that, you
are OK. Nevermind that the temp table dies with the procedure. ADO is
easy to fool.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

duff page in web dev

my page calls a stored procedure......

but it does not work.....

<%@.PageLanguage="C#"MasterPageFile="~/Masterhowit.master"Title="Untitled Page" %>

<%@.ImportNamespace="System.Net.Mail" %>

<%@.ImportNamespace="System.Data.SqlClient" %>

<scriptrunat="server">

protectedvoid Button1_Click(object sender,EventArgs e)

{

//.....sql insert statement for stored procedure...make active?

string numberintextbox = TextBox1.Text;

string yippyaddress = Request.UserHostAddress.ToString();

string dateandtime = DateTime.Now.ToString();

SqlDataSource Datast1000 = new SqlDataSource();

Datast1000.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringtodatast1000"].ToString();

Datast1000.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;

Datast1000.InsertCommand = "givemeinserting";

Datast1000.InsertParameters.Add("@.mobiles", numberintextbox);

Datast1000.InsertParameters.Add("@.ipaddress", yippyaddress);

The page collects mobile tele numbers......ipaddress.....and DateTimeStamp

well should.

The stored procedure works fine when executed from the database explorer and asks for the parameters all ok.

The connection string is fine and matches the web config......all compiles ok but at run time makes the berp noise

then my... if else.... throws exception to say no database entry made.

//--kickout---for no upload

int rowsAffected = 0;

try

{

rowsAffected = Datast1000.Insert();

}

catch (Exception ex)

{

// this error page says sorry error and sends message to admin about failure

Server.Transfer("erroratgiveme.aspx");

}

finally

{

Datast1000 =null;

}

if (rowsAffected != 1)

{

// this page says sorry there has been an error loading..try again

Server.Transfer("erroratgiveloading.aspx");

}

else

{

//happy days...go to giveme

Server.Transfer("giveme.aspx");

}

----------------------

I feel the code is fine because I have played with it for hours.....made basic insert statements etc.

Now this all worked fine two months ago until I tried to build a second page with insert statements then

nada.

Left it whilst I learned how to use microsoft sql server management express. Now tried for days to find problem

with no luck......

Should anyone from up high be available i would be very greatfull

Does anyone know what this means below

-------------------

The thread 0xb58 has exited with code 0 (0x0).

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Web.dll

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_9nwdvyfr.dll', Symbols loaded.

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll

An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

The program '[2652] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

Try to delete the Temporary files under the directory

'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files

and rebuild and try the application

|||

Try to delete the Temporary files under the directory

'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files

and rebuild and try the application

Delete all the folder under the specified path

|||

trying...trying..hold on|||

App_global.asax.compiled

App_global.asax.eo21_x31.0.cs

App_WebReferances.3-2oi4

App_Webreferances.compiled

hash.web

any of these?

Am i looking in the right place......did search for

TemporaryASP.NET Files

regards rich

|||

right found it and deleted all objects within the folder.....rerun application...still not doing

it...however I think you are close ......anything else to do?

|||

What error you are getting this time? Is it same please copy & paste the stack trace

|||

Well thats the problem.

there is no error.

asp thinks its done the job..... its just that no data is going into the db.

And there are no tipos......spent hours on that...

remember stored procedure works fine in database explorer.

Its as if my page data is all running fine its just not arriving at the database door.

I thought the page my have a corruption onit so made new page and deleted old one..still no good.

made an admin page and put onit a from view and gridview......all working fine at runtime and inserting data!

Also have form view on the fault page which is working fine.

its a no brainer

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\WebDev.WebServer.EXE', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\WebDev.WebHost\8.0.0.0__b03f5f7f11d50a3a\WebDev.WebHost.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Warning: Cannot debug script code. Script debugging is disabled for the application you are debugging. Please uncheck the 'Disable script debugging' option on the Internet Options dialog box (Advanced page) for Internet Explorer and restart the process.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.RegularExpressions\2.0.0.0__b03f5f7f11d50a3a\System.Web.RegularExpressions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.JScript\8.0.0.0__b03f5f7f11d50a3a\Microsoft.JScript.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_WebReferences.r9lj_2qs.dll', Symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_global.asax.lyflx3jz.dll', Symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Mobile\2.0.0.0__b03f5f7f11d50a3a\System.Web.Mobile.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_zbstgaax.dll', Symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_masterhowit.master.cdcab7d2.nufeebfc.dll', Symbols loaded.

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Web.dll

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll

An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

The thread 0x534 has exited with code 0 (0x0).

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Web.dll

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll

An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_2itmrd10.dll', Symbols loaded.

The program '[780] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

|||

Its not looking good we can try one more thing Uninstall the

aspnet_regiis.exe -ua

and then reinstall using

aspnet_regiis.exe -i

Make sure you are in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ directory before u run the commands. Send me the Code Behind as Well

I appriciate you patience

|||

mmm dont seem to have that file aspnet_regiis.exe -ua

I have four aspnet_regiis.exe files.......one of which is the 2.0.50727

search did not find ...aspnet_regiis.exe -ua

......so what is worst case sinario...delete and reload web dev

this all worked with sql insert statement in feb but suddenly lost the plot

|||

No the aspnet_regiis.exe is a file and

aspnet_regiis.exe -ua is command line parameters you need to supply when u execute from command line

and after that again from the command prompt run aspne_regiis.exe -i

|||

derr

|||

? what was that

|||

need help to run that.....got a mate who can help

but cant see him till friday.

going to have to put this on hold I think till then....going to mark post as answer for you

thanks so much for help.....ill post back here when Ive done it.

again thanks so much

rich

|||

don't worry we need to resovle this issue...

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS
'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS
' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].& #91;ASP_CCTS_RunImportContractorEmployee
_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS
'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:

> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS
'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS
' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].& #91;ASP_CCTS_RunImportContractorEmployee
_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS
'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:
> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTSRUN privileges?

I am trying to get a DTS package to be run from the command line with
the dtsrun utility. The DTS package is stored in the database. The user
I supply is a user in the database. I get an error stating "SQL Server
does not exist or access denied." It looks to me like the SQL Server
instance does exist because it tries to start the package. I get
"DTSRun: Executing". If I put in a server that is non-existent, I do not
get that message. I also know that my username and password are correct.

Here is output from my attempt to run dtsrun for my DTS pkg (server,
user, password change to protect my db security):

C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.

I suspect that my user I am connecting to the database with does not
have privileges to execute the DTS package. I cannot determine, from
BOL, what privs I need to grant to this user to let them execute this
package. Any ideas?

TIA,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - UnknownHi Brian.

The fact that it says:
> DTSRun: Loading...
> DTSRun: Executing...

indicates your user can run DTSRUN. What is more likely is that user cannot
connect to the servers and databases named in the package.

Try going into Query analyzer and connect to the server as that user and see
if you can connect to the database and select from some of its tables.

--
-Dick Christoph

"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:IyqxzH.BFw@.igsrsparc2.er.usgs.gov...
>I am trying to get a DTS package to be run from the command line with the
>dtsrun utility. The DTS package is stored in the database. The user I
>supply is a user in the database. I get an error stating "SQL Server does
>not exist or access denied." It looks to me like the SQL Server instance
>does exist because it tries to start the package. I get "DTSRun:
>Executing". If I put in a server that is non-existent, I do not get that
>message. I also know that my username and password are correct.
> Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
> password change to protect my db security):
> C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259
> (80004005)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 17 (11)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun: Package execution complete.
>
> I suspect that my user I am connecting to the database with does not have
> privileges to execute the DTS package. I cannot determine, from BOL, what
> privs I need to grant to this user to let them execute this package. Any
> ideas?
>
> TIA,
> Brian
>
>
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown|||To add to Dick's response, be aware that the DTS package runs on same
machine as DTSRUN. You'll get these errors if you run the package on a
client machine and have specified SQL Server name 'local' in the DTS
connection.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:IyqxzH.BFw@.igsrsparc2.er.usgs.gov...
>I am trying to get a DTS package to be run from the command line with the
>dtsrun utility. The DTS package is stored in the database. The user I
>supply is a user in the database. I get an error stating "SQL Server does
>not exist or access denied." It looks to me like the SQL Server instance
>does exist because it tries to start the package. I get "DTSRun:
>Executing". If I put in a server that is non-existent, I do not get that
>message. I also know that my username and password are correct.
> Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
> password change to protect my db security):
> C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259
> (80004005)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 17 (11)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun: Package execution complete.
>
> I suspect that my user I am connecting to the database with does not have
> privileges to execute the DTS package. I cannot determine, from BOL, what
> privs I need to grant to this user to let them execute this package. Any
> ideas?
>
> TIA,
> Brian
>
>
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown|||DickChristoph wrote:
> Hi Brian.
> The fact that it says:
>> DTSRun: Loading...
>> DTSRun: Executing...
> indicates your user can run DTSRUN. What is more likely is that user cannot
> connect to the servers and databases named in the package.
> Try going into Query analyzer and connect to the server as that user and see
> if you can connect to the database and select from some of its tables.

The user can connect to the database and query tables just fine with QA.
This is not a Windows user, but rather one that is authenticated in the
database.

Thanks,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||Dan Guzman wrote:
> To add to Dick's response, be aware that the DTS package runs on same
> machine as DTSRUN. You'll get these errors if you run the package on a
> client machine and have specified SQL Server name 'local' in the DTS
> connection.

I do plan on running the DTSRUN utility from a client machine, but I
have stated the SQL Server's name in the /S switch. I did not use
'local' or '.' for the server name since I know the package is not local
to the client.

Thanks,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||Hi Brian

Can you open the package and look at the connections. Dan was suggesting
that one of these connections might be "(local)". also I would add to my
previous comment, the username specified for these connections might lack
the privileges to connect to the database.

Also if to open the package and execute it from the designer you can see
which step is crashing.

--
-Dick Christoph
"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:Iyrtp9.25D@.igsrsparc2.er.usgs.gov...
> Dan Guzman wrote:
>> To add to Dick's response, be aware that the DTS package runs on same
>> machine as DTSRUN. You'll get these errors if you run the package on a
>> client machine and have specified SQL Server name 'local' in the DTS
>> connection.
>>
> I do plan on running the DTSRUN utility from a client machine, but I have
> stated the SQL Server's name in the /S switch. I did not use 'local' or
> '.' for the server name since I know the package is not local to the
> client.
> Thanks,
> Brian
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

DTSrun permissions

Hi

We are attempting to run a dtsrun statement remotely, via a stored procedure. We are getting the errors below, although I have ran this from a c: prompt on my PC.

C:\>dtsrun /SNT_LAKES02 /Usa /P-- /Nbmmenudev /Ap_menucode:8=101

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.

We have also tried this with the /E option with the same results.

Can anyone offer any help - thanks in advanceexec master..xp_cmdshell 'dtsrun /SNT_LAKES02 /Usa /P'

well you most likely will need to have permissions to exec master..xp_cmdshell. This most likely is the root of you problems, that is what was my issue when doing this for the first time.

DTSrun in Stored Procedure

Hi,

I'm writing a stored procedure to run a dts package and I've successfuly
got this working using my sotred proc and the syntax of dtsrun is
correct.

However, I'm trying to pass a variable to the DTSrun command and this is
where I'm having the problem

the code for the proc is:

Declare @.partcode nvarchar(255)

EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '

Everytime I run it I get this error:

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

can someone help me with this please?

M3ckon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You'll to build the command string before executing xp_cmdshell. For
example:

DECLARE @.Command varchar(1000)

SET @.Command = 'DTSRun /S "server" /U "user" /P "password" /N
"XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '

EXEC master..xp_cmdshell @.Command

--
Hope this helps.

Dan Guzman
SQL Server MVP

"m3ckon" <anonymous@.devdex.com> wrote in message
news:407eb7e2$0$206$75868355@.news.frii.net...
> Hi,
> I'm writing a stored procedure to run a dts package and I've successfuly
> got this working using my sotred proc and the syntax of dtsrun is
> correct.
> However, I'm trying to pass a variable to the DTSrun command and this is
> where I'm having the problem
> the code for the proc is:
> Declare @.partcode nvarchar(255)
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '
> Everytime I run it I get this error:
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> can someone help me with this please?
> M3ckon
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

dtsrun from stored procedure

Hi all,

I am currently using a series of dts packages to extract and export data. To run the packages, I have a table (TW_DTS) which lists each dts package name, I then use a stored procedure to loop through each and 'dtsrun' it. If a package fails then the error is stored in another table from within the individual dts.

This solution works in principle, however when the stored procedure runs some of the dts packages fail for an unknown reason. Unfortunately the problem is not consistent - it is not always the same package, or even same number of packages that fail. Each package will work when run individually.

I have listed the stored procedure below, any advice may save me from throwing my laptop through the window.

Cheers.

----

CREATE PROCEDURE [spTWDTS] AS

DECLARE @.unique_id varchar(50)
DECLARE @.dts_name varchar(50)
DECLARE @.start_dttm datetime
DECLARE @.end_dttm datetime
DECLARE @.CMD varchar(1000)
DECLARE @.ERROR varchar(1000)

DECLARE
dts_cur CURSOR FOR SELECT unique_id, dts_name, start_dttm, end_dttm from TW_DTS where status = 'A'

open dts_cur

FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

while @.@.fetch_status = 0

BEGIN
-- Set as No Error
SET @.ERROR = 0

--update the start date
UPDATE TW_DTS set start_dttm = getdate() where unique_id = @.unique_id

--run the package
SET @.CMD = 'dtsrun /S myserver /U myusername /P mypassword/N '+@.dts_name
EXECUTE @.ERROR = master..xp_cmdshell @.CMD

--update the end date
UPDATE TW_DTS set end_dttm = getdate() where unique_id = @.unique_id

--move to next dts package
FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

END

CLOSE dts_cur
DEALLOCATE dts_cur

RETURN @.ERROR
GONot sure about this but perhaps dtsrun /L might help in finding out where/when the execution fails. You might want to add a select 'executed: ' + @.dts_name, 'Error', @.error after the xp_cmdshell. I assume there's a space between the password and the /N ? And perhaps adding a 'start /w ' to the command might help.

Like I said, I'm not sure if their any help but it might get you on the right track.|||Thanks Kaiowas,

Tried that and got the following error message in the log file:

Step 'Copy Data from Results to [export].[dbo].[extract] Step' failed

Step Error Source: Microsoft OLE DB Provider for Oracle
Step Error Description:Oracle error occurred, but error message could not be retrieved from Oracle.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:

The step just runs an oracle query and put the results into the 'extract' table.

Not really any the wiser now, do you know how I can get to the oracle error?|||It's an MDAC error message, so Oracle won't help you there. I find google to be very helpful in such cases, it came up with: http://support.microsoft.com/kb/255084/EN-US/

DTS: Converting datatype in the source before import

Hi, In my source (text file connection) there is a date field but the date
is stored yyyymmdd (20080226)
I need to convert this into a real date 02/26/2008 and then import it into
the sql server database into a datetime field.
I'm a newbie with DTS so very detailed insturctions would be greatly
appreciated. When I just tried having it import into SQL Server I got an
error obviously since the date in the source data is not in a format sql
server will understandI'm not going to get detailed but I'll give you one way to do this. You'll
have to do some research on your own.
Import into a varchar column then use convert to transform and push into
your destination datetime column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi, In my source (text file connection) there is a date field but the date
> is stored yyyymmdd (20080226)
> I need to convert this into a real date 02/26/2008 and then import it into
> the sql server database into a datetime field.
> I'm a newbie with DTS so very detailed insturctions would be greatly
> appreciated. When I just tried having it import into SQL Server I got an
> error obviously since the date in the source data is not in a format sql
> server will understand
>
>|||i forgot to mention use convert function when pushing varchar into datetime
column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Knowledgy" <atlanta business intelligence consultants> wrote in message
news:XaednbkftahIS1nanZ2dnUVZ_gWdnZ2d@.comcast.com...
> I'm not going to get detailed but I'll give you one way to do this.
> You'll have to do some research on your own.
> Import into a varchar column then use convert to transform and push into
> your destination datetime column
> --
> Sincerely,
> John K
> Knowledgy Consulting
> http://knowledgy.org/
> Atlanta's Business Intelligence and Data Warehouse Experts
>
> "Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
> news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi, In my source (text file connection) there is a date field but the
>> date is stored yyyymmdd (20080226)
>> I need to convert this into a real date 02/26/2008 and then import it
>> into the sql server database into a datetime field.
>> I'm a newbie with DTS so very detailed insturctions would be greatly
>> appreciated. When I just tried having it import into SQL Server I got an
>> error obviously since the date in the source data is not in a format sql
>> server will understand
>>
>