Does SQL have a function that return "null" for records which dont exist in a FK realation

Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.

Thank you very much.

when doing select with 'join's over two tables it would return null.

For example, you have customers and customerdetails table

if you do

select *

from customers

left join customerdetails on customers.customerId = customerdetails.customerId

if there is no customer details for a customer the fields corresponding to the customer details table would be null.

Check the different types of joins 'left join', 'inner join', 'outer join', 'cross join'...i think that's all :)

Cheers,

Yani

|||

Thank you very much indeedYani Dzhurov.

Does scope_identity or @@identity always return NULL on Subscriber

I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||No I'm using SQL Server 2000...i may have to move to 2005 sooner than later.
Its hard to believe this can't work because let's say you have an order with
order details tables. You would first create the order on the order table
and get a new Order ID identity, then have to use that identity for the
parent child relationship to the order detail row. This type of scenario
must've been implemented in sql server 2000 with transactional replication
and immediate updates right? How do you retrieve the new ident from the
subscriber without doing maybe a Select max(ident_column) after the insert?
"Paul Ibison" wrote:
> Ben,
> interesting. These identity values are controlled by the publisher rather
> than the subscriber. In fact, you should find that on the subscriber there
> isn't an identity property on the tables for this reason. Are you're using
> SQL Server 2005? If so, you could capture the assigned value using the
> OUTPUT clause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Ben,
I've just retested this and it depends on your replication setup. If you
have the identity column set to 'Yes (Not for Replication)' then there are
problems, but if it is set to just 'Yes' (the recommended way) then
@.@.identity should return the correct value and scope_identity() will not
return anything. Perhaps this is the source of your issue? Alternatively I
was wondering if you have any user triggers in action?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I have no triggers on these tables other than the ones created by replication.
Paul, I'm assuming you're asking me if the Publisher has the identity column
set to 'Yes (Not for Replication)'. If so, then no its set to simply "Yes".
In your test environment, was the subscriber on the same server? I tested
this with the subscriber on the same server and the @.@.identity did return the
correct value, but when the subscriber was on another sql server it still
returned null.
so to clarify, my publisher has a identity column with "Yes", and my
subscriber has this column replicated as an int (i.e. no ident column)...the
subscriber column shouldn't have an ident right?
"Paul Ibison" wrote:
> Ben,
> I've just retested this and it depends on your replication setup. If you
> have the identity column set to 'Yes (Not for Replication)' then there are
> problems, but if it is set to just 'Yes' (the recommended way) then
> @.@.identity should return the correct value and scope_identity() will not
> return anything. Perhaps this is the source of your issue? Alternatively I
> was wondering if you have any user triggers in action?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>|||Hi Ben,
Yes - I agree with everything you're saying :)
I'm currently testing your scenario on my home PC (2 databases on same
instance) which catches the @.@.identity (without the NFR attribute). I will
set it up accross servers when I get to work tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thank you Paul!!! I've been racking my brain around this for almost 2 days.
My intern worse case scenario is to move to 2005 and use the OUTPUT clause
you mentioned or replacing the "Scope_Identity()" code to a Select
Max(Ident_Column) in the insert sp ...which to me feels more like a hack
thanks again!
"Paul Ibison" wrote:
> Hi Ben,
> Yes - I agree with everything you're saying :)
> I'm currently testing your scenario on my home PC (2 databases on same
> instance) which catches the @.@.identity (without the NFR attribute). I will
> set it up accross servers when I get to work tomorrow.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>|||Ben,
Unfortunately we only have 1 instance of SQL Server 2000 on the network and
when I finally got this set up on 2 networked instances of SQL Server 2005
it worked fine - I could pick up both the @.@.identity and the
scope_identity() values. However I noticed that this is implemented
differently and the identity property is on the subscriber in the new
version so this is not really a decent test. I'll try to install SQL Server
2000 on another box later on and test it this way, but I recommend getting a
support engineer (PSS) to test this for you as I won't have the time to set
all this up quickly.
Regards,
Paul Ibison|||thanks for the help Paul. I will do a quick test on 2005 and see if it
works. I'll keep you posted.
"Paul Ibison" wrote:
> Ben,
> Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> when I finally got this set up on 2 networked instances of SQL Server 2005
> it worked fine - I could pick up both the @.@.identity and the
> scope_identity() values. However I noticed that this is implemented
> differently and the identity property is on the subscriber in the new
> version so this is not really a decent test. I'll try to install SQL Server
> 2000 on another box later on and test it this way, but I recommend getting a
> support engineer (PSS) to test this for you as I won't have the time to set
> all this up quickly.
> Regards,
> Paul Ibison
>
>|||Paul, here's my update.
I tried sql server 2005 and Yes Scope_Identity() and @.@.identity worked as
you mentioned but it seems to work differently in 2005.
In 2000 replicated identity columns would be replicated to their base type,
in my case an int. In 2005 it seems to replicate it as an identity column,
which I guess is the reason why the Scope_Identity and @.@.identity work. But
in 2005 it seems like it defaults to Automatic Range Management, but in 2000
I was able to get the identities updating sequentially with no Automatic
Range Management required. Is this feature gone in 2005? I would prefer to
have the immediate updating subscribers get the next identity from the
publisher so that the subscriber and publisher use the same identity
"manager" to get the next identity value (i.e. no identity ranges and no
gaps).
I'm going to do some more testing and keep you up to date.
"Ben Lam" wrote:
> thanks for the help Paul. I will do a quick test on 2005 and see if it
> works. I'll keep you posted.
> "Paul Ibison" wrote:
> > Ben,
> > Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> > when I finally got this set up on 2 networked instances of SQL Server 2005
> > it worked fine - I could pick up both the @.@.identity and the
> > scope_identity() values. However I noticed that this is implemented
> > differently and the identity property is on the subscriber in the new
> > version so this is not really a decent test. I'll try to install SQL Server
> > 2000 on another box later on and test it this way, but I recommend getting a
> > support engineer (PSS) to test this for you as I won't have the time to set
> > all this up quickly.
> > Regards,
> > Paul Ibison
> >
> >
> >

Does scope_identity or @@identity always return NULL on Subscriber

I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does RS allow querying between tables?

I have a very complex query that needs to return monthly and year-to-date
data. I'm using one table (table A) to correctly return the monthly data. I
would now like to create another table and use a result from table A to
correctly filter and display the year-to-date data along side the monthly
data.
Does RS allow querying between tables?
Example of desired results
Account July 2004 Year-to-Date
Ace Hardware $3000.00 $8452.21
--
DCountYou can't really use table A to display results into a different table. But
what you may want to try is to use the same dataset used in table A to create
a new table B and then on table B goto the properties of the table and then
goto the filters entry there and apply the filters you want to the table.
That should get you the results you're looking for.
> I have a very complex query that needs to return monthly and year-to-date
> data. I'm using one table (table A) to correctly return the monthly data. I
> would now like to create another table and use a result from table A to
> correctly filter and display the year-to-date data along side the monthly
> data.
> Does RS allow querying between tables?
> Example of desired results
> Account July 2004 Year-to-Date
> Ace Hardware $3000.00 $8452.21
>
> --
> DCount

Does RETURN have reserved values?

Thank you for your reply.
A long while ago I did read some article that mentioned that RETURN has a
reserved range of -1 to -99.
Is that true? Is it true still for SQL 2005? Does it have any "reserved"
values
FarmerRETURN has to be an INT.
"Farmer" <someone@.somewhere.com> wrote in message
news:%23y2BhJpUGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Thank you for your reply.
> A long while ago I did read some article that mentioned that RETURN has a
> reserved range of -1 to -99.
> Is that true? Is it true still for SQL 2005? Does it have any "reserved"
> values
> Farmer
>|||Are you saying that this is all the limitation as -99 is int.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eLQaxOpUGHA.5332@.tk2msftngp13.phx.gbl...
> RETURN has to be an INT.
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23y2BhJpUGHA.3192@.TK2MSFTNGP09.phx.gbl...
>|||Where did you get -99 from? Where are you getting your information?
INT supports -2,147,483,648 -> 2,147,483,647. There is nothing in there
that stops RETURN values from being outside of the range -99 to -1.
"Farmer" <someone@.somewhere.com> wrote in message
news:Oebf8QpUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Are you saying that this is all the limitation as -99 is int.|||SQL Server used to reserve -99 to 0 for some return code of its own. This ha
sn't been the case since
at least 7.0, but perhaps you read this in some 6.5 book/doc, or earlier.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Farmer" <someone@.somewhere.com> wrote in message news:%23y2BhJpUGHA.3192@.TK2MSFTNGP09.phx.
gbl...
> Thank you for your reply.
> A long while ago I did read some article that mentioned that RETURN has a
reserved range of -1
> to -99.
> Is that true? Is it true still for SQL 2005? Does it have any "reserved" v
alues
> Farmer
>|||Aaron Bertrand [SQL Server MVP] skrev:
> Where did you get -99 from? Where are you getting your information?
> INT supports -2,147,483,648 -> 2,147,483,647. There is nothing in there
> that stops RETURN values from being outside of the range -99 to -1.
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:Oebf8QpUGHA.4884@.TK2MSFTNGP10.phx.gbl...
The question is rather if -1 through -99 is used (not really reserved,
I guess) for system return values, so it's 'better' to use -100 and
down for your own values. Googling quickly catches several posts about
it, but I didn't see any official stuff, and I have no idea if it's
something that still is valid. Someone else that has any insight?
/impslayer, aka Birger Johansson|||thank you very much. I thougt the same thing that it is no longe the case
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ObSq61vUGHA.328@.TK2MSFTNGP11.phx.gbl...
> SQL Server used to reserve -99 to 0 for some return code of its own. This
> hasn't been the case since at least 7.0, but perhaps you read this in some
> 6.5 book/doc, or earlier.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23y2BhJpUGHA.3192@.TK2MSFTNGP09.phx.gbl...
>

DocumentID not returned in Adventureworks

The Large Binary Object example installed with SQL Server 2005 has a table with the DocumentID as the key. When the row is inserted it should return the DocumentID that was created. When you debug the example stored procedure you can see the documentID is created and correct. When you run the sample unmodified it always returns -1 as the documentID. It appears the stored procedure is catching an exception and assigning the documentID a value of -1 as seen here in the Adventureworks LOB sample sored procedure (below).

However, it appears that something with the datbase engine or proce is broken and Microsoft knew this becasue the assignment to documentID upon return from the stored proc is commented out (see below). In fact the whole sample is faked because when you follow it you realize that it is reading into the database one document and writing out one that already existed in the database..

Does anyone know how to get the DocumentID back from the stored procedure that inserted the binary object into the Documents table of Adventureworks?

Thanks in advance.

Portion of Adventure works usp_InsertDocument

RAISERROR ('Insert failed.', 16, 1);

END TRY

BEGIN CATCH

SET @.DocumentID = -1;

RETURN(1);

END CATCH;

END -- END of usp_InsertDocument

;

Portion of LargeBinaryObject.cs

using (SqlConnection sqlConn = new SqlConnection(connectionString))

{

using (SqlCommand sprocCommand = new SqlCommand("[Production].[usp_InsertDocument]", sqlConn))

{

sqlConn.Open();

sprocCommand.CommandType = CommandType.StoredProcedure;

// Add time to the title because there is an unique constraint on this column.

sprocCommand.Parameters.Add(new SqlParameter("@.Title", SqlDbType.NVarChar, 50));

sprocCommand.Parameters[0].Value = fileName + DateTime.Now.TimeOfDay.ToString();

sprocCommand.Parameters.Add(new SqlParameter("@.FileName", SqlDbType.NVarChar, 400));

sprocCommand.Parameters[1].Value = fullFileName;

sprocCommand.Parameters.Add(new SqlParameter("@.FileExtension", SqlDbType.NVarChar, 8));

sprocCommand.Parameters[2].Value = fileExtension;

sprocCommand.Parameters.Add(new SqlParameter("@.Status", SqlDbType.TinyInt));

sprocCommand.Parameters[3].Value = 1;

sprocCommand.Parameters.Add(new SqlParameter("@.Document", SqlDbType.Image));

sprocCommand.Parameters[4].Value = bytes;

sprocCommand.Parameters.Add(new SqlParameter("@.DocumentID", SqlDbType.Int));

sprocCommand.Parameters[5].Direction = ParameterDirection.Output;

sprocCommand.ExecuteNonQuery();

//int DocumentID = (int)sprocCommand.Parameters[5].Value;

}

This here is a pretty easy one:

SqlCommand cmd = new SqlCommand("usp_YourProc");

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = conn;

SqlParameter param_InputoutputTest = new SqlParameter("@.InputoutputTest", SqlDbType.Int);

param_InputoutputTest.Value = "1";

param_InputoutputTest.Direction = ParameterDirection.InputOutput;

SqlParameter param_ReturntTest = new SqlParameter("@.ReturntTest", SqlDbType.Int);

param_ReturntTest.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(param_InputoutputTest);

cmd.Parameters.Add(param_ReturntTest);

conn.Open();

cmd.ExecuteNonQuery();

Console.WriteLine(cmd.Parameters["@.ReturntTest"].Value.ToString());

Console.WriteLine(cmd.Parameters["@.InputoutputTest"].Value.ToString());

conn.Close();

ALTER PROCEDURE usp_YourProc

(

@.InputoutputTest INT OUTPUT

)

AS

SELECT @.InputoutputTest = -1

RETURN 2

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

documentation on SQL OLE methods...Where to find?

I've seen a few examples in BOL and various websites where sp_OAMethod is
used to generates scripts, return rows, etc. For example:
SET @.exec_str = 'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
I can't seem to find any documentation that explains the possible methods
(like databases().Tables().Script).
Can someone point me to where I could find a list of these methods?
Thanks in advancesp_OACreate invokes an extermal program that has a COM OO interface.
The documentation you're looking for will reside with whichever external COM
application you're referring to.
You haevn't given us the name of the particular application you're looking
for in your post, so it's a bit hard to answer this. Your post only gives us
information on a method - perhaps if you go back to whever you got that code
snippet from & give us the part that has "sp_OACreate" in it, we may be able
to help you further.
Regards,
Greg Linwood
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I've seen a few examples in BOL and various websites where sp_OAMethod is
> used to generates scripts, return rows, etc. For example:
> SET @.exec_str =>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> '+ @.filename +'")'
> EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> I can't seem to find any documentation that explains the possible methods
> (like databases().Tables().Script).
> Can someone point me to where I could find a list of these methods?
> Thanks in advance
>|||Hi Greg,
Thanks for the reply. Here's the sp_OACreate statement:
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
Thanks,
Tom
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:uIDLvwrsDHA.2416@.TK2MSFTNGP10.phx.gbl...
> sp_OACreate invokes an extermal program that has a COM OO interface.
> The documentation you're looking for will reside with whichever external
COM
> application you're referring to.
> You haevn't given us the name of the particular application you're looking
> for in your post, so it's a bit hard to answer this. Your post only gives
us
> information on a method - perhaps if you go back to whever you got that
code
> snippet from & give us the part that has "sp_OACreate" in it, we may be
able
> to help you further.
> Regards,
> Greg Linwood
> SQL Server MVP
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I've seen a few examples in BOL and various websites where sp_OAMethod
is
> > used to generates scripts, return rows, etc. For example:
> > SET @.exec_str => >
>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> > '+ @.filename +'")'
> > EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> >
> > I can't seem to find any documentation that explains the possible
methods
> > (like databases().Tables().Script).
> >
> > Can someone point me to where I could find a list of these methods?
> >
> > Thanks in advance
> >
> >
>|||Most COM (or Automation) objects come with their own
documentation. For instance, the object hierarchy of SQL-
DMO can be found in SQL Server Books Online. If you need
to interact with an object hierarchy that doesn't seem to
have online/printed documentation, you can try tools such
as OLE-COM Object Viewer that comes with NT Resource kit.
The object viewer allows you browse the object hierarchy
and see all the objects, methods, and proerties.
By the way, if you can stay away from sp_OAxxx stuff, stay
away from it. It's pretty ugly and you can't import the
symbolic constants.
Linchi
>--Original Message--
>I've seen a few examples in BOL and various websites
where sp_OAMethod is
>used to generates scripts, return rows, etc. For example:
>SET @.exec_str =>'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))
+'").Script(74077,"
>'+ @.filename +'")'
>EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
>I can't seem to find any documentation that explains the
possible methods
>(like databases().Tables().Script).
>Can someone point me to where I could find a list of
these methods?
>Thanks in advance
>
>.
>