Hi all,
where / which table could i check the access information from the MSSQL
(i.e. which PC sent query request to my SQL server with the domain login ID,
IP, time...etc..
Thanks a lot
Rena.Rena,
This information is not logged in any table by default. You should read
about SQL Profiler and SQL traces to see how you might capture this
information. After that you may have more specific questions.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Hi,
Have a look into the below procedures as well
sp_who
sp_who2
and the master..sysprocesses system table.
Note:
All the above contains the information of currently connected users.
Thanks
Hari
MCDBA
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Thx Russell and Hari,
as I need to keep track whether my SQL Server in my Office is access by
hostile PC / outsider, so what should be done, or if there are any turn
around stuff to be done on SQL?
Profiler might keep a hug lock even open for one night, and i do not know
which event class is the most effiecient one in this case as I am not
familiar :p
Rena.
Thx.
"Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...
> Rena,
> This information is not logged in any table by default. You should read
> about SQL Profiler and SQL traces to see how you might capture this
> information. After that you may have more specific questions.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> > Hi all,
> > where / which table could i check the access information from the
> MSSQL
> > (i.e. which PC sent query request to my SQL server with the domain login
> ID,
> > IP, time...etc..
> >
> > Thanks a lot
> > Rena.
> >
> >
>|||Rena,
Profiler will not keep a huge lock, but it does require some resources. You
might try tracing the Audit Login / Logout events and, if you are interested
in what SQL is running, the SQL:BatchCompleted.
A lighter weight look might serve you as well using sp_who2 to find out who
is active at whatever interval you run it.
sp_who2 active -- only lists the active connections
You can get the results of either approach sent to your file system or to an
internal table. (For sp_who2 use osql to run a looping script in order to
send the results to a file. For Profiler, use the trace properties.)
Tracing to a table adds to the overhead and is generally recommended
against, so it depends out what your system constraints are.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Thx Russell and Hari,
> as I need to keep track whether my SQL Server in my Office is access
by
> hostile PC / outsider, so what should be done, or if there are any turn
> around stuff to be done on SQL?
> Profiler might keep a hug lock even open for one night, and i do not know
> which event class is the most effiecient one in this case as I am not
> familiar :p
> Rena.
> Thx.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
> news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...
> > Rena,
> >
> > This information is not logged in any table by default. You should read
> > about SQL Profiler and SQL traces to see how you might capture this
> > information. After that you may have more specific questions.
> >
> > Russell Fields
> > "Rena" <Rena@.mail.hongkong.com> wrote in message
> > news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> > > Hi all,
> > > where / which table could i check the access information from the
> > MSSQL
> > > (i.e. which PC sent query request to my SQL server with the domain
login
> > ID,
> > > IP, time...etc..
> > >
> > > Thanks a lot
> > > Rena.
> > >
> > >
> >
> >
>|||Thx. alot :)
"Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> > Thx Russell and Hari,
> > as I need to keep track whether my SQL Server in my Office is access
> by
> > hostile PC / outsider, so what should be done, or if there are any turn
> > around stuff to be done on SQL?
> >
> > Profiler might keep a hug lock even open for one night, and i do not
know
> > which event class is the most effiecient one in this case as I am not
> > familiar :p
> >
> > Rena.
> > Thx.
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
> > news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...
> > > Rena,
> > >
> > > This information is not logged in any table by default. You should
read
> > > about SQL Profiler and SQL traces to see how you might capture this
> > > information. After that you may have more specific questions.
> > >
> > > Russell Fields
> > > "Rena" <Rena@.mail.hongkong.com> wrote in message
> > > news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> > > > Hi all,
> > > > where / which table could i check the access information from
the
> > > MSSQL
> > > > (i.e. which PC sent query request to my SQL server with the domain
> login
> > > ID,
> > > > IP, time...etc..
> > > >
> > > > Thanks a lot
> > > > Rena.
> > > >
> > > >
> > >
> > >
> >
> >
>
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Sunday, March 25, 2012
Check log
Hi all,
where / which table could i check the access information from the MSSQL
(i.e. which PC sent query request to my SQL server with the domain login ID,
IP, time...etc..
Thanks a lot
Rena.Rena,
This information is not logged in any table by default. You should read
about SQL Profiler and SQL traces to see how you might capture this
information. After that you may have more specific questions.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Hi,
Have a look into the below procedures as well
sp_who
sp_who2
and the master..sysprocesses system table.
Note:
All the above contains the information of currently connected users.
Thanks
Hari
MCDBA
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Thx Russell and Hari,
as I need to keep track whether my SQL Server in my Office is access by
hostile PC / outsider, so what should be done, or if there are any turn
around stuff to be done on SQL?
Profiler might keep a hug lock even open for one night, and i do not know
which event class is the most effiecient one in this case as I am not
familiar :p
Rena.
Thx.
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
> Rena,
> This information is not logged in any table by default. You should read
> about SQL Profiler and SQL traces to see how you might capture this
> information. After that you may have more specific questions.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> MSSQL
> ID,
>|||Rena,
Profiler will not keep a huge lock, but it does require some resources. You
might try tracing the Audit Login / Logout events and, if you are interested
in what SQL is running, the SQL:BatchCompleted.
A lighter weight look might serve you as well using sp_who2 to find out who
is active at whatever interval you run it.
sp_who2 active -- only lists the active connections
You can get the results of either approach sent to your file system or to an
internal table. (For sp_who2 use osql to run a looping script in order to
send the results to a file. For Profiler, use the trace properties.)
Tracing to a table adds to the overhead and is generally recommended
against, so it depends out what your system constraints are.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Thx Russell and Hari,
> as I need to keep track whether my SQL Server in my Office is access
by
> hostile PC / outsider, so what should be done, or if there are any turn
> around stuff to be done on SQL?
> Profiler might keep a hug lock even open for one night, and i do not know
> which event class is the most effiecient one in this case as I am not
> familiar :p
> Rena.
> Thx.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> bl
> news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
login[vbcol=seagreen]
>|||Thx. alot
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> by
know[vbcol=seagreen]
read[vbcol=seagreen]
the[vbcol=seagreen]
> login
>
where / which table could i check the access information from the MSSQL
(i.e. which PC sent query request to my SQL server with the domain login ID,
IP, time...etc..
Thanks a lot
Rena.Rena,
This information is not logged in any table by default. You should read
about SQL Profiler and SQL traces to see how you might capture this
information. After that you may have more specific questions.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Hi,
Have a look into the below procedures as well
sp_who
sp_who2
and the master..sysprocesses system table.
Note:
All the above contains the information of currently connected users.
Thanks
Hari
MCDBA
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Thx Russell and Hari,
as I need to keep track whether my SQL Server in my Office is access by
hostile PC / outsider, so what should be done, or if there are any turn
around stuff to be done on SQL?
Profiler might keep a hug lock even open for one night, and i do not know
which event class is the most effiecient one in this case as I am not
familiar :p
Rena.
Thx.
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
> Rena,
> This information is not logged in any table by default. You should read
> about SQL Profiler and SQL traces to see how you might capture this
> information. After that you may have more specific questions.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> MSSQL
> ID,
>|||Rena,
Profiler will not keep a huge lock, but it does require some resources. You
might try tracing the Audit Login / Logout events and, if you are interested
in what SQL is running, the SQL:BatchCompleted.
A lighter weight look might serve you as well using sp_who2 to find out who
is active at whatever interval you run it.
sp_who2 active -- only lists the active connections
You can get the results of either approach sent to your file system or to an
internal table. (For sp_who2 use osql to run a looping script in order to
send the results to a file. For Profiler, use the trace properties.)
Tracing to a table adds to the overhead and is generally recommended
against, so it depends out what your system constraints are.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Thx Russell and Hari,
> as I need to keep track whether my SQL Server in my Office is access
by
> hostile PC / outsider, so what should be done, or if there are any turn
> around stuff to be done on SQL?
> Profiler might keep a hug lock even open for one night, and i do not know
> which event class is the most effiecient one in this case as I am not
> familiar :p
> Rena.
> Thx.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> bl
> news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
login[vbcol=seagreen]
>|||Thx. alot
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> by
know[vbcol=seagreen]
read[vbcol=seagreen]
the[vbcol=seagreen]
> login
>
Check log
Hi all,
where / which table could i check the access information from the MSSQL
(i.e. which PC sent query request to my SQL server with the domain login ID,
IP, time...etc..
Thanks a lot
Rena.
Rena,
This information is not logged in any table by default. You should read
about SQL Profiler and SQL traces to see how you might capture this
information. After that you may have more specific questions.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>
|||Hi,
Have a look into the below procedures as well
sp_who
sp_who2
and the master..sysprocesses system table.
Note:
All the above contains the information of currently connected users.
Thanks
Hari
MCDBA
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>
|||Thx Russell and Hari,
as I need to keep track whether my SQL Server in my Office is access by
hostile PC / outsider, so what should be done, or if there are any turn
around stuff to be done on SQL?
Profiler might keep a hug lock even open for one night, and i do not know
which event class is the most effiecient one in this case as I am not
familiar :p
Rena.
Thx.
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
> Rena,
> This information is not logged in any table by default. You should read
> about SQL Profiler and SQL traces to see how you might capture this
> information. After that you may have more specific questions.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> MSSQL
> ID,
>
|||Rena,
Profiler will not keep a huge lock, but it does require some resources. You
might try tracing the Audit Login / Logout events and, if you are interested
in what SQL is running, the SQL:BatchCompleted.
A lighter weight look might serve you as well using sp_who2 to find out who
is active at whatever interval you run it.
sp_who2 active -- only lists the active connections
You can get the results of either approach sent to your file system or to an
internal table. (For sp_who2 use osql to run a looping script in order to
send the results to a file. For Profiler, use the trace properties.)
Tracing to a table adds to the overhead and is generally recommended
against, so it depends out what your system constraints are.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Thx Russell and Hari,
> as I need to keep track whether my SQL Server in my Office is access
by[vbcol=seagreen]
> hostile PC / outsider, so what should be done, or if there are any turn
> around stuff to be done on SQL?
> Profiler might keep a hug lock even open for one night, and i do not know
> which event class is the most effiecient one in this case as I am not
> familiar :p
> Rena.
> Thx.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> bl
> news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
login
>
|||Thx. alot
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an[vbcol=seagreen]
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> by
know[vbcol=seagreen]
read[vbcol=seagreen]
the
> login
>
where / which table could i check the access information from the MSSQL
(i.e. which PC sent query request to my SQL server with the domain login ID,
IP, time...etc..
Thanks a lot
Rena.
Rena,
This information is not logged in any table by default. You should read
about SQL Profiler and SQL traces to see how you might capture this
information. After that you may have more specific questions.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>
|||Hi,
Have a look into the below procedures as well
sp_who
sp_who2
and the master..sysprocesses system table.
Note:
All the above contains the information of currently connected users.
Thanks
Hari
MCDBA
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>
|||Thx Russell and Hari,
as I need to keep track whether my SQL Server in my Office is access by
hostile PC / outsider, so what should be done, or if there are any turn
around stuff to be done on SQL?
Profiler might keep a hug lock even open for one night, and i do not know
which event class is the most effiecient one in this case as I am not
familiar :p
Rena.
Thx.
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
> Rena,
> This information is not logged in any table by default. You should read
> about SQL Profiler and SQL traces to see how you might capture this
> information. After that you may have more specific questions.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> MSSQL
> ID,
>
|||Rena,
Profiler will not keep a huge lock, but it does require some resources. You
might try tracing the Audit Login / Logout events and, if you are interested
in what SQL is running, the SQL:BatchCompleted.
A lighter weight look might serve you as well using sp_who2 to find out who
is active at whatever interval you run it.
sp_who2 active -- only lists the active connections
You can get the results of either approach sent to your file system or to an
internal table. (For sp_who2 use osql to run a looping script in order to
send the results to a file. For Profiler, use the trace properties.)
Tracing to a table adds to the overhead and is generally recommended
against, so it depends out what your system constraints are.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Thx Russell and Hari,
> as I need to keep track whether my SQL Server in my Office is access
by[vbcol=seagreen]
> hostile PC / outsider, so what should be done, or if there are any turn
> around stuff to be done on SQL?
> Profiler might keep a hug lock even open for one night, and i do not know
> which event class is the most effiecient one in this case as I am not
> familiar :p
> Rena.
> Thx.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> bl
> news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl g...
login
>
|||Thx. alot
"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an[vbcol=seagreen]
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> by
know[vbcol=seagreen]
read[vbcol=seagreen]
the
> login
>
Check last access/update date on each database
I need to check the date of the last update on each database on some
servers.
If the last update is many months ago, I'll need at least stop SQL Services.
How is the best was to do it?
Thanks folks!!!
Carlos Felipe Frana da Fonseca
Hi,
SQL Server will not keep track of these info by itself. Only way is to
verify the modifiedon column in your table. If you do not have modified on
column
then you may need to setup a profiler trace and based on the log you need to
go further.
Thanks
Hari
SQL Server MVP
"Carlos Felipe Frana da Fonseca" <carlos@.felipe.com.br> wrote in message
news:%23B0kfB52GHA.2196@.TK2MSFTNGP06.phx.gbl...
>I need to check the date of the last update on each database on some
>servers.
> If the last update is many months ago, I'll need at least stop SQL
> Services.
> How is the best was to do it?
> Thanks folks!!!
> Carlos Felipe Frana da Fonseca
>
|||Hello,
Alternative way is look into sysprocesses table in each server .
For SPID > 50 look for last_batch . Check the date and time .
Note this will provide database accessed time and not updated time .
You have to look into all user databases for last_batch before
stopping the service .
M A Srinivas
Carlos Felipe Fran=E7a da Fonseca wrote:
> I need to check the date of the last update on each database on some
> servers.
> If the last update is many months ago, I'll need at least stop SQL Servic=
es.
> How is the best was to do it?
>=20
> Thanks folks!!!
>=20
> Carlos Felipe Fran=E7a da Fonseca
sql
servers.
If the last update is many months ago, I'll need at least stop SQL Services.
How is the best was to do it?
Thanks folks!!!
Carlos Felipe Frana da Fonseca
Hi,
SQL Server will not keep track of these info by itself. Only way is to
verify the modifiedon column in your table. If you do not have modified on
column
then you may need to setup a profiler trace and based on the log you need to
go further.
Thanks
Hari
SQL Server MVP
"Carlos Felipe Frana da Fonseca" <carlos@.felipe.com.br> wrote in message
news:%23B0kfB52GHA.2196@.TK2MSFTNGP06.phx.gbl...
>I need to check the date of the last update on each database on some
>servers.
> If the last update is many months ago, I'll need at least stop SQL
> Services.
> How is the best was to do it?
> Thanks folks!!!
> Carlos Felipe Frana da Fonseca
>
|||Hello,
Alternative way is look into sysprocesses table in each server .
For SPID > 50 look for last_batch . Check the date and time .
Note this will provide database accessed time and not updated time .
You have to look into all user databases for last_batch before
stopping the service .
M A Srinivas
Carlos Felipe Fran=E7a da Fonseca wrote:
> I need to check the date of the last update on each database on some
> servers.
> If the last update is many months ago, I'll need at least stop SQL Servic=
es.
> How is the best was to do it?
>=20
> Thanks folks!!!
>=20
> Carlos Felipe Fran=E7a da Fonseca
sql
Check last access/update date on each database
I need to check the date of the last update on each database on some
servers.
If the last update is many months ago, I'll need at least stop SQL Services.
How is the best was to do it?
Thanks folks!!!
Carlos Felipe Frana da FonsecaHi,
SQL Server will not keep track of these info by itself. Only way is to
verify the modifiedon column in your table. If you do not have modified on
column
then you may need to setup a profiler trace and based on the log you need to
go further.
Thanks
Hari
SQL Server MVP
"Carlos Felipe Frana da Fonseca" <carlos@.felipe.com.br> wrote in message
news:%23B0kfB52GHA.2196@.TK2MSFTNGP06.phx.gbl...
>I need to check the date of the last update on each database on some
>servers.
> If the last update is many months ago, I'll need at least stop SQL
> Services.
> How is the best was to do it?
> Thanks folks!!!
> Carlos Felipe Frana da Fonseca
>|||Hello,
Alternative way is look into sysprocesses table in each server .
For SPID > 50 look for last_batch . Check the date and time .
Note this will provide database accessed time and not updated time .
You have to look into all user databases for last_batch before
stopping the service .
M A Srinivas
Carlos Felipe Fran=E7a da Fonseca wrote:
> I need to check the date of the last update on each database on some
> servers.
> If the last update is many months ago, I'll need at least stop SQL Servic=
es.
> How is the best was to do it?
>=20
> Thanks folks!!!
>=20
> Carlos Felipe Fran=E7a da Fonseca
servers.
If the last update is many months ago, I'll need at least stop SQL Services.
How is the best was to do it?
Thanks folks!!!
Carlos Felipe Frana da FonsecaHi,
SQL Server will not keep track of these info by itself. Only way is to
verify the modifiedon column in your table. If you do not have modified on
column
then you may need to setup a profiler trace and based on the log you need to
go further.
Thanks
Hari
SQL Server MVP
"Carlos Felipe Frana da Fonseca" <carlos@.felipe.com.br> wrote in message
news:%23B0kfB52GHA.2196@.TK2MSFTNGP06.phx.gbl...
>I need to check the date of the last update on each database on some
>servers.
> If the last update is many months ago, I'll need at least stop SQL
> Services.
> How is the best was to do it?
> Thanks folks!!!
> Carlos Felipe Frana da Fonseca
>|||Hello,
Alternative way is look into sysprocesses table in each server .
For SPID > 50 look for last_batch . Check the date and time .
Note this will provide database accessed time and not updated time .
You have to look into all user databases for last_batch before
stopping the service .
M A Srinivas
Carlos Felipe Fran=E7a da Fonseca wrote:
> I need to check the date of the last update on each database on some
> servers.
> If the last update is many months ago, I'll need at least stop SQL Servic=
es.
> How is the best was to do it?
>=20
> Thanks folks!!!
>=20
> Carlos Felipe Fran=E7a da Fonseca
Check last access/update date on each database
I need to check the date of the last update on each database on some
servers.
If the last update is many months ago, I'll need at least stop SQL Services.
How is the best was to do it?
Thanks folks!!!
Carlos Felipe França da FonsecaHi,
SQL Server will not keep track of these info by itself. Only way is to
verify the modifiedon column in your table. If you do not have modified on
column
then you may need to setup a profiler trace and based on the log you need to
go further.
Thanks
Hari
SQL Server MVP
"Carlos Felipe França da Fonseca" <carlos@.felipe.com.br> wrote in message
news:%23B0kfB52GHA.2196@.TK2MSFTNGP06.phx.gbl...
>I need to check the date of the last update on each database on some
>servers.
> If the last update is many months ago, I'll need at least stop SQL
> Services.
> How is the best was to do it?
> Thanks folks!!!
> Carlos Felipe França da Fonseca
>|||Hello,
Alternative way is look into sysprocesses table in each server .
For SPID > 50 look for last_batch . Check the date and time .
Note this will provide database accessed time and not updated time .
You have to look into all user databases for last_batch before
stopping the service .
M A Srinivas
Carlos Felipe Fran=E7a da Fonseca wrote:
> I need to check the date of the last update on each database on some
> servers.
> If the last update is many months ago, I'll need at least stop SQL Servic=es.
> How is the best was to do it?
> > Thanks folks!!!
> > Carlos Felipe Fran=E7a da Fonseca
servers.
If the last update is many months ago, I'll need at least stop SQL Services.
How is the best was to do it?
Thanks folks!!!
Carlos Felipe França da FonsecaHi,
SQL Server will not keep track of these info by itself. Only way is to
verify the modifiedon column in your table. If you do not have modified on
column
then you may need to setup a profiler trace and based on the log you need to
go further.
Thanks
Hari
SQL Server MVP
"Carlos Felipe França da Fonseca" <carlos@.felipe.com.br> wrote in message
news:%23B0kfB52GHA.2196@.TK2MSFTNGP06.phx.gbl...
>I need to check the date of the last update on each database on some
>servers.
> If the last update is many months ago, I'll need at least stop SQL
> Services.
> How is the best was to do it?
> Thanks folks!!!
> Carlos Felipe França da Fonseca
>|||Hello,
Alternative way is look into sysprocesses table in each server .
For SPID > 50 look for last_batch . Check the date and time .
Note this will provide database accessed time and not updated time .
You have to look into all user databases for last_batch before
stopping the service .
M A Srinivas
Carlos Felipe Fran=E7a da Fonseca wrote:
> I need to check the date of the last update on each database on some
> servers.
> If the last update is many months ago, I'll need at least stop SQL Servic=es.
> How is the best was to do it?
> > Thanks folks!!!
> > Carlos Felipe Fran=E7a da Fonseca
Monday, March 19, 2012
Check for Duplicate values in Access
Hi, I'm creating a database that needs to check a value in a text field =
=
called JobCreate in a form called frmJobCreated against a table called =
tblJobCreated which has a whole list of job numbers in a field called =
JobNo, then come back with a message telling the user if there is a =
duplicate value...any help would be greatly appreciated,,thanks, FraserPlease, post DDL, sample data, and specify expected results.
Are you trying to report duplicate values, prevent them from being inserted
or what?
ML|||Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
I'm trying to tell the user that a job number is already created if they
enter a duplicate number in the form that is already held in another
table, so it will display a message box telling them this, thanks
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
First of all, This is not the right forum because there is a dedicated forum
for access.
well. Use primary key (SOME THING LIKE SELECT COLUMN WHILE DESIGININGG AND
RIGHT CLICK SELECT PRIMARY KEY) in access on the column so that it will
prevent and throws an error. you can customise your error using VBA.
You have to use VBA because you are not using SQL SERVER 2000
Regards
R.D
--Knowledge gets doubled when shared
"Fraser Hetherington" wrote:
> Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
> I'm trying to tell the user that a job number is already created if they
> enter a duplicate number in the form that is already held in another
> table, so it will display a message box telling them this, thanks
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Why are you making your users think of unique values? Why would any user car
e
what number his job is given?
Use a column constraint and design a function that will automatically
generate unique values for the job column. You sound like a crappy ISP -
designed to serve millions of users, yet each of them must create his/her ow
n
*globally unique* username in order to create a valid account. A total waste
of time.
ML|||"Fraser" <fraser68@.tiscali.co.uk> wrote in message
news:op.sy2fmed6s8gdz1@.acer64...
Hi, I'm creating a database that needs to check a value in a text
field
called JobCreate in a form called frmJobCreated against a table called
tblJobCreated which has a whole list of job numbers in a field called
JobNo, then come back with a message telling the user if there is a
duplicate value...any help would be greatly appreciated,,thanks,
Fraser
Fraser,
Pleaes try reposting your above message in *one* of the following
newsgroups:
comp.databases.ms-access
microsoft.public.access.forms
microsoft.public.access.formscoding
Sincerely,
Chris O.
=
called JobCreate in a form called frmJobCreated against a table called =
tblJobCreated which has a whole list of job numbers in a field called =
JobNo, then come back with a message telling the user if there is a =
duplicate value...any help would be greatly appreciated,,thanks, FraserPlease, post DDL, sample data, and specify expected results.
Are you trying to report duplicate values, prevent them from being inserted
or what?
ML|||Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
I'm trying to tell the user that a job number is already created if they
enter a duplicate number in the form that is already held in another
table, so it will display a message box telling them this, thanks
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
First of all, This is not the right forum because there is a dedicated forum
for access.
well. Use primary key (SOME THING LIKE SELECT COLUMN WHILE DESIGININGG AND
RIGHT CLICK SELECT PRIMARY KEY) in access on the column so that it will
prevent and throws an error. you can customise your error using VBA.
You have to use VBA because you are not using SQL SERVER 2000
Regards
R.D
--Knowledge gets doubled when shared
"Fraser Hetherington" wrote:
> Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
> I'm trying to tell the user that a job number is already created if they
> enter a duplicate number in the form that is already held in another
> table, so it will display a message box telling them this, thanks
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Why are you making your users think of unique values? Why would any user car
e
what number his job is given?
Use a column constraint and design a function that will automatically
generate unique values for the job column. You sound like a crappy ISP -
designed to serve millions of users, yet each of them must create his/her ow
n
*globally unique* username in order to create a valid account. A total waste
of time.
ML|||"Fraser" <fraser68@.tiscali.co.uk> wrote in message
news:op.sy2fmed6s8gdz1@.acer64...
Hi, I'm creating a database that needs to check a value in a text
field
called JobCreate in a form called frmJobCreated against a table called
tblJobCreated which has a whole list of job numbers in a field called
JobNo, then come back with a message telling the user if there is a
duplicate value...any help would be greatly appreciated,,thanks,
Fraser
Fraser,
Pleaes try reposting your above message in *one* of the following
newsgroups:
comp.databases.ms-access
microsoft.public.access.forms
microsoft.public.access.formscoding
Sincerely,
Chris O.
Wednesday, March 7, 2012
check access to DB based on a systable?
I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURS
have a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS
|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURS
have a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS
|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.
check access to DB based on a systable?
I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.
check access to DB based on a systable?
I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.
check Access table exists from SSIS
I am writing my first SSIS task. I have an MS Access database and I'm moving the contents of each table into a like-named SQL Server database. The task is working properly except for one step.
The source Access database may or may not have one of the tables in it. It will always have TableA, TableB, and TableC, but may or may not have TableD. I need to write my SSIS package in such a way that it will detect the presence/absence of TableD and either run or skip the import step for this table accordingly.
Can someone assist with the step that detects the table presence/absence in the MSAccess database?
thanks,
matt tag
easy.I never work with Access/Excel, but can you run an Execute SQL task against it and populate an Integer variable with the result set?
This is the query. You'll need to map a parameter, or simply hard code the table name in place of the "?" below. If the output is greater than zero (which you can test in a precedence constraint) then the table exists, if not then the table does not exist:
SELECT count(*) as TableExists
FROM MSysObjects
WHERE MSysObjects.Type=1
and Name = ?|||
Matt,
The post of the Phil is excellent, but you must do it inside the control flow. Although you have a control error for the OLE DB Source that you can redirect to do something in the case of the source does not exist...
Regards,
Pedro
Subscribe to:
Posts (Atom)