How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJTry something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] ='temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:
>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized?
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.
The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:

> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:

> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql

How to Create Index

If I run query mostly on column expressions, like I have two columns in tabl
e
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:

> If I run query mostly on column expressions, like I have two columns in ta
ble
> one for date and other is for time, when i want to build index, then i bui
ld
> index on both columns, but will these separate index work when i write que
ry
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized
'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"Dan Guzman" wrote:

> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
>|||You would have to create a computed column containing that expression and th
en index that computed
column. Or, create view with the expression and index the view (note that op
timizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
>
> "Dan Guzman" wrote:
>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
> "Dan Guzman" wrote:
>

How to create and schedule a job using linked servers?

There are 2 sql servers : A and C.
I have to grab data on server A and update records with this data on server C. It has to run every night, so it has to be scheduled as a job.
I created linked server on server A and ran query select... It works.
I can create Update query.
What are my steps next? I read how to create a job, but not understand how to incorporate the query into it. I am totaly unexpirenced user, please explain step by step or give me a link to a good explanation. I also do not understand how and whether DTS should be used in all this.
Appreciate any help.

Ann2wrap ur sql query in SP,create and call that SP from job steps.
From BOL,
How to create a job (Enterprise Manager)

To create a job
Expand a server group, and then expand a server.
Expand Management, and then expand SQL Server Agent.
Right-click Jobs, and then click New Job.
In the Name box, enter a name for the job.
To create a Transact-SQL job step
Create a new job or right-click an existing job, and then click Properties. For more information on creating a job, see Creating Jobs (javascript:hhobj_301.Click()).
In the Job Properties dialog box, click the Steps tab, and then click New.
In the Step name box, enter a job step name.
In the Type list, click Transact-SQL Script (TSQL).
In the Database list, click a database for this job step to use.
In the Command box, enter the Transact-SQL command batch(es), or click Open to select a Transact-SQL file to use as the command.
Click Parse to check your syntax. The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.|||I created a stored procedure for the job and not sure what Type and Command do I use in this case? EXEC MyProcedure in command window eith type TSQL does not work. The procedur is working fine when I run this command in Query Analyser:EXEC MyProcedure .

Thank you.
Ann

How to create an event driven shared schedule

We have a number of jobs that run nightly. Typically these complete long before the shared schedule runs. However, when they do not complete in time, we need to stop the shared schedule from running. How is this accomplished?

I have seen that it is possible to create event driven subscriptions but am not sure this accomplishes the same thing. Basically, I need to pause the shared schedule if a particular job has not completed successfully. Then, I need to run the shared schedule once the job has completed. None of the reports we are running are snapshot based. Would moving to this method resolve the issue? Any help is greatly appreciated.

I had the same issue. What I did was kick off the subscription SQL Agent job at the end of my ETL job. That way I know the import will always be done before the reports are published.

BobP

|||

That will have to be the solution we employ as well, at least until a better solution can be found.

Thanks!

How to create a trigger to update a field

Hi -

I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.

Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.

Is there a way to do this with a trigger (or an otherwise elegant way)?

Thanks in advance for ANY help or ideas.

Oliver

Why do you need a trigger for that? Cant your application/stored proc handle the logic? Triggers are generally a maintenance nightmare (IMHO). You have to use them cautiously if you have to.

|||

Thanks for the advice. Oliver

|||

I agree with ndinakar, stay away from triggers.

Your best bet is to handle the logic in your app or stored proc. For a stored procedure you could handle it as so

Create Procedure dbo.myUpdateProc( @.fieldIdentityint, @.RequestIDbigint =NULL)If @.RequestIDISNOT NULLBegin Update <table>set RequestID = @.RequestID, Booktime =GetDate()where = @.fieldIdentityEnd

sql

How to create a spread sheet from a sql table results?

I have to run a simple query (say select name from table where id = 4) and get those results and make it into a report. I am not sure I have crystal reports (do I have to install some thing for this?). So what is the easiest way to create this? May in in a spread sheet or some thing like this? I am so new to SQL Server

Thanks

Possible way:

Retrieve data in DataSet
|||

Hi dotnet001,

Of course you can use Crystal reports to generate that report. But since you have mentioned that you want the "easiest" way and "may in a spead sheet", I think maybe you can try the DataGrid control. You can fist connect to the database and execute the sql query string,then call the databind method of the DataGrid control to retrieve data from the query result set. After that you will have a detailed list which show all the items you get from that database.

I would like to suggest you to read something on ADO.NET and databinding. See:http://www.w3schools.com/aspnet/aspnet_databinding.asp (tutorial)

http://www.codeproject.com/aspnet/Mastering_DataBinding.asp (code example)

Hope my suggestion can help

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

How to create a single reusable alert in SQL Server Agent?

Hi,
I have numerous SQL scripts that run as jobs under SQL Server Agent.
I've been playing with the alert system and got it working using a
test script.
However, it seems that it's not possible to create a single alert and
associate it with every script.
When right-clicking on a job under SQL Server Agent in SSMS and
selecting the 'Alerts' tab and the 'Add...' option, there is no way
to
select an alert that I have already created. So, this forces me to
create a new alert, which basically does exactly the same thing
(raises an alert when the job message contains the word 'error') as
one that I created previously, meaning the only difference will be
the
alert name.
The problem is that I have over a two dozen scripts that I want to
set
an alert on.
Am I missing something or are multiple duplicate scripts the right
way
to go?
Thanks,
Frank.
You can also write a VBScript that sends email alerts using SMTP. This way
you can pass parameters like probably the names of the jobs and call this
script inside your SQL job. This is what I did for log shipping alerts for a
clustered SQL Server 2000 since IMAP is not supported in this scenario
<francis.moore@.gmail.com> wrote in message
news:801cbe16-c882-4f96-bfb6-7dec556ddd50@.j44g2000hsj.googlegroups.com...
> Hi,
> I have numerous SQL scripts that run as jobs under SQL Server Agent.
> I've been playing with the alert system and got it working using a
> test script.
> However, it seems that it's not possible to create a single alert and
> associate it with every script.
> When right-clicking on a job under SQL Server Agent in SSMS and
> selecting the 'Alerts' tab and the 'Add...' option, there is no way
> to
> select an alert that I have already created. So, this forces me to
> create a new alert, which basically does exactly the same thing
> (raises an alert when the job message contains the word 'error') as
> one that I created previously, meaning the only difference will be
> the
> alert name.
> The problem is that I have over a two dozen scripts that I want to
> set
> an alert on.
> Am I missing something or are multiple duplicate scripts the right
> way
> to go?
> Thanks,
> Frank.
|||Tibor,
Thanks for the response.
I'm still confused, although I can see what you are saying.

> When an alert fires, you can specify to execute a job.
I can see that now, but what I want to do is execute a job using SQL
Server Agent and then if that job fails, I want it to trigger an alert
that emails me the fact that the job has failed. I've had a few
occasions recently where batch jobs have failed and it hasn't been
noticed until a day or so later.
In SSMS, when selecting a job under SQL Server Agent | Jobs and right-
clicking, you can get to a Properties dialog for that job.
On that dialog there are some tabs, General, Steps, Schedules, Alerts,
Notifications and Targets.
Selecting Alerts give you a page with a button titled 'Add...' at the
bottom of it.
Clicking 'Add...' brings up a 'New Alert' dialog.
I am expecting to enter details in there to create an alert for that
job.
It allows me to do this once, but when I go to the next job, to create
a similar alert for that job, I can neither pick the alert that I just
created, nor can I create another alert similar to the pervious one as
it says that one like this already exists.
What I would really like to do is create one alert (i.e. when message
contains text like 'error') and then select that to be the alert that
I can use for that job and possibly others as well. However, it
doesn't seem to work like that. So, I'm still confused.
So, is it possible to use alerts as I want to in SQL Server 2005?
And if not, how would you let an operator know that a batch job has
failed (preferably by email)?
Many thanks for your time,
Frank.
|||Tibor,
Once someone points it out to you it's becomes so blatantly
obvious :-)
You're a star!
Many thanks,
Frank.
|||On Nov 27, 12:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm glad you "see the light", Frank.
> Actually, I've always been confused what the "Alert" tab does for a job. I do know what alerts are
> (EventLog, Perfmon and now WMI), but I just failed to understand why we would have an alert tab for
> the job. It just felt ... backwards. You configure and alert, and then what job to fire. So your
> post made me investigate what this alert tab (for a job) does and indeed it doesn't bring any new
> functionality, it just allow us to configure that this job is to be fired for an alert - in a pretty
> backwards kind of way (and indeed the same config as you do for the alert).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <francis.mo...@.gmail.com> wrote in message
> news:d59e3dd6-8aa5-4bea-bc03-515257f5a3b3@.a35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
Frank - you should check out the xSQL Software's RSS Reporter for SQL
Server (http://www.xsqlsoftware.com/Product/
Sql_Server_Rss_Reporter.aspx) - it is a great way to notify someone on
the status of SQL Server Jobs. It automatically generates RSS feeds
that aggregate job status information from multiple servers - it
avoids the hassles associated with the email notifications, it allows
you to drill down on a job to see what step of it actually failed
etc.
JC

How to create a DUMP table in MSSQL

In mySQL it is easy to create a DUMP table which when run creates the table and inserts the data ....

In MSSQL I can create a file which I can use to re-create the table but I can not see how to also add the data with out doing a backup in MSSQL ... anyone know where this is possible?

There are a couple of choices;

1/ export the data out as text by using BCP or Integration Services.
2/ Instead of scripting data and schema just detach the database from one instance, copy it and then attach on the other end.

BCP is a sql solution, there is UI for the others under All Tasks on a right click.

How to Create a Catalog Alias

Hello,
I have run into a little problem,
We have a Database Catalog named "Product-V1.0", and I need to create a DTS
package. However when I create the SQL connection and point to the Catalog,
it truncates the Catalog name to "Product-V1" removing the ".0" from the
name. Hence I can't connect. I tried to add [] around the Catalog name
without any effect. So firstly is there a way that I can configure the DTS
connection to work with this Catalog name. Alternately I was thinking that
perhaps I could create a Alias name for the Catalog and connect to this
name. I tried to create a linked Server, but creating the Linked server
produced the same result as the DTS and I could not connect.
If possible I would like to find an Alternate to renaming the Database
Catalog name.
Regards,
Bernard.
Hi
You can not create an alias for a DB/catalog.
It is best to follow the standards as described in BOL "Using Identifiers".
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bernard Sircelj" wrote:

> Hello,
> I have run into a little problem,
> We have a Database Catalog named "Product-V1.0", and I need to create a DTS
> package. However when I create the SQL connection and point to the Catalog,
> it truncates the Catalog name to "Product-V1" removing the ".0" from the
> name. Hence I can't connect. I tried to add [] around the Catalog name
> without any effect. So firstly is there a way that I can configure the DTS
> connection to work with this Catalog name. Alternately I was thinking that
> perhaps I could create a Alias name for the Catalog and connect to this
> name. I tried to create a linked Server, but creating the Linked server
> produced the same result as the DTS and I could not connect.
> If possible I would like to find an Alternate to renaming the Database
> Catalog name.
> Regards,
> Bernard.
>
>

How to Create a Catalog Alias

Hello,
I have run into a little problem,
We have a Database Catalog named "Product-V1.0", and I need to create a DTS
package. However when I create the SQL connection and point to the Catalog,
it truncates the Catalog name to "Product-V1" removing the ".0" from the
name. Hence I can't connect. I tried to add [] around the Catalog name
without any effect. So firstly is there a way that I can configure the DTS
connection to work with this Catalog name. Alternately I was thinking that
perhaps I could create a Alias name for the Catalog and connect to this
name. I tried to create a linked Server, but creating the Linked server
produced the same result as the DTS and I could not connect.
If possible I would like to find an Alternate to renaming the Database
Catalog name.
Regards,
Bernard.Hi
You can not create an alias for a DB/catalog.
It is best to follow the standards as described in BOL "Using Identifiers".
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bernard Sircelj" wrote:
> Hello,
> I have run into a little problem,
> We have a Database Catalog named "Product-V1.0", and I need to create a DTS
> package. However when I create the SQL connection and point to the Catalog,
> it truncates the Catalog name to "Product-V1" removing the ".0" from the
> name. Hence I can't connect. I tried to add [] around the Catalog name
> without any effect. So firstly is there a way that I can configure the DTS
> connection to work with this Catalog name. Alternately I was thinking that
> perhaps I could create a Alias name for the Catalog and connect to this
> name. I tried to create a linked Server, but creating the Linked server
> produced the same result as the DTS and I could not connect.
> If possible I would like to find an Alternate to renaming the Database
> Catalog name.
> Regards,
> Bernard.
>
>

How to Create a Catalog Alias

Hello,
I have run into a little problem,
We have a Database Catalog named "Product-V1.0", and I need to create a DTS
package. However when I create the SQL connection and point to the Catalog,
it truncates the Catalog name to "Product-V1" removing the ".0" from the
name. Hence I can't connect. I tried to add [] around the Catalog name
without any effect. So firstly is there a way that I can configure the DTS
connection to work with this Catalog name. Alternately I was thinking that
perhaps I could create a Alias name for the Catalog and connect to this
name. I tried to create a linked Server, but creating the Linked server
produced the same result as the DTS and I could not connect.
If possible I would like to find an Alternate to renaming the Database
Catalog name.
Regards,
Bernard.Hi
You can not create an alias for a DB/catalog.
It is best to follow the standards as described in BOL "Using Identifiers".
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bernard Sircelj" wrote:

> Hello,
> I have run into a little problem,
> We have a Database Catalog named "Product-V1.0", and I need to create a DT
S
> package. However when I create the SQL connection and point to the Catalog
,
> it truncates the Catalog name to "Product-V1" removing the ".0" from the
> name. Hence I can't connect. I tried to add [] around the Catalog name
> without any effect. So firstly is there a way that I can configure the DTS
> connection to work with this Catalog name. Alternately I was thinking that
> perhaps I could create a Alias name for the Catalog and connect to this
> name. I tried to create a linked Server, but creating the Linked server
> produced the same result as the DTS and I could not connect.
> If possible I would like to find an Alternate to renaming the Database
> Catalog name.
> Regards,
> Bernard.
>
>

How to create a button in Reports

Hello every one,
I am using reports 10g.In the Run time parameter form after given the
values for the parameters, i want to click a button so that the report
can run. we already have an option in the menu bar of the Run time
parameter form to run the report but our customers specfically needs
the button option. So how to create a Button in the Run time parameter
form?
Can anyone please help me on this,
Thanks
Balaji.On Nov 6, 12:27 am, rampal...@.gmail.com wrote:
> Hello every one,
> I am using reports 10g.In the Run time parameter form after given the
> values for the parameters, i want to click a button so that the report
> can run. we already have an option in the menu bar of the Run time
> parameter form to run the report but our customers specfically needs
> the button option. So how to create a Button in the Run time parameter
> form?
> Can anyone please help me on this,
> Thanks
> Balaji.
If you want to mix HTML Forms with ReportingServices Reports, I
suggest using ASP.NET. This way:
1. the page renders on your IIS server and is callable from a URL
2. you can have an HTML form with Inputs & Submit button
3. the Submit posts back to the IIS server which returns a page with
a RS Object rendered as an IFRAME in the HTML (ReportViewer I think it
is called)
4. The IIS takes the parameters from the URL using Request objects,
then passes those as Parameters to the ReportViewer object, which then
calls the Reporting Services RDL file, which renders in the web page.
Down side is that the report cannot be deployed to the Reporting
Services Server, but can be Navigated to via a URL to the ASP.NET
folder on your web server.
-- Scott

how to copy sql database?

Hi there
I make a database called "TEST" for the template in ms sql 2000 server, and
I want to run VB.net to create another database called "TEST_COPY" in same
sql 2000 server and I want to transfer anything the "TEST" has (like tables
with data, views,stored procedure, trigger, role etc), how can I make a copy
in VB.net?
Thanks
Tony
Tony,
If you want everything (as it seems you do), then the easiest (assuming that
you have the needed rights) is to issue the following commands.
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi there
> I make a database called "TEST" for the template in ms sql 2000 server,
and
> I want to run VB.net to create another database called "TEST_COPY" in same
> sql 2000 server and I want to transfer anything the "TEST" has (like
tables
> with data, views,stored procedure, trigger, role etc), how can I make a
copy
> in VB.net?
>
> Thanks
> Tony
>
|||Or you can just issue the update query command:
SELECT * INTO test_copy FROM test;
Regards.
|||I'M SORRY FOR THIS INFO, I THOUGHT YOU'RE REFERRING TO A TABLE.
"Jon Gonzales" wrote:

> Or you can just issue the update query command:
> SELECT * INTO test_copy FROM test;
> Regards.
|||Dear Russell
when I use this you suggested
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
I change the first one to
BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
and it is works
and then when I use second one,
RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
I got Error as follow:
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.mdf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.ldf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
When I use with move as follow:
RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with Recovery,
move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to 'C:\Test_copy.ldf'
I got other error as follow
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How can I keep doing with the copy like you suggested?
Thanks
Tony
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> Tony,
> If you want everything (as it seems you do), then the easiest (assuming
that[vbcol=seagreen]
> you have the needed rights) is to issue the following commands.
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> Russell Fields
> "tony" <itdong@.hotmail.com> wrote in message
> news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> and
same
> tables
> copy
>
|||tony,
Sorry that my shorthand answer was not specific enough.
You will also need to use the option (from the BOL):
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is restored to
its original location. If the RESTORE statement is used to copy a database
to the same or different server, the MOVE option may be needed to relocate
the database files and to avoid collisions with existing files. Each logical
file in the database can be specified in different MOVE statements.
This keeps the file from trying to overwrite the active database.
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:%23wQZHgthEHA.2812@.tk2msftngp13.phx.gbl...
> Dear Russell
> when I use this you suggested
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> I change the first one to
> BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
> and it is works
> and then when I use second one,
> RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
> I got Error as follow:
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.mdf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.ldf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> When I use with move as follow:
> RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with
Recovery,
> move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to
'C:\Test_copy.ldf'[vbcol=seagreen]
> I got other error as follow
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
> How can I keep doing with the copy like you suggested?
> Thanks
> Tony
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> that
server,[vbcol=seagreen]
> same
a
>

how to copy sql database?

Hi there
I make a database called "TEST" for the template in ms sql 2000 server, and
I want to run VB.net to create another database called "TEST_COPY" in same
sql 2000 server and I want to transfer anything the "TEST" has (like tables
with data, views,stored procedure, trigger, role etc), how can I make a copy
in VB.net?
Thanks
TonyTony,
If you want everything (as it seems you do), then the easiest (assuming that
you have the needed rights) is to issue the following commands.
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi there
> I make a database called "TEST" for the template in ms sql 2000 server,
and
> I want to run VB.net to create another database called "TEST_COPY" in same
> sql 2000 server and I want to transfer anything the "TEST" has (like
tables
> with data, views,stored procedure, trigger, role etc), how can I make a
copy
> in VB.net?
>
> Thanks
> Tony
>|||Or you can just issue the update query command:
SELECT * INTO test_copy FROM test;
Regards.|||I'M SORRY FOR THIS INFO, I THOUGHT YOU'RE REFERRING TO A TABLE.
"Jon Gonzales" wrote:

> Or you can just issue the update query command:
> SELECT * INTO test_copy FROM test;
> Regards.|||Dear Russell
when I use this you suggested
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
I change the first one to
BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
and it is works
and then when I use second one,
RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
I got Error as follow:
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.mdf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.ldf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
---
When I use with move as follow:
RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with Recovery,
move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to 'C:\Test_copy.ldf'
I got other error as follow
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How can I keep doing with the copy like you suggested?
Thanks
Tony
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> Tony,
> If you want everything (as it seems you do), then the easiest (assuming
that
> you have the needed rights) is to issue the following commands.
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> Russell Fields
> "tony" <itdong@.hotmail.com> wrote in message
> news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> and
same[vbcol=seagreen]
> tables
> copy
>|||tony,
Sorry that my shorthand answer was not specific enough.
You will also need to use the option (from the BOL):
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is restored to
its original location. If the RESTORE statement is used to copy a database
to the same or different server, the MOVE option may be needed to relocate
the database files and to avoid collisions with existing files. Each logical
file in the database can be specified in different MOVE statements.
This keeps the file from trying to overwrite the active database.
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:%23wQZHgthEHA.2812@.tk2msftngp13.phx.gbl...
> Dear Russell
> when I use this you suggested
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> I change the first one to
> BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
> and it is works
> and then when I use second one,
> RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
> I got Error as follow:
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.mdf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.ldf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> ---
> When I use with move as follow:
> RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with
Recovery,
> move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to
'C:\Test_copy.ldf'
> I got other error as follow
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
> How can I keep doing with the copy like you suggested?
> Thanks
> Tony
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> that
server,[vbcol=seagreen]
> same
a[vbcol=seagreen]
>

how to copy sql database?

Hi there
I make a database called "TEST" for the template in ms sql 2000 server, and
I want to run VB.net to create another database called "TEST_COPY" in same
sql 2000 server and I want to transfer anything the "TEST" has (like tables
with data, views,stored procedure, trigger, role etc), how can I make a copy
in VB.net?
Thanks
TonyTony,
If you want everything (as it seems you do), then the easiest (assuming that
you have the needed rights) is to issue the following commands.
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi there
> I make a database called "TEST" for the template in ms sql 2000 server,
and
> I want to run VB.net to create another database called "TEST_COPY" in same
> sql 2000 server and I want to transfer anything the "TEST" has (like
tables
> with data, views,stored procedure, trigger, role etc), how can I make a
copy
> in VB.net?
>
> Thanks
> Tony
>|||Or you can just issue the update query command:
SELECT * INTO test_copy FROM test;
Regards.|||I'M SORRY FOR THIS INFO, I THOUGHT YOU'RE REFERRING TO A TABLE.
"Jon Gonzales" wrote:
> Or you can just issue the update query command:
> SELECT * INTO test_copy FROM test;
> Regards.|||Dear Russell
when I use this you suggested
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
I change the first one to
BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
and it is works
and then when I use second one,
RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
I got Error as follow:
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.mdf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.ldf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
---
When I use with move as follow:
RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with Recovery,
move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to 'C:\Test_copy.ldf'
I got other error as follow
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How can I keep doing with the copy like you suggested?
Thanks
Tony
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> Tony,
> If you want everything (as it seems you do), then the easiest (assuming
that
> you have the needed rights) is to issue the following commands.
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> Russell Fields
> "tony" <itdong@.hotmail.com> wrote in message
> news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> > Hi there
> >
> > I make a database called "TEST" for the template in ms sql 2000 server,
> and
> > I want to run VB.net to create another database called "TEST_COPY" in
same
> > sql 2000 server and I want to transfer anything the "TEST" has (like
> tables
> > with data, views,stored procedure, trigger, role etc), how can I make a
> copy
> > in VB.net?
> >
> >
> > Thanks
> >
> > Tony
> >
> >
>|||tony,
Sorry that my shorthand answer was not specific enough.
You will also need to use the option (from the BOL):
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is restored to
its original location. If the RESTORE statement is used to copy a database
to the same or different server, the MOVE option may be needed to relocate
the database files and to avoid collisions with existing files. Each logical
file in the database can be specified in different MOVE statements.
This keeps the file from trying to overwrite the active database.
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:%23wQZHgthEHA.2812@.tk2msftngp13.phx.gbl...
> Dear Russell
> when I use this you suggested
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> I change the first one to
> BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
> and it is works
> and then when I use second one,
> RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
> I got Error as follow:
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.mdf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.ldf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> ---
> When I use with move as follow:
> RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with
Recovery,
> move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to
'C:\Test_copy.ldf'
> I got other error as follow
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
> How can I keep doing with the copy like you suggested?
> Thanks
> Tony
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> > Tony,
> >
> > If you want everything (as it seems you do), then the easiest (assuming
> that
> > you have the needed rights) is to issue the following commands.
> >
> > BACKUP DATABASE TEST TO TestBackupFileName
> >
> > RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> >
> > Russell Fields
> > "tony" <itdong@.hotmail.com> wrote in message
> > news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> > > Hi there
> > >
> > > I make a database called "TEST" for the template in ms sql 2000
server,
> > and
> > > I want to run VB.net to create another database called "TEST_COPY" in
> same
> > > sql 2000 server and I want to transfer anything the "TEST" has (like
> > tables
> > > with data, views,stored procedure, trigger, role etc), how can I make
a
> > copy
> > > in VB.net?
> > >
> > >
> > > Thanks
> > >
> > > Tony
> > >
> > >
> >
> >
>

How to copy query result to a label?

Hi,

I want to run a select query and copy the result to a label. I created a SqlDataSource1, but I don't know how to go ahead.

I would like to do something like:

string x = SqlDataSource1.SelectCommand

Please, advice!

label takes values of 1D. Your SQL results are 2D. How do you expect to bind them together? You would need a data control such as datagrid/gridview etc.

|||The same way most people would think -- assume your talking about the first row always. Unfortunately ASP.NET is a pain that way -- You have to bind the datasource to a control that understands row traversing first (Even if your query only returns one row, which is stupid), place all your labels within that control, and then you can bind them to a particular column.

how to copy files using xp_cmdshell

Howdy, hope someone can help me out with this.
I want to run a job each night that copies files from one server to another.
I cant even get a simple copy one file from one directory to another,
statement to work.
When I try :

@.cmd 'copy c:\temp\file1.txt c:\backups\file1.txt'
master.dbo.xp_cmdshell @.cmd

I get the msg: 'c' is not recognised as an internal command, program or
batch file, Null.

What I ideally want to do is pass xp_cmdshell variables for the 2 file's
path+names. I have tried numerous variations of single and double quotes
without success, which is why I decided to work up form the simple 'copy
c:\temp\file1.txt c:\backups\file1.txt', but can't even get that to work.
Any advice much appreciated.SJM (na) writes:

Quote:

Originally Posted by

Howdy, hope someone can help me out with this. I want to run a job each
night that copies files from one server to another. I cant even get a
simple copy one file from one directory to another, statement to work.
When I try :
>
@.cmd 'copy c:\temp\file1.txt c:\backups\file1.txt'
master.dbo.xp_cmdshell @.cmd
>
I get the msg: 'c' is not recognised as an internal command, program or
batch file, Null.
>
What I ideally want to do is pass xp_cmdshell variables for the 2 file's
path+names. I have tried numerous variations of single and double quotes
without success, which is why I decided to work up form the simple 'copy
c:\temp\file1.txt c:\backups\file1.txt', but can't even get that to work.
Any advice much appreciated.

I would guess you have made a mistake in the declaration of @.cmd:

DECLARE @.cmd varchar

this is the same as

DECLARE @.cmd varchar(1)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 18, 3:15 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

SJM (na) writes:

Quote:

Originally Posted by

Howdy, hope someone can help me out with this. I want to run a job each
night that copies files from one server to another. I cant even get a
simple copy one file from one directory to another, statement to work.
When I try :

>

Quote:

Originally Posted by

@.cmd 'copy c:\temp\file1.txt c:\backups\file1.txt'
master.dbo.xp_cmdshell @.cmd

>

Quote:

Originally Posted by

I get the msg: 'c' is not recognised as an internal command, program or
batch file, Null.

>

Quote:

Originally Posted by

What I ideally want to do is pass xp_cmdshell variables for the 2 file's
path+names. I have tried numerous variations of single and double quotes
without success, which is why I decided to work up form the simple 'copy
c:\temp\file1.txt c:\backups\file1.txt', but can't even get that to work.
Any advice much appreciated.

>
I would guess you have made a mistake in the declaration of @.cmd:
>
DECLARE @.cmd varchar
>
this is the same as
>
DECLARE @.cmd varchar(1)
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

IMO, it should throw error saying "Length is not specified"
Sometimes it becomes tedius to debug if you forget to specify column
width|||Madhivanan (madhivanan2001@.gmail.com) writes:

Quote:

Originally Posted by

IMO, it should throw error saying "Length is not specified"
Sometimes it becomes tedius to debug if you forget to specify column
width

Yes, the default of 1 is silly.

While not explicitly mentioned, this is typically something that should
be flagged when SET STRICT_CHECKS ON is in effect, a SET option which I
have suggested in
https://connect.microsoft.com/SQLSe...edbackID=260762.

Votes are welcome!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx