internal consistency error occurred while restoring a database

this is what i get when restoring a database from a *.bak file, knowing that this is the only backup file i have for my DELETED databases !!!
please help ASAP
Microsoft SQL-DMO (ODBC SQLState: 42000)
An internal consistency error occurred. Contact Technical Support for assistance.
RESTORE DATABASE is terminating abnormally.
I would try what the message suggests: contact PSS.
David Portas
SQL Server MVP

internal consistency error occurred while restoring a database

this is what i get when restoring a database from a *.bak file, knowing that this is the only backup file i have for my DELETED databases !!
please help ASA
Microsoft SQL-DMO (ODBC SQLState: 42000
--
An internal consistency error occurred. Contact Technical Support for assistance
RESTORE DATABASE is terminating abnormallyi'm on SQL2K SP3,
and the file i'm restoring from is reported normally from the OS !|||I would try what the message suggests: contact PSS.
--
David Portas
SQL Server MVP
--

internal consistency error occurred while restoring a database

this is what i get when restoring a database from a *.bak file, knowing that
this is the only backup file i have for my DELETED databases !!!
please help ASAP
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
An internal consistency error occurred. Contact Technical Support for assist
ance.
RESTORE DATABASE is terminating abnormally.I would try what the message suggests: contact PSS.
David Portas
SQL Server MVP
--

Internal Architecture of MDF file

--I just created a new datbase. Added one row to the table and an index.
--Looking at the mdf file using a hex editor surprised me.
create database mydb1
go
use mydb1
go
create table table1 (a char(10), b char(10))
go
insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find one (1). Only one.
--Start sql
use mydb1
go
create index idx1 on table1(b)
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find THREE (3). Why?
--Serious replies only please.
/Bob
Bob,
You don't need a hex editor; you use use DBCC PAGE to view the page
structures without going to all of this trouble. Syntax here:
http://www.sql-server-performance.co...ented_dbcc.asp
If you're really interested in this stuff, you should get a copy of Kalen
Delaney's _Inside SQL Server 2000_, which describes the structures in great
detail.
"Bob" <utefan001@.gmail.com> wrote in message
news:40692455.0409281803.4ce67381@.posting.google.c om...
> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you
care.
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find THREE (3). Why?
> --Serious replies only please.
> /Bob
|||See
http://www.nigelrivett.net/PageStructure.html
"Bob" wrote:

> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find THREE (3). Why?
> --Serious replies only please.
> /Bob
>
|||Maybe its got to do with additional pointers in the index ?
Dylan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#kbGikcpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Bob,
> You don't need a hex editor; you use use DBCC PAGE to view the page
> structures without going to all of this trouble. Syntax here:
>
http://www.sql-server-performance.co...ented_dbcc.asp
> If you're really interested in this stuff, you should get a copy of Kalen
> Delaney's _Inside SQL Server 2000_, which describes the structures in
great
> detail.
>
> "Bob" <utefan001@.gmail.com> wrote in message
> news:40692455.0409281803.4ce67381@.posting.google.c om...
> care.
> (hackman)
> (hackman)
>

Internal Architecture of MDF file

--I just created a new datbase. Added one row to the table and an index.
--Looking at the mdf file using a hex editor surprised me.
create database mydb1
go
use mydb1
go
create table table1 (a char(10), b char(10))
go
insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find one (1). Only one.
--Start sql
use mydb1
go
create index idx1 on table1(b)
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find THREE (3). Why'
--Serious replies only please.
/BobBob,
You don't need a hex editor; you use use DBCC PAGE to view the page
structures without going to all of this trouble. Syntax here:
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
If you're really interested in this stuff, you should get a copy of Kalen
Delaney's _Inside SQL Server 2000_, which describes the structures in great
detail.
"Bob" <utefan001@.gmail.com> wrote in message
news:40692455.0409281803.4ce67381@.posting.google.com...
> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you
care.
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find THREE (3). Why'
> --Serious replies only please.
> /Bob|||See
http://www.nigelrivett.net/PageStructure.html
"Bob" wrote:
> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find THREE (3). Why'
> --Serious replies only please.
> /Bob
>|||Maybe its got to do with additional pointers in the index ?
Dylan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#kbGikcpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Bob,
> You don't need a hex editor; you use use DBCC PAGE to view the page
> structures without going to all of this trouble. Syntax here:
>
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
> If you're really interested in this stuff, you should get a copy of Kalen
> Delaney's _Inside SQL Server 2000_, which describes the structures in
great
> detail.
>
> "Bob" <utefan001@.gmail.com> wrote in message
> news:40692455.0409281803.4ce67381@.posting.google.com...
> > --I just created a new datbase. Added one row to the table and an index.
> > --Looking at the mdf file using a hex editor surprised me.
> > create database mydb1
> > go
> > use mydb1
> > go
> > create table table1 (a char(10), b char(10))
> > go
> > insert into table1 values ('firstTest','23571113') --primes 2-13 if you
> care.
> > --Stop here..Shut down sql and view the mdf file with a hex editor
> (hackman)
> > --Search for string 23571113. You will find one (1). Only one.
> > --Start sql
> >
> > use mydb1
> > go
> > create index idx1 on table1(b)
> > --Stop here..Shut down sql and view the mdf file with a hex editor
> (hackman)
> > --Search for string 23571113. You will find THREE (3). Why'
> > --Serious replies only please.
> > /Bob
>sql

Interfacing SQL Server with Access...

I have a web based SQL Server application that I need to modify to include data that is currently stored in an Access mdb. The mdb file and SQL Server are running on the same box. The data that is on the Access system cannot be migrated to SQL Server since it is a commercial package. But in our web application we want to query this data (and that in the SQL Server database) and present both current information to the users. We don't need to edit the Access data. Just view it and combine it with data we now have in SQL Server.

I was thinking I would build views or stored procedures that would pull info from both the SQL Server tables and the Access tables. But I'm not sure how efficient this is. One issue is that the users are remote, meaning many will not be in the same building that the SQL Server box is located at.

What is the best way to accomplish this? Any ideas? I've thought about using DTS but maybe it is overkill.

Thanks for the help.I would not query the access data from a web app. It will choke and die once you get about 20 connectins to it at a time. If you can live with the access data not being real time I would setup a job that fires daily or hourly that pulls the data into sql server daily or hourly or every 15 minutes. Or if you have the development manpower, redevelop the Access app to use sql as the datasource with an Access Data Project. Access as a backend to a web app is a recipe for disaster. Seen it before.|||Thanks for the advice Sean. Seeing as we cannot redevelop the backend for SQL Server, our only option appears to be batch updating of data into our system from Access on a scheduled basis. Would we use something like DTS for this? What can we do in SQL Server to facilitate the transfer of data from Access to SQL Server?

Thanks Sean.|||Use a SQL Server Agent job that fires a DTS job.|||Use a SQL Server Agent job that fires a DTS job.

Thanks. Will do.sql

Integration with Windows Rights Management

Does RS natively allow the application of Rights Management to reports as or
once they are rendered, e.g. to a web site or into a pdf file? If not do you
have any insight into how this can be done? Are there plans to integrate with
RM?This feature definitely on the future list but will not be in the SQL 2005
version of the product.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael J. O'Connell" <MichaelJOConnell@.discussions.microsoft.com> wrote in
message news:CA80EB14-8D0F-42CA-8940-D07214E7D2B1@.microsoft.com...
> Does RS natively allow the application of Rights Management to reports as
> or
> once they are rendered, e.g. to a web site or into a pdf file? If not do
> you
> have any insight into how this can be done? Are there plans to integrate
> with
> RM?|||Thanks Brian.
"Brian Welcker [MS]" wrote:
> This feature definitely on the future list but will not be in the SQL 2005
> version of the product.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Michael J. O'Connell" <MichaelJOConnell@.discussions.microsoft.com> wrote in
> message news:CA80EB14-8D0F-42CA-8940-D07214E7D2B1@.microsoft.com...
> > Does RS natively allow the application of Rights Management to reports as
> > or
> > once they are rendered, e.g. to a web site or into a pdf file? If not do
> > you
> > have any insight into how this can be done? Are there plans to integrate
> > with
> > RM?
>
>

Integration Services For Each Loop Question

I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing the
file name into a user variable, but can't figure out how to use the value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving each
file name into a variable to use in another for each file loop to copy the
file to another location.
The purpose of the entire exercise is to put file names into two tables for
log backups. I'm backing up log files to a local directory, and want to copy
them to a network share. Since I only want to copy newly backed up files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share, write
the names to another table, join the two tables to find which files do not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so far.
Thanks for any assistance with this.
TomTTomT wrote:
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file na
me
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing the
> file name into a user variable, but can't figure out how to use the value
of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving ea
ch
> file name into a variable to use in another for each file loop to copy the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables fo
r
> log backups. I'm backing up log files to a local directory, and want to co
py
> them to a network share. Since I only want to copy newly backed up files,
I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share, write
> the names to another table, join the two tables to find which files do not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so far.
> Thanks for any assistance with this.
> TomT
You're going about this the hard way. If you can run xp_cmdshell, you
can do this with a single insert statement:
CREATE TABLE #Table (
[FileName] VARCHAR(255)
)
DECLARE @.Command VARCHAR(255)
SELECT @.Command = 'master..xp_cmdshell ''DIR /B C:\WINDOWS'''
INSERT INTO #Table
EXEC (@.Command)
SELECT * FROM #Table
DROP TABLE #Table
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
which would do the backups, history clean up, and file copy. Also, trying to
get up to speed on SSIS capabilities.
"Tracy McKibben" wrote:

> TomT wrote:
> You're going about this the hard way. If you can run xp_cmdshell, you
> can do this with a single insert statement:
> CREATE TABLE #Table (
> [FileName] VARCHAR(255)
> )
> DECLARE @.Command VARCHAR(255)
> SELECT @.Command = 'master..xp_cmdshell ''DIR /B C:\WINDOWS'''
> INSERT INTO #Table
> EXEC (@.Command)
> SELECT * FROM #Table
> DROP TABLE #Table
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||TomT wrote:
> Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
> which would do the backups, history clean up, and file copy. Also, trying
to
> get up to speed on SSIS capabilities.
>
Ahh, ok... Well, this may or may not interest you, I have a script that
will automatically backup any database on your server, including t-logs,
and will handle the history cleanup too...
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||Thanks Tracy - that's very helpful, and definately of interest to me.
At the same time, I'm still interested in finding out if there's a way to do
what I'm attempting via SSIS, particularly getting the file loop variable
into a sql insert statement.
I appreciate your feedback and assistance with this issue...
"Tracy McKibben" wrote:

> TomT wrote:
> Ahh, ok... Well, this may or may not interest you, I have a script that
> will automatically backup any database on your server, including t-logs,
> and will handle the history cleanup too...
> http://realsqlguy.com/twiki/bin/vie...realsqlguy.com
>|||Hi,
Thanks for your post!
To make me clear about your issue, I appreciate to know:
1) You could retrieve each filename in a variable now and you didn't know
how to insert it into a table?
If so, you can write SQL like this:
INSERT INTO Table_Name([colname1],...[colnamen])
values(@.FileName1,...,[@.othervalue])
2) Are your purpose as following ?
Retrieve related files on the local drive and insert their names into
one table;
Retrieve related files on the remote network drive and insert their
names into a second table;
Compare the data between the two tables, if the 1st table's files names
are not in the second table, copy them into the network drive.
For such issue, you can't directly use TSQL in workflow control in SSIS.
I recommend you use "Execute SQL task" and map the variable to a
parameter in the task.
Right click task->Edit->Parameter Mapping.
Then you could use the parameter as you mentioned in the SQLstatement in
General tab of "Execute SQL task"
http://msdn2.microsoft.com/en-us/library/ms141003.aspx
Also, you could configure "variable mapping" of foreach loop container
to the variable you uses.
Other tasks like ActiveX Scripts Task can also help you deal with this
case.
http://msdn2.microsoft.com/en-us/library/ms137525.aspx
If you have any other concerns, please feel free to let me know. I'm
happy for your assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||TomT - it sounds like everyone has an opinion! Here is a way to do
what you want with SSIS. Then see below that for my opinion .
1 - Open your new SSIS project, and add a Foreach Loop container
2 - In the properties, make it a Foreach File Enumerator, and choose
the folder you want. Set the other properties, such as if you want the
fully qualified filename
3 - In the variable mappings area, Add a new user variable named
"FileName"; it should appear as User::FileName. Click OK & you're done
with the loop container
4 - Now go modify the variable properties (View, Other Windows,
Variables). Highlight the variable created in #3 and press F4 to get
the properties window.
5 - Change "EvaluateAsExpression" to True
6 - Change the "Expression" property to be your SQL Statement, but now
you're including your variable (notice the single quotes around your
variable):
"INSERT test SELECT '" + @.[User::FileName] + "'"
7 - Now add an "Execute SQL Task" to your loop container. Choose your
database. Change the SQL SourceType to be Variable.
8 - Choose your variable, which has now been filled with your SQL
statement.
9 - test out by debugging & then select the data from your table
SELECT * FROM test
Your next step - you mentioned it was to copy the files from the
original location to a new location using the table to loop. You can
skip all of the above madness, by just using the file system task, and
copy the entire contents of directory #1 to directory #2.
---
I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file
name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing
the
file name into a user variable, but can't figure out how to use the
value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving
each
file name into a variable to use in another for each file loop to copy
the
file to another location.
The purpose of the entire exercise is to put file names into two tables
for
log backups. I'm backing up log files to a local directory, and want to
copy
them to a network share. Since I only want to copy newly backed up
files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share,
write
the names to another table, join the two tables to find which files do
not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so
far.
Thanks for any assistance with this.
TomT|||Thanks Corey - that's the direction I wanted to go with this (getting the
variable mapped properly in the sql statement.
There are a couple of reasons I'm going about things in this way. Since I'm
doing log backups every hour locally, and then copying these files over to
another system, I want to only copy over the most recent file. The other
(network) system should also have copies of previously copied logs. I wanted
to keep the i/o down, and only copy over the latest backup.
Secondly, I wanted to do this as a way to get more familiar with SSIS. I
think all I need to do really is just get the name of the most recent backup
log file, but I don't know how to get that info via SSIS.
So, e.g., say I've got two days worth of log backups on the local system,
then copy these over (say manually just for now) to a network system. Then,
the next time a log backup takes place on the local system, I want to copy
JUST that one over to the network system. If I could just identify that
particular (most recent) log backup's file name, I'd be set.
Again, this is just as much to learn SSIS as anything else right now.
Thanks for your help
"CoreyB" wrote:

> TomT - it sounds like everyone has an opinion! Here is a way to do
> what you want with SSIS. Then see below that for my opinion .
> 1 - Open your new SSIS project, and add a Foreach Loop container
> 2 - In the properties, make it a Foreach File Enumerator, and choose
> the folder you want. Set the other properties, such as if you want the
> fully qualified filename
> 3 - In the variable mappings area, Add a new user variable named
> "FileName"; it should appear as User::FileName. Click OK & you're done
> with the loop container
> 4 - Now go modify the variable properties (View, Other Windows,
> Variables). Highlight the variable created in #3 and press F4 to get
> the properties window.
> 5 - Change "EvaluateAsExpression" to True
> 6 - Change the "Expression" property to be your SQL Statement, but now
> you're including your variable (notice the single quotes around your
> variable):
> "INSERT test SELECT '" + @.[User::FileName] + "'"
> 7 - Now add an "Execute SQL Task" to your loop container. Choose your
> database. Change the SQL SourceType to be Variable.
> 8 - Choose your variable, which has now been filled with your SQL
> statement.
> 9 - test out by debugging & then select the data from your table
> SELECT * FROM test
>
> Your next step - you mentioned it was to copy the files from the
> original location to a new location using the table to loop. You can
> skip all of the above madness, by just using the file system task, and
> copy the entire contents of directory #1 to directory #2.
>
> ---
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file
> name
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing
> the
> file name into a user variable, but can't figure out how to use the
> value of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving
> each
> file name into a variable to use in another for each file loop to copy
> the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables
> for
> log backups. I'm backing up log files to a local directory, and want to
> copy
> them to a network share. Since I only want to copy newly backed up
> files, I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share,
> write
> the names to another table, join the two tables to find which files do
> not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so
> far.
> Thanks for any assistance with this.
> TomT
>|||Sounds good - good luck!
TomT wrote:[vbcol=seagreen]
> Thanks Corey - that's the direction I wanted to go with this (getting the
> variable mapped properly in the sql statement.
> There are a couple of reasons I'm going about things in this way. Since I'
m
> doing log backups every hour locally, and then copying these files over to
> another system, I want to only copy over the most recent file. The other
> (network) system should also have copies of previously copied logs. I want
ed
> to keep the i/o down, and only copy over the latest backup.
> Secondly, I wanted to do this as a way to get more familiar with SSIS. I
> think all I need to do really is just get the name of the most recent back
up
> log file, but I don't know how to get that info via SSIS.
> So, e.g., say I've got two days worth of log backups on the local system,
> then copy these over (say manually just for now) to a network system. Then
,
> the next time a log backup takes place on the local system, I want to copy
> JUST that one over to the network system. If I could just identify that
> particular (most recent) log backup's file name, I'd be set.
> Again, this is just as much to learn SSIS as anything else right now.
> Thanks for your help
> "CoreyB" wrote:
>

Integration Services For Each Loop Question

I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing the
file name into a user variable, but can't figure out how to use the value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving each
file name into a variable to use in another for each file loop to copy the
file to another location.
The purpose of the entire exercise is to put file names into two tables for
log backups. I'm backing up log files to a local directory, and want to copy
them to a network share. Since I only want to copy newly backed up files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share, write
the names to another table, join the two tables to find which files do not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so far.
Thanks for any assistance with this.
TomTTomT wrote:
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file name
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing the
> file name into a user variable, but can't figure out how to use the value of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving each
> file name into a variable to use in another for each file loop to copy the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables for
> log backups. I'm backing up log files to a local directory, and want to copy
> them to a network share. Since I only want to copy newly backed up files, I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share, write
> the names to another table, join the two tables to find which files do not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so far.
> Thanks for any assistance with this.
> TomT
You're going about this the hard way. If you can run xp_cmdshell, you
can do this with a single insert statement:
CREATE TABLE #Table (
[FileName] VARCHAR(255)
)
DECLARE @.Command VARCHAR(255)
SELECT @.Command = 'master..xp_cmdshell ''DIR /B C:\WINDOWS'''
INSERT INTO #Table
EXEC (@.Command)
SELECT * FROM #Table
DROP TABLE #Table
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
which would do the backups, history clean up, and file copy. Also, trying to
get up to speed on SSIS capabilities.
"Tracy McKibben" wrote:
> TomT wrote:
> > I'm wondering if this can be done (I've had no luck so far):
> >
> > I would like to loop thru a set of files in a directory, place the file name
> > into a variable, then insert the file name as a record into a table.
> >
> > I've gotten the for each (file) loop set up and working fine, placing the
> > file name into a user variable, but can't figure out how to use the value of
> > the variable to insert it into a table.
> >
> > The next step would be to loop thru the records in the table retrieving each
> > file name into a variable to use in another for each file loop to copy the
> > file to another location.
> >
> > The purpose of the entire exercise is to put file names into two tables for
> > log backups. I'm backing up log files to a local directory, and want to copy
> > them to a network share. Since I only want to copy newly backed up files, I
> > would like to go thru all the files on the local drive, write them to a
> > table, go thru all the previously copied files on the network share, write
> > the names to another table, join the two tables to find which files do not
> > exist on the network share and copy them to it.
> >
> > There may be a better way to do this, but I've not figured it out so far.
> >
> > Thanks for any assistance with this.
> >
> > TomT
> You're going about this the hard way. If you can run xp_cmdshell, you
> can do this with a single insert statement:
> CREATE TABLE #Table (
> [FileName] VARCHAR(255)
> )
> DECLARE @.Command VARCHAR(255)
> SELECT @.Command = 'master..xp_cmdshell ''DIR /B C:\WINDOWS'''
> INSERT INTO #Table
> EXEC (@.Command)
> SELECT * FROM #Table
> DROP TABLE #Table
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||TomT wrote:
> Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
> which would do the backups, history clean up, and file copy. Also, trying to
> get up to speed on SSIS capabilities.
>
Ahh, ok... Well, this may or may not interest you, I have a script that
will automatically backup any database on your server, including t-logs,
and will handle the history cleanup too...
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy - that's very helpful, and definately of interest to me.
At the same time, I'm still interested in finding out if there's a way to do
what I'm attempting via SSIS, particularly getting the file loop variable
into a sql insert statement.
I appreciate your feedback and assistance with this issue...
"Tracy McKibben" wrote:
> TomT wrote:
> > Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
> > which would do the backups, history clean up, and file copy. Also, trying to
> > get up to speed on SSIS capabilities.
> >
> Ahh, ok... Well, this may or may not interest you, I have a script that
> will automatically backup any database on your server, including t-logs,
> and will handle the history cleanup too...
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Hi,
Thanks for your post!
To make me clear about your issue, I appreciate to know:
1) You could retrieve each filename in a variable now and you didn't know
how to insert it into a table?
If so, you can write SQL like this:
INSERT INTO Table_Name([colname1],...[colnamen])
values(@.FileName1,...,[@.othervalue])
2) Are your purpose as following ?
Retrieve related files on the local drive and insert their names into
one table;
Retrieve related files on the remote network drive and insert their
names into a second table;
Compare the data between the two tables, if the 1st table's files names
are not in the second table, copy them into the network drive.
For such issue, you can't directly use TSQL in workflow control in SSIS.
I recommend you use "Execute SQL task" and map the variable to a
parameter in the task.
Right click task->Edit->Parameter Mapping.
Then you could use the parameter as you mentioned in the SQLstatement in
General tab of "Execute SQL task"
http://msdn2.microsoft.com/en-us/library/ms141003.aspx
Also, you could configure "variable mapping" of foreach loop container
to the variable you uses.
Other tasks like ActiveX Scripts Task can also help you deal with this
case.
http://msdn2.microsoft.com/en-us/library/ms137525.aspx
If you have any other concerns, please feel free to let me know. I'm
happy for your assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||TomT - it sounds like everyone has an opinion! Here is a way to do
what you want with SSIS. Then see below that for my opinion :).
1 - Open your new SSIS project, and add a Foreach Loop container
2 - In the properties, make it a Foreach File Enumerator, and choose
the folder you want. Set the other properties, such as if you want the
fully qualified filename
3 - In the variable mappings area, Add a new user variable named
"FileName"; it should appear as User::FileName. Click OK & you're done
with the loop container
4 - Now go modify the variable properties (View, Other Windows,
Variables). Highlight the variable created in #3 and press F4 to get
the properties window.
5 - Change "EvaluateAsExpression" to True
6 - Change the "Expression" property to be your SQL Statement, but now
you're including your variable (notice the single quotes around your
variable):
"INSERT test SELECT '" + @.[User::FileName] + "'"
7 - Now add an "Execute SQL Task" to your loop container. Choose your
database. Change the SQL SourceType to be Variable.
8 - Choose your variable, which has now been filled with your SQL
statement.
9 - test out by debugging & then select the data from your table
SELECT * FROM test
Your next step - you mentioned it was to copy the files from the
original location to a new location using the table to loop. You can
skip all of the above madness, by just using the file system task, and
copy the entire contents of directory #1 to directory #2.
---
I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file
name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing
the
file name into a user variable, but can't figure out how to use the
value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving
each
file name into a variable to use in another for each file loop to copy
the
file to another location.
The purpose of the entire exercise is to put file names into two tables
for
log backups. I'm backing up log files to a local directory, and want to
copy
them to a network share. Since I only want to copy newly backed up
files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share,
write
the names to another table, join the two tables to find which files do
not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so
far.
Thanks for any assistance with this.
TomT|||Thanks Corey - that's the direction I wanted to go with this (getting the
variable mapped properly in the sql statement.
There are a couple of reasons I'm going about things in this way. Since I'm
doing log backups every hour locally, and then copying these files over to
another system, I want to only copy over the most recent file. The other
(network) system should also have copies of previously copied logs. I wanted
to keep the i/o down, and only copy over the latest backup.
Secondly, I wanted to do this as a way to get more familiar with SSIS. I
think all I need to do really is just get the name of the most recent backup
log file, but I don't know how to get that info via SSIS.
So, e.g., say I've got two days worth of log backups on the local system,
then copy these over (say manually just for now) to a network system. Then,
the next time a log backup takes place on the local system, I want to copy
JUST that one over to the network system. If I could just identify that
particular (most recent) log backup's file name, I'd be set.
Again, this is just as much to learn SSIS as anything else right now.
Thanks for your help
"CoreyB" wrote:
> TomT - it sounds like everyone has an opinion! Here is a way to do
> what you want with SSIS. Then see below that for my opinion :).
> 1 - Open your new SSIS project, and add a Foreach Loop container
> 2 - In the properties, make it a Foreach File Enumerator, and choose
> the folder you want. Set the other properties, such as if you want the
> fully qualified filename
> 3 - In the variable mappings area, Add a new user variable named
> "FileName"; it should appear as User::FileName. Click OK & you're done
> with the loop container
> 4 - Now go modify the variable properties (View, Other Windows,
> Variables). Highlight the variable created in #3 and press F4 to get
> the properties window.
> 5 - Change "EvaluateAsExpression" to True
> 6 - Change the "Expression" property to be your SQL Statement, but now
> you're including your variable (notice the single quotes around your
> variable):
> "INSERT test SELECT '" + @.[User::FileName] + "'"
> 7 - Now add an "Execute SQL Task" to your loop container. Choose your
> database. Change the SQL SourceType to be Variable.
> 8 - Choose your variable, which has now been filled with your SQL
> statement.
> 9 - test out by debugging & then select the data from your table
> SELECT * FROM test
>
> Your next step - you mentioned it was to copy the files from the
> original location to a new location using the table to loop. You can
> skip all of the above madness, by just using the file system task, and
> copy the entire contents of directory #1 to directory #2.
>
> ---
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file
> name
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing
> the
> file name into a user variable, but can't figure out how to use the
> value of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving
> each
> file name into a variable to use in another for each file loop to copy
> the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables
> for
> log backups. I'm backing up log files to a local directory, and want to
> copy
> them to a network share. Since I only want to copy newly backed up
> files, I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share,
> write
> the names to another table, join the two tables to find which files do
> not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so
> far.
> Thanks for any assistance with this.
> TomT
>|||Sounds good - good luck!
TomT wrote:
> Thanks Corey - that's the direction I wanted to go with this (getting the
> variable mapped properly in the sql statement.
> There are a couple of reasons I'm going about things in this way. Since I'm
> doing log backups every hour locally, and then copying these files over to
> another system, I want to only copy over the most recent file. The other
> (network) system should also have copies of previously copied logs. I wanted
> to keep the i/o down, and only copy over the latest backup.
> Secondly, I wanted to do this as a way to get more familiar with SSIS. I
> think all I need to do really is just get the name of the most recent backup
> log file, but I don't know how to get that info via SSIS.
> So, e.g., say I've got two days worth of log backups on the local system,
> then copy these over (say manually just for now) to a network system. Then,
> the next time a log backup takes place on the local system, I want to copy
> JUST that one over to the network system. If I could just identify that
> particular (most recent) log backup's file name, I'd be set.
> Again, this is just as much to learn SSIS as anything else right now.
> Thanks for your help
> "CoreyB" wrote:
> > TomT - it sounds like everyone has an opinion! Here is a way to do
> > what you want with SSIS. Then see below that for my opinion :).
> >
> > 1 - Open your new SSIS project, and add a Foreach Loop container
> > 2 - In the properties, make it a Foreach File Enumerator, and choose
> > the folder you want. Set the other properties, such as if you want the
> > fully qualified filename
> > 3 - In the variable mappings area, Add a new user variable named
> > "FileName"; it should appear as User::FileName. Click OK & you're done
> > with the loop container
> > 4 - Now go modify the variable properties (View, Other Windows,
> > Variables). Highlight the variable created in #3 and press F4 to get
> > the properties window.
> > 5 - Change "EvaluateAsExpression" to True
> > 6 - Change the "Expression" property to be your SQL Statement, but now
> > you're including your variable (notice the single quotes around your
> > variable):
> >
> > "INSERT test SELECT '" + @.[User::FileName] + "'"
> >
> > 7 - Now add an "Execute SQL Task" to your loop container. Choose your
> > database. Change the SQL SourceType to be Variable.
> > 8 - Choose your variable, which has now been filled with your SQL
> > statement.
> > 9 - test out by debugging & then select the data from your table
> >
> > SELECT * FROM test
> >
> >
> > Your next step - you mentioned it was to copy the files from the
> > original location to a new location using the table to loop. You can
> > skip all of the above madness, by just using the file system task, and
> > copy the entire contents of directory #1 to directory #2.
> >
> >
> > ---
> >
> > I'm wondering if this can be done (I've had no luck so far):
> >
> > I would like to loop thru a set of files in a directory, place the file
> > name
> > into a variable, then insert the file name as a record into a table.
> >
> > I've gotten the for each (file) loop set up and working fine, placing
> > the
> > file name into a user variable, but can't figure out how to use the
> > value of
> > the variable to insert it into a table.
> >
> > The next step would be to loop thru the records in the table retrieving
> > each
> > file name into a variable to use in another for each file loop to copy
> > the
> > file to another location.
> >
> > The purpose of the entire exercise is to put file names into two tables
> > for
> > log backups. I'm backing up log files to a local directory, and want to
> > copy
> > them to a network share. Since I only want to copy newly backed up
> > files, I
> > would like to go thru all the files on the local drive, write them to a
> > table, go thru all the previously copied files on the network share,
> > write
> > the names to another table, join the two tables to find which files do
> > not
> > exist on the network share and copy them to it.
> >
> > There may be a better way to do this, but I've not figured it out so
> > far.
> >
> > Thanks for any assistance with this.
> >
> > TomT
> >
> >

Integration Services Configuration on Cluster

I've followed the books online help to configure SSIS (move the
MsDtsSrvr.ini.xml file, edit the file to have the correct path virtual server
name, and change the registry), restart all services...and nothing. I can
connect to SSIS fine through Management Studio, but when I click on Stored
Packages -> MSDB it hangs and eventually times out with this error message:
"The SQL Server specified in SSIS service configuration is not present or is
not available. This might occur when there is no default instance of SQL
Server."
I have a no default instance of SQL 05 on a failover cluster.
Also, I've tried every possible combination on the Server section of the
MsDtsSrvr.ini.xml file: server name\instance name, cluster name-.
Is it a dash or a backslash, I've seen different advice in articles, also.
thanks!Perhaps the configuration file isn't on the shared disk and available to
both nodes?
Please check out this article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx for complete details
of the required configuration.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Integration Services Configuration on Cluster

I've followed the books online help to configure SSIS (move the
MsDtsSrvr.ini.xml file, edit the file to have the correct path virtual serve
r
name, and change the registry), restart all services...and nothing. I can
connect to SSIS fine through Management Studio, but when I click on Stored
Packages -> MSDB it hangs and eventually times out with this error message:
"The SQL Server specified in SSIS service configuration is not present or is
not available. This might occur when there is no default instance of SQL
Server."
I have a no default instance of SQL 05 on a failover cluster.
Also, I've tried every possible combination on the Server section of the
MsDtsSrvr.ini.xml file: server name\instance name, cluster name-.
Is it a dash or a backslash, I've seen different advice in articles, also.
thanks!Perhaps the configuration file isn't on the shared disk and available to
both nodes?
Please check out this article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx for complete details
of the required configuration.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Integration Services - Header and Footer

I have been struggling to get this right in Integration Services, Would really appreciate the help.

Basically I have a file with fixed length columns that I would like to import into my database using Integration Services, the file looks something like this

AN_HeaderRecord0001Testfile.txt

MyNameMySurname0127721506

MyName2 MySurname2 0127722322

FooterRec0000000004_02

What I need to find out is how do I import the data, the name and surname and telephone no, but skip the header and footer records. Also I need to be able to use the header and footer data for later use.

Any ideas ?

Rudolf,

You could do that easily in 2 steps(dataflows); the first one to add an extra column with the row number and determine the number of rows in the file (put the number in a variable using a rowcount transformation); then in the second one you can use a conditional split to re-direct the first and last row by comparing the rownumber column with the number in the variable from the first dataflow.

|||Thanx a mil for the response, I think I am getting the hang of this, will try it and let you know|||Your flat file connection manager should let you chose the option of skipping the first row altogether. Then all you'd have to worry about is removing the footer. If the detail records have a different indicator than the footer records, you can use a conditional split transformation to direct only the detail records to your destination.|||

Hi, Rapael

Could you perhaps point me in the correct direction (what components to use) to get the row number next to the row data

in the first data flow.

First Data Flow

I should have a flat file source and then a row count transformation component linked to each other ? I am having problems getting the row

data and the row number next to each other

Thanx in advance

|||

you can use an script component:

http://support.microsoft.com/kb/908460

|||Or use the Row Number transform here: http://www.sqlis.com/93.aspx

Integrating MSDE 2000 with VB Applications

Hi,
I have VB 6.0 application using MSDE 2000.
I created setup file using Package & Deployment Wizard.
I have included "SQL Server Desktop Engine Merge Modules" and
"sqlredis.exe".
However, when I try to test it on another computer, I get following error.
DBNETLIB[ConnectionOpen(Connect()).] SQL Server does not exists or access is
denied.
clicking OK comes up with
Runtime error -2147467259 (80004005)
Automation Error
Unspecified error.
What am I missing?
Any Idea? Let me know if further info needed.
Shailesh
hi Shailesh,
Shailesh Patel wrote:
> Hi,
> I have VB 6.0 application using MSDE 2000.
> I created setup file using Package & Deployment Wizard.
> I have included "SQL Server Desktop Engine Merge Modules" and
> "sqlredis.exe".
> However, when I try to test it on another computer, I get following
> error. DBNETLIB[ConnectionOpen(Connect()).] SQL Server does not
> exists or access is denied.
> clicking OK comes up with
> Runtime error -2147467259 (80004005)
> Automation Error
> Unspecified error.
> What am I missing?
> Any Idea? Let me know if further info needed.
> Shailesh
please have a look at
http://support.microsoft.com/default...06&Product=sql
if helps
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply