Can We assign DataSet object to Object Type

Hi Friends,

I am having a Package scope variable DS of type system.object .In the Script Component which accept this variable as ReadWrite type I am Making the following assignment.

Public Overrides Sub PostExecute()

Variables.DsReport = DsReport // DsReport is a Data Set Object Created and Populated inside the script Component

End Sub

I am getting error while doing this sort of assignment.

Please help me to solve this problem

Regards,

Mahe

Which error do you get?

-Tom

can sql maintenance plan be script out?

as title. any tips would be appreciated. thank you
You can script the scheduled job it creates but not the entire maintenance
plan.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edVFcoXLEHA.556@.tk2msftngp13.phx.gbl...
> as title. any tips would be appreciated. thank you
>

can sql maintenance plan be script out?

as title. any tips would be appreciated. thank youYou can script the scheduled job it creates but not the entire maintenance
plan.
--
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edVFcoXLEHA.556@.tk2msftngp13.phx.gbl...
> as title. any tips would be appreciated. thank you
>

can sql maintenance plan be script out?

as title. any tips would be appreciated. thank youYou can script the scheduled job it creates but not the entire maintenance
plan.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edVFcoXLEHA.556@.tk2msftngp13.phx.gbl...
> as title. any tips would be appreciated. thank you
>

Can someone proofread my remove duplicates script?

DELETE
FROM tblContacts
WHERE tblContacts.ID IN(
SELECT F.ID
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
)
AND tblContacts.ID NOT IN(
SELECT Min(ID)
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
GROUP BY email
)

I readily admit that I've shamelessly copied 'n pasted this from a tutorial and then taken a stab at tweaking it for my own ends. But I really don't understand what it's doing.

Really, all I want to know is that it will remove records with duplicate email fields. But I could also do with confirming - looking at the "SELECT Min(ID)" bit - does that mean that if it finds a duplicate, it'll delete the latest-added one? And if so, that changing it to remove the earliest-added one is simply a case of changing MIN to MAX?

Thanks :)A good tip for keeping your sanity is to always run scripts like this on a testing version of your database and then confirm that it has worked before even contemplating running it on prod. As such - the below is based on my best reading of the script.

Yes it will work. Yes it will delete the most recently inserted record(s) (assuming that the ID field is a monotonically increasing value such as an identity and a higher number always indicates a more recently inserted record). And yes - you can change MIN to MAX to retain the most recently inserted record.

Have a read through the script a few times though - even if you don't consider it necesary it would be nice to know what it is doing and why.

HTH|||Yeah, a test run would be advisable. Good point. I've tried reading my way through it and I just get bogged down in "so we get one list that's... and those exist in... and that doesn't exist... and..." and the will to live rapidly leaves me. I think I get it now, though. I guess I just needed someone to tell me it did do what I thought before I tried to figure out exactly how.

Thanks for the help.|||While it lookes to me like the code you posted should work, I'd suggest a simpler approach. It is a lot easier to read (at least for me anyway), and probably easier to understand.

DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))-PatP|||Ooh, that's easier :D Nice one :beer:|||Oh yeah... One thing I ought to mention before you go trundling off, this code snippet will trip over any rows that have an ID column that is NULL. This may make you need to add an ID IS NOT NULL to the outer clause if there is any chance of encountering a legitimate NULL value in the ID column. This is unlikely, but some schemas will permit it, and the results can be catastrophic!

-PatP|||A handy trick I have used in such cases is rewrite the statement as a SELECT, rather than an update or delete. See what records you are about to modify/maim, and if you have no objections, then you can run the actual data modification.|||DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))
Actually - reading this more carefully, I'm a bit confused. It looks like it'll only do one at a time? Is that right? If so, that's not a bad thing - in fact, it'd be good to know that I've managed to guess what a statement will do before I run it :rolleyes: :D

edit:

Well... having read MCrowley's excellent advice: clearly it doesn't. It gives me a big list of duplicates. But I don't understand how? It selects MAX - which is only going to return one record, right? And then it selects (or deletes) WHERE ID <> - not "is not in [a range]", but "does not equal [a value]".

I'm confused again :(|||The key is in the corrolation ;)

DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))
If you remove the bit in bold then yes - you would get one ID returned. However the bit in bold corrolates the inner and outer query so there is one MAX(ID) returned per email address.

It can be rewritten as a select query as a join of two tables that might make it more obvious:


SELECT tblContacts.*
FROM tblContacts INNER JOIN
(SELECT Max(z.ID) AS TheMaxID,
email
FROM tblContacts
GROUP BY email) AS z ON
z.email = tblContacts.email
WHERE tblContacts.ID <> TheMaxID

HTH|||The more I think about this, the harder it gets :D I swear there's a SQL gene.

Anyway - thanks for the help and patience, everyone. I think I need to just go and play with these statements and get my head round them.|||Just practice - SQL is very easy to learn but rather tricky to master.

Run the inner query on its own:
SELECT Max(z.ID) AS TheMaxID,
email
FROM tblContacts
GROUP BY emailThat might illuminate...|||When you have a subquery (one query that is logically "nested inside" of another query), the subquery gets re-evaluated for each row returned by the outer query. If the DELETE query materializes a million rows from the tblContacts table, then the SELECT query would be evaluated a million times, once for each of the rows materialized by the DELETE query.

For each row in tblContacts, that DELETE checks to see if that row has the Max(ID) value for a given email address. If the row does not have the Max(ID) for that email, then the row is deleted.

While this will sometimes confuse people, it does not confuse SQL! ;) The only point that can confuse SQL is NULL values. A NULL email is simply ignored, considered junque and deleted (the explanation of that gets a bit tricky, just take it on faith for now). A NULL ID is also deleted, for a different (but similar) reason.

Once you understand the way this works for non-NULL values, we'll worry about the NULL values. They are almost assuredly garbage anyway, so don't burn much time on them yet.

-PatP

Can someone help me in understanding the output of this TSQL script

declare @.startdate datetime

declare @.enddate datetime

declare @.testvalue datetime

set @.startdate = '2005-12-31'

set @.enddate = '2006-12-29'

set @.testvalue ='2006-05-17'

if convert(varchar(20), @.testvalue, 102) between convert(varchar(20), @.startdate, 102) and convert(varchar(20), @.enddate, 102)

print 'yes'

else

print 'no'

-

The above script print yes with format specifier as 102 where as it prints no with format specifier as 102. Why/how does the format specifier affect the output?

I agree that there are better was of achieving what is done in the above script. But I am curious to know the why sql server behaves this way in the above query.

Thanks

Take a look at the output of the following SQL:

declare @.startdate datetime
declare @.enddate datetime
declare @.testvalue datetime
set @.startdate = '2005-12-31'
set @.enddate = '2006-12-29'
set @.testvalue ='2006-05-17'

SELECT Mode = 'No Format', TestVal = convert(varchar(20), @.testvalue),
StartVal = convert(varchar(20), @.startdate),
EndVal = convert(varchar(20), @.enddate)
UNION ALL
SELECT 'Format 102', convert(varchar(20), @.testvalue, 102),
convert(varchar(20), @.startdate, 102),
convert(varchar(20), @.enddate, 102)

This gives the following results:

No Format | May 17 2006 12:00AM | Dec 31 2005 12:00AM |Dec 29 2006 12:00AM
Format 102 | 2006.05.17 | 2005.12.31 | 2006.12.29

As you can see the no format provides date text strings which do not sort alphabetically in date order at all. Format 102 produces a text string which does sort in date order when sorted alphabetically so can be used in the comparison. Of course the simple way to do it is to perform the comparison directly on the date data.

|||

I agree with your explanation for format specifier 102. However, This means that my script in the first thread should work with the format specifier as 101. But it doesnt. Can you explain this behaviour please?

|||

If you are comparing date values, you 'should NOT' be converting to varchar!

It is wasted effort and slows your process down.

|||

That's the reason i mentioned in my first post that we can do this in a better way. I am of the opinion that we have to use datetime instead of varchar(20).

But what i want to know is why does the change in format specifier (in my query) change the output value.

With 101 format specifier the expression evaluates to false where as with 102 it evaluates to true. Need to know/understand this behaviour of SQL server wrt the format specifier change.

|||

For exactly the same reason - again this is a text-based comparison.

Here are the strings you are comparing, ordered alphanumerically ascending:

--102
2005.12.31 - @.startdate
2006.05.17 - @.testvalue
2006.12.29 - @.enddate

--101
05/17/2006 - @.testvalue
12/29/2006 - @.enddate
12/31/2005 - @.startdate

It's clear to see that in the second example, @.testvalue < @.enddate. In fact using format 101 makes your BETWEEN condition impossible to meet.

Chris

|||Thanks Chris!

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor Raytsin
Igor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
-- SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> > The following script is reproducing the problem assuming you have
> > Northwind database on the server.
> > Please note it gives you the error message on line 12.
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> > Erlan, I think it has nothing to do with a linked servers it does a
> > creation on the local server and not a linked one. Or if I did not
> > understand you , can you please elaborate the explanation?
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Can someone explain this behaviour?

Hello All,

The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.

USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON

IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders

SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON

IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders

--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO

PRINT 'This_works'
EXECUTE This_works 0

PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0

Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.

I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Igor Raytsin (igorray@.sympatico.ca) writes:
>>The following script is reproducing the problem assuming you have
>>Northwind database on the server.
>>Please note it gives you the error message on line 12.
>
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)

Thank you for your reply Erland,

In other words - those procedures are never got compiled without a error (or warning),
but SQL Server ignores them if it is not in a transaction. Right?

Thanks,
Igor|||Igor
In addition
Inside the transaction spesify name of the database and it will work

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT

Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example

(Deferred name resolution does not apply to linked
> tables.)

Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> > The following script is reproducing the problem assuming you have
> > Northwind database on the server.
> > Please note it gives you the error message on line 12.
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?

It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor Raytsin (igorray@.sympatico.ca) writes:
> In other words - those procedures are never got compiled without a error
> (or warning), but SQL Server ignores them if it is not in a transaction.
> Right?

This particular error only occurs when you are in a transaction. I can't
really say why, but apparently the fact that the single-machine transaction
suddenly makes a linked reference is not handled well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> > Erlan, I think it has nothing to do with a linked servers it does a
> > creation on the local server and not a linked one. Or if I did not
> > understand you , can you please elaborate the explanation?
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Uri Dimant wrote:
Uri,

I tried to add the database name -

SELECT COUNT(*) FROM tempdb.dbo.Orders_TMP

But it did not work on my server :(

Igor

> Igor
> In addition
> Inside the transaction spesify name of the database and it will work
> BEGIN TRANSACTION
> Select 'Line 25'
> SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
> COMMIT
> Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
> is was remarted in the second example
> (Deferred name resolution does not apply to linked
>>tables.)
>
> Erlan, I think it has nothing to do with a linked servers it does a creation
> on the local server and not a linked one.
> Or if I did not understand you , can you please elaborate the explanation?
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96295474102Yazorman@.127.0.0.1...
>>Igor Raytsin (igorray@.sympatico.ca) writes:
>>
>>>The following script is reproducing the problem assuming you have
>>>Northwind database on the server.
>>>Please note it gives you the error message on line 12.
>>
>>I think I understand what's going on. Since you drop and recreate the
>>table, the next reference to the table after its recreation will cause
>>a recompilation of the procedure. If that SELECT is in a transaction, you
>>have a problem, because SQL Server then wants to talk with the linked
> server
>>to verify the table. (Deferred name resolution does not apply to linked
>>tables.)
>>
>>
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>>Books Online for SQL Server SP3 at
>>http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?

I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedur
e') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
-- SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can one input script file invoke another script file?

Say I have 2 files:
do_me_first.sql
do_me_second.sql
And I issue:
> sqlcmd -S localhost -i do_me_first.sql
What syntax would put in do_me_first.sql so that it would execute
do_me_second.sql without any further sqlcmd invocations?
In Oracle with Sql*Plus, I would use an "at sign" like this in
do_me_first.sql :
@.do_me_second.sql
I'm still getting used to Microsoft documentation, and I cannot for
the life of me find a reference to such syntax.Hi,
You could try xp_cmdshell.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189639150.068229.71660@.r29g2000hsg.googlegroups.com...
> Say I have 2 files:
> do_me_first.sql
> do_me_second.sql
> And I issue:
>> sqlcmd -S localhost -i do_me_first.sql
> What syntax would put in do_me_first.sql so that it would execute
> do_me_second.sql without any further sqlcmd invocations?
> In Oracle with Sql*Plus, I would use an "at sign" like this in
> do_me_first.sql :
> @.do_me_second.sql
> I'm still getting used to Microsoft documentation, and I cannot for
> the life of me find a reference to such syntax.
>|||Hi,
You could also use a batch/command file to call sqlcmd twice. (If you're a
UNIX person, batch/command files are like shell scripts).
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189639150.068229.71660@.r29g2000hsg.googlegroups.com...
> Say I have 2 files:
> do_me_first.sql
> do_me_second.sql
> And I issue:
>> sqlcmd -S localhost -i do_me_first.sql
> What syntax would put in do_me_first.sql so that it would execute
> do_me_second.sql without any further sqlcmd invocations?
> In Oracle with Sql*Plus, I would use an "at sign" like this in
> do_me_first.sql :
> @.do_me_second.sql
> I'm still getting used to Microsoft documentation, and I cannot for
> the life of me find a reference to such syntax.
>|||On Sep 12, 7:07 pm, "Daniel Jameson" <danja...@.newsgroup.nospam>
wrote:
> Hi,
> You could also use a batch/command file to call sqlcmd twice. (If you're a
> UNIX person, batch/command files are like shell scripts).
Thanks Daniel, I'm well aware of using multiple sqlcmd statements
wrapped in a batch file. However, I'm looking very specifically to
implement a simple approach where the person executing the deployment
script has an ultra-simple single invocation of a single sqlcmd
against a single infile sql script and I get the convenience and best
practice of dividing and conquering with my scripting files rather
than having one giant sql script file.|||> Say I have 2 files:
> do_me_first.sql
> do_me_second.sql
> And I issue:
>> sqlcmd -S localhost -i do_me_first.sql
> What syntax would put in do_me_first.sql so that it would execute
> do_me_second.sql without any further sqlcmd invocations?
Try:
:r do_me_first.sql
:r do_me_second.sql
You'll need to include a GO batch terminator after the :r file include if
you want to run the script immediately, unless the script is already
terminated with a GO. See the SQLCMD commands topic in the Books Online for
more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189639150.068229.71660@.r29g2000hsg.googlegroups.com...
> Say I have 2 files:
> do_me_first.sql
> do_me_second.sql
> And I issue:
>> sqlcmd -S localhost -i do_me_first.sql
> What syntax would put in do_me_first.sql so that it would execute
> do_me_second.sql without any further sqlcmd invocations?
> In Oracle with Sql*Plus, I would use an "at sign" like this in
> do_me_first.sql :
> @.do_me_second.sql
> I'm still getting used to Microsoft documentation, and I cannot for
> the life of me find a reference to such syntax.
>|||On Sep 12, 9:50 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > Say I have 2 files:
> > do_me_first.sql
> > do_me_second.sql
> > And I issue:
> >> sqlcmd -S localhost -i do_me_first.sql
> > What syntax would put in do_me_first.sql so that it would execute
> > do_me_second.sql without any further sqlcmd invocations?
> Try:
> :r do_me_first.sql
> :r do_me_second.sql
> You'll need to include a GO batch terminator after the :r file include if
> you want to run the script immediately, unless the script is already
> terminated with a GO. See the SQLCMD commands topic in the Books Online for
> more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cleveridea" <cleveridea...@.gmail.com> wrote in message
> news:1189639150.068229.71660@.r29g2000hsg.googlegroups.com...
> > Say I have 2 files:
> > do_me_first.sql
> > do_me_second.sql
> > And I issue:
> >> sqlcmd -S localhost -i do_me_first.sql
> > What syntax would put in do_me_first.sql so that it would execute
> > do_me_second.sql without any further sqlcmd invocations?
> > In Oracle with Sql*Plus, I would use an "at sign" like this in
> > do_me_first.sql :
> > @.do_me_second.sql
> > I'm still getting used to Microsoft documentation, and I cannot for
> > the life of me find a reference to such syntax.
This is PERFECT. Thank you so much. After 10 years with Oracle and 5
each with MySQL and PostgreSQL it is remarkable how feeble I feel
doing the simplest thing with scripting particulars of SQL Server.
I've got the Books Online link in my favorites for future problems.

Can not run script.

Hi,
I have to scripts, one is use to create table and
another one is use to create user define function. I want
to check why i can run the script to create table but i
can not run the script to create user define function. Is
it got any restriction in security? What i can check? Is
it security problem?
Any suggestion of where i can check?
Any help is much appreciated.
regards,
florenceHi,
To execute it directly m you have to be in any one of the below database
roles,
1. db_owner
2. db_ddladmin
SA can also give the user exclusive "Create FUNCTION" prev.
Grant Create Function to username
Thanks
Hari
MCDBA
"florence" <florencelee@.visualsolutions.com.my> wrote in message
news:906b01c3ea41$7cd6eda0$a001280a@.phx.gbl...

quote:

---

> Hi,
> I have to scripts, one is use to create table and
> another one is use to create user define function. I want
> to check why i can run the script to create table but i
> can not run the script to create user define function. Is
> it got any restriction in security? What i can check? Is
> it security problem?
> Any suggestion of where i can check?
> Any help is much appreciated.
> regards,
> florence

---