Database backup?

Hi,
Two questions, in the query analyzer I typed this:
backup database hcn to disk 'D:\Microsoft SQL
Server\MSSQL\BACKUP\mybackup01232006.bak'
And I got a syntax error: Line 1: Incorrect syntax near 'D:\Microsoft SQL
Server\MSSQL\BACKUP\hcnprod01232006.bak'.
I also tried it without the 'disk' parameter as it looked in the online help
but got the same message. What did I do wrong?
Secondly, how can I direct this backup to another server? Using a share on
a different server with similar results.
I am able to perform backups using the enterprise manager to the location in
my first question but not to a network share using the UNC:
\\server\share\directory\subdirectory\fi
le.bck
Any help is greatly appreciated,
LinnYou're missing an =
backup database hcn to disk = 'D:\Microsoft SQL
Server\MSSQL\BACKUP\mybackup01232006.bak'
HTH. Ryan
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:ePm17cCIGHA.2928@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Two questions, in the query analyzer I typed this:
> backup database hcn to disk 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\mybackup01232006.bak'
> And I got a syntax error: Line 1: Incorrect syntax near 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\hcnprod01232006.bak'.
> I also tried it without the 'disk' parameter as it looked in the online
> help but got the same message. What did I do wrong?
> Secondly, how can I direct this backup to another server? Using a share
> on a different server with similar results.
> I am able to perform backups using the enterprise manager to the location
> in my first question but not to a network share using the UNC:
> \\server\share\directory\subdirectory\fi
le.bck
> Any help is greatly appreciated,
> Linn
>|||Ah, I see, thanks Ryan. It's always simple once you see it. How about the
other question, any idea what I'm doing wrong there? Isn't it possible to
backup to a remote server?
Thanks,
Linn
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uPeAbgCIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> You're missing an =
>
> backup database hcn to disk = 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\mybackup01232006.bak'
> --
> HTH. Ryan
> "Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
> news:ePm17cCIGHA.2928@.TK2MSFTNGP10.phx.gbl...
>|||What is the account being used for running the services for SQL Server? Is i
t
a LocalSystem account? If so, it probably doesn't have access to the remote
location you have specified. Changing to a domain account that has access to
that location might solve the problem, but there could be security concerns
that you should read up on.
HTH
"Linn Kubler" wrote:

> Ah, I see, thanks Ryan. It's always simple once you see it. How about th
e
> other question, any idea what I'm doing wrong there? Isn't it possible to
> backup to a remote server?
> Thanks,
> Linn
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uPeAbgCIGHA.2064@.TK2MSFTNGP09.phx.gbl...
>
>|||I'm using the sa account and I see what you mean. How does the sa account
have rights to save files to the local directories? I can't locate it
anywhere outside of SQL. How does that work?
Thanks,
Linn
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:354CE2D0-6B8F-4AC3-A747-93B0A800DBD9@.microsoft.com...
> What is the account being used for running the services for SQL Server? Is
> it
> a LocalSystem account? If so, it probably doesn't have access to the
> remote
> location you have specified. Changing to a domain account that has access
> to
> that location might solve the problem, but there could be security
> concerns
> that you should read up on.
> HTH
> "Linn Kubler" wrote:
>|||Open up the Services (Start-ControlPanel-AdminTools-Services) and look for
the Sql server services. Check what account they are running under (Log on
as). If they are logging on as LocalSystem, that might be causing the
problem. Note that you must NOT change the service account from the Services
directly. Go to Enterprise manager (assuming you are using Sql 2k), select
the server and then, the properties. You will find a way to change it there
(I don't have access to a server right now, so cannot give you the exact tab
to look at, but it should be fairly easy to find. Change it there (again,
make sure you read up about the security concerns before you do that)
"Linn Kubler" wrote:

> I'm using the sa account and I see what you mean. How does the sa account
> have rights to save files to the local directories? I can't locate it
> anywhere outside of SQL. How does that work?
> Thanks,
> Linn
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:354CE2D0-6B8F-4AC3-A747-93B0A800DBD9@.microsoft.com...
>
>|||On the server properties, select the "Security" Tab and at the bottom of the
tab, you will notice "Startup service account" - change it from "system
account" to "This account" and put in your domain ID/pwd that has the
necessary access. If I'm not mistaken, it will require you to restart the sq
l
server service.
"Bob" wrote:

> Open up the Services (Start-ControlPanel-AdminTools-Services) and look for
> the Sql server services. Check what account they are running under (Log on
> as). If they are logging on as LocalSystem, that might be causing the
> problem. Note that you must NOT change the service account from the Servic
es
> directly. Go to Enterprise manager (assuming you are using Sql 2k), select
> the server and then, the properties. You will find a way to change it ther
e
> (I don't have access to a server right now, so cannot give you the exact t
ab
> to look at, but it should be fairly easy to find. Change it there (again,
> make sure you read up about the security concerns before you do that)
>

Database Backup using Query analyzer

Hi there,

How can I automate may database backup using query analyser?
My routine goes this way:
1. Every 5:00pm check Enterprise manager who is currenlty logged on to sql server.
2. If there are no currently logged on, edit the date sql script (back-up, both full and incremental). Be sure there are folders created at the main server.
3. Open Query analyser: log as backup operator
4. Open the SQL File, >Run>Close

This routine will take me 15 to 30 minutes a day because of the large file size.

Is there an easier way?I would recommend using SQL Agent for scheduling the job. You may need to make your query more robust depending on the reason you check for users in the database. If you are implying that you only perform a backup if there are no users connected, then your procedure would need to loop until that condition occurs -- SQL Server can successfully backup a database with active connections, so I'm not sure why you check for that.

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, Steven
Which version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>
|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, StevenWhich version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>|||... or perhaps the backup history tables in msdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
> Which version are you talking about? If it is 2000 then have a look at the
> sysdbmaintplan_databases table in msdb.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Steven" <Steven@.nospaml.com> wrote in message news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up in any Maintenance plan
>>or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>> Which version are you talking about? If it is 2000 then have a look at
>> the sysdbmaintplan_databases table in msdb.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Steven" <Steven@.nospaml.com> wrote in message
>> news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up
>>in any Maintenance plan or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out
>> using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>

Database as a Parameter?!

I have 10 Databases with same query to extract same info from all databases.
One thing I can do is to create 10 different datasets to get my results or
join 10 queries in one big query.
But what I really would like to do is to create one dataset and pass a
database-name parameter so users can select the particular database from pull
down menu and view the results from that database. So basically it will be
running query for one database at a time. I tried to do it the same way you
create all parameters but of no use. I will appreciate any kind of help.
Example:
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
I can define and use @.ReportMonth and @.ReportYear but database name
parameter @.DatabaseName is not working.
Thanks in advance.Hi,
I'm using a similar method for maintaining many report servers on different
locations and each connecting to their own databases.
I use the below text as dataset source
= "Exec " & Parameters!LSQLServerName.Value & "." &
Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
Parameters!ParameterOne.Value
LSQLServerName parameter is the Linked SQL Server. So even on the same
location you can change your sql server machine.
Note that you will not need this parameter if your reporting database is on
the same server with your datasource. Otherwise you have to declare a linked
server on the server running reporting server databases.
DatabaseName parameter is the catalog name in the database server.
Then you pass the query (or sp with parameters)
Actually you send at the end a similar query:
" Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
I hope this helps.
Eralper Yilmaz
http://www.eralper.com
http://www.kodyaz.com/default.aspx
"GJ" wrote:
> I have 10 Databases with same query to extract same info from all databases.
> One thing I can do is to create 10 different datasets to get my results or
> join 10 queries in one big query.
> But what I really would like to do is to create one dataset and pass a
> database-name parameter so users can select the particular database from pull
> down menu and view the results from that database. So basically it will be
> running query for one database at a time. I tried to do it the same way you
> create all parameters but of no use. I will appreciate any kind of help.
> Example:
> Select manufacturer, sales
> From @.DatabaseName.dbo.product FDP
> Where
> (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> I can define and use @.ReportMonth and @.ReportYear but database name
> parameter @.DatabaseName is not working.
> Thanks in advance.|||Hi Eralper,
Thank you for the quick reply. But I think I am confused now as how or where
to write those statements.
The way I have been using reporting server is to create a dataset, create a
datasource in the dataset (or use a shared datasource), either write text
query or exec SP there. Then write the parameters used in the query in the
'Report Parameters' tab in VS.NET.
I am not sure if I followed it correctly, but if you are saying to create a
new DATASOURCE.. and write the source as:
= "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
In my situation, that is giving me errors.
On the other hand, running this " Exec
[SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
solve my problem as here we are giving DBName (Pubs) and that is static.
What I am trying is ... create a pull-don menu in the report and give the
user an option to select the particular database. Then the query is run and
results are returned for the query for the particular database selected.
I may not have understood your explanation but am still confused.
Thank you for your time and patience.
GJ
"eralper" wrote:
> Hi,
> I'm using a similar method for maintaining many report servers on different
> locations and each connecting to their own databases.
> I use the below text as dataset source
> = "Exec " & Parameters!LSQLServerName.Value & "." &
> Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> Parameters!ParameterOne.Value
> LSQLServerName parameter is the Linked SQL Server. So even on the same
> location you can change your sql server machine.
> Note that you will not need this parameter if your reporting database is on
> the same server with your datasource. Otherwise you have to declare a linked
> server on the server running reporting server databases.
> DatabaseName parameter is the catalog name in the database server.
> Then you pass the query (or sp with parameters)
> Actually you send at the end a similar query:
> " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> I hope this helps.
> Eralper Yilmaz
> http://www.eralper.com
> http://www.kodyaz.com/default.aspx
>
> "GJ" wrote:
> > I have 10 Databases with same query to extract same info from all databases.
> > One thing I can do is to create 10 different datasets to get my results or
> > join 10 queries in one big query.
> >
> > But what I really would like to do is to create one dataset and pass a
> > database-name parameter so users can select the particular database from pull
> > down menu and view the results from that database. So basically it will be
> > running query for one database at a time. I tried to do it the same way you
> > create all parameters but of no use. I will appreciate any kind of help.
> > Example:
> >
> > Select manufacturer, sales
> > From @.DatabaseName.dbo.product FDP
> > Where
> > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> >
> > I can define and use @.ReportMonth and @.ReportYear but database name
> > parameter @.DatabaseName is not working.
> >
> > Thanks in advance.|||He is using the technique of having an expression as the query source (not
the data source). If you use the generic query designer (not the graphical
one) you can put an expression in, just as he shows you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:0FC916A0-A499-4131-8F49-CC26CDEA2911@.microsoft.com...
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or
where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create
a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create
a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run
and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on
different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is
on
> > the same server with your datasource. Otherwise you have to declare a
linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all
databases.
> > > One thing I can do is to create 10 different datasets to get my
results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database
from pull
> > > down menu and view the results from that database. So basically it
will be
> > > running query for one database at a time. I tried to do it the same
way you
> > > create all parameters but of no use. I will appreciate any kind of
help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.|||Hi,
Your desired sp is below.
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
First of all, create the @.DatabaseName parameter in the report parameters
screen
Then go to Data tab, and write the following statement
Select manufacturer, sales From OneOfYourDatabases.dbo.product FDP
Replace OneOfYourDatabases with a database name. And execute the query.
Running this query will populate the fields list for this dataset so you can
use these fields in the Layout tab. (You can also add field names later on
the Field Tab if necessary)
The third step will be rearrainging the query. Replace it with :
= "Select manufacturer, sales
From " & Parameters!DatabaseName.value & ".dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportMonth.value & "')
and
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportYear.value & "')"
Lastly, click "Refresh" button (somehow this solved problems a few times),
then save.
If I'm not wrong, these four steps should work as you want.
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is on
> > the same server with your datasource. Otherwise you have to declare a linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all databases.
> > > One thing I can do is to create 10 different datasets to get my results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database from pull
> > > down menu and view the results from that database. So basically it will be
> > > running query for one database at a time. I tried to do it the same way you
> > > create all parameters but of no use. I will appreciate any kind of help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.

Database access

Is there any way to query SQL Server to see the last time a given database w
as access?
thanks guys Hi,
SQL Server will not store this information into any tables. Only way is to
run the profiler or write you own triger to monitor the master..sysprocesses
table
and log the audit information into a table. Later you could query the table
to get the last access date and time for the database.
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys |||No.
What I do is run a stored proc executed via SQL Agent that polls the
sysprocesses table hourly for db connections and I write the info to a table
I've defined.
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys |||Hari,
Thanks so much for the help. I think I like the trigger idea. In any case, i
t will sharpen my scripting skills, such as they are.
Sara Beth
"Hari Prasad" wrote:

> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the master..sysprocess
es
> table
> and log the audit information into a table. Later you could query the tabl
e
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>|||Simon,
Thank you for the suggestion. I see from your answer and Hari's that this wi
ll be a wee bit of work for me. Ah, but that's ok. It'll keep me busy.
Sara Beth
"Simon" wrote:

> No.
> What I do is run a stored proc executed via SQL Agent that polls the
> sysprocesses table hourly for db connections and I write the info to a tab
le
> I've defined.
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>|||I believe the sysprocesses table is a virtual table and triggers cannot be
defined on them.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the
master..sysprocesses
> table
> and log the audit information into a table. Later you could query the
table
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
database[vbcol=seagreen]
> was access?
>|||Hi Simon
This is correct, but not the whole story.
Triggers cannot be created on ANY system table, not just the virtual tables.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Simon" <me@.me.me> wrote in message
news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
> I believe the sysprocesses table is a virtual table and triggers cannot be
> defined on them.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
> master..sysprocesses
> table
> database
>|||Hi Kalen... "Simon" is my pen name... you may remember me
from our discussions via email a couple weeks back revolving
around the aging out of statistics that Microsoft changed on you
after your book went to press... You may remember me!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O$7w0rcbEHA.644@.tk2msftngp13.phx.gbl...
> Hi Simon
> This is correct, but not the whole story.
> Triggers cannot be created on ANY system table, not just the virtual
tables.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Simon" <me@.me.me> wrote in message
> news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
is[vbcol=seagreen]
> to
>|||Hi Simon / Kalen,
Thanks for pointing out my error.
Sara Beth,
Like Simon suggested, you could create a table with Login name ,
database_name, login_time ,last_batch_time in some database and populate the
table using sysprocesses. Schedule the acticvity using sql agent jobs
Create the below table in some database.
---
Create table audit_database(login varchar(256),
dbname varchar(128),
login_time datetime,
last_batch_time datetime)
Schedule the below script to execute using SQL Agent -- Jobs
---
insert into dbname..audit_database select loginame
,db_name(dbid),login_time,last_batch from master..sysprocesses
change the dbname.. withoriginal database name in which the audit_database
table resides.
Use the below query to get the last login name for database wise.
----
select dbname, max(login_time) as last_login_time from audit_database
group by dbname
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:0ACD84B6-3BAF-4C58-AC57-20D17606B0DF@.microsoft.com...
> Simon,
> Thank you for the suggestion. I see from your answer and Hari's that
this will be a wee bit of work for me. Ah, but that's ok. It'll keep me
busy.[vbcol=seagreen]
> Sara Beth
> "Simon" wrote:
>
table[vbcol=seagreen]
database[vbcol=seagreen]

Database access

Is there any way to query SQL Server to see the last time a given database was access?
thanks guys
Hi,
SQL Server will not store this information into any tables. Only way is to
run the profiler or write you own triger to monitor the master..sysprocesses
table
and log the audit information into a table. Later you could query the table
to get the last access date and time for the database.
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys
|||No.
What I do is run a stored proc executed via SQL Agent that polls the
sysprocesses table hourly for db connections and I write the info to a table
I've defined.
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys
|||Hari,
Thanks so much for the help. I think I like the trigger idea. In any case, it will sharpen my scripting skills, such as they are.
Sara Beth
"Hari Prasad" wrote:

> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the master..sysprocesses
> table
> and log the audit information into a table. Later you could query the table
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>
|||Simon,
Thank you for the suggestion. I see from your answer and Hari's that this will be a wee bit of work for me. Ah, but that's ok. It'll keep me busy.
Sara Beth
"Simon" wrote:

> No.
> What I do is run a stored proc executed via SQL Agent that polls the
> sysprocesses table hourly for db connections and I write the info to a table
> I've defined.
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>
|||I believe the sysprocesses table is a virtual table and triggers cannot be
defined on them.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the
master..sysprocesses
> table
> and log the audit information into a table. Later you could query the
table[vbcol=seagreen]
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
database
> was access?
>
|||Hi Simon
This is correct, but not the whole story.
Triggers cannot be created on ANY system table, not just the virtual tables.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Simon" <me@.me.me> wrote in message
news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I believe the sysprocesses table is a virtual table and triggers cannot be
> defined on them.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
to
> master..sysprocesses
> table
> database
>
|||Hi Kalen... "Simon" is my pen name... you may remember me
from our discussions via email a couple weeks back revolving
around the aging out of statistics that Microsoft changed on you
after your book went to press... You may remember me!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O$7w0rcbEHA.644@.tk2msftngp13.phx.gbl...
> Hi Simon
> This is correct, but not the whole story.
> Triggers cannot be created on ANY system table, not just the virtual
tables.[vbcol=seagreen]
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Simon" <me@.me.me> wrote in message
> news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
is
> to
>
|||Hi Simon / Kalen,
Thanks for pointing out my error.
Sara Beth,
Like Simon suggested, you could create a table with Login name ,
database_name, login_time ,last_batch_time in some database and populate the
table using sysprocesses. Schedule the acticvity using sql agent jobs
Create the below table in some database.
Create table audit_database(login varchar(256),
dbname varchar(128),
login_time datetime,
last_batch_time datetime)
Schedule the below script to execute using SQL Agent -- Jobs
insert into dbname..audit_database select loginame
,db_name(dbid),login_time,last_batch from master..sysprocesses
change the dbname.. withoriginal database name in which the audit_database
table resides.
Use the below query to get the last login name for database wise.
select dbname, max(login_time) as last_login_time from audit_database
group by dbname
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:0ACD84B6-3BAF-4C58-AC57-20D17606B0DF@.microsoft.com...
> Simon,
> Thank you for the suggestion. I see from your answer and Hari's that
this will be a wee bit of work for me. Ah, but that's ok. It'll keep me
busy.[vbcol=seagreen]
> Sara Beth
> "Simon" wrote:
table[vbcol=seagreen]
database[vbcol=seagreen]

Data/Preview windows not showing same data return

When I test my query in the "Data" window (tab), I get a healthy number of
rows returned. When I test under the "Preview" window (tab), I get a tiny
fraction of rows returned. Can someone tell why this is? Perhaps, something
in the "Layout" that I am missing? I'm not even sure how the data I am
getting in the "Preview" window is unique compared to the other data I did
not get.
Thanks in advance for any clues!Here is what might be happening. The preview tab caches data. If the
parameter is not changed it re-uses the data. Look where your .rdl file is
and you should see files with a .data extension. Either delete this file to
force it to hit the database again or just run your report with a different
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lu" <Lu@.discussions.microsoft.com> wrote in message
news:E1372BDD-F320-4687-9390-F703136400FF@.microsoft.com...
> When I test my query in the "Data" window (tab), I get a healthy number of
> rows returned. When I test under the "Preview" window (tab), I get a tiny
> fraction of rows returned. Can someone tell why this is? Perhaps,
> something
> in the "Layout" that I am missing? I'm not even sure how the data I am
> getting in the "Preview" window is unique compared to the other data I did
> not get.
> Thanks in advance for any clues!|||Thanks, Bruce. I flushed the old data, but this did not resolve the issue.
I forgot to mention that I deployed as well thinking it may have been cached
data. However, the problem also persists on production. I have the
parameters the same on both test preview and the raw query (data tab) and
even in the SQL Srvr Mgmt Studio...the preview and production windows only
retrieve 3 rows of data as opposed to the expected 3,000+ rows of data. It
should also be noted that something must've changed that I didn't catch
because the query was pulling more info than it is now...not all of it, but
was still pulling more than now.
"Bruce L-C [MVP]" wrote:
> Here is what might be happening. The preview tab caches data. If the
> parameter is not changed it re-uses the data. Look where your .rdl file is
> and you should see files with a .data extension. Either delete this file to
> force it to hit the database again or just run your report with a different
> parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lu" <Lu@.discussions.microsoft.com> wrote in message
> news:E1372BDD-F320-4687-9390-F703136400FF@.microsoft.com...
> > When I test my query in the "Data" window (tab), I get a healthy number of
> > rows returned. When I test under the "Preview" window (tab), I get a tiny
> > fraction of rows returned. Can someone tell why this is? Perhaps,
> > something
> > in the "Layout" that I am missing? I'm not even sure how the data I am
> > getting in the "Preview" window is unique compared to the other data I did
> > not get.
> > Thanks in advance for any clues!
>
>

Data Using DTS

Hi I have a query and when I run it in SQL query analyzer and select "results to file option I get the nice header of the column in included in the file that it generates. My problem is when I want to do this in a DTS job, the header does not align as nicely. For example see below...
Query run in Query analyzer and select results in file option I get this header...
Employee ID Social Security Number Birth Date
---------------
111111111 123456789 01/01/2000
(not how it is aligned)...
Result from DTS...
"Employee ID","Social Security Number","Birth Date"
"111111111","123456789","01/01/2000"
How can I output as the query analyzer using DTS?
Thanks.

In the Export Data wizard, change the "Delimited" option to "Fixed Width".

Data type Object in Send Mail Task?

I have an Execute SQL Task that runs a simple SELECT query. I have the result set = Full Result Set. The variable is of type Object and the value is System.Oject. After successful completion of the Execute SQL Task, I am doing a Send Mail task. For the Message Source, I want to use this Object. The drop down is only listing variables of type String. Can you not use a variable of type Object in a Send Mail Task? If not, what is the easiest workaround? Thanks!

No, you can't use an object for the message source. You could use a column from the resultset, though. If you give more description on what you are trying to do, we'll be able to suggest some work arounds.

If you are trying to send the contents of the recordset in an email, here are a few posts that might help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1768087&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1502762&SiteID=1

Data type conversion

I have one column which contains numbers [but the column is in char format]
What will be the proper SQL query to sort that column ? If i use order by
clause, the records are sorted in characted method, i.e 40,50 not 40,41Arsalan,
Try:
ORDER BY LTRIM(RTRIM(column))
HTH
Jerry
"Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
news:%23o3VP5czFHA.3836@.TK2MSFTNGP10.phx.gbl...
>I have one column which contains numbers [but the column is in char format]
> What will be the proper SQL query to sort that column ? If i use order by
> clause, the records are sorted in characted method, i.e 40,50 not 40,41
>|||Doesnt work.
LTRIM and RTRIM will remove the space, but doesnt sort properly.
MY records are like this
40
41
42
.
..
..
50
when i use order by clause, its sorts like this --> 40,50 [bec if character
field]. How do I sort the numeric part of the character field properly?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...
> Arsalan,
> Try:
>
> ORDER BY LTRIM(RTRIM(column))
> HTH
> Jerry
> "Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
> news:%23o3VP5czFHA.3836@.TK2MSFTNGP10.phx.gbl...
>|||create table #t(s char(4))
insert into #t(s) values('5')
insert into #t(s) values('50')
insert into #t(s) values('40')
insert into #t(s) values('41')
select s
from #t
order by s
select cast(s as int) p
from #t
order by p
drop table #t
Payson
Arsalan wrote:
> Doesnt work.
> LTRIM and RTRIM will remove the space, but doesnt sort properly.
> MY records are like this
> 40
> 41
> 42
> .
> ..
> ..
> 50
> when i use order by clause, its sorts like this --> 40,50 [bec if characte
r
> field]. How do I sort the numeric part of the character field properly?
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...|||order by cast(ltrim(rtrim(column as int)))
"Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
news:OKxzKOdzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Doesnt work.
> LTRIM and RTRIM will remove the space, but doesnt sort properly.
> MY records are like this
> 40
> 41
> 42
> .
> ..
> ..
> 50
> when i use order by clause, its sorts like this --> 40,50 [bec if
> character field]. How do I sort the numeric part of the character field
> properly?
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||Arsalan,
Worked on my server. Try the examples listed in the following posts with
CAST --> INT.
HTH
Jerry
"Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
news:OKxzKOdzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Doesnt work.
> LTRIM and RTRIM will remove the space, but doesnt sort properly.
> MY records are like this
> 40
> 41
> 42
> .
> ..
> ..
> 50
> when i use order by clause, its sorts like this --> 40,50 [bec if
> character field]. How do I sort the numeric part of the character field
> properly?
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||Thanks
"Jay" <msnews.microsoft.com> wrote in message
news:unWe6ddzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> order by cast(ltrim(rtrim(column as int)))
>
> "Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
> news:OKxzKOdzFHA.3924@.TK2MSFTNGP14.phx.gbl...
>

data transform and compare with in a query

Okay all I have a problem. I have two list of adresses and phone
numbers. I do not have control over the contents of the lists The only
unique field between the two is the phone number. I need to be able to
inner join the two lists on phone number.

This would normally be straigt forward but the problem is that they are
formated different and one of them does't even have a control on the
formating.

*Phone numbers are US phone numbers only.

The one list (table) that does have a control uses this format

AAA3334444

where AAA is the area code
333 is the 3 digit prefix
4444 is the four digit suffix

the second list (table) does not have any standardized formating and
can be filled with extraneous spaces, parentheses and dashes not to
mention the leading 1 in some instances.

I thought that I could do some kind of regular expression to do a
comparison but I havn't as yet found a good resource to tell me how to
do it or if it is even possible. Or maybe break up the one I know has
a standardized format into something like this:

'%AAA%333%4444%' and doing my comparison that way. however It is very
important that only those list items in both list that are truly the
same place be listed.

suggestions and solutions are apreciated"Dan Gidman" <danatcofo@.gmail.com> wrote in message
news:1109353284.020286.122200@.g14g2000cwa.googlegr oups.com...
> Okay all I have a problem. I have two list of adresses and phone
> numbers. I do not have control over the contents of the lists The only
> unique field between the two is the phone number. I need to be able to
> inner join the two lists on phone number.
> This would normally be straigt forward but the problem is that they are
> formated different and one of them does't even have a control on the
> formating.
> *Phone numbers are US phone numbers only.
> The one list (table) that does have a control uses this format
> AAA3334444
> where AAA is the area code
> 333 is the 3 digit prefix
> 4444 is the four digit suffix
> the second list (table) does not have any standardized formating and
> can be filled with extraneous spaces, parentheses and dashes not to
> mention the leading 1 in some instances.
> I thought that I could do some kind of regular expression to do a
> comparison but I havn't as yet found a good resource to tell me how to
> do it or if it is even possible. Or maybe break up the one I know has
> a standardized format into something like this:
> '%AAA%333%4444%' and doing my comparison that way. however It is very
> important that only those list items in both list that are truly the
> same place be listed.
> suggestions and solutions are apreciated

It probably depends how bad the data is - if the number and type of the
unwanted characters is relatively predictable, then you can achieve quite a
lot with REPLACE():

create table #t (pnum varchar(20))

insert into #t select '1-800-456 7890 '
insert into #t select '(800)- 456 7890'
insert into #t select '1 800 456 7890'
insert into #t select '+1 800 (456) 7890 '
insert into #t select '(800) (456) 7890'

select pnum from #t

/* Strip out unwanted characters */
update #t set pnum = replace(pnum, ' ', '')
update #t set pnum = replace(pnum, '(', '')
update #t set pnum = replace(pnum, ')', '')
update #t set pnum = replace(pnum, '-', '')
update #t set pnum = replace(pnum, '+', '')

/* Remove leading 1 */
update #t set pnum = stuff(pnum, 1, 1, '')
where left(pnum,1) = '1'
and len(pnum) = 11

select pnum from #t

But if it gets much more complicated than that, I would consider using
bcp.exe to export the data, clean it up with an external script in a
language that has better string functions than TSQL, then load it again.

It is possible to use regexes in TSQL, by instantiating the VBScript Regex
object using the sp_OA% procedures, but it's rather clumsy and requires
sysadmin permissions, so it's probably not a good general solution.

Simon|||I have achieved some good success with nesting using the replace
function like you have suggested it. It occured to me after doing my
initial post.

replace(replace(replace(replace(number, '(',''), ')',''), '-',''), '
','')

this actually works very well. still not getting the results I need
however. I think that I next need to check for a 1 at the begining of
the string and remove it.

after that I think if I just grab the first 10 digits it will get
something I can compare against|||I think I found the answer to my own question. so far this little sql
statement is a wonder worker for cleaning up the data.

SELECT PhoneNumber = CASE WHEN SUBSTRING(temper.PhoneNumber,1,1) = '1'
THEN SUBSTRING(temper.PhoneNumber,2,11) ELSE
SUBSTRING(temper.PhoneNumber,1,10) END FROM (SELECT
Replace(Replace(Replace(Replace(Replace(LatestTWPr ops.PhoneNumber,'
',''),'-',''),'(',''),')',''),'/','') AS PhoneNumber FROM LatestTWProps
WHERE countryCode = 'US' AND PhoneNumber IS NOT NULL) AS temper