Flat File Data Source with variable number of delimited columns

I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).

Is it possible to use a single flat file data source that will work with all of the different width files?
No.

The only thing you can do is read in each line as one big record and then maybe use substrings or something to pick apart the files.

First Timer Having an Insert Problem

Ok...I am stumped

I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.

What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.

The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.

I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.

Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.

Thank you in Advance!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

LoopDo you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.

It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.

blindman|||Thanks a lot Blindman!!

This seems to have worked!! I would have never found that solution.

thanks again,

TRoche|||"First timer having an insert problem."?

Maybe that's why you always remember your first....

First Timer having an INSERT Problem

Ok I am stumped....

I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.

This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.

To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.

I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.

Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.

I have included the script for my insert loop and an example of how the data looks when it is in error.

I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

Loop

Time(x) GPSx Time(y) GPS(y)
58.9447 383421.96 58.94497 1213470.912 58.94526 488
58.99134 383421.959 58.99162 1213470.912 58.99191 489
59.04329 383421.957 59.04356 1213470.912 59.04385 490
59.0951 383421.956 59.09538 1213470.912 59.09566 490
59.14204 383421.955 59.14231 1213470.912 59.1426 490
221.19337 383447.07 221.19364 1213349.901 221.19393 479
221.24045 383447.069 221.24072 1213349.895 221.241 479
221.29253 383447.068 221.2928 1213349.889 221.29308 479
221.34434 383447.067 221.34461 1213349.883 221.3449 478'************************************************* **********
' Visual Basic ActiveX Script
'************************************************* **********

Function Main()

dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim rc

' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection")
set RSSQL = CreateObject("ADODB.Recordset")

'OPEN DATA CONNECTION
ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"

'create a select statement and put into temp table in the
'format that you are after from the three tables
'I assume you can do that
'Once you have all those records, then just insert them
'from the temp table

strSQL = "SELECT * into #temp from table " & _
"insert into newtable(f1, f2, f3, f4)" & _
"select f1, f2, f3, f4 from #temp"

RSSQL.Open strSQL, ConnSQL1
ConnSQL1.execute strSQL
RSSQL.close
Main = rc
End Function

First time writing, string manipulation?

I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.

For Example:
"GOLF IS FUN,BOWLING IS GREAT"

What Id like to get as my results:

"Golf is fun, Bowling is great"

Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.Hi

Welcome to the forum :D

By coincidence I was tootling around Vyas's site earlier today and noticed his proper case function.
http://vyaskn.tripod.com/code.htm#propercase

I am confess I have not used it in anger. I would not be surprised if an extended sproc might be a little faster. There is one optimisation I would do in the first place - one single SELECT @.var = 'this', @.other_var = 'that' rather than multiple SETs.

Anyhoo - see how you get on.

HTH|||Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g. PATINDEX('%, %', MyCol)|||CREATE Proc sp_Parsing( @.String Varchar(255))
AS
Declare @.Recepient Varchar(100)
Declare @.Comma Int
DEclare @.OutPut varchar(8000)
create Table #Test(Names Varchar(55))

set nocount on

While @.String is not null
Begin
Set @.Comma = Patindex('%,%',@.String)
if @.Comma <> 0
Begin
Select @.recepient = Ltrim(Rtrim(Substring(@.string,1,(@.Comma - 1))))
Select @.String = Ltrim(Rtrim(Substring(@.String,(@.Comma + 1),255)))

Insert Into #Test
Values(@.Recepient)
Continue
End
Else
Begin
Select @.recepient = @.string
Select @.String = null
Insert Into #Test
Values(@.Recepient)
Break
End
End
set @.OutPut = ''
Select @.OutPut = @.OutPut + Upper(substring(Names,1,1)) + Lower(Substring(Names,2, Len(Names))) + ',' from #test
Select substring(@.OutPut,1, len( @.OutPut) -1)
Drop Table #Test
GO

Exec sp_Parsing 'GOLF IS FUN,BOWLING IS GREAT'|||string manipulation is pretty much always better done in compiled code, not sql, as pootle suggests.

what sql is good at is set based operations.

First attempt at Stored Procedure - can anyone offer advice

SQL SERVER 2000

Hi all

This is my first attempt at writing a stored procedure. I have managed to
get it working but its unlikely to be the best way of handling the problem.
While writing it I found some things that I don't understand so if any one
could shed any light it would be much appreciated. I have posted these at
the end.

Sorry about the length but I thought it might be worthwhile posting the code

The purpose of the procedures is as follows : we have a view of lots of bits
of information that need automatically mailing to different people. each
element of information has a name allocated against it. If we had 100 pieces
of data, 50 could go to manager 1 25 could go to manager 2 and 25 to manager
3 etc...

Both SP's look at the same view

The first SP generates a distinct list of managers and for each manager
calls the second SP

The second SP filters the view for the data belonging to the selected
manager, and builds an HTML mail. It then sends all the bits of information
belonging to that manager off in an EMAIL to him/her. ( It uses a brilliant
bit of code from sqldev.net to handle the mail)

the first mail then repeats for all the managers in the list

CODE -- SP 1
ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2
AS
begin
SET NOCOUNT ON
declare @.no_of_managers as int
declare @.current_record as int
declare @.manager_name as varchar(100)

-- count how many distinct managers we need to send the mail to
select @.no_of_managers = COUNT(DISTINCT manager_name) FROM
dbo.vw_client_notification_email_1

-- open a cursor to the same distinct list
declare email_list cursor for select distinct manager_name from
dbo.vw_client_notification_email_1 dsc
open email_list

-- for each distinct manager get the managers name and pass it to the stored
procedure that generates the mail.
set @.current_record = 0
while (@.current_record) < @.no_of_managers
begin
fetch next from email_list into @.manager_name
EXECUTE dbo.pr_admin_client_weekly_notification @.manager_name
set @.current_record = @.current_record+1
end
-- close the cursor
close email_list
deallocate email_list
end

CODE -- SP2
ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION
(@.current_manager_name as varchar(100))
-- a unique managers name is passed from the calling procedure
as begin
SET NOCOUNT ON
-- declarations for use in the stored procedure
DECLARE @.to as varchar(100)
DECLARE @.entry varchar(500)
DECLARE @.region as varchar(100)
DECLARE @.type as varchar(100)
DECLARE @.site_ref as varchar(100)
DECLARE @.aborted as varchar(100)
DECLARE @.weblink as varchar(1000)
DECLARE @.manager_name as varchar(100)
DECLARE @.manager_email as varchar(100)
DECLARE @.body VARCHAR(8000)
DECLARE @.link varchar(150)
DECLARE @.web_base VARCHAR(150)

-- set up a connection to the view that contains the details for the mail

DECLARE email_contents cursor for select region,type,
site_ref,aborted_visit,link,manager_name,manager_e mail from
vw_client_notification_email_1 where manager_name = @.current_manager_name
open email_contents
--some initial text
set @.body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),
getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to log
on to xxxxx </a><br><br> '
--fetch the first matching record from the table and build the body of the
message
fetch next from email_contents into
@.region,@.type,@.site_ref,@.aborted,@.link,@.manager_na me,@.manager_email
set @.web_base = 'http://'
set @.weblink = @.web_base + @.link
if @.aborted = 0 set @.aborted = '' else set @.aborted = 'ABORTED'
set @.body = @.body + '<font size="2"><b> Region </b>' + @.region
+ ' <b>Type</b> ' + @.type
+ ' <b>Site Reference </b> <a href = "' + @.weblink + '">' + @.site_ref+
'</a>'
+ ' <b>Unique Report Reference </b>' + @.link + '<br>'

-- continue reading the records for this particular message and adding on to
the body of the text
while(@.@.fetch_status = 0)
begin
fetch next from email_contents into
@.region,@.type,@.site_ref,@.aborted,@.link,@.manager_na me,@.manager_email
if @.aborted = 0 set @.aborted = '' else set @.aborted = 'ABORTED'
if (@.@.fetch_status = 0) set @.body = @.body + '<b> Region </b>' + @.region
+ ' <b>Type</b> ' + @.type
+ ' <b>Site Reference </b> <a href = "' + @.weblink + '">' + @.site_ref+
'</a>'
+ '<b>Unique Report Reference </b>' + @.link + '<br>'
end

-- close the cursor
set @.body = @.body + '</font>'
close email_contents
deallocate email_contents
-- generate the mail
DECLARE @.rc int EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'FROM ME',
@.TO = @.manager_email,
@.server = N'server',
@.subject = N'Weekly Import',
@.message = @.body,
@.type = N'text/html'

end

Questions

is the way I've done it OK. I thought I would be able to do it in a single
SP but I really struggled nesting the cursor things.

@.@.fetchstatus seems to be global, so if your using nested cursors, how do
you know which one you are refering to. If you have multiple calls to the
same SP how does it know which instance of the SP it refers to.

When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn't
get the while loop working - I have a feeling it was down to the @.@.
fetchstatus in the 'calling' procedure being overwritten by the
@.@.fetchstatus in the 'called' procedure.

The whole @.@.fetchatus thing seems a bit odd. In the second procedure, I have
to fetch, then check, manipulate then fetch again, meaning that the same
manipulation code is written twice. thats why in the first procedure I used
the select distint count to know how long the record set is so I only have
to run the manipulation code once. Is what I have done wrong?

its possible that the body of the mail could be > 8K, is there another
datatype I can use to hold more than 8K

many thanks for any help or advice

Andy"aaj" <a.b@.c.com> wrote in message
news:40236978$0$15408$afc38c87@.news.easynet.co.uk. ..
> SQL SERVER 2000
> Hi all
> This is my first attempt at writing a stored procedure. I have managed to
> get it working but its unlikely to be the best way of handling the
problem.
> While writing it I found some things that I don't understand so if any one
> could shed any light it would be much appreciated. I have posted these at
> the end.
> Sorry about the length but I thought it might be worthwhile posting the
code

<snip
> Questions
> is the way I've done it OK. I thought I would be able to do it in a single
> SP but I really struggled nesting the cursor things.
> @.@.fetchstatus seems to be global, so if your using nested cursors, how do
> you know which one you are refering to. If you have multiple calls to the
> same SP how does it know which instance of the SP it refers to.
> When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn't
> get the while loop working - I have a feeling it was down to the @.@.
> fetchstatus in the 'calling' procedure being overwritten by the
> @.@.fetchstatus in the 'called' procedure.
> The whole @.@.fetchatus thing seems a bit odd. In the second procedure, I
have
> to fetch, then check, manipulate then fetch again, meaning that the same
> manipulation code is written twice. thats why in the first procedure I
used
> the select distint count to know how long the record set is so I only have
> to run the manipulation code once. Is what I have done wrong?
> its possible that the body of the mail could be > 8K, is there another
> datatype I can use to hold more than 8K
>
> many thanks for any help or advice
> Andy

I must admit I didn't read your code in detail, but I'm not sure why you
need two procedures. The inner one appears to go through every row in
dbo.vw_client_notification_email_1, so I don't see the benefit of the outer
one (unless perhaps you removed some code to simplify it). Also, the outer
procedure seems to use a counter to find the end of the cursor, but
@.@.FETCH_STATUS will tell you when you have reached the end of the cursor
anyway.

As for the data type, there are larger data types available (text and
ntext), but xp_smtp_sendmail doesn't support them. If you need to send large
emails, I would consider using an external script instead of pure SQL code -
it's much easier to create attachment files, do text/HTML formatting and
validation etc.If you want to keep control within a procedure, then you
could use xp_cmdshell to call your script, or perhaps just schedule it as a
SQL Agent job.

Simon|||aaj (a.b@.c.com) writes:
> is the way I've done it OK. I thought I would be able to do it in a single
> SP but I really struggled nesting the cursor things.

It could be done in a single SP, but splitting it in two has the
advantage that you don't risk that the value of some variable spill
over from the previous manager.

You could also do it with one cursor only. In this case you would
have something like:

IF @.old_manager IS NOT NULL AND @.manager <> @.old_manager
BEGIN
EXEC master.dbo.xp_sendmail ...
-- Reset all variables.
SELECT @.old_manager = @.manager
END

You could of course have to order the cursor by manager.

> @.@.fetchstatus seems to be global, so if your using nested cursors, how do
> you know which one you are refering to. If you have multiple calls to the
> same SP how does it know which instance of the SP it refers to.

@.@.fetch_status refers to the cursor you last operated on. And if you
check @.@.fetch_status directly after FETCH you are safe. Here is the
idiom for writing a cursor loop:

DECLARE some_cur INSENSITIVE CURSOR FOR
SELECT yadayada

OPEN some_cur

WHILE 1 = 1
BEGIN
FETCH some_cur INTO ...
IF @.@.fetch_status <> 0
BREAK
...
END

DEALLOCATE some_cur

The INSENSITIVE is there, because the default keyset-driven cursors can
sometimes come with nasty suprises.

By only using one FETCH statement your code is easier to maintain; if you
need another column in the cursor, you only have to change in two places.

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

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

I'll have a read in detail and see if I can pick up some tips

Andy

"aaj" <a.b@.c.com> wrote in message
news:40236978$0$15408$afc38c87@.news.easynet.co.uk. ..
> SQL SERVER 2000
> Hi all
> This is my first attempt at writing a stored procedure. I have managed to
> get it working but its unlikely to be the best way of handling the
problem.
> While writing it I found some things that I don't understand so if any one
> could shed any light it would be much appreciated. I have posted these at
> the end.
> Sorry about the length but I thought it might be worthwhile posting the
code
> The purpose of the procedures is as follows : we have a view of lots of
bits
> of information that need automatically mailing to different people. each
> element of information has a name allocated against it. If we had 100
pieces
> of data, 50 could go to manager 1 25 could go to manager 2 and 25 to
manager
> 3 etc...
> Both SP's look at the same view
> The first SP generates a distinct list of managers and for each manager
> calls the second SP
> The second SP filters the view for the data belonging to the selected
> manager, and builds an HTML mail. It then sends all the bits of
information
> belonging to that manager off in an EMAIL to him/her. ( It uses a
brilliant
> bit of code from sqldev.net to handle the mail)
> the first mail then repeats for all the managers in the list
> CODE -- SP 1
> ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2
> AS
> begin
> SET NOCOUNT ON
> declare @.no_of_managers as int
> declare @.current_record as int
> declare @.manager_name as varchar(100)
> -- count how many distinct managers we need to send the mail to
> select @.no_of_managers = COUNT(DISTINCT manager_name) FROM
> dbo.vw_client_notification_email_1
> -- open a cursor to the same distinct list
> declare email_list cursor for select distinct manager_name from
> dbo.vw_client_notification_email_1 dsc
> open email_list
> -- for each distinct manager get the managers name and pass it to the
stored
> procedure that generates the mail.
> set @.current_record = 0
> while (@.current_record) < @.no_of_managers
> begin
> fetch next from email_list into @.manager_name
> EXECUTE dbo.pr_admin_client_weekly_notification @.manager_name
> set @.current_record = @.current_record+1
> end
> -- close the cursor
> close email_list
> deallocate email_list
> end
>
> CODE -- SP2
> ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION
> (@.current_manager_name as varchar(100))
> -- a unique managers name is passed from the calling procedure
> as begin
> SET NOCOUNT ON
> -- declarations for use in the stored procedure
> DECLARE @.to as varchar(100)
> DECLARE @.entry varchar(500)
> DECLARE @.region as varchar(100)
> DECLARE @.type as varchar(100)
> DECLARE @.site_ref as varchar(100)
> DECLARE @.aborted as varchar(100)
> DECLARE @.weblink as varchar(1000)
> DECLARE @.manager_name as varchar(100)
> DECLARE @.manager_email as varchar(100)
> DECLARE @.body VARCHAR(8000)
> DECLARE @.link varchar(150)
> DECLARE @.web_base VARCHAR(150)
> -- set up a connection to the view that contains the details for the mail
> DECLARE email_contents cursor for select region,type,
> site_ref,aborted_visit,link,manager_name,manager_e mail from
> vw_client_notification_email_1 where manager_name = @.current_manager_name
> open email_contents
> --some initial text
> set @.body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),
> getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to
log
> on to xxxxx </a><br><br> '
> --fetch the first matching record from the table and build the body of the
> message
> fetch next from email_contents into
> @.region,@.type,@.site_ref,@.aborted,@.link,@.manager_na me,@.manager_email
> set @.web_base = 'http://'
> set @.weblink = @.web_base + @.link
> if @.aborted = 0 set @.aborted = '' else set @.aborted = 'ABORTED'
> set @.body = @.body + '<font size="2"><b> Region </b>' + @.region
> + ' <b>Type</b> ' + @.type
> + ' <b>Site Reference </b> <a href = "' + @.weblink + '">' + @.site_ref+
> '</a>'
> + ' <b>Unique Report Reference </b>' + @.link + '<br>'
> -- continue reading the records for this particular message and adding on
to
> the body of the text
> while(@.@.fetch_status = 0)
> begin
> fetch next from email_contents into
> @.region,@.type,@.site_ref,@.aborted,@.link,@.manager_na me,@.manager_email
> if @.aborted = 0 set @.aborted = '' else set @.aborted = 'ABORTED'
> if (@.@.fetch_status = 0) set @.body = @.body + '<b> Region </b>' + @.region
> + ' <b>Type</b> ' + @.type
> + ' <b>Site Reference </b> <a href = "' + @.weblink + '">' + @.site_ref+
> '</a>'
> + '<b>Unique Report Reference </b>' + @.link + '<br>'
> end
> -- close the cursor
> set @.body = @.body + '</font>'
> close email_contents
> deallocate email_contents
> -- generate the mail
> DECLARE @.rc int EXEC @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'FROM ME',
> @.TO = @.manager_email,
> @.server = N'server',
> @.subject = N'Weekly Import',
> @.message = @.body,
> @.type = N'text/html'
>
> end
>
>
>
> Questions
> is the way I've done it OK. I thought I would be able to do it in a single
> SP but I really struggled nesting the cursor things.
> @.@.fetchstatus seems to be global, so if your using nested cursors, how do
> you know which one you are refering to. If you have multiple calls to the
> same SP how does it know which instance of the SP it refers to.
> When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn't
> get the while loop working - I have a feeling it was down to the @.@.
> fetchstatus in the 'calling' procedure being overwritten by the
> @.@.fetchstatus in the 'called' procedure.
> The whole @.@.fetchatus thing seems a bit odd. In the second procedure, I
have
> to fetch, then check, manipulate then fetch again, meaning that the same
> manipulation code is written twice. thats why in the first procedure I
used
> the select distint count to know how long the record set is so I only have
> to run the manipulation code once. Is what I have done wrong?
> its possible that the body of the mail could be > 8K, is there another
> datatype I can use to hold more than 8K
>
> many thanks for any help or advice
> Andy