August 18, 2008 at 1:08 pm
For crying out load - use BCP. It is not "not format friendly" what it is, is beyond your currently skill level. This can be changed by doing some reading. You asked the question on Thursday, it was answered pretty much instantly. You certainly are allowed to disagree but when your findings correspond with what was said on the forums, accept the fact that that is how it is.
In the 3 days you have been trying to use a solution which is not fit for purpose, you could have become a master at BCP. BCP is not hard, it simply requires an understanding. As does SQL, as does SSIS, as does walking.
If you are hell bent on creating a SSIS package to do this, do it programatically. This however requires a very good understanding of both SSIS and VB/C#.net. BCP is the easiest, quickest way to go.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 6:13 pm
bluepaperbag (8/18/2008)
Mr. Jeff,Yes, the 40 tables have different columns but they all have the same createdate and changedate columns which helps for the incremental extract.
The format has to be pipe delimited with a comma. Something like:
|col1|,|col2|,|col3|...
|row1|,|row1|,|row1|...
And what do you mean that you were able to create the files but no data? What are you using for that?
I used SSIS. Dataflow->Foreachloop and a Dataflow inside the Foreachloop. Im just capturing table names with a user variable with the Dataflow and pass it to another map variable then use the Foreachloop to create the .txt files with these names. But seems like SSIS can only create TXT files and it doesnt allow dynamic export of data for individual tables. I read that if the source gets change in the package, the columns remains the same. So I have to recreate a new package for a new table. Basically SSIS won't let me do dynamic export from several tables to TXT files. I'm guessing I will have to use BCP. But BCP is not very format friendly.
I agree with Crispin... BCP woud be the quickest way to get this up and running.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2008 at 12:59 pm
bluepaperbag (8/15/2008)
I BCP THE tables out + data. Now how can I do incremtanl extract like using changeDate = Yesterday?
Sounds like you are trying to track changes?
My first thought would be to create a copy of your database(s) and write a script to create triggers on all tables. Write out any changes from database A to database B. Then, every night run a process (can be a simple stored procedure or a complex SSIS package) to write out and then purge the tables from database B.
Though I am VERY curious as to why you are writing these to a text file. If you scope is so large that you are duplicating all except two tables, why not set up some form of replication? If you are creating audit history, why aren't the transaction logs enough? Maybe you can post the business problem, and the community can point you in the direction of a better solution, instead of looking for what sounds like a "quick fix" to a broken process.
August 20, 2008 at 1:09 am
My first thought would be to create a copy of your database(s) and write a script to create triggers on all tables. Write out any changes from database A to database B. Then, every night run a process (can be a simple stored procedure or a complex SSIS package) to write out and then purge the tables from database B.
My first thought would be to march you out the door if you did that to one of my databases.
To write put a trigger on a table simply because you want to extract date based history. What about the extra overhead? What a waste of server time that would be in a heavy system.
Secondly, space?
To the OP: Please don't put triggers on 40 odd tables simply to achive this task.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 20, 2008 at 6:00 am
Crispin Proctor (8/20/2008)
My first thought would be to create a copy of your database(s) and write a script to create triggers on all tables. Write out any changes from database A to database B. Then, every night run a process (can be a simple stored procedure or a complex SSIS package) to write out and then purge the tables from database B.
My first thought would be to march you out the door if you did that to one of my databases.
To write put a trigger on a table simply because you want to extract date based history. What about the extra overhead? What a waste of server time that would be in a heavy system.
Secondly, space?
To the OP: Please don't put triggers on 40 odd tables simply to achive this task.
LOL. Fair enough...my first thought is a bad idea. Especially re-reading and noticing the OP has a create date and change date on each table.
August 20, 2008 at 8:07 am
well, avoiding SSIS, this seems to work; in order to gett his to work, i had to enable xp_cmdshell, and change the credentials for the account that sql server runs under.
my assumptions were to find the tables which contained both date columns dynamically, but you can obviously change how to find which tables to export.
i also dynamically built the file names to include a timestamp.
tell me what you think:
[font="Courier New"]
/*
--sample tables
--three tables, only two have both columns that are required:
create table tbl1(tbl1id int identity(1,1) primary key,tbltxt varchar(15),createdate datetime,changedate datetime)
create table tbl2(tbl1id int identity(1,1) primary key,tbltxt varchar(15),tbltxt2 varchar(15),
tbltxt3 varchar(15),createdate datetime,changedate datetime)
create table tbl3(tbl1id int identity(1,1) primary key,tbltxt varchar(15),createdate datetime)
--insert sample data
insert into tbl1(tbltxt,createdate,changedate) select 'blah',getdate() -1,getdate()
UNION select 'blah2',getdate() -1,getdate()
insert into tbl2(tbltxt,tbltxt2,tbltxt3,createdate,changedate) select 'blah3','m','w',getdate() -1,getdate()
UNION select 'blah4','m','w',getdate() -1,getdate()
insert into tbl3(tbltxt,createdate) select 'blah',getdate() -1 UNION select 'blah2',getdate() -1
*/
--my dates will be mdy in this example!
SET DATEFORMAT mdy
DECLARE
@table VARCHAR(128),
@filename VARCHAR(128),
@begindate DATETIME,
@enddate DATETIME,
@sql VARCHAR(500)
--dDateAdd(interval, number, date)
SET @begindate = DATEADD(d,-1 ,CONVERT(VARCHAR,GETDATE(),101)) --Aug 19 2008 12:00AM for example
SET @enddate = DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101)) --Aug 20 2008 12:00AM for example
--only tables that have BOTH createdate and changedate
DECLARE c1 CURSOR FOR
SELECT
DISTINCT sysobjects.name
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id=syscolumns.id
INNER JOIN syscolumns dbl ON sysobjects.id=dbl.id
WHERE sysobjects.xtype = 'U'
AND syscolumns.name IN('createdate')
AND dbl.name IN('changedate')
--loop thru all the tables
OPEN c1
FETCH next FROM c1 INTO @table
WHILE @@fetch_status <> -1
BEGIN
--we need a dynamic name as well, i'm doing tablename+timestamp, like tbl1_20080819.txt
-- C:\tbl1_20080819.txt
SET @filename = 'C:\' + @table + '_' + CONVERT(VARCHAR,@begindate,112) + '.txt'
SET @sql = 'SELECT * FROM ' + @table + ' WHERE createdate between ''' + CONVERT(VARCHAR,@begindate,101)
+ ''' and ''' + CONVERT(VARCHAR,@enddate,101)
+ ''' or changedate between ''' + CONVERT(VARCHAR,@begindate,101)
+ ''' and ''' + CONVERT(VARCHAR,@enddate,101) + ''''
--put it all together
SET @sql = 'bcp "' + @sql + '" queryout "' + @filename + '" -T -c -Usa -Pbiteme -SD223\SQLEXPRESS'
--print it!
PRINT @sql
--run it?
EXEC xp_CmdShell @sql, NO_OUTPUT
FETCH next FROM c1 INTO @table
END
CLOSE c1
DEALLOCATE c1
[/font]
Lowell
August 20, 2008 at 8:13 am
Handy script (even if I don't agree with the approch) but handy non the less...
Instead of sysobjects and syscolumns etc, rather use the catalog views sys.objects and sys.columns.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 20, 2008 at 8:52 am
Crispin Proctor (8/20/2008)
Handy script (even if I don't agree with the approch) but handy non the less...Instead of sysobjects and syscolumns etc, rather use the catalog views sys.objects and sys.columns.
I'm definitely influenced by all the pre-sql2005 work i used to do...I still write everything as if I was using sql2000; I really need to use either catalog views or information_schema stuff, you are right.
Lowell
August 20, 2008 at 8:55 am
I am the same 🙁 I still have many scripts floating around that use the "bad" stuff. Truth be told (noobs block your ears) I have never had a problem with it. Just the new offers more.(Does it really? 🙂 )
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply