How can i output result to a log file?

Hi all,
In case i have a script file containt tables, functions, ... when i use Query Analyzer to run this file, the result output in a window. Now i want this result output to a file named logfile.txt. How can i do that?
Thanks first.qa has this capability. Options -> Results -> Defaul results to: FILE.

if you want to do it from command line, you can use OSQL. See book online for details.

How can I obtain a report of users and roles in SQL Server?

I need a query (script) or a tool to obtain a report (or audit report) for
all databases instanced into SQL Server, just for an entittlement review aun
audit reports.
The fields are:
User, role, permissions, last date access
Best regards to everyone.
JosSee:-
SYSPROTECT system table and system procedure SP_HELPROTECT
Thanks
Hari
SQL Server MVP
"Jos Archondo" <jarchondo@.spvs.gov.bo> wrote in message
news:uMv%23UlC1FHA.664@.tk2msftngp13.phx.gbl...
>I need a query (script) or a tool to obtain a report (or audit report) for
> all databases instanced into SQL Server, just for an entittlement review
> aun
> audit reports.
> The fields are:
> User, role, permissions, last date access
> Best regards to everyone.
> Jos
>|||You should also run sp_helplogins. That will help define where
sp_helprotect should be run.
RLF
"Jos Archondo" <jarchondo@.spvs.gov.bo> wrote in message
news:uMv%23UlC1FHA.664@.tk2msftngp13.phx.gbl...
>I need a query (script) or a tool to obtain a report (or audit report) for
> all databases instanced into SQL Server, just for an entittlement review
> aun
> audit reports.
> The fields are:
> User, role, permissions, last date access
> Best regards to everyone.
> Jos
>

How can i move logins after a Mirror failover

I've search a lot of articles trying to find a script which not also re
create SQL logins from one server to another but that also makes really
automatically a SQL 2005 Mirror failover (map sql login to database and
set permissions)
Does anybody know how to do this? I am looking for a script.
Hi
The logins should be created on the mirror server before you invoke
failover. This is easier if you use trusted connections and have your users
in a windows group (as only the group needs to be added).
See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
http://support.microsoft.com/kb/246133/ for a script.
John
"jocamp3@.gmail.com" wrote:

> I've search a lot of articles trying to find a script which not also re
> create SQL logins from one server to another but that also makes really
> automatically a SQL 2005 Mirror failover (map sql login to database and
> set permissions)
> Does anybody know how to do this? I am looking for a script.
>
|||Hello John:
Thanks for reply.
This does not work. Yes .. i can create a login in the Mirror Server
but i can not map that login name to the desired database, because
simply it does not exists or it's in recovery state. So ... when i make
the failover, app. does not have the required credentials to log in,
because the SQL login is there but with no map to the Mirrored
database.
Got my point?
John Bell wrote:[vbcol=seagreen]
> Hi
> The logins should be created on the mirror server before you invoke
> failover. This is easier if you use trusted connections and have your users
> in a windows group (as only the group needs to be added).
> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> http://support.microsoft.com/kb/246133/ for a script.
> John
> "jocamp3@.gmail.com" wrote:
|||Hello Tibor:
Maybe i'm doin' something wrong ... or i can not explain myself well.
...Could you please provide me the steps order or the whole process?
Mirror 1st? Script first? etc ... i will try to recreate everything
again.
Tibor Karaszi wrote:[vbcol=seagreen]
> But the mapping exists *inside* the database, so it is already there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegr oups.com...
|||Hi
sp_help_revlogin and how to use it is described in the second link I posted
http://support.microsoft.com/kb/246133/
John
"jocamp3@.gmail.com" wrote:

> Hello Tibor:
> Maybe i'm doin' something wrong ... or i can not explain myself well.
> ...Could you please provide me the steps order or the whole process?
> Mirror 1st? Script first? etc ... i will try to recreate everything
> again.
> Tibor Karaszi wrote:
>
|||Ok ... got it! ... and i will post this because there are a lot of
persons with the same problem. I can not believe nobody knows about
this ...
Some times, a step by step explanation helps much more than just point
to a simple script or link ...
1. On the server which will be Principal, create the Login (which the
app. will use to connect) at Server level WITH NO MAPPING to the
database. Put master database at default db instead.
2. Use SSID and execute the required package to copy Logins from one
server to another. If package turns green, it works. You should also be
able to see the Login in the second server.
3. Now, on 1st server again, set database mapping for SQL login. Set
the required permissions to database also (data reader, data writer,
etc)
3. Restore database on 2nd server (future mirror) in recovery mode.
4. Establish the Mirror
5. Test the failover: check the 2nd server (now the Principal) and you
should be able to see the SQL login. Change default database from
master to the desired database (now you can, because you're in the
principal)
6. Make another manual failover. Since now ... SQL server login its
already there and mapping exist with the required permissions also.
Jose Campo
John Bell wrote:[vbcol=seagreen]
> Hi
> sp_help_revlogin and how to use it is described in the second link I posted
> http://support.microsoft.com/kb/246133/
> John
> "jocamp3@.gmail.com" wrote:

How can i move logins after a Mirror failover

I've search a lot of articles trying to find a script which not also re
create SQL logins from one server to another but that also makes really
automatically a SQL 2005 Mirror failover (map sql login to database and
set permissions)
Does anybody know how to do this? I am looking for a script.Hi
The logins should be created on the mirror server before you invoke
failover. This is easier if you use trusted connections and have your users
in a windows group (as only the group needs to be added).
See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
http://support.microsoft.com/kb/246133/ for a script.
John
"jocamp3@.gmail.com" wrote:

> I've search a lot of articles trying to find a script which not also re
> create SQL logins from one server to another but that also makes really
> automatically a SQL 2005 Mirror failover (map sql login to database and
> set permissions)
> Does anybody know how to do this? I am looking for a script.
>|||Hello John:
Thanks for reply.
This does not work. Yes .. i can create a login in the Mirror Server
but i can not map that login name to the desired database, because
simply it does not exists or it's in recovery state. So ... when i make
the failover, app. does not have the required credentials to log in,
because the SQL login is there but with no map to the Mirrored
database.
Got my point?
John Bell wrote:[vbcol=seagreen]
> Hi
> The logins should be created on the mirror server before you invoke
> failover. This is easier if you use trusted connections and have your user
s
> in a windows group (as only the group needs to be added).
> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> http://support.microsoft.com/kb/246133/ for a script.
> John
> "jocamp3@.gmail.com" wrote:
>|||But the mapping exists *inside* the database, so it is already there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.
com...
> Hello John:
> Thanks for reply.
> This does not work. Yes .. i can create a login in the Mirror Server
> but i can not map that login name to the desired database, because
> simply it does not exists or it's in recovery state. So ... when i make
> the failover, app. does not have the required credentials to log in,
> because the SQL login is there but with no map to the Mirrored
> database.
> Got my point?
>
> John Bell wrote:
>|||Hello Tibor:
Maybe i'm doin' something wrong ... or i can not explain myself well.
...Could you please provide me the steps order or the whole process?
Mirror 1st? Script first? etc ... i will try to recreate everything
again.
Tibor Karaszi wrote:[vbcol=seagreen]
> But the mapping exists *inside* the database, so it is already there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g200
0cwb.googlegroups.com...|||Al that should be needed is to have the same logins existing on the mirror s
erver, and there having
the same name and SID. You can handle that using the sp_help_revlogin proced
ure in a scheduled job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jocamp3@.gmail.com> wrote in message news:1163112082.076012.93440@.e3g2000cwe.googlegroups.co
m...
> Hello Tibor:
> Maybe i'm doin' something wrong ... or i can not explain myself well.
> ...Could you please provide me the steps order or the whole process?
> Mirror 1st? Script first? etc ... i will try to recreate everything
> again.
> Tibor Karaszi wrote:
>|||Hi
sp_help_revlogin and how to use it is described in the second link I posted
http://support.microsoft.com/kb/246133/
John
"jocamp3@.gmail.com" wrote:

> Hello Tibor:
> Maybe i'm doin' something wrong ... or i can not explain myself well.
> ...Could you please provide me the steps order or the whole process?
> Mirror 1st? Script first? etc ... i will try to recreate everything
> again.
> Tibor Karaszi wrote:
>|||Ok ... got it! ... and i will post this because there are a lot of
persons with the same problem. I can not believe nobody knows about
this ...
Some times, a step by step explanation helps much more than just point
to a simple script or link ...
1. On the server which will be Principal, create the Login (which the
app. will use to connect) at Server level WITH NO MAPPING to the
database. Put master database at default db instead.
2. Use SSID and execute the required package to copy Logins from one
server to another. If package turns green, it works. You should also be
able to see the Login in the second server.
3. Now, on 1st server again, set database mapping for SQL login. Set
the required permissions to database also (data reader, data writer,
etc)
3. Restore database on 2nd server (future mirror) in recovery mode.
4. Establish the Mirror
5. Test the failover: check the 2nd server (now the Principal) and you
should be able to see the SQL login. Change default database from
master to the desired database (now you can, because you're in the
principal)
6. Make another manual failover. Since now ... SQL server login its
already there and mapping exist with the required permissions also.
Jose Campo
John Bell wrote:[vbcol=seagreen]
> Hi
> sp_help_revlogin and how to use it is described in the second link I poste
d
> http://support.microsoft.com/kb/246133/
> John
> "jocamp3@.gmail.com" wrote:
>|||Corrections... i must say SSIS (old DTS) instead of SSID.
jocamp3@.gmail.com wrote:[vbcol=seagreen]
> Ok ... got it! ... and i will post this because there are a lot of
> persons with the same problem. I can not believe nobody knows about
> this ...
> Some times, a step by step explanation helps much more than just point
> to a simple script or link ...
> 1. On the server which will be Principal, create the Login (which the
> app. will use to connect) at Server level WITH NO MAPPING to the
> database. Put master database at default db instead.
> 2. Use SSID and execute the required package to copy Logins from one
> server to another. If package turns green, it works. You should also be
> able to see the Login in the second server.
> 3. Now, on 1st server again, set database mapping for SQL login. Set
> the required permissions to database also (data reader, data writer,
> etc)
> 3. Restore database on 2nd server (future mirror) in recovery mode.
> 4. Establish the Mirror
> 5. Test the failover: check the 2nd server (now the Principal) and you
> should be able to see the SQL login. Change default database from
> master to the desired database (now you can, because you're in the
> principal)
> 6. Make another manual failover. Since now ... SQL server login its
> already there and mapping exist with the required permissions also.
> Jose Campo
>
> John Bell wrote:|||Corrections... i must say SSIS (old DTS) instead of SSID.
jocamp3@.gmail.com wrote:[vbcol=seagreen]
> Ok ... got it! ... and i will post this because there are a lot of
> persons with the same problem. I can not believe nobody knows about
> this ...
> Some times, a step by step explanation helps much more than just point
> to a simple script or link ...
> 1. On the server which will be Principal, create the Login (which the
> app. will use to connect) at Server level WITH NO MAPPING to the
> database. Put master database at default db instead.
> 2. Use SSID and execute the required package to copy Logins from one
> server to another. If package turns green, it works. You should also be
> able to see the Login in the second server.
> 3. Now, on 1st server again, set database mapping for SQL login. Set
> the required permissions to database also (data reader, data writer,
> etc)
> 3. Restore database on 2nd server (future mirror) in recovery mode.
> 4. Establish the Mirror
> 5. Test the failover: check the 2nd server (now the Principal) and you
> should be able to see the SQL login. Change default database from
> master to the desired database (now you can, because you're in the
> principal)
> 6. Make another manual failover. Since now ... SQL server login its
> already there and mapping exist with the required permissions also.
> Jose Campo
>
> John Bell wrote:sql

How can i move logins after a Mirror failover

I've search a lot of articles trying to find a script which not also re
create SQL logins from one server to another but that also makes really
automatically a SQL 2005 Mirror failover (map sql login to database and
set permissions)
Does anybody know how to do this? I am looking for a script.Hi
The logins should be created on the mirror server before you invoke
failover. This is easier if you use trusted connections and have your users
in a windows group (as only the group needs to be added).
See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
http://support.microsoft.com/kb/246133/ for a script.
John
"jocamp3@.gmail.com" wrote:
> I've search a lot of articles trying to find a script which not also re
> create SQL logins from one server to another but that also makes really
> automatically a SQL 2005 Mirror failover (map sql login to database and
> set permissions)
> Does anybody know how to do this? I am looking for a script.
>|||Hello John:
Thanks for reply.
This does not work. Yes .. i can create a login in the Mirror Server
but i can not map that login name to the desired database, because
simply it does not exists or it's in recovery state. So ... when i make
the failover, app. does not have the required credentials to log in,
because the SQL login is there but with no map to the Mirrored
database.
Got my point?
John Bell wrote:
> Hi
> The logins should be created on the mirror server before you invoke
> failover. This is easier if you use trusted connections and have your users
> in a windows group (as only the group needs to be added).
> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> http://support.microsoft.com/kb/246133/ for a script.
> John
> "jocamp3@.gmail.com" wrote:
> > I've search a lot of articles trying to find a script which not also re
> > create SQL logins from one server to another but that also makes really
> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > set permissions)
> >
> > Does anybody know how to do this? I am looking for a script.
> >
> >|||But the mapping exists *inside* the database, so it is already there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> Hello John:
> Thanks for reply.
> This does not work. Yes .. i can create a login in the Mirror Server
> but i can not map that login name to the desired database, because
> simply it does not exists or it's in recovery state. So ... when i make
> the failover, app. does not have the required credentials to log in,
> because the SQL login is there but with no map to the Mirrored
> database.
> Got my point?
>
> John Bell wrote:
>> Hi
>> The logins should be created on the mirror server before you invoke
>> failover. This is easier if you use trusted connections and have your users
>> in a windows group (as only the group needs to be added).
>> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
>> http://support.microsoft.com/kb/246133/ for a script.
>> John
>> "jocamp3@.gmail.com" wrote:
>> > I've search a lot of articles trying to find a script which not also re
>> > create SQL logins from one server to another but that also makes really
>> > automatically a SQL 2005 Mirror failover (map sql login to database and
>> > set permissions)
>> >
>> > Does anybody know how to do this? I am looking for a script.
>> >
>> >
>|||Hello Tibor:
Maybe i'm doin' something wrong ... or i can not explain myself well.
...Could you please provide me the steps order or the whole process?
Mirror 1st? Script first? etc ... i will try to recreate everything
again.
Tibor Karaszi wrote:
> But the mapping exists *inside* the database, so it is already there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > Hello John:
> >
> > Thanks for reply.
> >
> > This does not work. Yes .. i can create a login in the Mirror Server
> > but i can not map that login name to the desired database, because
> > simply it does not exists or it's in recovery state. So ... when i make
> > the failover, app. does not have the required credentials to log in,
> > because the SQL login is there but with no map to the Mirrored
> > database.
> >
> > Got my point?
> >
> >
> > John Bell wrote:
> >> Hi
> >>
> >> The logins should be created on the mirror server before you invoke
> >> failover. This is easier if you use trusted connections and have your users
> >> in a windows group (as only the group needs to be added).
> >>
> >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> >> http://support.microsoft.com/kb/246133/ for a script.
> >>
> >> John
> >>
> >> "jocamp3@.gmail.com" wrote:
> >>
> >> > I've search a lot of articles trying to find a script which not also re
> >> > create SQL logins from one server to another but that also makes really
> >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> >> > set permissions)
> >> >
> >> > Does anybody know how to do this? I am looking for a script.
> >> >
> >> >
> >|||Al that should be needed is to have the same logins existing on the mirror server, and there having
the same name and SID. You can handle that using the sp_help_revlogin procedure in a scheduled job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jocamp3@.gmail.com> wrote in message news:1163112082.076012.93440@.e3g2000cwe.googlegroups.com...
> Hello Tibor:
> Maybe i'm doin' something wrong ... or i can not explain myself well.
> ...Could you please provide me the steps order or the whole process?
> Mirror 1st? Script first? etc ... i will try to recreate everything
> again.
> Tibor Karaszi wrote:
>> But the mapping exists *inside* the database, so it is already there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <jocamp3@.gmail.com> wrote in message
>> news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
>> > Hello John:
>> >
>> > Thanks for reply.
>> >
>> > This does not work. Yes .. i can create a login in the Mirror Server
>> > but i can not map that login name to the desired database, because
>> > simply it does not exists or it's in recovery state. So ... when i make
>> > the failover, app. does not have the required credentials to log in,
>> > because the SQL login is there but with no map to the Mirrored
>> > database.
>> >
>> > Got my point?
>> >
>> >
>> > John Bell wrote:
>> >> Hi
>> >>
>> >> The logins should be created on the mirror server before you invoke
>> >> failover. This is easier if you use trusted connections and have your users
>> >> in a windows group (as only the group needs to be added).
>> >>
>> >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
>> >> http://support.microsoft.com/kb/246133/ for a script.
>> >>
>> >> John
>> >>
>> >> "jocamp3@.gmail.com" wrote:
>> >>
>> >> > I've search a lot of articles trying to find a script which not also re
>> >> > create SQL logins from one server to another but that also makes really
>> >> > automatically a SQL 2005 Mirror failover (map sql login to database and
>> >> > set permissions)
>> >> >
>> >> > Does anybody know how to do this? I am looking for a script.
>> >> >
>> >> >
>> >
>|||Hi
sp_help_revlogin and how to use it is described in the second link I posted
http://support.microsoft.com/kb/246133/
John
"jocamp3@.gmail.com" wrote:
> Hello Tibor:
> Maybe i'm doin' something wrong ... or i can not explain myself well.
> ...Could you please provide me the steps order or the whole process?
> Mirror 1st? Script first? etc ... i will try to recreate everything
> again.
> Tibor Karaszi wrote:
> > But the mapping exists *inside* the database, so it is already there.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > > Hello John:
> > >
> > > Thanks for reply.
> > >
> > > This does not work. Yes .. i can create a login in the Mirror Server
> > > but i can not map that login name to the desired database, because
> > > simply it does not exists or it's in recovery state. So ... when i make
> > > the failover, app. does not have the required credentials to log in,
> > > because the SQL login is there but with no map to the Mirrored
> > > database.
> > >
> > > Got my point?
> > >
> > >
> > > John Bell wrote:
> > >> Hi
> > >>
> > >> The logins should be created on the mirror server before you invoke
> > >> failover. This is easier if you use trusted connections and have your users
> > >> in a windows group (as only the group needs to be added).
> > >>
> > >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> > >> http://support.microsoft.com/kb/246133/ for a script.
> > >>
> > >> John
> > >>
> > >> "jocamp3@.gmail.com" wrote:
> > >>
> > >> > I've search a lot of articles trying to find a script which not also re
> > >> > create SQL logins from one server to another but that also makes really
> > >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > >> > set permissions)
> > >> >
> > >> > Does anybody know how to do this? I am looking for a script.
> > >> >
> > >> >
> > >
>|||Ok ... got it! ... and i will post this because there are a lot of
persons with the same problem. I can not believe nobody knows about
this ...
Some times, a step by step explanation helps much more than just point
to a simple script or link ...
1. On the server which will be Principal, create the Login (which the
app. will use to connect) at Server level WITH NO MAPPING to the
database. Put master database at default db instead.
2. Use SSID and execute the required package to copy Logins from one
server to another. If package turns green, it works. You should also be
able to see the Login in the second server.
3. Now, on 1st server again, set database mapping for SQL login. Set
the required permissions to database also (data reader, data writer,
etc)
3. Restore database on 2nd server (future mirror) in recovery mode.
4. Establish the Mirror
5. Test the failover: check the 2nd server (now the Principal) and you
should be able to see the SQL login. Change default database from
master to the desired database (now you can, because you're in the
principal)
6. Make another manual failover. Since now ... SQL server login its
already there and mapping exist with the required permissions also.
Jose Campo
John Bell wrote:
> Hi
> sp_help_revlogin and how to use it is described in the second link I posted
> http://support.microsoft.com/kb/246133/
> John
> "jocamp3@.gmail.com" wrote:
> > Hello Tibor:
> >
> > Maybe i'm doin' something wrong ... or i can not explain myself well.
> > ...Could you please provide me the steps order or the whole process?
> > Mirror 1st? Script first? etc ... i will try to recreate everything
> > again.
> >
> > Tibor Karaszi wrote:
> > > But the mapping exists *inside* the database, so it is already there.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > > > Hello John:
> > > >
> > > > Thanks for reply.
> > > >
> > > > This does not work. Yes .. i can create a login in the Mirror Server
> > > > but i can not map that login name to the desired database, because
> > > > simply it does not exists or it's in recovery state. So ... when i make
> > > > the failover, app. does not have the required credentials to log in,
> > > > because the SQL login is there but with no map to the Mirrored
> > > > database.
> > > >
> > > > Got my point?
> > > >
> > > >
> > > > John Bell wrote:
> > > >> Hi
> > > >>
> > > >> The logins should be created on the mirror server before you invoke
> > > >> failover. This is easier if you use trusted connections and have your users
> > > >> in a windows group (as only the group needs to be added).
> > > >>
> > > >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> > > >> http://support.microsoft.com/kb/246133/ for a script.
> > > >>
> > > >> John
> > > >>
> > > >> "jocamp3@.gmail.com" wrote:
> > > >>
> > > >> > I've search a lot of articles trying to find a script which not also re
> > > >> > create SQL logins from one server to another but that also makes really
> > > >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > > >> > set permissions)
> > > >> >
> > > >> > Does anybody know how to do this? I am looking for a script.
> > > >> >
> > > >> >
> > > >
> >
> >|||Corrections... i must say SSIS (old DTS) instead of SSID.
jocamp3@.gmail.com wrote:
> Ok ... got it! ... and i will post this because there are a lot of
> persons with the same problem. I can not believe nobody knows about
> this ...
> Some times, a step by step explanation helps much more than just point
> to a simple script or link ...
> 1. On the server which will be Principal, create the Login (which the
> app. will use to connect) at Server level WITH NO MAPPING to the
> database. Put master database at default db instead.
> 2. Use SSID and execute the required package to copy Logins from one
> server to another. If package turns green, it works. You should also be
> able to see the Login in the second server.
> 3. Now, on 1st server again, set database mapping for SQL login. Set
> the required permissions to database also (data reader, data writer,
> etc)
> 3. Restore database on 2nd server (future mirror) in recovery mode.
> 4. Establish the Mirror
> 5. Test the failover: check the 2nd server (now the Principal) and you
> should be able to see the SQL login. Change default database from
> master to the desired database (now you can, because you're in the
> principal)
> 6. Make another manual failover. Since now ... SQL server login its
> already there and mapping exist with the required permissions also.
> Jose Campo
>
> John Bell wrote:
> > Hi
> >
> > sp_help_revlogin and how to use it is described in the second link I posted
> > http://support.microsoft.com/kb/246133/
> >
> > John
> >
> > "jocamp3@.gmail.com" wrote:
> >
> > > Hello Tibor:
> > >
> > > Maybe i'm doin' something wrong ... or i can not explain myself well.
> > > ...Could you please provide me the steps order or the whole process?
> > > Mirror 1st? Script first? etc ... i will try to recreate everything
> > > again.
> > >
> > > Tibor Karaszi wrote:
> > > > But the mapping exists *inside* the database, so it is already there.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > > > > Hello John:
> > > > >
> > > > > Thanks for reply.
> > > > >
> > > > > This does not work. Yes .. i can create a login in the Mirror Server
> > > > > but i can not map that login name to the desired database, because
> > > > > simply it does not exists or it's in recovery state. So ... when i make
> > > > > the failover, app. does not have the required credentials to log in,
> > > > > because the SQL login is there but with no map to the Mirrored
> > > > > database.
> > > > >
> > > > > Got my point?
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > >> Hi
> > > > >>
> > > > >> The logins should be created on the mirror server before you invoke
> > > > >> failover. This is easier if you use trusted connections and have your users
> > > > >> in a windows group (as only the group needs to be added).
> > > > >>
> > > > >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> > > > >> http://support.microsoft.com/kb/246133/ for a script.
> > > > >>
> > > > >> John
> > > > >>
> > > > >> "jocamp3@.gmail.com" wrote:
> > > > >>
> > > > >> > I've search a lot of articles trying to find a script which not also re
> > > > >> > create SQL logins from one server to another but that also makes really
> > > > >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > > > >> > set permissions)
> > > > >> >
> > > > >> > Does anybody know how to do this? I am looking for a script.
> > > > >> >
> > > > >> >
> > > > >
> > >
> > >|||Corrections... i must say SSIS (old DTS) instead of SSID.
jocamp3@.gmail.com wrote:
> Ok ... got it! ... and i will post this because there are a lot of
> persons with the same problem. I can not believe nobody knows about
> this ...
> Some times, a step by step explanation helps much more than just point
> to a simple script or link ...
> 1. On the server which will be Principal, create the Login (which the
> app. will use to connect) at Server level WITH NO MAPPING to the
> database. Put master database at default db instead.
> 2. Use SSID and execute the required package to copy Logins from one
> server to another. If package turns green, it works. You should also be
> able to see the Login in the second server.
> 3. Now, on 1st server again, set database mapping for SQL login. Set
> the required permissions to database also (data reader, data writer,
> etc)
> 3. Restore database on 2nd server (future mirror) in recovery mode.
> 4. Establish the Mirror
> 5. Test the failover: check the 2nd server (now the Principal) and you
> should be able to see the SQL login. Change default database from
> master to the desired database (now you can, because you're in the
> principal)
> 6. Make another manual failover. Since now ... SQL server login its
> already there and mapping exist with the required permissions also.
> Jose Campo
>
> John Bell wrote:
> > Hi
> >
> > sp_help_revlogin and how to use it is described in the second link I posted
> > http://support.microsoft.com/kb/246133/
> >
> > John
> >
> > "jocamp3@.gmail.com" wrote:
> >
> > > Hello Tibor:
> > >
> > > Maybe i'm doin' something wrong ... or i can not explain myself well.
> > > ...Could you please provide me the steps order or the whole process?
> > > Mirror 1st? Script first? etc ... i will try to recreate everything
> > > again.
> > >
> > > Tibor Karaszi wrote:
> > > > But the mapping exists *inside* the database, so it is already there.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > > > > Hello John:
> > > > >
> > > > > Thanks for reply.
> > > > >
> > > > > This does not work. Yes .. i can create a login in the Mirror Server
> > > > > but i can not map that login name to the desired database, because
> > > > > simply it does not exists or it's in recovery state. So ... when i make
> > > > > the failover, app. does not have the required credentials to log in,
> > > > > because the SQL login is there but with no map to the Mirrored
> > > > > database.
> > > > >
> > > > > Got my point?
> > > > >
> > > > >
> > > > > John Bell wrote:
> > > > >> Hi
> > > > >>
> > > > >> The logins should be created on the mirror server before you invoke
> > > > >> failover. This is easier if you use trusted connections and have your users
> > > > >> in a windows group (as only the group needs to be added).
> > > > >>
> > > > >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> > > > >> http://support.microsoft.com/kb/246133/ for a script.
> > > > >>
> > > > >> John
> > > > >>
> > > > >> "jocamp3@.gmail.com" wrote:
> > > > >>
> > > > >> > I've search a lot of articles trying to find a script which not also re
> > > > >> > create SQL logins from one server to another but that also makes really
> > > > >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > > > >> > set permissions)
> > > > >> >
> > > > >> > Does anybody know how to do this? I am looking for a script.
> > > > >> >
> > > > >> >
> > > > >
> > >
> > >|||Hi Jose
Thanks for posting your findings. Logins will be less of an issue if you use
windows authentication and are on the same domain (especially if you use
windows groups!), but that is not always possible.
John
"jocamp3@.gmail.com" wrote:
> Corrections... i must say SSIS (old DTS) instead of SSID.
> jocamp3@.gmail.com wrote:
> > Ok ... got it! ... and i will post this because there are a lot of
> > persons with the same problem. I can not believe nobody knows about
> > this ...
> >
> > Some times, a step by step explanation helps much more than just point
> > to a simple script or link ...
> >
> > 1. On the server which will be Principal, create the Login (which the
> > app. will use to connect) at Server level WITH NO MAPPING to the
> > database. Put master database at default db instead.
> > 2. Use SSID and execute the required package to copy Logins from one
> > server to another. If package turns green, it works. You should also be
> > able to see the Login in the second server.
> > 3. Now, on 1st server again, set database mapping for SQL login. Set
> > the required permissions to database also (data reader, data writer,
> > etc)
> > 3. Restore database on 2nd server (future mirror) in recovery mode.
> > 4. Establish the Mirror
> > 5. Test the failover: check the 2nd server (now the Principal) and you
> > should be able to see the SQL login. Change default database from
> > master to the desired database (now you can, because you're in the
> > principal)
> > 6. Make another manual failover. Since now ... SQL server login its
> > already there and mapping exist with the required permissions also.
> >
> > Jose Campo
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > sp_help_revlogin and how to use it is described in the second link I posted
> > > http://support.microsoft.com/kb/246133/
> > >
> > > John
> > >
> > > "jocamp3@.gmail.com" wrote:
> > >
> > > > Hello Tibor:
> > > >
> > > > Maybe i'm doin' something wrong ... or i can not explain myself well.
> > > > ...Could you please provide me the steps order or the whole process?
> > > > Mirror 1st? Script first? etc ... i will try to recreate everything
> > > > again.
> > > >
> > > > Tibor Karaszi wrote:
> > > > > But the mapping exists *inside* the database, so it is already there.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > > > > > Hello John:
> > > > > >
> > > > > > Thanks for reply.
> > > > > >
> > > > > > This does not work. Yes .. i can create a login in the Mirror Server
> > > > > > but i can not map that login name to the desired database, because
> > > > > > simply it does not exists or it's in recovery state. So ... when i make
> > > > > > the failover, app. does not have the required credentials to log in,
> > > > > > because the SQL login is there but with no map to the Mirrored
> > > > > > database.
> > > > > >
> > > > > > Got my point?
> > > > > >
> > > > > >
> > > > > > John Bell wrote:
> > > > > >> Hi
> > > > > >>
> > > > > >> The logins should be created on the mirror server before you invoke
> > > > > >> failover. This is easier if you use trusted connections and have your users
> > > > > >> in a windows group (as only the group needs to be added).
> > > > > >>
> > > > > >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> > > > > >> http://support.microsoft.com/kb/246133/ for a script.
> > > > > >>
> > > > > >> John
> > > > > >>
> > > > > >> "jocamp3@.gmail.com" wrote:
> > > > > >>
> > > > > >> > I've search a lot of articles trying to find a script which not also re
> > > > > >> > create SQL logins from one server to another but that also makes really
> > > > > >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > > > > >> > set permissions)
> > > > > >> >
> > > > > >> > Does anybody know how to do this? I am looking for a script.
> > > > > >> >
> > > > > >> >
> > > > > >
> > > >
> > > >
>|||Hi John
Exactly! I just work at Database level. Have no authority at app.
level. But developer is planning to use a Windows user. One of the
reason we're currently using an SQL account it's because app. it's
running on a Linux box ...
Best regards,
JC
John Bell wrote:
> Hi Jose
> Thanks for posting your findings. Logins will be less of an issue if you use
> windows authentication and are on the same domain (especially if you use
> windows groups!), but that is not always possible.
> John
> "jocamp3@.gmail.com" wrote:
> > Corrections... i must say SSIS (old DTS) instead of SSID.
> >
> > jocamp3@.gmail.com wrote:
> > > Ok ... got it! ... and i will post this because there are a lot of
> > > persons with the same problem. I can not believe nobody knows about
> > > this ...
> > >
> > > Some times, a step by step explanation helps much more than just point
> > > to a simple script or link ...
> > >
> > > 1. On the server which will be Principal, create the Login (which the
> > > app. will use to connect) at Server level WITH NO MAPPING to the
> > > database. Put master database at default db instead.
> > > 2. Use SSID and execute the required package to copy Logins from one
> > > server to another. If package turns green, it works. You should also be
> > > able to see the Login in the second server.
> > > 3. Now, on 1st server again, set database mapping for SQL login. Set
> > > the required permissions to database also (data reader, data writer,
> > > etc)
> > > 3. Restore database on 2nd server (future mirror) in recovery mode.
> > > 4. Establish the Mirror
> > > 5. Test the failover: check the 2nd server (now the Principal) and you
> > > should be able to see the SQL login. Change default database from
> > > master to the desired database (now you can, because you're in the
> > > principal)
> > > 6. Make another manual failover. Since now ... SQL server login its
> > > already there and mapping exist with the required permissions also.
> > >
> > > Jose Campo
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > sp_help_revlogin and how to use it is described in the second link I posted
> > > > http://support.microsoft.com/kb/246133/
> > > >
> > > > John
> > > >
> > > > "jocamp3@.gmail.com" wrote:
> > > >
> > > > > Hello Tibor:
> > > > >
> > > > > Maybe i'm doin' something wrong ... or i can not explain myself well.
> > > > > ...Could you please provide me the steps order or the whole process?
> > > > > Mirror 1st? Script first? etc ... i will try to recreate everything
> > > > > again.
> > > > >
> > > > > Tibor Karaszi wrote:
> > > > > > But the mapping exists *inside* the database, so it is already there.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > http://www.solidqualitylearning.com/
> > > > > >
> > > > > >
> > > > > > <jocamp3@.gmail.com> wrote in message news:1163105989.698968.293540@.h54g2000cwb.googlegroups.com...
> > > > > > > Hello John:
> > > > > > >
> > > > > > > Thanks for reply.
> > > > > > >
> > > > > > > This does not work. Yes .. i can create a login in the Mirror Server
> > > > > > > but i can not map that login name to the desired database, because
> > > > > > > simply it does not exists or it's in recovery state. So ... when i make
> > > > > > > the failover, app. does not have the required credentials to log in,
> > > > > > > because the SQL login is there but with no map to the Mirrored
> > > > > > > database.
> > > > > > >
> > > > > > > Got my point?
> > > > > > >
> > > > > > >
> > > > > > > John Bell wrote:
> > > > > > >> Hi
> > > > > > >>
> > > > > > >> The logins should be created on the mirror server before you invoke
> > > > > > >> failover. This is easier if you use trusted connections and have your users
> > > > > > >> in a windows group (as only the group needs to be added).
> > > > > > >>
> > > > > > >> See http://msdn2.microsoft.com/en-us/library/ms191458.aspx and
> > > > > > >> http://support.microsoft.com/kb/246133/ for a script.
> > > > > > >>
> > > > > > >> John
> > > > > > >>
> > > > > > >> "jocamp3@.gmail.com" wrote:
> > > > > > >>
> > > > > > >> > I've search a lot of articles trying to find a script which not also re
> > > > > > >> > create SQL logins from one server to another but that also makes really
> > > > > > >> > automatically a SQL 2005 Mirror failover (map sql login to database and
> > > > > > >> > set permissions)
> > > > > > >> >
> > > > > > >> > Does anybody know how to do this? I am looking for a script.
> > > > > > >> >
> > > > > > >> >
> > > > > > >
> > > > >
> > > > >
> >
> >

How can I make SQL Server automatic generate scheme script for backup everyday?

How can I make SQL Server automatic generate scheme script for backup
everyday?Google "scptxfr.exe". It's a tool from Microsoft used in the Upgrade
process, but it'll work for this.
see
http://www.databasejournal.com/feat...10894_3401081_1
for an example.
HTH,
Stu|||Wow, so fast replied. Very thanks
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1133921881.553339.60870@.g43g2000cwa.googlegroups.com...
> Google "scptxfr.exe". It's a tool from Microsoft used in the Upgrade
> process, but it'll work for this.
> see
> http://www.databasejournal.com/feat...10894_3401081_1
> for an example.
> HTH,
> Stu
>sql

How can I make SQL Server 2005 automatic generate scheme script for backup everyday?

How can I make SQL Server 2005 automatic generate scheme script for backup
everyday?"ABC" <abc@.abc.com> wrote in message
news:ef5YwPOEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> How can I make SQL Server 2005 automatic generate scheme script for backup
> everyday?
>
>
I believe you could use the SMO Scripter class to achieve this.
If your schema is in development I'd recommend you use a source control
program to backup the source code. Or you can use utilities like DBGhost or
RedGate SQL Compare to script the changes.
David Portas
SQL Server MVP
--

How can I make a script out of a table data ?

Hello,

I want to take a table (SQL server) and show all it's data in a form of INSERT... (text file)
so that I can show it on a web-page and just paste the INSERT text into aMyTableData.sql
and then I can just run this script on the Query analyzer and fill my table with data

I need it so I can backup my DATA both in English and other languages...
(a replacement for the DTS packages that gives me hard time with the LOCALE / UNICODE translation)

did anyone already made such program ?
where can I find something like this ?My initial thought is "Don't do it!"!

You are planning on having a textbox that the user will have complete control over, and then you are taking the contents of that textbox and turning it into a SQL script that will be run?

This is extremely insecure and could result in all sorts of malicious script being executed against your database. Even in a trusted intranet environment, I would not write such functionality.

My 2 cents.

Terri|||Here's an app that will do it in its Export feature: http://www.microsoft.com/downloads/details.aspx?FamilyId=F0D03472-5E6C-459E-A6D8-6745A729C3C9&displaylang=en

Terri, your concern is valid, but I'd assume (or at least hope) that the page on the site for doing this would be completely secure.|||

My advice is to use the following softwarehttp://www.denovations.com/products/sqlpopulator/ It's looks handy...

How can I list the students in the CIS department? (SQL Server Query)

I need to write the SQL script for the above question.

Student table contains students' IDs, names, Addresses.

MajorMinor table contains 4 departments with unique code.
Name is a field and CIS is an attribute. Code(primary key) is a field and CIS is 13.

Student declares major. Declares table has StudentID and MajorMinorCode. The only student, his StudentID is 3579, has MajorMinorCode 13. (Meaning he is the only student who declared CIS as his major.)

I created and populated my database in SQL Server Management.

I am having trouble if I am selecting 2 tables or just one table with any other clauses?
Any help is appreciated! Thanks!That looks like an assignment / home work .

Can you kindly post what you have tried to solve the mentioned problem.|||

Quote:

Originally Posted by debasisdas

That looks like an assignment / home work .

Can you kindly post what you have tried to solve the mentioned problem.

Here you go.
SELECT Student.Name
FROM (Student INNER JOIN Declares ON Student.ID = Declares.StudentID) INNER JOIN MajorMinor ON Declares.MajorMinorCode = MajorMinor.Code
WHERE ((MajorMinor.Name)="CIS"));

How can I insert a column using a script (instead of appending)?

Hi,

I recently had to add a new column to a table that resides off-site,
in a customer's environment. Previously, the table had around 300
columns, all of which were in alphabetical order.

I found that the ALTER TABLE command appeared to be only capable of
appending a new column, but wasn't capable of inserting one into the
middle of the table.

Since alphabetical order wasn't a requirement, but just a "nice to
look at" feature, I appended it anyway, and forgot about it.

Anyone know of a clever way to do this though?

Curiously,

Warren Wright
Scorex Development TeamWarren Wright (warren.wright@.us.scorex.com) writes:
> I recently had to add a new column to a table that resides off-site,
> in a customer's environment. Previously, the table had around 300
> columns, all of which were in alphabetical order.
> I found that the ALTER TABLE command appeared to be only capable of
> appending a new column, but wasn't capable of inserting one into the
> middle of the table.
> Since alphabetical order wasn't a requirement, but just a "nice to
> look at" feature, I appended it anyway, and forgot about it.
> Anyone know of a clever way to do this though?

The "clever" way is to fiddle with syscolumns, but I highly discourge
from this.

The normal way is to go the long way: rename the old table, create
the new table including triggers, constrains and clustered index, insert
data from the old table to the new, move referencing foreign keys, drop the
old table. Restore non-clustered index.

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

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

How can I implement error handling within a SQL script that uses b

I am trying to incorporate error handling into a SQL script that creates
several database objects.
Those objects are referenced later in the script by other blocks of code.
It is my understanding that I need to use a new batch to reference an object
that was just created. I can do this by inserting a GO statement after the
object creation statement.
But how do I implement error handling within a SQL script that uses batches?
IOW I don't want a subsequent batch to execute if a previous batch failed.
If I didn't have separate batches in my script I could use a RETURN or GOTO
statement to handle errors. But with batches I have the following problem:
--use RETURN
PRINT '1'
GO
PRINT '2'
RETURN
GO
PRINT '3'
--OUTPUT
1
2
3
--use GOTO
PRINT '1'
GO
PRINT '2'
GOTO error
GO
PRINT '3'
ERROR:
PRINT 'ERROR'
--OUTPUT
1
Server: Msg 133, Level 15, State 1, Line 2
A GOTO statement references the label 'error' but the label has not been
declared.
3
ERROR
Is the only solution to break up the script to run as indivifual batchs and
then evaluate each one after execution?Use the
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE
id=object_id(@.v_objectname))
BEGIN
-- start processing
END
ELSE
BEGIN
PRINT @.v_objectname + ' was not created'
END|||Do you need a new batch to reference an object created in the current bacth?
try this
create table t1 (A int)
insert into t1 values(1)
select * from t1
drop table t1
Or did I misunderstand u?|||On Wed, 31 May 2006 10:36:02 -0700, Dave wrote:
(snip)
>But how do I implement error handling within a SQL script that uses batches
?
> IOW I don't want a subsequent batch to execute if a previous batch failed.[/color
]
Hi Dave,
There's no good answer for this. Since the cient sends just one batch to
the server, waits for it to execute, then sends the other one, there is
no simple way to have an error in batch #1 abort batch #2.
There are kludges, though. The easiest (but nastiest) is to use a
RAISERROR with a severity of at least 20 - those are considered fatal,
and prompt SQL Server to terminate the connection after sending the
error message.
Somewhat more work (but still the version I'd prefer) is to use a table
to carry over status information. This can be a temp table. For
instance:
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
Hugo Kornelis, SQL Server MVP

How can I implement error handling within a SQL script that us

Hi Hugo,
I was thinking in similar lines as the solution you gave (not the one
with severity 20.. I would have never come up with such a solution :)
But I think there is a problem in the other one. We are talking about
different batches. and these batches will be creating database objects.
Can you give an example on how will you implement the error handling with
temp tables for this scenario?
1. Create a view on some existing table first (select *)
2. If the view creation is successful,then create another view on this view.On Thu, 1 Jun 2006 22:35:01 -0700, Omnibuzz wrote:
(snip)
>Can you give an example on how will you implement the error handling with
>temp tables for this scenario?
>1. Create a view on some existing table first (select *)
>2. If the view creation is successful,then create another view on this view.[/color
]
Hi Omnibuzz,
There's one thing I hadn't thought about - CREATE VIEW must be the only
in a batch, so you'll have to use dynamic SQL to make it conditional.
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
EXEC ('CREATE VIEW v1
AS
SELECT 1 AS a')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
EXEC ('CREATE VIEW v2
AS
SELECT a
FROM v1')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
go
Hugo Kornelis, SQL Server MVP

How can I ignore errors in script and resume execute this script? thx :)

How can I ignore errors in script and resume execute this script? thx :)IF you are talking about syntax errors in the script you can not..
SQL checks for the syntax of all items in a batch. If ALL the statements
syntax correctly, THEN the batch executes. You can make your batches smaller
by inserting go between statements.
If you are talking about SQL errors while executing a statement, you may
simply ignore the error... However some errors abort the batch and there is
not much (nothing) you can do about that ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>|||By the way...
One thing I used to do is in every batch update a row in a table which had
the version # of the batch... Each batch checked this version number to see
if the batch had to run or had already run... That way, I could fix the
offending batch within the script and just re-run the entire script... It
was a pain in the butt to keep up with , but it worked...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>

How can I ignore errors in script and resume execute this script? thx :)

How can I ignore errors in script and resume execute this script? thx :)IF you are talking about syntax errors in the script you can not..
SQL checks for the syntax of all items in a batch. If ALL the statements
syntax correctly, THEN the batch executes. You can make your batches smaller
by inserting go between statements.
If you are talking about SQL errors while executing a statement, you may
simply ignore the error... However some errors abort the batch and there is
not much (nothing) you can do about that ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>
|||By the way...
One thing I used to do is in every batch update a row in a table which had
the version # of the batch... Each batch checked this version number to see
if the batch had to run or had already run... That way, I could fix the
offending batch within the script and just re-run the entire script... It
was a pain in the butt to keep up with , but it worked...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>

How can I ignore errors in script and resume execute this script? thx :)

How can I ignore errors in script and resume execute this script? thx :)IF you are talking about syntax errors in the script you can not..
SQL checks for the syntax of all items in a batch. If ALL the statements
syntax correctly, THEN the batch executes. You can make your batches smaller
by inserting go between statements.
If you are talking about SQL errors while executing a statement, you may
simply ignore the error... However some errors abort the batch and there is
not much (nothing) you can do about that ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>|||By the way...
One thing I used to do is in every batch update a row in a table which had
the version # of the batch... Each batch checked this version number to see
if the batch had to run or had already run... That way, I could fix the
offending batch within the script and just re-run the entire script... It
was a pain in the butt to keep up with , but it worked...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"atremote" <atremote@.sina.com> wrote in message
news:%235IZGhqfEHA.3944@.tk2msftngp13.phx.gbl...
>

How can I get the package name within a script?

Greetings,

I need to programmatically grab the name of the executing package so that it can be used in logging. Does someone have a code sample for this?

Thanks!

Use the system variable, PackageName.|||

Thanks.

For those who may benefit from a code sample:

Dim source AsString

Dim vars As Variables

Dts.VariableDispenser.LockOneForRead("System:ackageName", vars)

source = vars("PackageName").Value.ToString()

|||Also the other options (perhaps not for this example) are to use a derived column to add the system variable to the data flow, or in the control flow, using an Execute SQL task, you can map the variable to a parameter in the SQL statement.

how can I get the error message?

I want to insert csv file into table.

In Control Flow Tab I insert a Data Flow Task and a Script Task

The Script Task is under the Data Flow Task as the failure handle to generate Log.

but I don't konw how to get the Data Flow Task's error message in Script Task?

thank you for you answer!!

Try moving the script task into an OnError eventhandler. The error message is available in there.

Let me know if this creates a problem.

-Jamie

|||

thank you for the answer.

I moved the script task to event handler tag. but it didn't run though my deta flow task generated a primary key error.

also I would like to know wether it is the err.description from that property I can konw the error message?

How can I get modified data using timestamp columns

I am putting together an SQL script that is pulling recently modified data from 3 tables and INSERTing that data into another table.

All 3 of my input tables have a timestamp column and I have the previous values for these 3 timestamp columns at the time my SQL script was run previously. So, using the timestamp column values that I had from the previous run of my SQL script and the current timestamp columns that exist in my 3 tables, I am able to derive any recently modified rows.

So, here are my 3 input tables:

Items (has a timestamp column) and has several million rows.

Attributes1 (has a timestamp column) and has a million rows.

Attributes2 (has a timnestamp column) and has a million rows.

The Attributes1 and Attributes2 tables have attributes that describe the items in the Items table. I want to INSERT the Items rows with all of their attributes into a fourth table (that doesn't need a timestamp column).

The kicker is if any attribute changes in the Attributes1 and/or Attributes2 tables, I want to completely resummarize the entire item in the fourth table.

So, I have 3 INSERT/SELECTs in my SQL Script so that I can pickup any combination of modified data in my 3 input tables.

INSERT INTO Table4

.......

SELECT

.....

FROM Items

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Items.TimestampColumn BETWEEN a AND b

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes1.TimestampColumn BETWEEN c AND d

AND (the Items row is not already in Table4)

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes2

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes2.TimestampColumn BETWEEN e AND f

AND (the Items row is not already in Table4)

This SQL takes a whole long time to run (more than an hour).

I would like to consense my SQL into a single INSERT/SELECT.

Does anybody know of an SQL technique that I haven't thought of...

TIA

Will this work:

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE (Attributes1.TimestampColumn BETWEEN a AND b

OR Attributes1.TimestampColumn BETWEEN c AND d

OR Attributes1.TimestampColumn BETWEEN e AND f)

AND (the Items row is not already in Table4)

Alternatively, you could try doing a UNION on the select statements to get them into one derived table (if the items table is empty before the first query is run, you can drop the NOT EXISTS as the UNION will remove duplicate rows), and then insert into the table in one go from the derived table (UNION statement). The latter may be quicker if the items table is empty to begin with.

How can I generate insert statements for sample data?

Hi,
I want to post a question but I want to also include a script for you guys
to create the table on your end as well as some sample data.
I used to have a tool that would take the data in a table and generate a
bunch of INSERT statements. I can't seem to locate it. Could someone please
give me a pointer on where I can find that script?
--
Thanks,
SamSam wrote:
> Hi,
> I want to post a question but I want to also include a script for you
> guys to create the table on your end as well as some sample data.
> I used to have a tool that would take the data in a table and
> generate a bunch of INSERT statements. I can't seem to locate it.
> Could someone please give me a pointer on where I can find that
> script?
www.aspfaq.com/5006
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Find my post of yesterday :)
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:DF35D0A0-C291-4B03-9860-37DF09F92846@.microsoft.com...
> Hi,
> I want to post a question but I want to also include a script for you guys
> to create the table on your end as well as some sample data.
> I used to have a tool that would take the data in a table and generate a
> bunch of INSERT statements. I can't seem to locate it. Could someone
> please
> give me a pointer on where I can find that script?
> --
> Thanks,
> Sam|||To generate Insert statements from the existing data Check out
http://vyaskn.tripod.com/code.htm its really good.
Source code:: http://vyaskn.tripod.com/code/generate_inserts.txt
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Mark Nijhof" wrote:

> Find my post of yesterday :)
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:DF35D0A0-C291-4B03-9860-37DF09F92846@.microsoft.com...
>
>|||Try www.sqlscripter.com to generate INSERT, UDPATE, Delete data scripts.
"Sam" wrote:

> Hi,
> I want to post a question but I want to also include a script for you guys
> to create the table on your end as well as some sample data.
> I used to have a tool that would take the data in a table and generate a
> bunch of INSERT statements. I can't seem to locate it. Could someone pleas
e
> give me a pointer on where I can find that script?
> --
> Thanks,
> Sam

How can I generate an SQL Script with the INSERT INTO instruction?

Hi,
I'd like to know if there is a way that I can generate a script with the
INSERT INTO instruction of the content of the table. I know how to generate
a script for the create table, drop the constraint, index, recreate the
table, constraint... but I need to have the content of the table into that
script. How can I do this? For the moment I do this by using a DTS that
generate a .txt file with the content of the table, and I add manualy the
INSERT INTO... instruction at the beginning of each row. A lost of time!
Thank you for your help.> I'd like to know if there is a way that I can generate a script with the
> INSERT INTO instruction of the content of the table.
http://www.aspfaq.com/5006 has a link to one such script.|||"Jacques Rhaume" <Jacques Rhaume@.discussions.microsoft.com> wrote in
message news:190EBD0A-275E-454A-851D-BF5083AD8D53@.microsoft.com...
> Hi,
> I'd like to know if there is a way that I can generate a script with the
> INSERT INTO instruction of the content of the table. I know how to
> generate
> a script for the create table, drop the constraint, index, recreate the
> table, constraint... but I need to have the content of the table into that
> script. How can I do this? For the moment I do this by using a DTS that
> generate a .txt file with the content of the table, and I add manualy the
> INSERT INTO... instruction at the beginning of each row. A lost of time!
> Thank you for your help.
http://vyaskn.tripod.com/code.htm#inserts
Try here.
Rick Sawtell
MCT, MCSD, MCDBA|||Not quite sure exactly what you are trying to do. You can generate create
and drop table scripts using QA, and right clicking on the table itself.
If you are just trying to get column names, try this:
Select column_name from
Information_Schema.columns where table_name = 'yourTable'
Archer
"Jacques Rhéaume" wrote:

> Hi,
> I'd like to know if there is a way that I can generate a script with the
> INSERT INTO instruction of the content of the table. I know how to genera
te
> a script for the create table, drop the constraint, index, recreate the
> table, constraint... but I need to have the content of the table into that
> script. How can I do this? For the moment I do this by using a DTS that
> generate a .txt file with the content of the table, and I add manualy the
> INSERT INTO... instruction at the beginning of each row. A lost of time!
> Thank you for your help.|||> Not quite sure exactly what you are trying to do. You can generate create
> and drop table scripts using QA, and right clicking on the table itself.
Jacques is also trying to script the data in the table.