Solved

Tsql 2005 Procedure

Posted on 2010-11-30
25
428 Views
Last Modified: 2012-06-27

I need a procedure that will go to a specified file path. Read the first line which has the today's date and the number of rows in the file after the pipe
example
20101130|100

Then I need to verify that the number of rows matches on the file match what the header says...
0
Comment
Question by:Leo Torres
  • 13
  • 10
  • +1
25 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34238295
hmmm.
this is a bit much for 1 single question on EE (EE is not a rent-a-coder site!)

you should split up the problem into specific issue you have.

* procedure parameter to pass file name
* read file
* split data
* use data

please clarify what the issue is that you have.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34239111

So created a procedure that downloads a file from FTP site..

I was thinking about reading this file and putting the data in a temp table in the procedure. Then do my counts but not sure and verification..

I just need help with getting the file and being able to count the rows in the file. temp table was my first guess but not sure if that is the best way or if it can be done.. Once I complete the verification I will drop the table.

sorry if my questions was a bit vague..

Thank!!
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34239596

Maybe I should have phrased the question this way.. How can I create a table from a file... Given the file location.. Once I have table I can do my operations
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Expert Comment

by:rmm2001
ID: 34240488
Use SSIS.

File source would be the file you downloaded (Flat file). Then add a Row Count component to get how many rows were in your file. Then read the file into your destination table. Or do a row sampling to get the data that you want. You could also use an oledb command to manually insert the data using whatever business rules you want to add into the insert.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34241800
Hi,

if file is fixed format then bulkinsert will do

check this link.

http://msdn.microsoft.com/en-us/library/ms188365.aspx
BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34241944
Actually I did try that.


it but everything in 1 row
gave me this

col1                       col2
20101129           3 Leo | 1 Leo | 2 Test | 3


this is what i in file
20101129 | 3
Leo | 1
Leo | 2
Test | 3

I will use bulk or bcp at this point I just want this to work..

Thanks for the help.. what am  doing wrong??

Here is what I tried
BULK insert dbo.testTable from 'E:\Vendor\Feeds\Tranzact\MatchOfferRates'
With
(
	Fieldterminator = '|',
	RowTerminator = '\n'

)

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34241973
hi,

can you share your file now
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34241983
which file??
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34241991

The one you importing

E:\Vendor\Feeds\Tranzact\MatchOfferRates
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34241993
data file?

This is the datafile
This is what in the file..Its a dummy file I created

20101129 | 3
Leo | 1
Leo | 2
Test | 3
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34242047
Hi,

problem is your row terminator is not proper.
Just open file in wordpad and just save.

file should be attached format.
TEST.txt
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34242057
sorry this one
test.txt
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34242232

Think I know where your getting at I see you removed the last cartridge return but I used your exact file CTRL+A then pasted it to my file executed and gave me the same thing
sampleFile.txt
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34242313
hi,

i just import your file with ur code.
its working...

your table contains 2 columns....right??

it should work....

which sql version you using
TEST.txt
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34242347
SQl 2005

Useing this to run Code

exec dbo.sp_DynamicTable 'E:\Vendor\Feeds\Tranzact\20101130.Sapient.TranzactMatchOfferRates'
/*
Created: Edmond Fox
Purpose: Creates Dynamic tables 
Edited By: Leo Torres
*/
Alter Procedure dbo.sp_DynamicTable(
@FilePath varchar(max),
@alertDistro varchar(255) = 'ltorres@sapient.com'--'msolares@sapient.com; embridgetrackrbc@sapient.com; embridgetracksupport@sapient.com'

)
AS

BeGIN TRY

declare @filename		varchar(max),
		@myStr			varchar(1500), 
		@countStr		varchar(100),
		@cmd			varchar(4000),
		@tableString	varchar(max),
		@tableName		varchar(max),
		@counter		int,
	 	@columnCount	int,
        @caughtError	varchar(8000),
		@sqlString      varchar(max)

set @tableName		= 'testTable'
Set @countStr		= '|'
set @tableString	= 'create table dbo.' + @tableName + ' ( '


Select @filename = Right(@FilePath,CharIndex('\',Reverse(@FilePath))-1)
Print @filename


truncate table Vendor_DataMart.dbo.fileFeeler
SET @cmd ='bcp Vendor_DataMart.dbo.fileFeeler in "' + @FilePath + '" 
			-f "E:\Vendor\bcpFormatFiles\fileFeeler.fmt" -m1 -T -F2 -L2'
Print @cmd
EXEC @myStr = MASTER.DBO.XP_CMDSHELL @cmd, no_output

select top 1 @myStr =  string from Vendor_DataMart.dbo.fileFeeler (nolock)


Select @columnCount = (len(@myStr)-len(replace(@myStr,@countStr,'')))/len(@countStr)+1 

--loop
set @counter = 1
while (@counter <= @columnCount+1)
	begin
		set @tableString = @tableString + 'col' + cast(@counter as varchar(100)) + ' varchar(max), '
		set @counter = @counter + 1
	end

set @tableString = @tableString + ')'
set @tableString = replace(@tableString, ', )',' )')
print @tableString

IF OBJECT_ID('dbo.testTable') Is Not NULL
	Drop table dbo.testTable

exec(@tableString)

set @sqlString ='BULK insert dbo.testTable from '''+ @FilePath +'''
With
(
	Fieldterminator = ''|'',
	RowTerminator = ''\n''

)'
print @sqlString
exec (@sqlString)

END TRY 
BEGIN Catch
select  @caughtError = ERROR_MESSAGE()

	PRINT @caughtError
	EXEC dbo.sp_SQLSMTPMail  
		@vcFrom='notifications@bridgetrack.com', 
		@vcTo = @alertdistro,
		@vcSubject='ALERT!!!! Failed Retrieving Tranzact File data-->sp_DynamicTable', 
		@vcBody=@caughtError,
		@vcBodyType='TextBody'
END Catch 

PRINT 'End --- ' + convert(varchar(20), getDate())


Select * from dbo.testTable

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34242365
hi,

i was trying to solve ur problem now but now need to understand.

and its 1.40 AM,so need to go.

sorry,I will be get back to you in morning.

meanwhile,other experts surely help u.

tc

bye
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34246708

Hi,

E:\Vendor\Feeds\Tranzact\20101130.Sapient.TranzactMatchOfferRates
is ur file name???

can you share this file....

btw can you try to upload my file.

create table with two columns and try to upload....

tell me that is uploading...??
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34247856
i already sent you the file its the test file I sent you...
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34247862
Think I know what the problem is will send to you once in the office
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34283618
did this using bcp much cleaner..

Thanks for the help!!


USE [Vendor_DataMart]
GO
/****** Object:  StoredProcedure [dbo].[sp_DynamicTable]    Script Date: 12/01/2010 14:05:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Created: Edmond Fox
Purpose: Creates Dynamic tables 
Edited By: Leo Torres
*/
ALTER Procedure [dbo].[sp_DynamicTable](
@FilePath varchar(max),
@alertDistro varchar(255) = 'embridgetracksupport@sapient.com'

)
AS

BeGIN TRY

Print 'Running ... Procedure.. sp_DynamicTable'
declare @filename		varchar(max),	@myStr			varchar(1500), 
		@countStr		varchar(100),	@cmd			varchar(4000),
		@tableString	varchar(max),	@tableName      varchar(255),
		@counter		int,			@columnCount	int,
        @caughtError	varchar(8000),	@sqlString      varchar(4000),
		@DummyString    varchar(max),	@formatFile		varchar(max)

truncate table CapitalOne_DataMart.dbo.fileFeeler
IF OBJECT_ID('dbo.DynamicTable') Is Not NULL
	Drop table dbo.DynamicTable
/****************** CREATE IMPORT TABLE ************************/
Set @tableName = 'DynamicTable'
Set @countStr		= '|'
set @tableString	= 'create table dbo.' + @tableName + ' ( '
Select @filename	= Right(@FilePath,CharIndex('\',Reverse(@FilePath))-1)
Print @filename

SET @cmd ='bcp Vendor_DataMart.dbo.fileFeeler in "' + 
				@filepath + '" -f "E:\Vendor\bcpFormatFiles\fileFeeler.fmt" -m1 -T -F2 -L2'

Print @cmd
EXEC MASTER.DBO.XP_CMDSHELL @cmd

--loop
set @counter		= 1
select top 1 @myStr =  string from Vendor_DataMart.dbo.fileFeeler (nolock)
Select @columnCount = (len(@myStr)-len(replace(@myStr,@countStr,'')))/len(@countStr)+1 

while (@counter <= @columnCount)
	begin
		set @tableString = @tableString + 'col' + cast(@counter as varchar(100)) + ' varchar(max), '
		set @counter = @counter + 1
	end


set @tableString = @tableString + ')'
set @tableString = replace(@tableString, ', )',' )')


print @tableString

if @tableString <> 'create table dbo.DynamicTable ( )'
begin 
print 'Im inside Dynamic create'
	exec(@tableString)
print '****************** CREATE BCP FORMAT FILE ************************'
/****************** CREATE BCP FORMAT FILE ************************/
	set @formatFile	= @tablename + '.fmt'

	set @cmd = 'bcp Vendor.dbo.' + @tablename + ' format nul -c -f  E:\Vendor\bcpFormatFiles\' + @formatFile + ' -t^| -r \n -T'
	print @cmd
	EXEC MASTER.DBO.XP_CMDSHELL @cmd

Print '****************** IMPORT DATA ************************'
/****************** IMPORT DATA ************************/
	SET @sqlString ='bcp Vendor_DataMart.dbo.DynamicTable in "'+ @FilePath + '" -f "E:\Vendor\bcpFormatFiles\' + @formatFile + '" -m1 -T'

	Print @sqlString 
	EXEC @DummyString = MASTER.DBO.XP_CMDSHELL @sqlString
	print @DummyString
return 1
END

Else 
Begin
print 'Should not be here on good file'
	Select @caughtError = 'File Can not be processed not in correct Format'
		EXEC dbo.sp_SQLSMTPMail  
		@vcFrom='notifications@bridgetrack.com', 
		@vcTo = @alertdistro,
		@vcSubject='ALERT!!!! Failed Retrieving Tranzact File data-->sp_DynamicTable', 
		@vcBody=@caughtError,
		@vcBodyType='TextBody'

return 0
End

END try

BEGIN Catch

select  @caughtError = 'File needs to be pipe delimited, Process Failed due to file Format. Please verify'--ERROR_MESSAGE()

	PRINT @caughtError
	EXEC dbo.sp_SQLSMTPMail  
		@vcFrom='notifications@bridgetrack.com', 
		@vcTo = @alertdistro,
		@vcSubject='ALERT!!!! Failed Retrieving Tranzact File data-->sp_DynamicTable', 
		@vcBody=@caughtError,
		@vcBodyType='TextBody'
END Catch 



--exec dbo.sp_DynamicTable 'E:\CapitalOne\Feeds\20101117.Vendor.AnalysisRequest'

--Select * from dbo.DynamicTable

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 34289231
sorry buddy...

I'm not clear from your Question.
You asking me or telling me??

If u asking m then yeah bcp is also one of good way(But I didnt know how to use)
I'm using bulk insert as thats simple and plus xp_cmdshell is disabled at our end...
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 34292336
Solution was not working on my end
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34292592
Sorry I ment to close this the last time..
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34292617
hi,

not cleared.
if you need more assistance,please dont close now.
open the Question and we will try to help u
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34293078
no my proc did finally work.. I do appreciate the help!!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question