create a table prior to bcping data

I need to do the following;
1. bcp out data from our reporting server
2. Move bcp file to our archival server
Sounds pretty straight forward.. but of course there is
more...
Since we are using this data for archival purposes, we
just want to store the bcp file. My concern is that there
are many schema changes to the source table and I wanted
to make sure that I had the correct schema needed to bcp
that file back into a table, say three years from now.
Is there a option in bcp or bulk copy that would create
the schema for you? Or can this be done via DTS?
Thanks in advance
Susan
Any help would be greatly appreciated.
Thanks
Susan
Hi,
You can "Generate a script " of all objects and keep it along with the BCP
OUT data.
How to Generate scripts:
1. In Enterprise manager
2. Connect to the SQL server
3. Select the database you need to generate the script
4. Right click All Tasks - "Select the Generate SQL Script" option
5. Click Show all
6. Go to Options Tab - in the Table options select Script INdexes, Triggers
and Primary keys.../
Click the save button and save the file to your hard disk in safe place.
Once you require create a ne database and Execute this script
and BCP IN the the data back.
Thanks
Hari
MCDBA
"Susan" <anonymous@.discussions.microsoft.com> wrote in message
news:1a40701c41db3$13400250$a501280a@.phx.gbl...
> I need to do the following;
> 1. bcp out data from our reporting server
> 2. Move bcp file to our archival server
> Sounds pretty straight forward.. but of course there is
> more...
> Since we are using this data for archival purposes, we
> just want to store the bcp file. My concern is that there
> are many schema changes to the source table and I wanted
> to make sure that I had the correct schema needed to bcp
> that file back into a table, say three years from now.
> Is there a option in bcp or bulk copy that would create
> the schema for you? Or can this be done via DTS?
> Thanks in advance
> Susan
>
>
> Any help would be greatly appreciated.
> Thanks
> Susan
>

create a table prior to bcping data

I need to do the following;
1. bcp out data from our reporting server
2. Move bcp file to our archival server
Sounds pretty straight forward.. but of course there is
more...
Since we are using this data for archival purposes, we
just want to store the bcp file. My concern is that there
are many schema changes to the source table and I wanted
to make sure that I had the correct schema needed to bcp
that file back into a table, say three years from now.
Is there a option in bcp or bulk copy that would create
the schema for you? Or can this be done via DTS?
Thanks in advance
Susan
Any help would be greatly appreciated.
Thanks
SusanHi,
You can "Generate a script " of all objects and keep it along with the BCP
OUT data.
How to Generate scripts:
1. In Enterprise manager
2. Connect to the SQL server
3. Select the database you need to generate the script
4. Right click All Tasks - "Select the Generate SQL Script" option
5. Click Show all
6. Go to Options Tab - in the Table options select Script INdexes, Triggers
and Primary keys.../
Click the save button and save the file to your hard disk in safe place.
Once you require create a ne database and Execute this script
and BCP IN the the data back.
Thanks
Hari
MCDBA
"Susan" <anonymous@.discussions.microsoft.com> wrote in message
news:1a40701c41db3$13400250$a501280a@.phx.gbl...
> I need to do the following;
> 1. bcp out data from our reporting server
> 2. Move bcp file to our archival server
> Sounds pretty straight forward.. but of course there is
> more...
> Since we are using this data for archival purposes, we
> just want to store the bcp file. My concern is that there
> are many schema changes to the source table and I wanted
> to make sure that I had the correct schema needed to bcp
> that file back into a table, say three years from now.
> Is there a option in bcp or bulk copy that would create
> the schema for you? Or can this be done via DTS?
> Thanks in advance
> Susan
>
>
> Any help would be greatly appreciated.
> Thanks
> Susan
>|||I've found a solution to my issue and wanted to let you
all know!!
I had also posted my question on the SQL Server Central
site and Mr. Mortensen was nice enough to provide a GENIUS
solution. He has a proc that will
1. Generate schema of the table in question
2. Update stats on table prior to BCPing
3. BCP's out the data into a readable text file
4. Verify row counts from the BCP and the actual table
(VERY NICE)
5. He also has a bat file that will run the schema and bcp
the data into the DB/table of your choice.
It was EXACTLY what I need and I want to thank him for his
help.
BTW - Why did Microsoft get rid of the backup 'table'
option? I used it quite a bit in 6.5 and there are MANY
time you only need to save one table and not the whole
database. I know there is dts and bcp, but nothing
compares to backing up and restoring a table. Other DBMS
provide this.. Just wondering.
Thanks
Susan
Here's the link in case your curious.
http://www.sqlservercentral.com/forums/shwmessage.aspx?
forumid=8&messageid=111276
>--Original Message--
>I need to do the following;
>1. bcp out data from our reporting server
>2. Move bcp file to our archival server
>Sounds pretty straight forward.. but of course there is
>more...
>Since we are using this data for archival purposes, we
>just want to store the bcp file. My concern is that there
>are many schema changes to the source table and I wanted
>to make sure that I had the correct schema needed to bcp
>that file back into a table, say three years from now.
>Is there a option in bcp or bulk copy that would create
>the schema for you? Or can this be done via DTS?
>Thanks in advance
>Susan
>
>
>Any help would be greatly appreciated.
>Thanks
>Susan
>.
>

create a table prior to bcping data

I need to do the following;
1. bcp out data from our reporting server
2. Move bcp file to our archival server
Sounds pretty straight forward.. but of course there is
more...
Since we are using this data for archival purposes, we
just want to store the bcp file. My concern is that there
are many schema changes to the source table and I wanted
to make sure that I had the correct schema needed to bcp
that file back into a table, say three years from now.
Is there a option in bcp or bulk copy that would create
the schema for you? Or can this be done via DTS?
Thanks in advance
Susan
Any help would be greatly appreciated.
Thanks
SusanHi,
You can "Generate a script " of all objects and keep it along with the BCP
OUT data.
How to Generate scripts:
1. In Enterprise manager
2. Connect to the SQL server
3. Select the database you need to generate the script
4. Right click All Tasks - "Select the Generate SQL Script" option
5. Click Show all
6. Go to Options Tab - in the Table options select Script INdexes, Triggers
and Primary keys.../
Click the save button and save the file to your hard disk in safe place.
Once you require create a ne database and Execute this script
and BCP IN the the data back.
Thanks
Hari
MCDBA
"Susan" <anonymous@.discussions.microsoft.com> wrote in message
news:1a40701c41db3$13400250$a501280a@.phx
.gbl...
> I need to do the following;
> 1. bcp out data from our reporting server
> 2. Move bcp file to our archival server
> Sounds pretty straight forward.. but of course there is
> more...
> Since we are using this data for archival purposes, we
> just want to store the bcp file. My concern is that there
> are many schema changes to the source table and I wanted
> to make sure that I had the correct schema needed to bcp
> that file back into a table, say three years from now.
> Is there a option in bcp or bulk copy that would create
> the schema for you? Or can this be done via DTS?
> Thanks in advance
> Susan
>
>
> Any help would be greatly appreciated.
> Thanks
> Susan
>

counting records in all non system database table

is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?

Try this:

EXECsp_msforeachtable'sp_spaceused "?"'

|||

You can use the TSQL below to get this information and some others. The results is similar to SP_SPACEUSED.

SELECT object_name(id) AS name,
rowcnt AS rows,
reserved * 8 AS reserved_kb,
dpages * 8 AS data_kb,
(sum(used) * 8) - (dpages * 8) AS index_size_kb,
(sum(reserved) * 8) - (sum(used) * 8) AS unused_kb
FROM sysindexes
WHERE indid IN (0,1) -- cluster e n?o cluster
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, rowcnt, reserved, dpages
ORDER BY rowcnt DESC

|||

Hi,

Try the following procedure

Code Snippet

ALTER procedure USP_CountAllTables
AS

DECLARE @.TblName Nvarchar(255)
DECLARE @.SQL nvarchar(1000)
DECLARE CntCursor CURSOR FOR
select Name from sysobjects where xtype='U'
Set @.SQL=NULL

create table #Temp
(tableName nvarchar(255),
Record_Count int
)

OPEN CntCursor
FETCH NEXT FROM CntCursor
into @.TblName
WHILE @.@.FETCH_STATUS = 0
BEGIN

SET @.SQL='Select ''' + @.TblName + ''',count(*) from '+ @.TblName

insert #Temp
execute sp_Executesql @.SQL

FETCH NEXT FROM CntCursor
into @.TblName
END
CLOSE CntCursor
DEALLOCATE CntCursor
select * from #Temp
Drop table #Temp

This will give u the record count for all the user tables.

But its gonna take some time so have patience.

RegarDs,

Jacx