Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;
What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;
|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index = idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index => idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
--
David Portas
SQL Server MVP
--

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP
--sql

Duplicate records

I'm trying to use this script to delete duplicate records but am getting
errors
Thanks for you help in advance
http://www.sql-server-performance.c..._duplicates.asp
Stephen K. Miyasato
/*
** This file will delete dupe rows from a table based on a column list. ie.,
the compound value of the chosen column list is used to determine dupe
status and subsequent delete status. We construct some dynamic SQL and use
the ROWCOUNT function in determining how many rows to delete.
**
** Revision History:
** ----
** Date Name Description
** ----
** 07/24/03 DVDS Create
*/
-- declare all variables!
DECLARE @.iErrorVar int,
@.PID_1 varchar(50),
@.DateReport varchar(50),
@.OrderNo varchar(50),
@.TestL varchar(50),
@.Result varchar(50),
@.iReturnCode int,
@.iCount int,
@.chCount char(3),
@.nvchCommand nvarchar(4000)
-- set initial environment
SET ROWCOUNT 0
SET NOCOUNT ON
-- Build cursor to find duplicated information
DECLARE DelDupe CURSOR FOR
SELECT COUNT(*) AS Amount,
PID_1,
DateReport,
OrderNo,
TestL,
Result
FROM LData2Batch
GROUP BY PID_1,DateReport,OrderNo,TestL,Result
HAVING COUNT(*) > 1
ORder by dateReport DESC
OPEN DelDupe
FETCH NEXT FROM DelDupe INTO @.iCount,
@.PID_1,
@.DateReport ,
@.OrderNo ,
@.TestL ,
@.Result
WHILE (@.@.fetch_status = 0)
BEGIN
-- Calculate number of rows to delete for each grouping by subtracting
-- 1 from the total count for a given group.
SELECT @.iCount = @.iCount - 1
SELECT @.chCount = CONVERT(char(3),@.iCount)
-- now build the rowcount and delete statements.
SELECT @.nvchCommand = N'SET ROWCOUNT ' + @.chCount +
'DELETE LData2Batch ' + <= original was 'DELETE IndTest '+
' WHERE PID_1 = ' + CHAR(34) + @.PID_1 + CHAR(34) +
' AND DateReport = ' + CHAR (34) + @.DateReport + CHAR(34) +
' AND OrderNo = ' + CHAR(34) + @.OrderNo + CHAR(34) +
' AND TestL = ' + CHAR(34) + @.TestL + CHAR(34) +
' AND Result = ' + CHAR(34) + @.Result + CHAR(34)
-- print the statement. For your viewing pleasure only.
PRINT @.nvchCommand
-- execute the statement.
EXEC sp_executesql @.nvchCommand
SELECT @.iErrorVar = @.@.Error
IF @.iErrorVar <> 0
BEGIN
RETURN
END
FETCH NEXT FROM DelDupe INTO @.iCount,
@.PID_1 ,
@.DateReport ,
@.OrderNo ,
@.TestL ,
@.Result
END
CLOSE DelDupe
DEALLOCATE DelDupe
RETURN
This is the error:
SET ROWCOUNT 1 DELETE LData2Batch WHERE PID_1 = "77237813" AND DateReport
= "Oct 3 2005 8:35PM" AND OrderNo = "M73283" AND TestL = "Comments" AND
Result = "DNR"
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '77237813'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Oct 3 2005 8:35PM'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'M73283'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Comments'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DNR'.
/****** Object: Table [dbo].[LData2Batch] Script Date: 10/5/2005 7:21:25
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[LData2Batch]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
BEGIN
CREATE TABLE [LData2Batch] (
[LabIndex] [int] NOT NULL ,
[PID_1] [varchar] (10) NULL ,
[OrderNo] [varchar] (10) NULL ,
[dateDrawn] [smalldatetime] NULL ,
[Rindex] [int] IDENTITY (1, 1) NOT NULL ,
[testO] [varchar] (100) NULL ,
[Tests] [varchar] (50) NULL ,
[TestL] [varchar] (50) NULL ,
[Result] [varchar] (50) NULL ,
[Units] [varchar] (50) NULL ,
[Range] [varchar] (50) NULL ,
[AbnFlag] [varchar] (1) NULL ,
[memo] [varchar] (1000) NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_LData2Batch_rowguid] DEFAULT (newid()),
[LastUpdate] AS (getdate()) ,
[Who] [smallint] NULL ,
[DateReport] [datetime] NULL ,
CONSTRAINT [PK_LData2Batch] PRIMARY KEY CLUSTERED
(
[Rindex]
) ON [PRIMARY]
) ON [PRIMARY]
END
GOYOu have to set Quoted_Identifier OFF (Look in the BOL for
quoted_identifier --> SET QUOTED_IDENTIFIER OFF, because the CHAR(34) =
" is treated as a identifier that a Identitifier is follwing, such as a
column name.
Or you use the CHAR(39) which is the ' Sign and converts the
epxressions you use in a string.
HTH, Jens Suessmeyer.

Dump database objects without owners

Dear Friends,
Sql Server 2000
While creating the database script using EM
right-click the database to script, point to All
Tasks, and then click Generate SQL Scripts.
This creates a script with owner of the objects, like
CREATE TABLE [usera].[tbl_wi_lookup] (
[wi_lookup_id] [int] IDENTITY (1, 1) NOT NULL ,
[wi_lookup_type] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wi_lookup_value] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
But i want to create a script without the user of the
object.
In the development machine the user 'A' owns all the
tables and scripts. I want to give the script file to
the client who may use user 'A' or User 'B' or user
'sa'.
So i want to dump the database objects without owner
information. Is there anyway to get such ddl ?
Please shed some light.
Kumar
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I would simply open the file in a text editor and replace lit...
Unsophisticated , but done!...
As an aside, it is generally a bad idea to allow any individuals, other than
the DBO to own objects inthe database... It leads to performance issues,
code maintenance issues, etc - the first of which you see here... If DBO
owned all of the tables, you could merely generate the script and run it...
The DBO in each database could be different, and there would be no impact on
the scripting..\\\\\
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kumar ss" <sgnerd@.yahoo.com.sg> wrote in message
news:Ou9judkFEHA.3188@.TK2MSFTNGP09.phx.gbl...
> Dear Friends,
> Sql Server 2000
> While creating the database script using EM
> right-click the database to script, point to All
> Tasks, and then click Generate SQL Scripts.
> This creates a script with owner of the objects, like
> CREATE TABLE [usera].[tbl_wi_lookup] (
> [wi_lookup_id] [int] IDENTITY (1, 1) NOT NULL ,
> [wi_lookup_type] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [wi_lookup_value] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> But i want to create a script without the user of the
> object.
> In the development machine the user 'A' owns all the
> tables and scripts. I want to give the script file to
> the client who may use user 'A' or User 'B' or user
> 'sa'.
> So i want to dump the database objects without owner
> information. Is there anyway to get such ddl ?
> Please shed some light.
> Kumar
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
There is a stored procedure in the below link using SQL DMO, which will be
suit your requirement.
http://www.mssqlcity.com/Scripts/T-...erateScript.sql
Thanks
Hari
MCDBA
"kumar ss" <sgnerd@.yahoo.com.sg> wrote in message
news:Ou9judkFEHA.3188@.TK2MSFTNGP09.phx.gbl...
> Dear Friends,
> Sql Server 2000
> While creating the database script using EM
> right-click the database to script, point to All
> Tasks, and then click Generate SQL Scripts.
> This creates a script with owner of the objects, like
> CREATE TABLE [usera].[tbl_wi_lookup] (
> [wi_lookup_id] [int] IDENTITY (1, 1) NOT NULL ,
> [wi_lookup_type] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [wi_lookup_value] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> But i want to create a script without the user of the
> object.
> In the development machine the user 'A' owns all the
> tables and scripts. I want to give the script file to
> the client who may use user 'A' or User 'B' or user
> 'sa'.
> So i want to dump the database objects without owner
> information. Is there anyway to get such ddl ?
> Please shed some light.
> Kumar
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||EM scripting is not very customizable, I'm afraid. Query Analyzer has that o
ption, though (but you can only
script one object at a time in QA). I imagine that some of below tools also
has that option:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"kumar ss" <sgnerd@.yahoo.com.sg> wrote in message news:Ou9judkFEHA.3188@.TK2MSFTNGP09.phx.gb
l...
> Dear Friends,
> Sql Server 2000
> While creating the database script using EM
> right-click the database to script, point to All
> Tasks, and then click Generate SQL Scripts.
> This creates a script with owner of the objects, like
> CREATE TABLE [usera].[tbl_wi_lookup] (
> [wi_lookup_id] [int] IDENTITY (1, 1) NOT NULL ,
> [wi_lookup_type] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [wi_lookup_value] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> But i want to create a script without the user of the
> object.
> In the development machine the user 'A' owns all the
> tables and scripts. I want to give the script file to
> the client who may use user 'A' or User 'B' or user
> 'sa'.
> So i want to dump the database objects without owner
> information. Is there anyway to get such ddl ?
> Please shed some light.
> Kumar
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.com
Replace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com
|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com
|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script
.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/ ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A99
9760D84E91CD170332E5D7CB590B764064631A19
51589D6F310C930314F0F68864FCADAF0232
E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B
352C4319ED868A5232FAEDCA9031A0BB43FB
FF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> / ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A
999760D84E91CD170332E5D7CB590B764064631A
1951589D6F310C930314F0F68864FCADAF02
32E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE36
1B352C4319ED868A5232FAEDCA9031A0BB43
FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an I
D
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job vi
a
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
--
Message posted via http://www.sqlmonster.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?
Message posted via http://www.sqlmonster.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
SQLMonster.com" <u3288@.uwe> wrote:
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some
>> sort
>> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>> So what you are telling me is there the long string does not refer to an
>> ID
>[quoted text clipped - 14 lines]
>> sort
>> that I can compare?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1

DTS-example

I found this useful:
http://www.perfectxml.com/articles/x...rtxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,
"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.
|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTS-example

I found this useful:
http://www.perfectxml.com/articles/...ortxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/...ortxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTS-example

I found this useful:
http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> > I found this useful:
> > http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
> >
> > But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> > work.Got the error message:
> > 'Can't create object: MSXML2.DOMDocument.4.0'
> >
> > /Kent J,
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTSDestination - equivalent in SSIS?

Hi,

I am converting an ActiveX script. I am looking to replace the functionality of this statement:

If DTSDestination("CartoonCharacter") = "Felix" Then ...

So, it's checking the value in the TABLE, not the file.

I need to rewrite this for SSIS. What is the recommended approach? Is there a built-in task that will do this and how?

Thanks much

You'd use a lookup component to query the destination and then later use a derived column or a conditional split transformation to compare the lookup result with the source value (or a hardcoded value, if you wish)

dts2000 migration, activex script and odbc connections help needed

Apologies if this has been covered before or if i am in the wrong place.

I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS package that connects to an AS400 to retrieve teh record count of a table for integrity checking. Under SQL server 2000 this worked fine but in SSIS it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server 05 on windows 2003. The odbc driver for the as400 has been installed also.

The activex script is as follows:

dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string

set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%

'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"

set rs1 = cn.execute (insertstr1)

'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function

I have since changed MSDASQL to SQLNCLI but (after errors re ad hoc distributed queries which i have enabled) i now get errors re linked servers:

ole db provider "sqlncli" for linked server "(null)" returned message "invalid connection string attrbute"

Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks

Chris

I am contacting the SSIS team but I suspect that you need to run the 32-bit version of SSIS since - as you pointed out - the ODBC driver is a 32-bit DLL, somehwat similar to the case discussed at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1.

|||

I confirmed: if there is only 32-bit ODBC driver, you need to run the package using 32-bit runtime.

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Error

I have an SSIS Package that first goes out using an ActiveX script to search to see if an access database exists. If it exists it deletes it and recreates the database shell, if not it creates a new one. The second step is to create the tables the Access database is going to need. The last step is to populate the Access tables with data from a SQL Server database. I have changed the package from running in 64Bit to False. I have tried EVERY security mode for the package and the package still fails with the:

[Access - Definition [3781]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[DTS.Pipeline] Error: component "Access - Definition" (3781) failed the pre-execute phase and returned error code 0xC020801C.

Please help!

Thanks!

The error message may be appearing for one of the following reasons:

· The connection string specifies a provider that is not supported.

· You do not have access to the specified data source.

· The specified data source is being used by another application.

|||

Thanks for your help! What I had to end up doing is...I have 83 tables I need to copy over to Access...so come to find out I had to split half of the tables over to another data flow and now I see nothing but green when I execute the package...its the weirdest thing...but it works so I am going with it...thanks for you help tho!

|||I know that this question is update dbut for others who have the same problem need to check out the security option. If you're using a password to access a dB then you have to use Persist Security info=true otherwise use Integrated security=true

Good luck

DTS: single SELECT from 2 databases possible?

Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?

I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:

SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat

(where DB_ONE and DB_TWO are the 2 different db names)

I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?

Thanks,

Andre

Dont know if this is the one and only answer, but by changing the datasource to the MASTER db, it then allows me to use my original SQL.

Dts.log in script task

Hi

Can someone tell me where I can find the log file created by the Dts.log method in script task.

I have created a log provider file as Mylog.xml, but the messages recorded are from the Dts.Event.FireInformation method and not the Dts.log method.

I don't know where the messages are filed.

Regards

Baldev

To log the output of Dts.Log() to a Log Provider, go to the "Configure SSIS Logs" dialog (e.g. SSIS/Logging...). Change the Logging Mode on the Script Task from the default of "UseParentSetting" to "Enabled". That is , click on the check box next to the Script task until it is checked ( LoggingMode = "Enabled") and not checked and greyed out (LoggingMode = "UseParentSetting") or unchecked (LoggingMode = "Disabled").

With the Script Task node selected, navigate to the Details tab, and select the "ScriptTaskLogEntry" event. Dts.Log() calls will now be sent to whatever log providers are enabled for the Script Task itself. Also, make sure to select the log provider for the script task "again", since this is effectively overriding the parent containers logging settings.

|||

Great, that's exactly what I wanted.

Thanks a lot

Baldev

DTS with ActiveX script schedule problem

Hello gang, can someone help me with a problem?
I have a DTS package that uses ActiveX script to copy and rename files. I
can run this DTS package manually and the files are renamed and copied.
However, if I schedule this package with a job, the job runs successfully bu
t
the files are not copied and renamed. I have logged both the DTS activity
and Job activity and everything appears successful but no files are written.
Has anyone ran into this same problem.
These are the items I have thouroughly checked out:
The paths are correct (because the DTS package works)
The owner of the DTS has privleges to write(because the DTS package works)
The Owner of the Job has privileges to write(because the log activity shows
the steps in the DTS running)
Thanks for your help in advance.
Thanks Kllyj64BTW. Both the Job and the DTS package are running on the actual server, so
I
am not having a problem about "where I am executing the objects."
--
Thanks Kllyj64
"kllyj64" wrote:

> Hello gang, can someone help me with a problem?
> I have a DTS package that uses ActiveX script to copy and rename files. I
> can run this DTS package manually and the files are renamed and copied.
> However, if I schedule this package with a job, the job runs successfully
but
> the files are not copied and renamed. I have logged both the DTS activity
> and Job activity and everything appears successful but no files are writte
n.
> Has anyone ran into this same problem.
> These are the items I have thouroughly checked out:
> The paths are correct (because the DTS package works)
> The owner of the DTS has privleges to write(because the DTS package works)
> The Owner of the Job has privileges to write(because the log activity show
s
> the steps in the DTS running)
> Thanks for your help in advance.
>
> --
> Thanks Kllyj64|||I found the problem, SQL jobs runs under NT and does not recognize mapped
drives. I changed the DTS ActiveX scripts to use UNC drives and the job
worked. I should have looked further before posting.
--
Thanks Kllyj64
"kllyj64" wrote:
[vbcol=seagreen]
> BTW. Both the Job and the DTS package are running on the actual server, s
o I
> am not having a problem about "where I am executing the objects."
> --
> Thanks Kllyj64
>
> "kllyj64" wrote:
>

DTS with ActiveX script schedule problem

Hello gang, can someone help me with a problem?
I have a DTS package that uses ActiveX script to copy and rename files. I
can run this DTS package manually and the files are renamed and copied.
However, if I schedule this package with a job, the job runs successfully but
the files are not copied and renamed. I have logged both the DTS activity
and Job activity and everything appears successful but no files are written.
Has anyone ran into this same problem.
These are the items I have thouroughly checked out:
The paths are correct (because the DTS package works)
The owner of the DTS has privleges to write(because the DTS package works)
The Owner of the Job has privileges to write(because the log activity shows
the steps in the DTS running)
Thanks for your help in advance.
--
Thanks Kllyj64BTW. Both the Job and the DTS package are running on the actual server, so I
am not having a problem about "where I am executing the objects."
--
Thanks Kllyj64
"kllyj64" wrote:
> Hello gang, can someone help me with a problem?
> I have a DTS package that uses ActiveX script to copy and rename files. I
> can run this DTS package manually and the files are renamed and copied.
> However, if I schedule this package with a job, the job runs successfully but
> the files are not copied and renamed. I have logged both the DTS activity
> and Job activity and everything appears successful but no files are written.
> Has anyone ran into this same problem.
> These are the items I have thouroughly checked out:
> The paths are correct (because the DTS package works)
> The owner of the DTS has privleges to write(because the DTS package works)
> The Owner of the Job has privileges to write(because the log activity shows
> the steps in the DTS running)
> Thanks for your help in advance.
>
> --
> Thanks Kllyj64|||I found the problem, SQL jobs runs under NT and does not recognize mapped
drives. I changed the DTS ActiveX scripts to use UNC drives and the job
worked. I should have looked further before posting.
--
Thanks Kllyj64
"kllyj64" wrote:
> BTW. Both the Job and the DTS package are running on the actual server, so I
> am not having a problem about "where I am executing the objects."
> --
> Thanks Kllyj64
>
> "kllyj64" wrote:
> > Hello gang, can someone help me with a problem?
> >
> > I have a DTS package that uses ActiveX script to copy and rename files. I
> > can run this DTS package manually and the files are renamed and copied.
> > However, if I schedule this package with a job, the job runs successfully but
> > the files are not copied and renamed. I have logged both the DTS activity
> > and Job activity and everything appears successful but no files are written.
> > Has anyone ran into this same problem.
> >
> > These are the items I have thouroughly checked out:
> > The paths are correct (because the DTS package works)
> > The owner of the DTS has privleges to write(because the DTS package works)
> > The Owner of the Job has privileges to write(because the log activity shows
> > the steps in the DTS running)
> >
> > Thanks for your help in advance.
> >
> >
> > --
> > Thanks Kllyj64