generate login as well as user script

I know from sp_help_revlogin, we can generate login with correct password
information, how to generate a one step script for particular user with the
login as well as privileges information? sp_help_revlogin only generates the
login information, but not including the roles granted to the user.I don't think there is any script available to do this.
Mohammed.
"renhai" wrote:
> I know from sp_help_revlogin, we can generate login with correct password
> information, how to generate a one step script for particular user with the
> login as well as privileges information? sp_help_revlogin only generates the
> login information, but not including the roles granted to the user.

generate login as well as user script

I know from sp_help_revlogin, we can generate login with correct password
information, how to generate a one step script for particular user with the
login as well as privileges information? sp_help_revlogin only generates the
login information, but not including the roles granted to the user.I don't think there is any script available to do this.
Mohammed.
"renhai" wrote:

> I know from sp_help_revlogin, we can generate login with correct password
> information, how to generate a one step script for particular user with th
e
> login as well as privileges information? sp_help_revlogin only generates t
he
> login information, but not including the roles granted to the user.

generate login as well as user script

I know from sp_help_revlogin, we can generate login with correct password
information, how to generate a one step script for particular user with the
login as well as privileges information? sp_help_revlogin only generates the
login information, but not including the roles granted to the user.
I don't think there is any script available to do this.
Mohammed.
"renhai" wrote:

> I know from sp_help_revlogin, we can generate login with correct password
> information, how to generate a one step script for particular user with the
> login as well as privileges information? sp_help_revlogin only generates the
> login information, but not including the roles granted to the user.

Generate linked series

Hi,
I would like to generate the following:
suppose we have the following table:
Table1:
Col1 Col2
a b
a c
d e
f c
d b
Then the result should give the following series:
b, a, c
a, b, d
a, c, f
e, d, b
b, a, c, f
Anyone an idea what would be the fastest way to resolve this?
Thanx in advance...
PeterThere are no built-in SQL statements which generates such series. The sample
data somewhat resembles hierarchical data representation. Please search
google for "Nested Sets" and "Path Enumeration" which are two commonly used
approaches to tackle such problems.
Anith|||You need to provide more info about what you want... I had a hard time even
discerning a pattern between the table data and the desired output, and the
one pattern I seemed to get doesn't make any sense...
(because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
the output...)
"PeterM" wrote:

> Hi,
> I would like to generate the following:
> suppose we have the following table:
> Table1:
> Col1 Col2
> a b
> a c
> d e
> f c
> d b
> Then the result should give the following series:
> b, a, c
> a, b, d
> a, c, f
> e, d, b
> b, a, c, f
> Anyone an idea what would be the fastest way to resolve this?
> Thanx in advance...
> Peter
>|||Hi CBretana,
The logic behind it is the following:
I try to search for all possible length "paths" (until the longest path is
found);
these are created by using each row and see if one of the two columns in the
row also exist in another row, if this is the case I concatenate the result
as a "path" (and where the "link" value is only used once in the string).
I hope you see the point?
Peter
"CBretana" wrote:
> You need to provide more info about what you want... I had a hard time eve
n
> discerning a pattern between the table data and the desired output, and th
e
> one pattern I seemed to get doesn't make any sense...
> (because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
> the output...)
> "PeterM" wrote:
>|||Hi,
Let me just explain where this would be useful:
by using the sp_fkeys on all tables in a relational design, I could build a
result table containing rows with all the PK_Table and their related FK_Tabl
e
info.
With this table I would search for all possible "paths" within my relational
design no matter the "depth" of the "path".
In this way I could easily generate, no matter which tables I would give as
input, the shortest path to connect these input tables together and make a
dynamic join statement.
That's why I'm really interested in a solution (preferably also performant).
Peter
"CBretana" wrote:
> You need to provide more info about what you want... I had a hard time eve
n
> discerning a pattern between the table data and the desired output, and th
e
> one pattern I seemed to get doesn't make any sense...
> (because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
> the output...)
> "PeterM" wrote:
>|||Peter,
Don't you want to restrict the generation of these paths to go in one
direction only? what I mean is that a value in Column 1 of some row of the
table that also exists in Column 1 of another record is an intrinsically
different kind of relationship than when it exists in Column 2 of some other
row in the table.. Using the example you describe in yr oother post, one
column is PKs, and other would be FKs, right ? In that case would you ever
WANT to create "Path" that connects two values becase they were both in the
FK column of the table ' That would not imply any relationship at all...
"PeterM" wrote:
> Hi CBretana,
> The logic behind it is the following:
> I try to search for all possible length "paths" (until the longest path is
> found);
> these are created by using each row and see if one of the two columns in t
he
> row also exist in another row, if this is the case I concatenate the resul
t
> as a "path" (and where the "link" value is only used once in the string).
> I hope you see the point?
> Peter
> "CBretana" wrote:
>|||CBretana,
No I don't want any restriction because it is perfectly possible that two
tables are lnked to eachother by their FK_Table; how else would you represen
t
many to many relationships?
Peter
"CBretana" wrote:
> Peter,
> Don't you want to restrict the generation of these paths to go in one
> direction only? what I mean is that a value in Column 1 of some row of th
e
> table that also exists in Column 1 of another record is an intrinsically
> different kind of relationship than when it exists in Column 2 of some oth
er
> row in the table.. Using the example you describe in yr oother post, one
> column is PKs, and other would be FKs, right ? In that case would you ev
er
> WANT to create "Path" that connects two values becase they were both in t
he
> FK column of the table ' That would not imply any relationship at all...
> "PeterM" wrote:
>|||The right way to represent a many-to-many relationship is with a thord table
containing a multple-column (composite) primary Key, consisting of the Both
Primary Keys from the two other tables that have the many-to-many
relationship.
In this third table there is a single Composite PK, and 2 Foreign Keys, one
each to the PK of each of the two original tables.
"PeterM" wrote:
> CBretana,
> No I don't want any restriction because it is perfectly possible that two
> tables are lnked to eachother by their FK_Table; how else would you repres
ent
> many to many relationships?
> Peter
> "CBretana" wrote:
>|||CBrentana,
The third table you're talking about is exactly the FK_table of my example.
If you would run sp_fkeys on the PK_Tables the result would be two rows:
PK_Table1 - FK_Table
PK_Table2 - FK_Table
By linking the result of these two rows you would find the path to link
PK_Table1 with PK_Table2 (through the FK_Table in common).
which is exactly the point of depart.
So I hope you understand the question now (since you confirmed the point of
the many-to-many relationships).
Look I'm more interested in a solution of my question than in trying to
convince you in the "usefullness" of my question, if this would still be you
r
concern.;-)
Thanx...
Peter
"CBretana" wrote:
> The right way to represent a many-to-many relationship is with a thord tab
le
> containing a multple-column (composite) primary Key, consisting of the Bot
h
> Primary Keys from the two other tables that have the many-to-many
> relationship.
> In this third table there is a single Composite PK, and 2 Foreign Keys, on
e
> each to the PK of each of the two original tables.
>
> "PeterM" wrote:
>|||CBrentana,
In reviewing your answers I noticed we're both using different terms.
You're talking about FK_columns, where I'm talking about FK_Tables.
The columns of my examples contain Tables (PK_Tables or FK_Tables) and not
PK-FK columns (that would indeed make no sense). So instead of thinking in
terms of columns change to tables and you'll see that it makes sense :-)
Peter
"PeterM" wrote:
> CBrentana,
> The third table you're talking about is exactly the FK_table of my example
.
> If you would run sp_fkeys on the PK_Tables the result would be two rows:
> PK_Table1 - FK_Table
> PK_Table2 - FK_Table
> By linking the result of these two rows you would find the path to link
> PK_Table1 with PK_Table2 (through the FK_Table in common).
> which is exactly the point of depart.
> So I hope you understand the question now (since you confirmed the point o
f
> the many-to-many relationships).
> Look I'm more interested in a solution of my question than in trying to
> convince you in the "usefullness" of my question, if this would still be y
our
> concern.;-)
> Thanx...
> Peter
>
> "CBretana" wrote:
>

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
>

Generate insert script

does anyone know if there is a way to generate an insert script based
on the records in a table? I want to distribute the contents of a
couple of tables to customers without having to use BCP or DTS.

Greetings Sjaak"Sjaak van Esdonk" <sjaakvanesdonk@.hotmail.com> wrote in message
news:7479e65c.0404050347.1105649b@.posting.google.c om...
> does anyone know if there is a way to generate an insert script based
> on the records in a table? I want to distribute the contents of a
> couple of tables to customers without having to use BCP or DTS.
> Greetings Sjaak

http://vyaskn.tripod.com/code.htm#inserts
http://www.rac4sql.net/objectscriptr_main.asp

Simon|||Thanks Simon, thats exactly what i needed! This is going to save me lots of time !|||sjaakvanesdonk@.hotmail.com (Sjaak van Esdonk) wrote in message news:<7479e65c.0404050347.1105649b@.posting.google.com>...
> does anyone know if there is a way to generate an insert script based
> on the records in a table? I want to distribute the contents of a
> couple of tables to customers without having to use BCP or DTS.
> Greetings Sjaak

Hi,
use SQL Scripter (http://www.sqlscripter.com) to create Insert, Update
or Delete scripts ...

Thomas