build sql script: dts

Showing posts with label dts. Show all posts

Tuesday, March 27, 2012

Bulk Load Help - Parent Child relationship in schema

I am running a bulk load from a DTS package. I am able to insert the parent records succesfully into the article table, but I cannot get the child data to insert at all. The package returns an "executed succesfully" message, so I am having a tough time debugging. Here is my schema file, and a sample of the XML file I am trying to load. Please let me know if you spot anything in the schema I can fix (the xml file is supplied to me, and I have no control over it) All I am trying to insert in the child table (xmlTopic) is the Article_ID and the Topic_Type. Thanks in advance.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="ArticleTopic"
parent="xmlARTICLE"
parent-key="ARTICLE_ID"
child="xmlTOPIC"
child-key="ARTICLE_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="ARTICLE" sql:relation="xmlArticle" sql:key-fields="ARTICLE_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="ATTRIBUTION" type="xs:string" sql:field="ATTRIBUTION"/>
<xs:element name="BLURB" type="xs:string" sql:field="BLURB"/>
<xs:element name="BODY" type="xs:string" sql:field="ARTICLE_BODY"/>
<xs:element name="BRAND" type="xs:string" sql:field="BRAND"/>
<xs:element name="BYLINE" type="xs:string" sql:field="BYLINE"/>
<xs:element name="COPYRIGHT" type="xs:string" sql:field="COPYRIGHT"/>
<xs:element name="FEATURE_BLURB" type="xs:string" sql:field="FEATURE_BLURB"/>
<xs:element name="FEATURE_IMAGE" type="xs:string" sql:field="FEATURE_IMAGE"/>
<xs:element name="HEADLINE" type="xs:string" sql:field="HEADLINE"/>
<xs:element name="NEWS_TYPE" type="xs:string" sql:field="NEWS_TYPE"/>
<xs:element name="SOURCE" type="xs:string" sql:field="SOURCE"/>
<xs:element name="TEASER" type="xs:string" sql:field="TEASER"/>
<xs:element name="URL" type="xs:string" sql:field="URL"/>
<xs:element name="TOPICS" sql:mapped="false">
<xs:complexType>
<xs:sequence>
<xs:element name="TOPIC" sql:relation="xmlTopic" sql:relationship="ArticleTopic" sql:key-fields="xmlTopicID">
<xs:complexType>
<xs:attribute name="TOPIC_TYPE" type="xs:string" sql:field="TOPIC_TYPE"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ARTICLE_ID" type="xs:string" sql:field="ARTICLE_ID"/>
<xs:attribute name="POSTING_DATE" type="xs:string" sql:field="POSTING_DATE"/>
<xs:attribute name="ARCHIVE_DATE" type="xs:string" sql:field="ARCHIVE_DATE"/>
</xs:complexType>
</xs:element>
</xs:schema>

<?xml version="1.0"?>
<!DOCTYPE NEWSFEED SYSTEM "http://www.blah.com">
<NEWSFEED>
<ARTICLE ARTICLE_ID="531320" POSTING_DATE="08-Mar-2006" ARCHIVE_DATE="01-Mar-2007">
<NEWS_TYPE>blah</NEWS_TYPE>
<HEADLINE><![CDATA[blah]]></HEADLINE>
<BLURB><![CDATA[blah]]></BLURB>
<BYLINE><![CDATA[blah]]></BYLINE>
<BODY><![CDATA[blahblahblah]]></BODY>
<ATTRIBUTION><![CDATA[blah]]></ATTRIBUTION>
<SOURCE><![CDATA[blah]]></SOURCE>
<FEATURE_BLURB><![CDATA[blah]]></FEATURE_BLURB>
<FEATURE_IMAGE></FEATURE_IMAGE>
<TEASER><![CDATA[blah]]></TEASER>
<COPYRIGHT><![CDATA[blah]]></COPYRIGHT>
<BRAND><![CDATA[vlah]]></BRAND>
<URL><![CDATA[blah]></URL>
<TOPICS>
<TOPIC TOPIC_TYPE="BLPR"/>
<TOPIC TOPIC_TYPE="BLPR2"/>
</TOPICS>
</ARTICLE>
</NEWSFEED>

Well it turns out I answered my own question. I checked the MSDN Library(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp - see sql:relationship and the Key Ordering Rule), and found the following info:

"This means that in defining the <Customer> element, you must specify the CustomerID attribute in the schema before you specify <sql:relationship>. Otherwise, when an <Order> element enters into scope, XML Bulk Load generates a record for the CustOrder table; and when the XML Bulk Load reaches the </Order> end tag, it sends the record to SQL Server without the CustomerID foreign key column value."

So I put my sql:relationship at the end of the schema file, and it worked like a charm. As reference, here is my revised schema file:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="ARTICLE" sql:relation="xmlArticle">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ATTRIBUTION" type="xsd:string" sql:field="ATTRIBUTION"/>
<xsd:element name="BLURB" type="xsd:string" sql:field="BLURB"/>
<xsd:element name="BODY" type="xsd:string" sql:field="ARTICLE_BODY"/>
<xsd:element name="BRAND" type="xsd:string" sql:field="BRAND"/>
<xsd:element name="BYLINE" type="xsd:string" sql:field="BYLINE"/>
<xsd:element name="COPYRIGHT" type="xsd:string" sql:field="COPYRIGHT"/>
<xsd:element name="FEATURE_BLURB" type="xsd:string" sql:field="FEATURE_BLURB"/>
<xsd:element name="FEATURE_IMAGE" type="xsd:string" sql:field="FEATURE_IMAGE"/>
<xsd:element name="HEADLINE" type="xsd:string" sql:field="HEADLINE"/>
<xsd:element name="NEWS_TYPE" type="xsd:string" sql:field="NEWS_TYPE"/>
<xsd:element name="SOURCE" type="xsd:string" sql:field="SOURCE"/>
<xsd:element name="TEASER" type="xsd:string" sql:field="TEASER"/>
<xsd:element name="URL" type="xsd:string" sql:field="URL"/>
<xsd:element name="TOPIC" sql:relation="xmlTopic" sql:relationship="ArticleTopic">
<xsd:complexType>
<xsd:attribute name="TYPE2" type="xsd:string" sql:field="TopicType"/>
<xsd:attribute name="TOPIC_TYPE" type="xsd:string" sql:field="TopicID"/>
<xsd:attribute name="SIGNIFICANCE_FACTOR" type="xsd:string" sql:field="SignificanceFactor"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ARTICLE_ID" type="xsd:string" sql:field="ARTICLE_ID"/>
<xsd:attribute name="POSTING_DATE" type="xsd:string" sql:field="POSTING_DATE"/>
<xsd:attribute name="ARCHIVE_DATE" type="xsd:string" sql:field="ARCHIVE_DATE"/>
</xsd:complexType>
</xsd:element>

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ArticleTopic"
parent="xmlArticle"
parent-key="ARTICLE_ID"
child="xmlTopic"
child-key="ARTICLE_ID" />
</xsd:appinfo>
</xsd:annotation>

</xsd:schema>

sql

bulk load change in 2005?

I have a dts package that does some bulk load operations without a problem in 2000. When trying to convert them to SISS or trying to run the same dts package against the a copy of the database (in 2000 mode) running on a 2005 server I get Bulk load data conversion errors (truncation) for row xxxx....

Has the bulk load process changed in 2005?

My Sproc is using a bulk insert command with a fmt file..

Even when you are using SSIS for this; I think this question is better asked in the databse engine forum...|||

Daveko wrote:
Has the bulk load process changed in 2005?

Yes, there have been certain changes in the bulk load that may cause seemingly fine SQL Server 2000 scenarios to fail when they run against SQL Server 2005: the engine now enforces stricter data purity checks and additional physical consistency checks for the input data streams both in BCP and BULK INSERT/OPENROWSET.|||

Is there any way to relax those checks? I was under the assumption that running a database in 2000 mode would allow backward compatibility. I am under a huge time crunch now, and at this point my only contingency plan is to keep a 2000 server up for this specific database.

Any assistance would be appreciated..

|||Unfortunately, there is no 'officially' documented way of relaxing those checks. Please contact CSS - they might be able to assist you.|||

What is CSS?

I have cleaned up my data and got the import to run successfully.

|||CSS stands for Customer Service and Support. It used to be called PSS.

bulk load change in 2005?

Has the bulk load process changed in 2005?

My Sproc is using a bulk insert command with a fmt file..

Even when you are using SSIS for this; I think this question is better asked in the databse engine forum...|||

Daveko wrote:
Has the bulk load process changed in 2005?

Any assistance would be appreciated..

|||Unfortunately, there is no 'officially' documented way of relaxing those checks. Please contact CSS - they might be able to assist you.|||

What is CSS?

I have cleaned up my data and got the import to run successfully.

|||CSS stands for Customer Service and Support. It used to be called PSS.

Sunday, March 25, 2012

Bulk Insert with Added Columns

Hi, I'd like to do a bulk copy insert inside a dts package but the table i'm
inserting to has more columns than the text file. I know you can change the
format file to take care of this, but how do you insert actual values into
those extra columns instead of just NULLS? The values for those extra column
s
are uniquely identified by the text file.
What I could do is do a bulk insert and then run an execute sql command
afterwards which updates the table by replacing the NULL values with actual
values. However, this db is multi-user with each user having different value
s
to insert, so if two imports are done at the same time, someone could be
updating the rows of another person's import. Locking the table is also not
a
possibility because the imports take a long time and we don't want one user
waiting on another one to finish.
Right now, I'm using an ActiveX script in a transform data task in the DTS
package. So there's two transformations going on in that task: one is the
copy column from the text file, and another is adding values to the extra
columns. This works well, however, it requires row by row processing and thu
s
it takes over 3 times longer than a bulk insert.
Does anyone have a solution to setting values of extra columns in a bulk
insert? Any help is appreciated. Thanks!Where do those extra values come from?
ML|||These extra values are global variables in the dts package
"ML" wrote:

> Where do those extra values come from?
>
> ML|||In that case you can design a set-based solution. You need something like th
is:
1) you need access to the file - one way to achieve this is linking the file
as a linked server. Read more on this here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
http://msdn.microsoft.com/library/d...ma_ini_file.asp
2) you need access to your global variables - the best way would be to
create a procedure that accesses the linked file and accepts the values of
those global variables as parameters;
3) in your procedure you then combine the values from the source file and
the values from the DTS, and insert them into the destination table(s).
You could even use a folder-sniffer to start the job automatically, as soon
as the source file becomes available in the source folder.
ML

bulk insert with a primary key?

i'm using sql2k.
can i do a bulk isnert operation to a table with a primary key
(identity field) on it? i suspect the dts pacakge didn't utilize the
bulk insert because pk is automatically a non-clustering index, and
bulk insert can only work on table w/o any index. in this case, what
should i do to make sure the fastest load possible?
thank you.> bulk insert can only work on table w/o any index.
Since when? I have several applications where BULK INSERT affects a table
with a clustered index on a datetime column and a non-clustered index on a
foreign key column. The only way it differs from your scenario is that all
the data is in the file (there is no surrogate column generated by the
system).

> in this case, what
> should i do to make sure the fastest load possible?
As long as the generation of the IDENTITY values does not need to correspond
directly 1:1 with the physical order of the file, you may wish to bulk
insert into a heap, and then insert real_table(column_list) select * from
heap.
A|||It is not true that bulk insert will only work with non-indexed tables.
In fact, you can achieve better throughput, if you have a clustered index on
the table, and input file is also sorted in the same order as the clustered
index.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1123518093.288607.254270@.g14g2000cwa.googlegroups.com...
> i'm using sql2k.
> can i do a bulk isnert operation to a table with a primary key
> (identity field) on it? i suspect the dts pacakge didn't utilize the
> bulk insert because pk is automatically a non-clustering index, and
> bulk insert can only work on table w/o any index. in this case, what
> should i do to make sure the fastest load possible?
> thank you.
>

Bulk Insert Utility

I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.
SubhasishHi!

Bulk Insert task is a kind of task that can be included into a DTS package.
I believe you CANT import from an excel file using an stored procedure, you have to create a DTS package

see ya
Lorena

Originally posted by subhasishray
I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.

Subhasish|||Can I do it with the help of BCP?|||No, it needs to be a csv (or some delimited) or fixed width file...|||So either save the xls as csv or call the dts package from your stored procedure. Any reason why you do not want to use dts ?

Sunday, March 11, 2012

Bulk Insert Import Question

Hi:
I am importing data from text files in sql tables using bulk insert object
in dts package. First row of text files contains headings. For some text
files, headings are also imported in the tables and for some headings are no
t
imported. I am not sure why is that happening.
I do not want to import headings. Is there anyway to tell bulk insert
object to not import first row (headings)? Please let me know.
Thanks a lotBULK INSERT [YourTable] FROM 'c:\yourfile.txt'
WITH (FIRSTROW = 5)
Where 5 indicates "start importing from the 5th row of text". Adjust for
your import as necessary. Don't use quotation marks around the number.
"Mike" wrote:

> Hi:
> I am importing data from text files in sql tables using bulk insert object
> in dts package. First row of text files contains headings. For some text
> files, headings are also imported in the tables and for some headings are
not
> imported. I am not sure why is that happening.
> I do not want to import headings. Is there anyway to tell bulk insert
> object to not import first row (headings)? Please let me know.
> Thanks a lot|||Thank you very much
"Mark Williams" wrote:
> BULK INSERT [YourTable] FROM 'c:\yourfile.txt'
> WITH (FIRSTROW = 5)
> Where 5 indicates "start importing from the 5th row of text". Adjust for
> your import as necessary. Don't use quotation marks around the number.
> --
>
> "Mike" wrote:
>

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know.

|||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Bulk Insert from CSV - trouble with .FMT

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

I have tried '","' (single quote doublequote comma) to no avail.

Does anyone know how to resolve this?

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know.

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Saturday, February 25, 2012

BULK INSERT - Urgent

I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........This is a multi-part message in MIME format.
--020900050805040007080207
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
One thing I noticed is that you use "\r\n" as your row terminator in
your DTS package but only "\n" in your BULK INSERT statement. Perhaps
every row in your input file doesn't have a "\n" to terminate but only a
"\r" (from memory, although it's been a while so I could be wrong,
typically text files generated with Unix/Linux tools only use a "\r"
(ASCII 13) to terminate a line). I don't know why \r\n would work in
the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT statement
(like "\n", "\r\n", "\r").
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Sanjay wrote:
>I have a tab delimeted file which i BCP into a temp table using BULK INSERT
>When i use DTS Transform data task i get all 7000 records in the table
>In DTS data transform i choose Column Delimeter is a tab ann row delimeter
>is {CR}{LF}
>When i do Bulk insert in using command below a lot of these records are
>combined with previous records dud to improper row terminators on some records
>How do i make this work in BULK INSERT same way as it works in DTS
>BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
>Pls help..........
>
>
--020900050805040007080207
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>One thing I noticed is that you use "\r\n" as your row terminator
in your DTS package but only "\n" in your BULK INSERT statement.Â
Perhaps every row in your input file doesn't have a "\n" to terminate
but only a "\r" (from memory, although it's been a while so I could be
wrong, typically text files generated with Unix/Linux tools only use a
"\r" (ASCII 13) to terminate a line).Â I don't know why \r\n would work
in the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT
statement (like "\n", "\r\n", "\r").</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Sanjay wrote:
<blockquote cite="midBC983130-5E18-4E77-92BB-0FFC20421FAA@.microsoft.com"
type="cite">
<pre wrap="">I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........
</pre>
</blockquote>
</body>
</html>
--020900050805040007080207--|||I have gotten this bulk insert statement to work and all you would have to do
is change the fieldterminator to '\t'. Notice the file I was working with
was CSV and it had "," for field termination. You can try using a FORMAT
file parameter but so far I have been unsuccessful in getting that to work.
You can make a format file from the table using the following code:
EXEC master.dbo.xp_cmdshell 'BCP sqlsrv.dbo.SystemTemp FORMAT -Usa
-Ppassword -N -fc:\TBL_Format.fmt'
Which will make you a format file of the table you are trying to bulk import
into.
---
BULK INSERT [dbo].[SystemTemp]
FROM 'c:\SystemTempIn.csv'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '","',
FIRSTROW = 2,
ROWTERMINATOR = '"\n'
)
"Sanjay" wrote:
> I have a tab delimeted file which i BCP into a temp table using BULK INSERT
> When i use DTS Transform data task i get all 7000 records in the table
> In DTS data transform i choose Column Delimeter is a tab ann row delimeter
> is {CR}{LF}
> When i do Bulk insert in using command below a lot of these records are
> combined with previous records dud to improper row terminators on some records
> How do i make this work in BULK INSERT same way as it works in DTS
> BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
> Pls help..........
>

Friday, February 24, 2012

BULK INSERT

Question: All of my developers (2000) use DTS to load data. However, I jus
t
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...

BULK INSERT

Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...

BULK INSERT

Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...
Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...

bulk insert

I create format file with DTS then I execute the bulk insert statement:
BULK INSERT tableTest FROM 'c:\testData.txt'
WITH (FORMATFILE = 'c:\testFormat.fmt')
but I get error:
string or binary data would be truncated
Why? What could be the reason?
Format file seems to be ok.
Regards,SSimon,
You receive an error like this if the destination column is smaller than the
value you are trying to INSERT.
HTH
Jerry
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>|||You get a "string or binary data would be truncated" when the data you are
trying to insert in a column is longer than the maximum length allowed in
that column. You can compare the format file with the table definition to
see if that is the case. If it seems to be ok, but you still get the error,
please post both the table definition and the format file.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>|||Check the data itself. One of the fields is too long on one of the lines
causing an overflow. Try importing with the Import/Export tool and you shoul
d
find the culprit rather quickly.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"simon" wrote:

> I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>
>|||everything seems OK to me.
FORMAT FILE:
7.0
38
1 SQLCHAR 0 10 "," 1 column1
2 SQLCHAR 0 4 "," 2 column2
3 SQLCHAR 0 10 "," 3 column3
4 SQLCHAR 0 100 "," 4 column4
5 SQLCHAR 0 8 "," 5 column5
6 SQLCHAR 0 8 "," 6 column6
7 SQLCHAR 0 8 "," 7 column7
8 SQLCHAR 0 8 "," 8 column8
9 SQLCHAR 0 8 "," 9 column9
10 SQLCHAR 0 8 "," 10 column10
11 SQLCHAR 0 2 "," 11 column11
12 SQLCHAR 0 2 "," 12 column12
13 SQLCHAR 0 4 "," 13 column13
14 SQLCHAR 0 2 "," 14 column14
15 SQLCHAR 0 8 "," 15 column15
16 SQLCHAR 0 2 "," 16 column16
17 SQLCHAR 0 10 "," 17 column17
18 SQLCHAR 0 2 "," 18 column18
19 SQLCHAR 0 10 "," 19 column19
20 SQLCHAR 0 2 "," 20 column20
21 SQLCHAR 0 100 "," 21 column21
22 SQLCHAR 0 16 "," 22 column22
23 SQLCHAR 0 100 "," 23 column23
24 SQLCHAR 0 16 "," 24 column24
25 SQLCHAR 0 2 "," 25 column25
26 SQLCHAR 0 16 "," 26 column26
27 SQLCHAR 0 2 "," 27 column27
28 SQLCHAR 0 16 "," 28 column28
29 SQLCHAR 0 2 "," 29 column29
30 SQLCHAR 0 16 "," 30 column30
31 SQLCHAR 0 20 "," 31 column31
32 SQLCHAR 0 2 "," 32 column32
33 SQLCHAR 0 8 "," 33 column33
34 SQLCHAR 0 8 "," 34 column34
35 SQLCHAR 0 8 "," 35 column35
36 SQLCHAR 0 2 "," 36 column36
37 SQLCHAR 0 8 "," 37 column37
38 SQLCHAR 0 8 "\r\n" 38 column38
table definition:
CREATE TABLE [T_TABLE] (
[column1] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column2] [int] NOT NULL ,
[column3] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column4] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column5] [float] NOT NULL CONSTRAINT [DF_T_column5] DEFAULT (0),
[column6] [float] NULL CONSTRAINT [DF_T_column6] DEFAULT (0),
[column7] [float] NOT NULL CONSTRAINT [DF_T_column7] DEFAULT (0),
[column8] [float] NOT NULL CONSTRAINT [DF_T_column8] DEFAULT (0),
[column9] [float] NULL CONSTRAINT [DF_T_column9] DEFAULT (0),
[column10] [float] NOT NULL CONSTRAINT [DF_T_column10] DEFAULT (0),
[column11] [smallint] NULL CONSTRAINT [DF_T_column11] DEFAULT (0),
[column12] [Boolean] NOT NULL ,
[column13] [int] NULL ,
[column14] [smallint] NULL CONSTRAINT [DF_T_column14] DEFAULT (0),
[column15] [float] NULL CONSTRAINT [DF_T_column15] DEFAULT (0),
[column16] [Boolean] NOT NULL ,
[column17] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column18] [Boolean] NOT NULL ,
[column19] [varchar] (10) COLLATE Slovenian_CI_AS NULL ,
[column20] [smallint] NULL CONSTRAINT [DF_T_column20] DEFAULT (0),
[column21] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column22] [smalldatetime] NULL ,
[column23] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column24] [smalldatetime] NULL ,
[column25] [Boolean] NOT NULL ,
[column26] [smalldatetime] NULL CONSTRAINT [DF_T_column26] DEFAULT
(getdate()),
[column27] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
[DF__T_column27] DEFAULT ('**'),
[column28] [smalldatetime] NOT NULL CONSTRAINT [DF__T_column28] DEFAULT
(getdate()),
[column29] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
[DF__T_column29] DEFAULT ('**'),
[column30] [smalldatetime] NOT NULL CONSTRAINT [DF_T_column30] DEFAULT
(getdate()),
[column31] [varchar] (20) COLLATE Slovenian_CI_AS NULL ,
[column32] [Boolean] NOT NULL CONSTRAINT [DF_T_column32] DEFAULT (0),
[column33] [float] NULL CONSTRAINT [DF_T_column33] DEFAULT (0),
[column34] [timestamp] NULL ,
[column35] [varbinary] (8) NULL ,
[column36] [char] (2) COLLATE Slovenian_CI_AS NULL ,
[column37] [float] NULL CONSTRAINT [DF_T_column37] DEFAULT (0),
[column38] [float] NULL CONSTRAINT [DF_T_column38] DEFAULT (0),
CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
(
[column1],
[column2]
) ON [PRIMARY]
) ON [PRIMARY]
I just change column names.
And the data file was exported directly from Enterprise manager. One line
looks like:
"_000000281",1156055,"I0121","product",-1,0,39960,0,0,-39960,0,0,0,0,0,0,"AV
",0,"",0,"",,"",,0,2003-01-23
20:50:00,"NS",2000-01-02 09:25:00,"99",2000-01-02
09:25:00,"#NS:2000000281",-1,0,000000018AC09D10,,"",,
If I import this data without format file it works (not for all lines).
If I include only good lines in other file and try with format file, I get
the following error.
Without format file, works.
Regards,
Simon
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eU4cRioyFHA.2960@.tk2msftngp13.phx.gbl...
> You get a "string or binary data would be truncated" when the data you are
> trying to insert in a column is longer than the maximum length allowed in
> that column. You can compare the format file with the table definition to
> see if that is the case. If it seems to be ok, but you still get the
> error, please post both the table definition and the format file.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>|||I think the problem could be with the varbinary column. The implicit
conversion from strings to binaries is not very intuitive. I can quickly
pinpoint the exact problem though.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:eEUNbqwyFHA.1256@.TK2MSFTNGP09.phx.gbl...
> everything seems OK to me.
> FORMAT FILE:
> 7.0
> 38
> 1 SQLCHAR 0 10 "," 1 column1
> 2 SQLCHAR 0 4 "," 2 column2
> 3 SQLCHAR 0 10 "," 3 column3
> 4 SQLCHAR 0 100 "," 4 column4
> 5 SQLCHAR 0 8 "," 5 column5
> 6 SQLCHAR 0 8 "," 6 column6
> 7 SQLCHAR 0 8 "," 7 column7
> 8 SQLCHAR 0 8 "," 8 column8
> 9 SQLCHAR 0 8 "," 9 column9
> 10 SQLCHAR 0 8 "," 10 column10
> 11 SQLCHAR 0 2 "," 11 column11
> 12 SQLCHAR 0 2 "," 12 column12
> 13 SQLCHAR 0 4 "," 13 column13
> 14 SQLCHAR 0 2 "," 14 column14
> 15 SQLCHAR 0 8 "," 15 column15
> 16 SQLCHAR 0 2 "," 16 column16
> 17 SQLCHAR 0 10 "," 17 column17
> 18 SQLCHAR 0 2 "," 18 column18
> 19 SQLCHAR 0 10 "," 19 column19
> 20 SQLCHAR 0 2 "," 20 column20
> 21 SQLCHAR 0 100 "," 21 column21
> 22 SQLCHAR 0 16 "," 22 column22
> 23 SQLCHAR 0 100 "," 23 column23
> 24 SQLCHAR 0 16 "," 24 column24
> 25 SQLCHAR 0 2 "," 25 column25
> 26 SQLCHAR 0 16 "," 26 column26
> 27 SQLCHAR 0 2 "," 27 column27
> 28 SQLCHAR 0 16 "," 28 column28
> 29 SQLCHAR 0 2 "," 29 column29
> 30 SQLCHAR 0 16 "," 30 column30
> 31 SQLCHAR 0 20 "," 31 column31
> 32 SQLCHAR 0 2 "," 32 column32
> 33 SQLCHAR 0 8 "," 33 column33
> 34 SQLCHAR 0 8 "," 34 column34
> 35 SQLCHAR 0 8 "," 35 column35
> 36 SQLCHAR 0 2 "," 36 column36
> 37 SQLCHAR 0 8 "," 37 column37
> 38 SQLCHAR 0 8 "\r\n" 38 column38
> table definition:
> CREATE TABLE [T_TABLE] (
> [column1] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column2] [int] NOT NULL ,
> [column3] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column4] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column5] [float] NOT NULL CONSTRAINT [DF_T_column5] DEFAULT (0),
> [column6] [float] NULL CONSTRAINT [DF_T_column6] DEFAULT (0),
> [column7] [float] NOT NULL CONSTRAINT [DF_T_column7] DEFAULT (0),
> [column8] [float] NOT NULL CONSTRAINT [DF_T_column8] DEFAULT (0),
> [column9] [float] NULL CONSTRAINT [DF_T_column9] DEFAULT (0),
> [column10] [float] NOT NULL CONSTRAINT [DF_T_column10] DEFAULT (0),
> [column11] [smallint] NULL CONSTRAINT [DF_T_column11] DEFAULT (0),
> [column12] [Boolean] NOT NULL ,
> [column13] [int] NULL ,
> [column14] [smallint] NULL CONSTRAINT [DF_T_column14] DEFAULT (0),
> [column15] [float] NULL CONSTRAINT [DF_T_column15] DEFAULT (0),
> [column16] [Boolean] NOT NULL ,
> [column17] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column18] [Boolean] NOT NULL ,
> [column19] [varchar] (10) COLLATE Slovenian_CI_AS NULL ,
> [column20] [smallint] NULL CONSTRAINT [DF_T_column20] DEFAULT (0),
> [column21] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column22] [smalldatetime] NULL ,
> [column23] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column24] [smalldatetime] NULL ,
> [column25] [Boolean] NOT NULL ,
> [column26] [smalldatetime] NULL CONSTRAINT [DF_T_column26] DEFAULT
> (getdate()),
> [column27] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
> [DF__T_column27] DEFAULT ('**'),
> [column28] [smalldatetime] NOT NULL CONSTRAINT [DF__T_column28] DEFAULT
> (getdate()),
> [column29] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
> [DF__T_column29] DEFAULT ('**'),
> [column30] [smalldatetime] NOT NULL CONSTRAINT [DF_T_column30] DEFAULT
> (getdate()),
> [column31] [varchar] (20) COLLATE Slovenian_CI_AS NULL ,
> [column32] [Boolean] NOT NULL CONSTRAINT [DF_T_column32] DEFAULT (0),
> [column33] [float] NULL CONSTRAINT [DF_T_column33] DEFAULT (0),
> [column34] [timestamp] NULL ,
> [column35] [varbinary] (8) NULL ,
> [column36] [char] (2) COLLATE Slovenian_CI_AS NULL ,
> [column37] [float] NULL CONSTRAINT [DF_T_column37] DEFAULT (0),
> [column38] [float] NULL CONSTRAINT [DF_T_column38] DEFAULT (0),
> CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
> (
> [column1],
> [column2]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I just change column names.
> And the data file was exported directly from Enterprise manager. One line
> looks like:
> "_000000281",1156055,"I0121","product",-1,0,39960,0,0,-39960,0,0,0,0,0,0,"
AV",0,"",0,"",,"",,0,2003-01-23
> 20:50:00,"NS",2000-01-02 09:25:00,"99",2000-01-02
> 09:25:00,"#NS:2000000281",-1,0,000000018AC09D10,,"",,
> If I import this data without format file it works (not for all lines).
> If I include only good lines in other file and try with format file, I get
> the following error.
> Without format file, works.
> Regards,
> Simon
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:eU4cRioyFHA.2960@.tk2msftngp13.phx.gbl...
>

Bulk Import Diacritics Issue

Hi There,
When I do a bulk import from a DTS Package into a table the import fails at
record that contains diacritic characters like C, Í and E. The field type is
nvarchar.
Is it a collaction type I need to assign the field?
Do you have any idea how I can bypass this?
Thanks,
Kevin.Hi Kevin
"Kevin Humphreys" wrote:
> Hi There,
> When I do a bulk import from a DTS Package into a table the import fails at
> record that contains diacritic characters like C, Ã? and E. The field type is
> nvarchar.
> Is it a collaction type I need to assign the field?
> Do you have any idea how I can bypass this?
> Thanks,
> Kevin.
>
This seems that you need to change the codepage (-C) or specify a unicode
(-w or -N) parameters for BCP. Check in books online for the values.
John

Bulk Import Diacritics Issue

Hi There,
When I do a bulk import from a DTS Package into a table the import fails at
record that contains diacritic characters like C, and E. The field type is
nvarchar.
Is it a collaction type I need to assign the field?
Do you have any idea how I can bypass this?
Thanks,
Kevin.Hi Kevin
"Kevin Humphreys" wrote:

> Hi There,
> When I do a bulk import from a DTS Package into a table the import fails a
t
> record that contains diacritic characters like C, í and E. The field type
is
> nvarchar.
> Is it a collaction type I need to assign the field?
> Do you have any idea how I can bypass this?
> Thanks,
> Kevin.
>
This seems that you need to change the codepage (-C) or specify a unicode
(-w or -N) parameters for BCP. Check in books online for the values.
John

Sunday, February 19, 2012

Bulk Edit DTS Packages

I have a Whole Bunch of DTS packages that copy data from Database1 to Database2.
I want to quickly make duplicates of all these packages, except that they will copy data from Database1 to Database3. Everything else stays the same.
Is there an easy way to do this?Refer to this SQL DTS (http://www.sqldts.com/default.aspx?6,105,204,0,0) link.

Or you can use COPY DATABASE wizard to accomplish the task.

Bulk Copy from one SQL Server to another SQL Server

Hi,

How can i use DTS for Bulk Copy from one SQL Server to another SQL Server?

Thank you,

Regards,

Palak Shah

Rather DTS use Replication..

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

http://msdn2.microsoft.com/en-us/library/aa237426(SQL.80).aspx

|||

Hi Palak,

There are various ways to do what you want. As suggested you could use Replication, however, to get the best answer for your scenario, what are the SQL versions of both the host and target server?

|||Actually I want to transfer Data from one DB to another provided that both are SQL Server 2000 with Target and Source at different Location.|||

If that is the case, you could use the sp_AddLinkedServer system stored procedure to register the remote server (providing you have the appropriate security principles in place) and execute your dts package under a transaction.

See here for an example of sp_AddLinkedServer;

http://www.codeproject.com/cs/database/Distributed_Servers.asp

Here for using DTS with transactions;

http://doc.ddart.net/mssql/sql2000/html/dtssql/dts_addf_tx_660j.htm

Also please note that Books online has extensive documentation on what you are trying to do, as does the following site(s);

http://msdn2.microsoft.com/en-gb/sql/aa336304.aspx

http://www.microsoft.com/technet/prodtechnol/sql/community/default.mspx

Sunday, February 12, 2012

Building a mailing list

Hi folks,

I'm building a DTS package which needs to mail a list of users nightly

The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.

I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.

The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3...etc).

I'm a bit unsure on how to go about doing / writing this.

Can anyone help?

Thanks,

Davethis would be far easier if you trashed the dts, built a stored procedure and then created a job to run the procedure.|||Thought about that but my Stored Proc skills are nothing to write home about. If you have anything I could tweak to suit my needs I'd be grateful.

Thanks,

Dave|||Try running this sql through Query Analyzer:declare @.MyEmailString varchar(8000)

select @.MyEmailString = coalesce(@.MyEmailString + ',', '') + coalesce(email, '')
from employees
where role = 'MGR'

select @.MyEmailString|||Thanks Blindman.

Still trying to get my head around the syntax and use of Coalesce but it works.

Thanks again,

Dave|||Hi Blindman,

I need to run the code below against a MySql database.
It works fine when I modify it for an MS SQL db however I'm getting syntax errors when running it against a MySQL (5.1.9) db.

Any ideas?

Thanks,

Dave|||I wouldn't be surprised if this syntax didn't work in MySQL. Try posting your question in the MySQL forum.|||Lord, where's R937 when somebody mentions his favorite toy! You probably want the GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html) function in MySQL.

<Afterthought>
After posting this, I realized that Rudy would probably pitch a fit, mostly because he enjoy's pitching fits. Note that I do NOT consider MySQL to be a toy, Rudy just likes to play with it because MySQL offers functions that violate the basic rules of relational algebra (such as the GROUP_CONCAT() function). This violation of hte rules doesn't make MySQL a bad product in any way, although I see it as removing MySQL from the category of Relational databases since it doesn't follow the basic rules required for a relational database.

-PatP

Friday, February 10, 2012

Build Query button in DTS 2000 Package Designer

When trying to modify a DTS package in DTS 2000 Package Designer in SQL Server 2005, the Build Query button on a Transform SQL Data Task box is not enabled.

Does anyone know why?

If its not a complex package then why don't you try build in SSIS.

Tuesday, March 27, 2012

Bulk Load Help - Parent Child relationship in schema

bulk load change in 2005?

bulk load change in 2005?

Sunday, March 25, 2012

Bulk Insert with Added Columns

bulk insert with a primary key?

Bulk Insert Utility

Sunday, March 11, 2012

Bulk Insert Import Question

Bulk Insert from CSV - trouble with .FMT

Bulk Insert from CSV - trouble with .FMT

Saturday, February 25, 2012

BULK INSERT - Urgent

Friday, February 24, 2012

BULK INSERT

BULK INSERT

BULK INSERT

bulk insert

Bulk Import Diacritics Issue

Bulk Import Diacritics Issue

Sunday, February 19, 2012

Bulk Edit DTS Packages

Bulk Copy from one SQL Server to another SQL Server

Sunday, February 12, 2012

Building a mailing list

Friday, February 10, 2012

Build Query button in DTS 2000 Package Designer

build sql script

Blog Archive

About Me