Get a Tables Row Count from system tables

Anyone knows how to get a Table's Row Count from system tables?

Thanks.select count(*) from sysobjects

sysobjects is a system table name. Replace this with the system table name which you want to get the row count.

Generating Replication Alerts through Perfmon monitoring.

We use a third party monitoring system that polls perfmon statistics on
different servers and generates alerts if a threshold is reached. For general
SQL tasks like backup, I programmatically set/reset User Settable counters
(10 in all) through system stored procedures sp_user_counter[1...10] in the
Scheduled task command.
But, this is not working in the case of replication. However, there are
other perfmon monitors that are specifically related to replication.
Would someone be able to identify what is the best parameter to monitor?
Thanks!!!
MZeeshan
Hi MZeeshan,
From your descriptions, I understand you would like to select a better
performance counter for your monitoring. However, I am not sure what you
are going to monitor and improve. Would you please show me more detailed
information, which I believe will make us closer to the resolution
You could find detailed introduction of all replication performance
counters in BOL topic "Using SQL Server Objects"
Sincerely yours,
Michael Cheng
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.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||use replication alerts for this. Connect to your sql server using EM, expand
your server, expand the replication monitor node, and then expand the
replication alerts section.
You could monitor conflicts in SQLServer:Replication Merge, monitor the
latency of SQLServer:Replication LogReader and SQLServer:Replication Dist.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:F2BF344D-36CE-47D8-8D76-DDD8A0150B71@.microsoft.com...
> We use a third party monitoring system that polls perfmon statistics on
> different servers and generates alerts if a threshold is reached. For
general
> SQL tasks like backup, I programmatically set/reset User Settable counters
> (10 in all) through system stored procedures sp_user_counter[1...10] in
the
> Scheduled task command.
> But, this is not working in the case of replication. However, there are
> other perfmon monitors that are specifically related to replication.
> Would someone be able to identify what is the best parameter to monitor?
> Thanks!!!
> MZeeshan
|||Let me give you an example of how I am monitoring other tasks now. For
instance, I put the T-SQL below as a Schedule task:
BACKUP DATABASE AMA TO DISK='D:\DailyBackups\Full\AMA.bak' WITH INIT
IF (@.@.error<>0)
BEGIN
EXEC sp_user_counter1 900150
WAITFOR DELAY '00:01:10'
EXEC sp_user_counter1 0
END
Now, this keeps the counter1 with given value for 70 seconds. This is enough
time for the manager program to poll and pick up the value. This is then send
as a page or e-mail.
I tried to implement similar approach on system generated jobs (in SQL
Agent|Scheduler) once transactional replication was created. However, it
didn't work out.
What I basically need is to get information on some perfmon counter(s) that
shows "a change in state" if there is an error in any replication process
(logreader, distributer). The third party vendor we are using has ability to
monitor perfmon counters and provide information as alerts or graphs. FYI...
our internal standards do not allow us to use any of the SQL Server provided
alerting mechanisms (like SQL mail or smtp mail).
Is this clarification enough for you to understand what I am trying to get?
"Michael Cheng [MSFT]" wrote:

> Hi MZeeshan,
> From your descriptions, I understand you would like to select a better
> performance counter for your monitoring. However, I am not sure what you
> are going to monitor and improve. Would you please show me more detailed
> information, which I believe will make us closer to the resolution
> You could find detailed introduction of all replication performance
> counters in BOL topic "Using SQL Server Objects"
>
> Sincerely yours,
> Michael Cheng
> 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.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Thanks for the information. I'll definitely check the counter information.
"Hilary Cotter" wrote:

> use replication alerts for this. Connect to your sql server using EM, expand
> your server, expand the replication monitor node, and then expand the
> replication alerts section.
> You could monitor conflicts in SQLServer:Replication Merge, monitor the
> latency of SQLServer:Replication LogReader and SQLServer:Replication Dist.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:F2BF344D-36CE-47D8-8D76-DDD8A0150B71@.microsoft.com...
> general
> the
>
>
|||Hi MZeeshan,

>What I basically need is to get information on some perfmon counter(s)
that
>shows "a change in state" if there is an error in any replication process
>(logreader, distributer).
Based on my experience, you may perform:
1. SQL Server: Replication Agents Object. It represents the number of
instances of a given replication agent running currently.
2. Since Replication agents (logreader, distributer) correlate to some SQL
Agent jobs, an agent's failing is equivalent to a job's failing. They
should be able to add a step and run something like:
EXEC sp_user_counter2 nnnnnn
WAITFOR DELAY '00:01:10'
EXEC sp_user_counter2 0
Once the step in the replication agent job fails, the custom step can be
run.
However, please note that you will have to test it in your development
scenario before deploy it to the production server.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
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.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I'll definitely check #1. However, with #2, I have already tested that and it
doesn't work. Every time there is a failure, the task will fail and a system
error is generated resulting in non-execution of the remaining portion of the
task.
I had similar problem with DBCC monitoring but I end up creating a DTS
package utilizing ON FAILURE and ON SUCCESS workflow options to identify next
command execution (in other words... generation of alerts through them).
However, I don't have that luxury of making such changes in system setup
replication tasks.
"Michael Cheng [MSFT]" wrote:

> Hi MZeeshan,
> that
> Based on my experience, you may perform:
> 1. SQL Server: Replication Agents Object. It represents the number of
> instances of a given replication agent running currently.
> 2. Since Replication agents (logreader, distributer) correlate to some SQL
> Agent jobs, an agent's failing is equivalent to a job's failing. They
> should be able to add a step and run something like:
> EXEC sp_user_counter2 nnnnnn
> WAITFOR DELAY '00:01:10'
> EXEC sp_user_counter2 0
> Once the step in the replication agent job fails, the custom step can be
> run.
> However, please note that you will have to test it in your development
> scenario before deploy it to the production server.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> 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.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||I checked different Replication counters and the two below as
SQL Server: Replication Logreader: Delivery Latency
Normal Working Range: 2-7 seconds
Error Threshold: 60 sec
Monitoring Duration: 2 minutus
SQL Server: Replication Distribution: Delivery Latency
Normal Working Range: 8-9 seconds
Error Threshold: 90 sec
Monitoring Duration: 2 minutus
In words, if the vendor Manager application finds out that the counters have
value equal to or higher than given in Error Threshold and finds this for two
cycles (2 minutes), then it will generate an error alert.
How that sounds?
"MZeeshan" wrote:
[vbcol=seagreen]
> I'll definitely check #1. However, with #2, I have already tested that and it
> doesn't work. Every time there is a failure, the task will fail and a system
> error is generated resulting in non-execution of the remaining portion of the
> task.
> I had similar problem with DBCC monitoring but I end up creating a DTS
> package utilizing ON FAILURE and ON SUCCESS workflow options to identify next
> command execution (in other words... generation of alerts through them).
> However, I don't have that luxury of making such changes in system setup
> replication tasks.
> "Michael Cheng [MSFT]" wrote:
|||Hi MZeeshan,
It's OK and if you have any questions or experience during your monitoring,
don't hesitate to let us know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
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.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Generating overnight data vs Live - suggestions needed.

We have an MIS system which has approx 100 reports. Each of these
reports can take up to several minutes to run due to the complexity of
the queries (hundreds of lines each in most cases). Each report can be
run by many users, so in effect we have a slow system.

I want to seperate the complex part of the queries into a process that
is generated each night. Then the reports will only have to query
pre-formatted data with minimal parameters as the hard part will have
been completed for the users when they are not in. Ideally we will
generate (stored procedure possibly) a set of data for each report and
hold this on the server. We can then query with simpler parameters
such as by date and get the data back quite quickly.

The whole process of how we obtain the data is very complex. There are
various views which gather data from the back office system. These are
very complex and when queries are run against them including other
tables to bring in more data, it gets nicely complicated.

The only problem is that the users want to have access to LIVE data
from the back office system, specifically the Sales team who want to
access this remotely. My method only allows for data from the night
before, so is there an option available to me which will allow me to
do this ? The queries can't be improved on an awful lot, so they will
take as long as they take. The idea of running them once is the only
way I can see to improve the performance in any significant way.

True I could just let them carry on as they are and let them suffer
with the performance on live data, but I'd like to do something to
improve the situation for them.

Any advice would be appreciated.

Thanks

RyanYep, that definitely makes a difference. Taken me a while to get back
to this, but we're looking at a solution along these lines.

By taking a copy of the data from the view into a table and then
replicating this onto another database (for generating) and then
running stored procedures (from each complex query) against this,
we've been able to reduce the time from 5 minutes for a test generate
query to about 3 seconds. We've timed the other parts and can probably
generate all of our data in less than 5 minutes for 100 complex
queries that would normally take hours.

Once this data is generated we'll simply swap it for the current data
which should be pretty quick. Our report now loads in less than a
second as opposed to 5 mins or so.

This means we can probably update the data once an hour and push it
onto the query database which will be more than quick enough for the
users. This has the knock on effect of being able to produce the
reports in seconds instead of minutes.

Erland, thanks for the advice on this it should make quite a
difference.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93DEF27F53F61Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > The only problem is that the users want to have access to LIVE data
> > from the back office system, specifically the Sales team who want to
> > access this remotely. My method only allows for data from the night
> > before, so is there an option available to me which will allow me to
> > do this ? The queries can't be improved on an awful lot, so they will
> > take as long as they take. The idea of running them once is the only
> > way I can see to improve the performance in any significant way.
> If users want current data, you cannot pre-compute it for them. You
> know, have the cake and eat it.
> What you could to do is to set up a report server which you replicate
> to, so that you take the load of the back-office system.
> Yet another alternative, is to have three databases:
> A - the source-system.
> B - the view system that the users use.-
> C - a computation system.
> You keep the computation database updated by means of log shipping
> (this is better than replication in this case). One you have applied
> a log, you pre-compute all data. When this step is done, you flush the
> pre-computed tables on B, and insert the data from C. Now you go and
> pick up a new log from A. A more fancy variant is to have a seamless
> switch between B and C which you flip constantly. I believe there are
> such things.|||Ryan (ryanofford@.hotmail.com) writes:
> By taking a copy of the data from the view into a table and then
> replicating this onto another database (for generating) and then
> running stored procedures (from each complex query) against this,
> we've been able to reduce the time from 5 minutes for a test generate
> query to about 3 seconds. We've timed the other parts and can probably
> generate all of our data in less than 5 minutes for 100 complex
> queries that would normally take hours.

300 seconds down to three! That's not bad!

> Erland, thanks for the advice on this it should make quite a
> difference.

I'm only glad to have helped! And thanks for reporing back!

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

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

generating DDL

In Oracle, there is a system packaged function that returns the DDL for a
database object (DBMS_METADATA.GET_DDL). What is the equivalent in SQL
Server?you can generate DDL from an object using Query Analyzer by
right-clicking the object in the object browser and selecting "Script
Object to <dest> as <action>"
- Baileys
arch wrote:
> In Oracle, there is a system packaged function that returns the DDL for a
> database object (DBMS_METADATA.GET_DDL). What is the equivalent in SQL
> Server?
>|||Yes, I know. But I wanted the DDL to be used in a query. I need a function
or something that returns so I can use it in an SQL statement.
"Baileys" <no_email@.server.com> wrote in message
news:%23%23B7GGLhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> you can generate DDL from an object using Query Analyzer by right-clicking
> the object in the object browser and selecting "Script Object to <dest> as
> <action>"
> - Baileys
> arch wrote:|||You can use SQL DMO to get a table object and then use the script function t
o
script out the table.
You can search for the script function in Books Online. If you need this as
a function look for activating COM objects though the use of extended stored
procedures.
Arun
"arch" wrote:

> Yes, I know. But I wanted the DDL to be used in a query. I need a functi
on
> or something that returns so I can use it in an SQL statement.
> "Baileys" <no_email@.server.com> wrote in message
> news:%23%23B7GGLhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>
>|||arch wrote:
> Yes, I know. But I wanted the DDL to be used in a query. I need a functi
on
> or something that returns so I can use it in an SQL statement.
>
You can't directly generate SQL from within SQL code itself.
As another poster said, you can use DMO, which is a COM object (aka
ActiveX), with some surrounding code, to build your SQL statements,
then submit the to the database.
Or, in some cases you could roll your own sql-building code that would
work within SQL. If you're interested in tables, for example, the
metatable INFORMATION_SCHEMA.COLUMNS can give you column names, data
types and lengths; INFORMATION_SCHEME.CONSTRAINT_COLUMN_USAGE gives
information about which columns are involved in constraints, etc. That
would be a particularly tedious path to have to follow, in my opinion.

Generate insert statement for table

I have data in a table that I wish to transfer to a remote system (both SQL
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...

quote:

---

> I have data in a table that I wish to transfer to a remote system (both

---

SQL

quote:

---

> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If

---

possible

quote:

---

> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>

---

|||Here's a PERL script for generating SQL INSERT statements.
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-

quote:

---

>SQL_COLUMN_TYPE_NAME, @.fields);

---

# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help '$attrib{$field}'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/'/''/g;
push @.values, "'$data{$column}'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############

quote:

---

>--Original Message--
>I have data in a table that I wish to transfer to a

---

remote system (both SQL

quote:

---

>2000).
>Is there an easy way to generate a series of insert

---

statements from my

quote:

---

>database so I can transfer this text file to my remote

---

system. If possible

quote:

---

>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:

---

19/01/2004

quote:

---

>
>.
>

---

|||If that's your sole purpose, you can get by more easily with
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...

quote:

---

> I have data in a table that I wish to transfer to a remote system (both

---

SQL

quote:

---

> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If

---

possible

quote:

---

> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>

---

Generate insert statement for table

I have data in a table that I wish to transfer to a remote system (both SQL
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>|||Here's a Perl script for generating SQL INSERT statements.
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
>SQL_COLUMN_TYPE_NAME, @.fields);
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help \'$attrib{$field}\'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/\'/\'\'/g;
push @.values, "\'$data{$column}\'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
>--Original Message--
>I have data in a table that I wish to transfer to a
remote system (both SQL
>2000).
>Is there an easy way to generate a series of insert
statements from my
>database so I can transfer this text file to my remote
system. If possible
>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:
19/01/2004
>
>.
>|||If that's your sole purpose, you can get by more easily with
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>

General Technique Advice - One Server Updating Another

I have a situation where a web application needs to obtain hourly updates as
to inventory levels from second system. There is also a once-a-day update w
here new catalog products that have been initially entered into the second s
ystem need to be automatica
lly pulled and inserted into the web application where they must be massaged
and made ready for presentation on the web site. Both systems have MS SQL S
erver backends.
My question is ... what is the most common, simplest, most reliable, uses le
ast overhead ... (best) ... way to go about this?
Is this what Subscriptions are for? Stored Procedures?
Thanks for your advice,
Chip DukesChip,
you could use DTS or replication or linked servers.
If DTS, you would have 2 packages, each scheduled.
If replication, then the hourly movement would be transactional, and
once-a-day process would be transactional with a transformable subscription
(which amounts to using DTS behind the scenes anyway). Alternatively, the
second process could be plain transactional and (indexed) views used on the
subscriber to massage the output.
As a third posibility you could use linked servers and scheduled jobs.
My preference would be transactional replication. This has a series of
alerts to maintain notification of the process success/failure, and the
distribution database can easily be queried at any time to see which
transactions are waiting in the 'pipeline', ready to be delivered, so
troubleshooting is not difficult. The only stipulation is that you have
primary keys on your table articles, but I doubt that would be an issue.
HTH,
Paul Ibison