Change a Views data types

Question

Post reply

Change a Views data types

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 1

Sorry to be Stupid, but what do you mean by

"just 4 numbers"?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

A Little Help Please (12/5/2008)
Sorry to be Stupid, but what do you mean by
"just 4 numbers"?

Nah, me stupid not you, sorry.

SELECT COUNT(*)

FROM dbo.Bargains b

--GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))

-- COUNT(*) = ?

SELECT COUNT(*)

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS NVARCHAR (10)))

--GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))

-- COUNT(*) = ?

SELECT COUNT(*)

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS VARCHAR (10)))

--GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10))

-- COUNT(*) = ?

SELECT COUNT(*)

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID = CAST(b.ClientID AS NVARCHAR (10)))

--GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))

-- COUNT(*) = ?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 3

No problem, the following are the results in order of the statements above:

1632

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Thanks...this tells us that none of the rows in the bargains table have a match in the clients table according to the matching methods we've tried.

Time to retry the INSERT statement:

INSERT INTO dbo.tblClients

(Client_Short_Name, Client_Long_Name, CountryCode, ClientID)

SELECT b.Client, b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10)) AS ClientID

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID = CAST(b.ClientID AS NVARCHAR (10)))

GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 5

I had to put the Collate command in:

INSERT INTO dbo.tblClients

(Client_Short_Name, Client_Long_Name, CountryCode, ClientID)

SELECT b.Client, b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10)) AS ClientID

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS NVARCHAR (10)))

GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))

:sick:

ERROR

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_tblClients'.

Cannot insert duplicate key in object 'dbo.tblClients'.

The statement has been terminated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SELECT *

FROM dbo.Bargains

WHERE CAST(ClientID AS NVARCHAR (10)) IN (

SELECT CAST(b.ClientID AS NVARCHAR (10)) AS ClientID

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS NVARCHAR (10)))

GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))

) --

This query will show that there are rows with the same values of CAST(b.ClientID AS NVARCHAR (10)), but different values of b.Client, b.CountryCode. Have a look at them, decide what you would like to do.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 7

Ok, so they have the same Client ID but differant Client & CountryCode in Baragins.

Can I create a new ClientID with that new entry in tblclients?

Is that a way around it?

Can you suggest a way aroudn this?

One thing that I dont understand, these same tables/views/SP all are the same as in my mirror istance in SQL2000, once i moved it to SQL2005 these errors occured....:doze:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Have a look at the ClientID dupes in the bargains table. Are they really different clients, or do they differ in spelling, or missing country code?

Is this a one-off process, like a migration, or is it a periodical process?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 9

Question

Have a look at the ClientID dupes in the bargains table. Are they really different clients, or do they differ in spelling, or missing country code?

Answer

If I run the following statement:

select client, clientid, CountryCode

from bargains

order by clientid

The following are a few reseult examples:

Client ClientID

LCH BMG5361W1047

PERRY CAPITAL BMG5361W1047

As you can see I have loads of LCH entrys with the same ClientID and then on other entry PERRY CAPITAL with the same ID. This is exspected and is somthing which needs to happen.

Another example results:

Client ClientID

LCH GB0000946276

STANDARD LIFE GB0000946276

MONTANARO INVESTMENT MAN LTD GB0000946276

Your other question

Is this a one-off process, like a migration, or is it a periodical process?

Answer

This is not a one of process. This whole process is a Job.

The Job executes:

1. SSIS Package Import data into 4 temp tables:

Bargains

CheckDate

MKTMKG_PL

2. Runs SP procImportAllFiles

Which updates Bargains, client etc... This is where the error is caused.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Why do you have different clients with the same clientID?

Why call your column ClientID if itdoesn't uniquely identify clients?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

A Little Help Please (12/5/2008)
One thing that I dont understand, these same tables/views/SP all are the same as in my mirror istance in SQL2000, once i moved it to SQL2005 these errors occured....:doze:

It would help if you could identify and describe any changes which may have taken place in the transfer between the two versions. Since you have the same clientID for multiple clients in the bargains table, is it possible that the clientID has been truncated somewhere? What is the column length/type of clientID in the source table for the SSIS package?

SSIS Package Import data into 4 temp tables:
Bargains
CheckDate
CheckDate
MKTMKG_PL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 12

Chris,

I have just recently taken over looking after this DB. It was well before my time so Im not sure why this issue with the ClientID duplication in the bargains table. I am trying to find this out now.

No changes have taklen place between the transfer between the two versions of DB. All I have done is:

- Backup the old 2000DB, restore this on the new SQL2005

- Design a new SSIS to replace the old DTS

- Recreate the jobs

No script changes or table changes have been made.

In the SSIS package column 30 points to ClientID in the Bargains File is set to:

OutputColumnWidth: 20

ColumnDelimiter: {,}

TextQualified: True

DataType: string[DT_STR]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

A Little Help Please (12/8/2008)
Chris,
I have just recently taken over looking after this DB. It was well before my time so Im not sure why this issue with the ClientID duplication in the bargains table. I am trying to find this out now.
No changes have taklen place between the transfer between the two versions of DB. All I have done is:
- Backup the old 2000DB, restore this on the new SQL2005
- Design a new SSIS to replace the old DTS
- Recreate the jobs
No script changes or table changes have been made.
In the SSIS package column 30 points to ClientID in the Bargains File is set to:
OutputColumnWidth: 20
ColumnDelimiter: {,}
TextQualified: True
DataType: string[DT_STR]

Investigate the table which is the source for the clientID column and the client name column in the bargains table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 14

Chris, I think I have spotted the error, as you suggested the problem was within SSIS package and the mapping between the FlatFile and the table.

But.... when I run the SSIS package I now get the error:

Error: 0xC002F210 at ProcImportAllFiles, Execute SQL Task: Executing the query "exec

procImportAllFiles" failed with the following error: "Import Updates Failed". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set

correctly, or connection not established correctly.

Task failed: ProcImportAllFiles

Warning: 0x80019002 at Package: SSIS Warning Code

DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number

of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the

number of errors reaches the number specified in MaximumErrorCount. Change the

MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

But... when I run the SP from SQL Server Management Studio line by line it works!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

You're importing from a flat file to the table bargains? Is this the only step performed by SSIS?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden