Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>sql

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
Kay
Are you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.
|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegr oups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

Avoiding temp tables

Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:

>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan

avoid Timeout expired?

Hi,
When I execute the "Delete" statement in Query for large database, it
prompts the error
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I know there is setting to configurate it, but I couldn't find out
which one. can anyone tell me please? thanks
--
GinolaThis is a client side configuration issue (ODBC really...)
from query analyzer... you can control this from tools - options -
connections - query time out
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403c13f7.8256406@.msnews.microsoft.com...
> Hi,
> When I execute the "Delete" statement in Query for large database, it
> prompts the error
> "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> I know there is setting to configurate it, but I couldn't find out
> which one. can anyone tell me please? thanks
>
> --
> Ginola|||Thanks, I found it. my setting is 0 under QueryTime out, it should
mean unlimited, right ' But I do get the error message
"[Microsoft][ODBC SQL Server Driver]Timeout expired "
when deleting a lot of record under EM !! Any idea?
>This is a client side configuration issue (ODBC really...)
>from query analyzer... you can control this from tools - options -
>connections - query time out
--
Ginola|||Hi,
"0 is Unlimited".
Try to delete the records from Query Analyzer using Delete statement. Here
delete statement will be faster if you have indexes on columns used in where
clause of delete statement.
Thanks
Hari
MCDBA
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> >This is a client side configuration issue (ODBC really...)
> >
> >from query analyzer... you can control this from tools - options -
> >connections - query time out
> --
> Ginola|||Hi,
I am really really new in SQL server and I haven't had much experience
in using Query Analyzer. Most of my experience is from MS-Access
If I write the following, will it work ?
Use DBName
go
Delete * from [TableName]
go
Why we don't use delete under EM instead ?
On Wed, 25 Feb 2004 13:04:58 +0530, "Hari" <hari_prasad_k@.hotmail.com>
wrote:
>Hi,
>"0 is Unlimited".
>Try to delete the records from Query Analyzer using Delete statement. Here
>delete statement will be faster if you have indexes on columns used in where
>clause of delete statement.
>Thanks
>Hari
>MCDBA
>
>"Ginola" <ginola@.mailcity.om> wrote in message
>news:403d30ed.15670093@.msnews.microsoft.com...
>> Thanks, I found it. my setting is 0 under QueryTime out, it should
>> mean unlimited, right ' But I do get the error message
>> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
>> when deleting a lot of record under EM !! Any idea?
>>
>> >This is a client side configuration issue (ODBC really...)
>> >
>> >from query analyzer... you can control this from tools - options -
>> >connections - query time out
>> --
>> Ginola
>
Ginola|||Enterprise Manager does not look at the timeout settings used by QA. There
is no way to change the default timeout setting for EM...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> >This is a client side configuration issue (ODBC really...)
> >
> >from query analyzer... you can control this from tools - options -
> >connections - query time out
> --
> Ginola

avoid Timeout expired?

Hi,
When I execute the "Delete" statement in Query for large database, it
prompts the error
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I know there is setting to configurate it, but I couldn't find out
which one. can anyone tell me please? thanks
GinolaThis is a client side configuration issue (ODBC really...)
from query analyzer... you can control this from tools - options -
connections - query time out
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403c13f7.8256406@.msnews.microsoft.com...
> Hi,
> When I execute the "Delete" statement in Query for large database, it
> prompts the error
> "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> I know there is setting to configurate it, but I couldn't find out
> which one. can anyone tell me please? thanks
>
> --
> Ginola|||Thanks, I found it. my setting is 0 under QueryTime out, it should
mean unlimited, right ' But I do get the error message
"[Microsoft][ODBC SQL Server Driver]Timeout expired "
when deleting a lot of record under EM !! Any idea?

>This is a client side configuration issue (ODBC really...)
>from query analyzer... you can control this from tools - options -
>connections - query time out
Ginola|||Hi,
"0 is Unlimited".
Try to delete the records from Query Analyzer using Delete statement. Here
delete statement will be faster if you have indexes on columns used in where
clause of delete statement.
Thanks
Hari
MCDBA
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> --
> Ginola|||Hi,
I am really really new in SQL server and I haven't had much experience
in using Query Analyzer. Most of my experience is from MS-Access
If I write the following, will it work ?
Use DBName
go
Delete * from [TableName]
go
Why we don't use delete under EM instead ?
On Wed, 25 Feb 2004 13:04:58 +0530, "Hari" <hari_prasad_k@.hotmail.com>
wrote:

>Hi,
>"0 is Unlimited".
>Try to delete the records from Query Analyzer using Delete statement. Here
>delete statement will be faster if you have indexes on columns used in wher
e
>clause of delete statement.
>Thanks
>Hari
>MCDBA
>
>"Ginola" <ginola@.mailcity.om> wrote in message
>news:403d30ed.15670093@.msnews.microsoft.com...
>
Ginola|||Enterprise Manager does not look at the timeout settings used by QA. There
is no way to change the default timeout setting for EM...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> --
> Ginola|||Try a Truncate table <tablename>. It's the fastest way to delete all records
from a table.

Avoid Looping / Cursors. Help with Statement.

Hi Folks,
I have two tables, one of which I want to update from another.
Essentiall I have a table of orders and a product table. I want to
subtract the qty sold in the orders table from the QtyInStock column in
the Products table, for every line in an order.
But I dont really want to loop through each line in the order, either
in application or with a cursor as something is telling me there must
be a neater solution!
Example Orders Table.
OrderID ProductID Qty
1, 104, 2
1, 199, 1
2, 100, 3
3, 858, 1
ProductID, QtyInStock
104, 3
199, 1
etc ...
As you can see I want to be able to run a query against OrderID 1,
and it to reduce the QtyInStock column by the correct amount for
products 104 and 199 as an example.
Can this be done with one statement, or will I have to loop ?
Thanks in Advance.
Craig.Hi
CREATE TABLE #Test1
(
OrderID int,
ProductID int,
Qty int
)
INSERT INTO #Test1 VALUES (1,104,2)
INSERT INTO #Test1 VALUES (1,199,1)
INSERT INTO #Test1 VALUES (2,100,3)
INSERT INTO #Test1 VALUES (3,828,1)
CREATE TABLE #Test2
(
ProductID int,
QtyInStock int
)
INSERT INTO #Test2 VALUES (104,3)
INSERT INTO #Test2 VALUES (199,1)
UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
WHERE EXISTS (SELECT * FROM #Test2
T WHERE T.ProductID=#Test1.ProductID)
DROP TABLE #Test1,#Test2
<craig.parsons@.crawfos.com> wrote in message
news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column in
> the Products table, for every line in an order.
> But I dont really want to loop through each line in the order, either
> in application or with a cursor as something is telling me there must
> be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
> Thanks in Advance.
>
> Craig.
>|||Uri,
I think Craig wants to update the quantity in stock
from the Product table, not the quantity in the Orders
table, which your query updates. He could use Jens's
solution, or one like this:
UPDATE #Products SET
QtyInStock = QtyInStock - (
SELECT SUM(O.Qty)
FROM #Orders AS O
WHERE O.ProductID = #Products.ProductID
)
WHERE EXISTS (
SELECT * FROM #Orders
WHERE #Orders.ProductID = #Products.ProductID
)
Steve Kass
Drew University
Uri Dimant wrote:

>Hi
>CREATE TABLE #Test1
>(
> OrderID int,
> ProductID int,
> Qty int
> )
>INSERT INTO #Test1 VALUES (1,104,2)
>INSERT INTO #Test1 VALUES (1,199,1)
>INSERT INTO #Test1 VALUES (2,100,3)
>INSERT INTO #Test1 VALUES (3,828,1)
>
>CREATE TABLE #Test2
>(
> ProductID int,
> QtyInStock int
> )
>INSERT INTO #Test2 VALUES (104,3)
>INSERT INTO #Test2 VALUES (199,1)
>
>UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
>FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
>WHERE EXISTS (SELECT * FROM #Test2
>T WHERE T.ProductID=#Test1.ProductID)
>
>
>DROP TABLE #Test1,#Test2
>
>
><craig.parsons@.crawfos.com> wrote in message
>news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
>
>
>|||craig.parsons@.crawfos.com wrote:
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column
> in the Products table, for every line in an order.
> But I dont really want to loop through each line in the order,
> either in application or with a cursor as something is telling me
> there must be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
Here is the ANSI version (I used the Sum function to guarantee only a single
result would be returned):
UPDATE Products
SET QtyInStock = QtyInStock -
(SELECT Sum(Qty) FROM Orders o
WHERE o.OrderID = 1 AND o.ProductID = Products.ProductID)
The T-SQL version:
UPDATE p
SET QtyInStock = QtyInStock - o.Qty
FROM Products p inner join (
SELECT ProductID,Sum(Qty) AS Qty FROM Orders
WHERE OrderID = 1 GROUP BY ProductID) o
ON o.ProductID = p.ProductID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.