Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylThere are two options:
1=2E Do a Restore (Perhaps you have a point in time backup of the
database with transaction logs)
2=2E If you capsulated the query in a transaction (which I assume you
didn=B4t) Roll it back with ROLLBACK
HTH, Jens Suessmeyer.|||Daryl
yes, if you are lucky. you have to resort to back up. when did you take last
back up?
If you have taken any back up just prior to you can apply that. Wait I
understand that is not there. so you have to depend on 'Point in time '
Recovery.
BOL Has this: But remember. It will restore back to that particulat time. So
all the oprations after that time are be done manually again. Anyway take
full back up with no_truncate option before doing anything.
To restore to a point in time BOL has this.
Expand a server group, and then expand a server.
Expand Databases, right-click the database, point to All Tasks, and then
click Restore Database.
In Restore as database, type or select the name of the database to restore,
if different from the default.
Click Database.
In the First backup to restore list, click the backup set to restore.
In the Restore list, select the database backup and one or more transaction
logs to restore.
Click Point in time restore, and then type values for Date and Time.
Click the Options tab, and then click Leave database operational. No
additional transaction logs can be restored.
--
search and read for point-in-time;point of failure and related topics before
doing anything
--
Take Preventive measures like using
1) use delete trigger to not to rollback when
@.@.rowcount >1 or some records( if it is practical for you)
2) use trigger to store in history tables if data is so crucial.
Regards
R.D
"Daryl" wrote:
> Dear All,
> If I used a SQL command "Delete" to delete for example 1000 records, can I
> get it back after? It is because I executed the delete command and later
> found out I need some records back. Thank you for your help.
>
> Daryl
>
>
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Tuesday, March 27, 2012
Can I get back the deleted records
Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
Daryl
Daryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
Daryl
Daryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Can I get back the deleted records
Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Can I get back the deleted records
Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Sunday, March 25, 2012
Can I do This
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
AhmedAhmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Sorry,correction
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=C.Code)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahmed,
Try:
select code, name, monthdate, code,
(select sum(amount)
from b
where b.code = a.code
and month(b.date) = a.monthdate) as total
from a;
If you're after applying a table expression querying B and returning a
rowset, to each row from A, there's no set-based way to achieve this in SQL
Server 2000.
You'll have to use itterative logic. SQL Server 2005 solves this by
introducing the APPLY operator, e.g.,
-- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each Store
USE pubs;
SELECT ST.stor_id, CA.*
FROM dbo.Stores AS ST
CROSS APPLY
(SELECT TOP(2) ord_num, title_id, ord_date, qty
FROM dbo.Sales AS SL
WHERE SL.stor_id = ST.stor_id AND qty >= 10
ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on
a value in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON
A.Code=C.Code
thanks in advance
Ahmed|||I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahemd
Yes , you are right , please take a look at Itzik's solution
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=B.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:u5bldm1lFHA.32
88@.TK2MSFTNGP09.phx.gbl...
I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Thanks BG
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OLrJph1lFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Ahmed,
> Try:
> select code, name, monthdate, code,
> (select sum(amount)
> from b
> where b.code = a.code
> and month(b.date) = a.monthdate) as total
> from a;
> If you're after applying a table expression querying B and returning a
> rowset, to each row from A, there's no set-based way to achieve this in
> SQL Server 2000.
> You'll have to use itterative logic. SQL Server 2005 solves this by
> introducing the APPLY operator, e.g.,
> -- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each
> Store
> USE pubs;
> SELECT ST.stor_id, CA.*
> FROM dbo.Stores AS ST
> CROSS APPLY
> (SELECT TOP(2) ord_num, title_id, ord_date, qty
> FROM dbo.Sales AS SL
> WHERE SL.stor_id = ST.stor_id AND qty >= 10
> ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Dear all
> I need to make a correlated subquery that has the where clause depend
> on a value in the master query, ex
> Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
> I want to perform SQL like this
> SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
> B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C
> ON A.Code=C.Code
>
> thanks in advance
> Ahmed
>
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
AhmedAhmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Sorry,correction
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=C.Code)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahmed,
Try:
select code, name, monthdate, code,
(select sum(amount)
from b
where b.code = a.code
and month(b.date) = a.monthdate) as total
from a;
If you're after applying a table expression querying B and returning a
rowset, to each row from A, there's no set-based way to achieve this in SQL
Server 2000.
You'll have to use itterative logic. SQL Server 2005 solves this by
introducing the APPLY operator, e.g.,
-- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each Store
USE pubs;
SELECT ST.stor_id, CA.*
FROM dbo.Stores AS ST
CROSS APPLY
(SELECT TOP(2) ord_num, title_id, ord_date, qty
FROM dbo.Sales AS SL
WHERE SL.stor_id = ST.stor_id AND qty >= 10
ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on
a value in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON
A.Code=C.Code
thanks in advance
Ahmed|||I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahemd
Yes , you are right , please take a look at Itzik's solution
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=B.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:u5bldm1lFHA.32
88@.TK2MSFTNGP09.phx.gbl...
I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Thanks BG
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OLrJph1lFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Ahmed,
> Try:
> select code, name, monthdate, code,
> (select sum(amount)
> from b
> where b.code = a.code
> and month(b.date) = a.monthdate) as total
> from a;
> If you're after applying a table expression querying B and returning a
> rowset, to each row from A, there's no set-based way to achieve this in
> SQL Server 2000.
> You'll have to use itterative logic. SQL Server 2005 solves this by
> introducing the APPLY operator, e.g.,
> -- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each
> Store
> USE pubs;
> SELECT ST.stor_id, CA.*
> FROM dbo.Stores AS ST
> CROSS APPLY
> (SELECT TOP(2) ord_num, title_id, ord_date, qty
> FROM dbo.Sales AS SL
> WHERE SL.stor_id = ST.stor_id AND qty >= 10
> ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Dear all
> I need to make a correlated subquery that has the where clause depend
> on a value in the master query, ex
> Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
> I want to perform SQL like this
> SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
> B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C
> ON A.Code=C.Code
>
> thanks in advance
> Ahmed
>
Sunday, March 11, 2012
Can I access sql 2000 from machine having sql2005
Dear All,
I am developing a network application in asp.net. The database is in local machine having sql 2005. But the user database is already existent. So I am accessing that database which is sql2000. does it give any problem while connecting from sql2005 machine to sql2000. If yes it is giving me error as follows:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
connection string is :
"Data Source=192.168.1.16,1433;Network Library=DBMSSOCN;Initial Catalog=mycatalog;User ID=myuserid;Password=mypwd;"
Please correct me if I am wrong.
Thanks and Regards.
Fazal
This indicates that your TCP/IP was not enabled or your sql server was not listening on the appropriate port.
|||Go to "Microsoft SQL Server 2005" in programm files => onfiguration Tools =>SQL Server Surface Area Configuration =>first option=>Remote Connections
and here allow remote connection = Use both TCP/IP and name pipes => OK
After that you must restart SQL Server 2005 in order to apply the modification and try againa
Popa IUlia
_____________________
MCP.MCAD.MCSD
can define UDF in DLL
Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
Tarvirdi
answered in microsoft.public.sqlserver.programming
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:eiDuCG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
Tarvirdi
answered in microsoft.public.sqlserver.programming
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:eiDuCG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
can define UDF in DLL
Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
TarvirdiHi
SQL Server 2005 allows you to create .NET CLR objects that can be used
inside SQL Server.
http://www.microsoft.com/sql/prodin...ation-demo.mspx
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>|||The best way to implement this depends on what version of SQL Server you are
using.
Extended Stored Procedure Architecture
http://msdn.microsoft.com/library/d...r />
_67vp.asp
Adding an Extended Stored Procedure to SQL Server
http://msdn2.microsoft.com/en-us/library/ms164653.aspx
For reasons of performance, security and maintainability, it is best to
avoid the use of external library function calls unless there is a very
special need. As an example, if you are basically wanting access to
functions for string parsing or financial calculations, then this could be
implemented using advanced SQL techniques or perhaps implemented at the
application level.
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
TarvirdiHi
SQL Server 2005 allows you to create .NET CLR objects that can be used
inside SQL Server.
http://www.microsoft.com/sql/prodin...ation-demo.mspx
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>|||The best way to implement this depends on what version of SQL Server you are
using.
Extended Stored Procedure Architecture
http://msdn.microsoft.com/library/d...r />
_67vp.asp
Adding an Extended Stored Procedure to SQL Server
http://msdn2.microsoft.com/en-us/library/ms164653.aspx
For reasons of performance, security and maintainability, it is best to
avoid the use of external library function calls unless there is a very
special need. As an example, if you are basically wanting access to
functions for string parsing or financial calculations, then this could be
implemented using advanced SQL techniques or perhaps implemented at the
application level.
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
Sunday, February 12, 2012
Calling Visual Foxpro 7 COM
Dear Experts,
We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).
We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.
The Automation manager connection doesn't release all the connections.
What effects is this having to our SQL Server 2005 installation and how can we avoid it?
We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.
Thank you for your time.
Regards,
Spyros Christodoulou
You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,Thanks for your reply.
Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.
The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)
For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.
Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?
Regards,
Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,
Thanks for your reply,
I found a post you made at microsoft.public.sqlserver.programming on august of 2001.
"I have used this. It works perfectly fine. Are you making a EXE? Does it
have UI? Make sure it doesn't have any of those. Here is one sample:
NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE
NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE
-- Now run this on the SQL Server
declare @.o int, @.h int, @.p varchar( 255 )
exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.o
exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.p
exec sp_oadestroy @.o"
I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)
Do you know how to make it an out of process and called it based on the following scenario:
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
Any help will be greatly appreciated.
Thanks again,
Spyros Christodoulou
|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||
Hi Umachandar and Spyros:
I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.
The remaining problem is also similar as the one Spyros described:
"(...)
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
(...)"
In server 1, I have the COM+ application o package correctly installed and working since years
In Clients computers various front end consume the server classes via, for example in VB
CreateObject('AppMastervs.cOrga', mtsServer1)
or in VFP
CreateObjectEx('AppMastervs.cOrga', mtsServer1)
These clients has registered the corresponding .vbr & tlb via CliReg32
I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5
Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.
So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.
Thanks in advance;
Claudio Facundo Lacivita
Calling Visual Foxpro 7 COM
Dear Experts,
We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).
We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.
The Automation manager connection doesn't release all the connections.
What effects is this having to our SQL Server 2005 installation and how can we avoid it?
We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.
Thank you for your time.
Regards,
Spyros Christodoulou
You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,Thanks for your reply.
Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.
The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)
For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.
Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?
Regards,
Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,
Thanks for your reply,
I found a post you made at microsoft.public.sqlserver.programming on august of 2001.
"I have used this. It works perfectly fine. Are you making a EXE? Does it
have UI? Make sure it doesn't have any of those. Here is one sample:
NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE
NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE
-- Now run this on the SQL Server
declare @.o int, @.h int, @.p varchar( 255 )
exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.o
exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT
if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h
print @.p
exec sp_oadestroy @.o"
I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)
Do you know how to make it an out of process and called it based on the following scenario:
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
Any help will be greatly appreciated.
Thanks again,
Spyros Christodoulou
|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||
Hi Umachandar and Spyros:
I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.
The remaining problem is also similar as the one Spyros described:
"(...)
Server 1: VFP Database
Server 2: SQL Server
I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)
(...)"
In server 1, I have the COM+ application o package correctly installed and working since years
In Clients computers various front end consume the server classes via, for example in VB
CreateObject('AppMastervs.cOrga', mtsServer1)
or in VFP
CreateObjectEx('AppMastervs.cOrga', mtsServer1)
These clients has registered the corresponding .vbr & tlb via CliReg32
I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4
EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5
Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.
So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.
Thanks in advance;
Claudio Facundo Lacivita
Subscribe to:
Posts (Atom)