Grid display: sql2000

Showing posts with label sql2000. Show all posts

Thursday, March 29, 2012

Get Backup Directory

I am writing a stored procedure, and I want to set a variable to the default Backup Directory for the current instance. (7.0 or SQL2000).

What is the Standard"way of doing this

Do I have to write a C program that calls HRESULT GetBackupDirectory(SQLDMO_LPBSTR pRetVal);

Or should I install DtReg.exe on all my servers?

/* get default backup location -- by Bob Sturnfield */
--DtReg.exe can be found at http://www.tamedos.com/downloads
set nocount on
declare @.string varchar(4000),
@.regloc varchar(100),
@.BackupDirectory varchar(1000),
@.servernm varchar(30)

select @.regloc='MSSQLServer'
select @.servernm=rtrim(convert(varchar(30),SERVERPROPERTY ('servername')))
if CHARINDEX('\', @.servernm)>0
select @.regloc='Microsoft SQL Server\' + substring(@.servernm, CHARINDEX('\', @.servernm)+1, 30)

create table #DtReg( BackupDirectory varchar(4000))

select @.string='xp_cmdshell ''DtReg -ListValue "HKEY_LOCAL_MACHINE\Software\Microsoft\' +
@.regloc + '\MSSQLServer\BackupDirectory"'''

insert into #DtReg exec(@.string)

select top 1 @.BackupDirectory=substring(BackupDirectory,8,1000) from #DtReg
Where BackupDirectory like 'REG_SZ%'

if @.@.rowcount<>1
Select * from #DtReg

drop table #DtReg

print @.BackupDirectoryHave you looked at xp_regread ?

exec xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'BackupDirectory'

Value Data
------------------------
BackupDirectory C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP|||Thank you,

xp_regread -- not in books on-line

http://www.4guysfromrolla.com/webtech/101499-1.shtml

There's a little known xp procedure in the master database called xp_regread. This stored procedure accepts three parameters. The first one being the root key, next is the path to the key, and finally the key value you are looking to return.

http://www.swynk.com/friends/green/xp_reg.asp

xp_regread [@.rootkey=]'rootkey', [@.key=]'key'[, [@.value_name=]'value_name'][, [@.value=]@.value OUTPUT]

xp_regwrite [@.rootkey=]'rootkey', [@.key=]'key', [@.value_name=]'value_name', [@.type=]'type', [@.value=]'value'

http://www.sql-server-performance.com/ac_extended_stored_procedures.asp

These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.

This is great, I very much appreciate the response

Bob Sturnfield

/* get default backup location -- by Bob Sturnfield */
set nocount on
declare @.regloc varchar(100),
@.BackupDirectory varchar(1000)

select @.regloc=
'Software\Microsoft\MSSQLServer\MSSQLServer'

if CHARINDEX('\\', @.@.servername)>0
select @.regloc='Software\Microsoft\Microsoft SQL Server\' +
substring(@.@.servername, CHARINDEX('\', @.@.servername)+1, 30)+ '\MSSQLServer'

execute master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key=@.regloc, @.value_name='BackupDirectory',
@.value=@.BackupDirectory OUTPUT

print @.BackupDirectory

Tuesday, March 27, 2012

Geographic Cluster

I have one site which is running W2K with one IIS server with 100
Websites and one SQL2000 server, we are running with 6 meg of
bandwidth at our main site and 1.5 meg of bandwith at our remote
site.in case of a disaster at my main site, i want my remote site to
start taking web requests. I am looking into Microsofts Geographic
Cluster option, how would I go about setting up this option? can it
all be done with Microsofts products, if so how? if not what third
party software do I need? I have not used clustering before, so I
might have left something out, if more info is needed, please let me
know.Fist, MS has no geographic cluster option. Unless you are
referring to a Majority Node Set cluster, which is only in
Windows Server 2003 (which isn't good for SQL and I'll
explain in a minute), all geographic cluster solutions are
third-party based. You should read KB article
http://support.microsoft.com/default.aspx?scid=kb;en-
us;280743.
MNS clusters are not good for SQL Server because in a
clustered situation, you still have a shared disk
requirement.
Other options you may want to consider are things like NLB
for your web servers and log shipping to create the
standby DB at your other site.
>--Original Message--
>I have one site which is running W2K with one IIS server
with 100
>Websites and one SQL2000 server, we are running with 6
meg of
>bandwidth at our main site and 1.5 meg of bandwith at
our remote
>site.in case of a disaster at my main site, i want my
remote site to
>start taking web requests. I am looking into Microsofts
Geographic
>Cluster option, how would I go about setting up this
option? can it
>all be done with Microsofts products, if so how? if not
what third
>party software do I need? I have not used clustering
before, so I
>might have left something out, if more info is needed,
please let me
>know.
>.
>|||thanks for the quick response.
On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
<allanh@.NOSPAMavanade.com> wrote:
>Fist, MS has no geographic cluster option. Unless you are
>referring to a Majority Node Set cluster, which is only in
>Windows Server 2003 (which isn't good for SQL and I'll
>explain in a minute), all geographic cluster solutions are
>third-party based. You should read KB article
>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;280743.
>MNS clusters are not good for SQL Server because in a
>clustered situation, you still have a shared disk
>requirement.
>Other options you may want to consider are things like NLB
>for your web servers and log shipping to create the
>standby DB at your other site.
>
>>--Original Message--
>>I have one site which is running W2K with one IIS server
>with 100
>>Websites and one SQL2000 server, we are running with 6
>meg of
>>bandwidth at our main site and 1.5 meg of bandwith at
>our remote
>>site.in case of a disaster at my main site, i want my
>remote site to
>>start taking web requests. I am looking into Microsofts
>Geographic
>>Cluster option, how would I go about setting up this
>option? can it
>>all be done with Microsofts products, if so how? if not
>what third
>>party software do I need? I have not used clustering
>before, so I
>>might have left something out, if more info is needed,
>please let me
>>know.
>>.|||Allen,
do you know what third party packages are out there for handling
geographic clustering?
On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
<allanh@.NOSPAMavanade.com> wrote:
>Fist, MS has no geographic cluster option. Unless you are
>referring to a Majority Node Set cluster, which is only in
>Windows Server 2003 (which isn't good for SQL and I'll
>explain in a minute), all geographic cluster solutions are
>third-party based. You should read KB article
>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;280743.
>MNS clusters are not good for SQL Server because in a
>clustered situation, you still have a shared disk
>requirement.
>Other options you may want to consider are things like NLB
>for your web servers and log shipping to create the
>standby DB at your other site.
>
>>--Original Message--
>>I have one site which is running W2K with one IIS server
>with 100
>>Websites and one SQL2000 server, we are running with 6
>meg of
>>bandwidth at our main site and 1.5 meg of bandwith at
>our remote
>>site.in case of a disaster at my main site, i want my
>remote site to
>>start taking web requests. I am looking into Microsofts
>Geographic
>>Cluster option, how would I go about setting up this
>option? can it
>>all be done with Microsofts products, if so how? if not
>what third
>>party software do I need? I have not used clustering
>before, so I
>>might have left something out, if more info is needed,
>please let me
>>know.
>>.|||http://www.nsisoftware.com/pro/geocluster/
"Pat" <htech@.hotmail.com> wrote in message
news:t1ihtv4b04b6ten1u43ohha02a7vmsrc19@.4ax.com...
>
> Allen,
> do you know what third party packages are out there for handling
> geographic clustering?
> On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
> <allanh@.NOSPAMavanade.com> wrote:
> >Fist, MS has no geographic cluster option. Unless you are
> >referring to a Majority Node Set cluster, which is only in
> >Windows Server 2003 (which isn't good for SQL and I'll
> >explain in a minute), all geographic cluster solutions are
> >third-party based. You should read KB article
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;280743.
> >
> >MNS clusters are not good for SQL Server because in a
> >clustered situation, you still have a shared disk
> >requirement.
> >
> >Other options you may want to consider are things like NLB
> >for your web servers and log shipping to create the
> >standby DB at your other site.
> >
> >
> >>--Original Message--
> >>I have one site which is running W2K with one IIS server
> >with 100
> >>Websites and one SQL2000 server, we are running with 6
> >meg of
> >>bandwidth at our main site and 1.5 meg of bandwith at
> >our remote
> >>site.in case of a disaster at my main site, i want my
> >remote site to
> >>start taking web requests. I am looking into Microsofts
> >Geographic
> >>Cluster option, how would I go about setting up this
> >option? can it
> >>all be done with Microsofts products, if so how? if not
> >what third
> >>party software do I need? I have not used clustering
> >before, so I
> >>might have left something out, if more info is needed,
> >please let me
> >>know.
> >>.
> >>
>|||Thank You.
On Thu, 11 Dec 2003 16:19:38 -0500, "Eric Sabine"
<mopar41@.____h_o_t_m_a_i_l_._ScPoAmM> wrote:
>http://www.nsisoftware.com/pro/geocluster/
>
>"Pat" <htech@.hotmail.com> wrote in message
>news:t1ihtv4b04b6ten1u43ohha02a7vmsrc19@.4ax.com...
>>
>> Allen,
>> do you know what third party packages are out there for handling
>> geographic clustering?
>> On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
>> <allanh@.NOSPAMavanade.com> wrote:
>> >Fist, MS has no geographic cluster option. Unless you are
>> >referring to a Majority Node Set cluster, which is only in
>> >Windows Server 2003 (which isn't good for SQL and I'll
>> >explain in a minute), all geographic cluster solutions are
>> >third-party based. You should read KB article
>> >http://support.microsoft.com/default.aspx?scid=kb;en-
>> >us;280743.
>> >
>> >MNS clusters are not good for SQL Server because in a
>> >clustered situation, you still have a shared disk
>> >requirement.
>> >
>> >Other options you may want to consider are things like NLB
>> >for your web servers and log shipping to create the
>> >standby DB at your other site.
>> >
>> >
>> >>--Original Message--
>> >>I have one site which is running W2K with one IIS server
>> >with 100
>> >>Websites and one SQL2000 server, we are running with 6
>> >meg of
>> >>bandwidth at our main site and 1.5 meg of bandwith at
>> >our remote
>> >>site.in case of a disaster at my main site, i want my
>> >remote site to
>> >>start taking web requests. I am looking into Microsofts
>> >Geographic
>> >>Cluster option, how would I go about setting up this
>> >option? can it
>> >>all be done with Microsofts products, if so how? if not
>> >what third
>> >>party software do I need? I have not used clustering
>> >before, so I
>> >>might have left something out, if more info is needed,
>> >please let me
>> >>know.
>> >>.
>> >>
>sql

Friday, February 24, 2012

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...