Computer name

Hello,
I need to know the computer name in an SQL Server 2005 User Defined
Functions. Inside the SQL Server I can't use the
System.Windows.Forms.SystemInformation.Computer, is there another function ?
AlanWhat about
SELECT host_name() ?
HTH, jens Suessmeyer.|||The name of the database server, or the name of the server that is calling
the function (e.g. from an app), or the end client?
"Alan" <Alan@.discussions.microsoft.com> wrote in message
news:587F8A8A-A788-4BF8-9DAA-11E3776C12FC@.microsoft.com...
> Hello,
> I need to know the computer name in an SQL Server 2005 User Defined
> Functions. Inside the SQL Server I can't use the
> System.Windows.Forms.SystemInformation.Computer, is there another function
> ?
> Alan|||thanks
"Jens" wrote:

> What about
> SELECT host_name() ?
> HTH, jens Suessmeyer.
>

Compress text column on SQL2000

I am trying to compress text/image column on a table on MSSQL200 Enterprise Ed.
When will compress when write to the table and uncompress when user retrieve
the data. Anyone can please suggest me how to or any tool to do this.
You need to do the compression and uncompression in the client app or middle
tier and not the database server.
Andrew J. Kelly SQL MVP
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
>I am trying to compress text/image column on a table on MSSQL200 Enterprise
>Ed.
> When will compress when write to the table and uncompress when user
> retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||To add to Andrew's response, I recommend that you test very heavily before
implementing this in a production environment; I've done fairly extensive
testing of various on-the-fly .NET compression libraries for the purpose of
compressing LOB data on the way in and out of the database, and found that
instead of improving performance as I expected (due to lowering disk IOs and
network bandwidth required to retrieve the data), there was instead a
moderate degredation due to the extra processor strain on the middle tier.
If you do find a way to improve performance using compression, I would be
very interested in hearing about your techniques and results -- of course,
if you're doing this only for disk space savings, you can disregard my
rantings...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> I am trying to compress text/image column on a table on MSSQL200
Enterprise Ed.
> When will compress when write to the table and uncompress when user
retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||Andrew J. Kelly wrote:
> You need to do the compression and uncompression in the client app or
> middle tier and not the database server.
>
The OP could place the TEXTIMAGE on a filegroup that is located on a
compressed folder on the server. That's supported by SQL Server if I'm
not mistaken and won't require any additional libraries to manage.
Andrew, any thoughts?
David Gugick
Imceda Software
www.imceda.com
|||Actually I know using compressed volumes is not recommended and I think it
may even be unsupported for Sql Server.
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23S5%23N4uGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
> The OP could place the TEXTIMAGE on a filegroup that is located on a
> compressed folder on the server. That's supported by SQL Server if I'm not
> mistaken and won't require any additional libraries to manage.
> Andrew, any thoughts?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Andrew J. Kelly wrote:
> Actually I know using compressed volumes is not recommended and I
> think it may even be unsupported for Sql Server.
>
You are correct:
http://support.microsoft.com/kb/231347/EN-US/
David Gugick
Imceda Software
www.imceda.com
|||What about upgrade to use Yukon, use CLR written in c# to write unpresss text
and store on sqlserver? Anyone has done this sort of thing?
"Adam Machanic" wrote:

> To add to Andrew's response, I recommend that you test very heavily before
> implementing this in a production environment; I've done fairly extensive
> testing of various on-the-fly .NET compression libraries for the purpose of
> compressing LOB data on the way in and out of the database, and found that
> instead of improving performance as I expected (due to lowering disk IOs and
> network bandwidth required to retrieve the data), there was instead a
> moderate degredation due to the extra processor strain on the middle tier.
> If you do find a way to improve performance using compression, I would be
> very interested in hearing about your techniques and results -- of course,
> if you're doing this only for disk space savings, you can disregard my
> rantings...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
> news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> Enterprise Ed.
> retrieve
>
>
|||"SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> What about upgrade to use Yukon, use CLR written in c# to write unpresss
text
> and store on sqlserver? Anyone has done this sort of thing?
I haven't tried yet -- but I plan to when the next beta is released and
performance is improved a bit. MS hadn't started performance tuning the
last releases much so it wasn't worth testing, IMO.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Please let us know later the.
"Adam Machanic" wrote:

> "SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
> news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> text
> I haven't tried yet -- but I plan to when the next beta is released and
> performance is improved a bit. MS hadn't started performance tuning the
> last releases much so it wasn't worth testing, IMO.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

Complex Table Problem

I know you can't have a table bound to 2 datasets so what I need is a way to
allow the user to retrieve some results for this year, and then side-by-side
with each column, the results for the previous year are also shown. I have
the parameters, etc working OK, I just need to find a way to get the columns
in the table setup.
EXAMPLE DATA FROM TABLE
Year Dept Applications Interviews
---
2003/4 Admin 56 37
2003/4 HR 12 9
2004/5 Admin 15 3
2004/5 HR 5 1
HOW THE REPORT SHOULD LOOK
Applications Interviews
Team 2003/4 2004/5 2003/4 2004/5
---
Admin 56 15 37 3
HR 12 5 9 1
Can anybody offer any advice on how I can achieve this?
Thanks.On Thu, 18 Nov 2004 01:47:01 -0800, Mark Parter
<MarkParter@.discussions.microsoft.com> wrote:
> EXAMPLE DATA FROM TABLE
> Year Dept Applications Interviews
> ---
> 2003/4 Admin 56 37
> 2003/4 HR 12 9
> 2004/5 Admin 15 3
> 2004/5 HR 5 1
> HOW THE REPORT SHOULD LOOK
> Applications Interviews
> Team 2003/4 2004/5 2003/4 2004/5
> ---
> Admin 56 15 37 3
> HR 12 5 9 1
> Can anybody offer any advice on how I can achieve this?
you can't do that in RS, you have to write new select/procedure.
I think you can use INNER JOIN for that.
--
Ing. Branislav Gerzo|||Tis Ok, I've managed to sort it out myself.
"Mark Parter" wrote:
> I know you can't have a table bound to 2 datasets so what I need is a way to
> allow the user to retrieve some results for this year, and then side-by-side
> with each column, the results for the previous year are also shown. I have
> the parameters, etc working OK, I just need to find a way to get the columns
> in the table setup.
> EXAMPLE DATA FROM TABLE
> Year Dept Applications Interviews
> ---
> 2003/4 Admin 56 37
> 2003/4 HR 12 9
> 2004/5 Admin 15 3
> 2004/5 HR 5 1
> HOW THE REPORT SHOULD LOOK
> Applications Interviews
> Team 2003/4 2004/5 2003/4 2004/5
> ---
> Admin 56 15 37 3
> HR 12 5 9 1
> Can anybody offer any advice on how I can achieve this?
> Thanks.
>
>|||That's easy. It is a matrix. column group one is activity(App/Interview)
column group 2 is year
Row group is Department.
"Ing. Branislav Gerzo" wrote:
> On Thu, 18 Nov 2004 01:47:01 -0800, Mark Parter
> <MarkParter@.discussions.microsoft.com> wrote:
> > EXAMPLE DATA FROM TABLE
> >
> > Year Dept Applications Interviews
> > ---
> > 2003/4 Admin 56 37
> > 2003/4 HR 12 9
> > 2004/5 Admin 15 3
> > 2004/5 HR 5 1
> >
> > HOW THE REPORT SHOULD LOOK
> >
> > Applications Interviews
> > Team 2003/4 2004/5 2003/4 2004/5
> > ---
> > Admin 56 15 37 3
> > HR 12 5 9 1
> >
> > Can anybody offer any advice on how I can achieve this?
> you can't do that in RS, you have to write new select/procedure.
> I think you can use INNER JOIN for that.
> --
> Ing. Branislav Gerzo
>

complex SQL select query

Hi all

I im trying to write a SELECT query to display a set of my logged in user's 'Friends'. Although the way that i have designed my tables means that its very complex, and im hoping someone out there can tackle it!

To start ill show you how i contruct friends:

Friends

FriendshipID Incrementing PK
InviteeID Unique UserID of person who offered the friendship link
InvitedID Unique UserID of person who was invites
ApprovedBInvitee True/False - sets to 'True' by default (probably isnt needed come to think of it)
ApprovedByInvited True/False/Declined - an nvarchar

Next, I have my UserDetails table:

UserDetails

UserID Unique UserID PK
UserName Unique Username (foreign key from aspnet_Users as created by aspnet_regsql.exe)
Avatar Integer which represents an image name in a photos folder

So, on the myFriends.aspx i firstly set an invisible label's text property to the unique UserID of the logged in user. This gives me a control paremater for the select statement.

The information I want to display is just the UserName and Avatar of all users who are friends with the logged in user.

I know that to get the records where the logged in user is either that Invited or the Invitee, I do this:

WHERE (@.loggedInUser = Friends.IniteeID)OR (@.loggedInUser = Friends.InvitedID)

(that will show the logged in user as his own friend but i dont mind that)

After that I am stuck more or less... it seems to become very complex... maybe i need 2 queries?

If anyone can help i would be very very grateful

This is actually a very simple query... it may seem a bit complex because you join back twice on the user class... actually, you don't *have* to do that... there are many ways to accomplish this.

SELECT
friendUsers.UserName,
friendUsers.Avatar
FROM
dbo.UserDetails u INNER JOIN dbo.Friends f
ON u.UserID = f.InviteeID OR u.UserID = f.InvitedID
INNER JOIN dbo.UserDetails friendUsers
ON friendUser.UserID = f.InviteeID OR friendUser.UserID = f.InvitedID
WHERE
u.UserID = @.loggedInUser

That should work.

|||

Another way to do it would be this:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = InviteeID OR UserID = InvitedID)

Believe it or not, those are the same query.

|||

Nullable:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = InviteeID OR UserID = InvitedID)

I forgot one more piece to filter down by the current user:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

There :)

|||

Hi Nullable

Thanks for the rsponse, you obviously have more skills with sql than me!

I have tried the corrected second query:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

There is only one friends entry at the moment, one where the logged in userid will be the InvitedID (although that will not always be the case of course)

...that query is returning the Avatar and UserName of that user - the logged in one - rather than those of his friend. We need to stick a WHERE ApprovedByInvited = 'True' too, but i think i can manage that.

Do you know why we are getting the wrong user details?

Thanks again

|||I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.|||

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

I'll look into the query again to see where I crossed wires :)

|||

Heh, my "correction" to my earlier query was done in much haste and not thought out :) ... it was close, but not quite right:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHEREUserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

That is forcing only the current user... which was pretty dumb

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID != @.loggedInUser
AND (UserID = InviteeID OR UserID = InvitedID) AND (@.loggedInUser= InviteeID OR @.loggedInUser= InvitedID))

To read that in English you would say "Give me the UserName and Avatar FROM the UsersDetails Table WHERE the user that I'm looking at is part of the following list: (Give me all Users who are linked in the friend table WHERE either the user is the Invitee OR the user is the Invited AND the loggedInUser is an Invitee OR the loggedInUser is the Invited)"

Got it? Good :) (Please make sure to mark one of these posts as the answer when you're done so that I know this issue has been resolved.)

Peace,

|||

Nullable:

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

- Sorry to correct you like that, I don't mean to seem rude, so here is a quick explaination into why I corrected you.

SELECT

c.*FROM dbo.SysColumns cINNERJOIN dbo.SysObjects oON c.id= o.id
-- Subtree Cost : 0.0317435

SELECT

*FROM dbo.SysColumns cWHERE idIN(SELECT idFROM dbo.SysObjects)
-- Subtree Cost : 0.0317125These two queries will return the EXACT same result set... but the one with the JOIN is actually slightly more expensive (and takes longer) to run... Do you know why? Well, to put it very simply and I will probably be "corrected" on this explaination... but here goes: The RESULTS of the query were only from the SysColumns table... so joining the two (thereby forcing SQL to have to ORDER the SysObjects table by ID to do it's cross streaming) is more expensive than the second query which only needed to get the list of IDs (in any order) from the SysObjects table.

As a punishment for your crime, you must go tohttp://www.SingingEels.com and spread the word!

|||Thank you nullable and French Duke, I must try to reproduce this myself with varying amounts of test data.|||

Hey Timothy

Thanks bro, thats done the job just nicely. Marked you up

|||

Nullable:

Nullable:

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

- Sorry to correct you like that, I don't mean to seem rude, so here is a quick explaination into why I corrected you.

SELECT

c.*FROM dbo.SysColumns cINNERJOIN dbo.SysObjects oON c.id= o.id
-- Subtree Cost : 0.0317435

SELECT

*FROM dbo.SysColumns cWHERE idIN(SELECT idFROM dbo.SysObjects)
-- Subtree Cost : 0.0317125These two queries will return the EXACT same result set... but the one with the JOIN is actually slightly more expensive (and takes longer) to run... Do you know why? Well, to put it very simply and I will probably be "corrected" on this explaination... but here goes: The RESULTS of the query were only from the SysColumns table... so joining the two (thereby forcing SQL to have to ORDER the SysObjects table by ID to do it's cross streaming) is more expensive than the second query which only needed to get the list of IDs (in any order) from the SysObjects table.

As a punishment for your crime, you must go tohttp://www.SingingEels.com and spread the word!

Here's one article that I could find with peformance issues with IN:http://support.microsoft.com/kb/829205|||

Just recollected that the queries work differently if you have duplicate records in the subquery table. If your subquery has more records (like a 1-many relationship) doing a JOIN will return multiple records where as an IN might return only one record.

Complex Recursive Query

I have a complex query that I am strugling with in MS SQL Server 2000. I am trying to get all of the websites a user has control of. My schema is as follows:

A user can control many companies, and a company can be controlled by many users. A company can also control other companies (thus a recursive parent relationship). A company can have many websites, but a website can only belong to one company. Thus the design:

USER TABLE
User_ID
User_Name

COMPANY TABLE
Company_ID
Company_Name
Company_ID_Parent (recursive)

USER_COMPANY TABLE
User_ID
Company_ID

WEBSITE TABLE
Website_ID
Company_ID (Foreign Key)

That said, how do I get a list of websites that a user is associated with... meaning, all of the websites that belong to a company that either the user controls directly, or a child-company of a company that either the user controls directly (at any depth).

Getting the websites is actually easy. I need the recursive part figured out.

Thx in Advance.Do you have a fixed relationship depth (companies owning other companies), or is there no limit? A fixed depth makes a static query simple, which will perform better and is a lot easier to keep portable. A SQL function would allow you to solve the problem allowing infinite depth, but at the expense of additional complexity and loss of portability.

-PatP|||Here is a solution I have used effectively in the same type of situation as you describe:

Create table #CompanyList(Company_ID Int)

Insert into #CompanyList
(Company_ID)
Select Company_ID
from USER_COMPANY
where User_ID = [YourUserID]

While @.@.ROWCOUNT > 0
Insert into #CompanyList
(Company_ID)
Select Distinct
COMPANY.Company_ID
From COMPANY
Inner join #CompanyList on COMPANY.Company_ID_Parent = #CompanyList.Company_ID
Where not exists
(select *
from #CompanyList
where #CompanyList.Company_ID = COMPANY.Company_ID)

Select Website_ID
From WEBSITE
Inner join #CompanyList on WEBSITE.Company_ID = #CompanyList.Company_ID

You can convert this algorithm to a function if you like, or replace the temporary table with a table variable.|||Pat, Do you have a fixed relationship depth (companies owning other companies), or is there no limit? A fixed depth makes a static query simple, which will perform better and is a lot easier to keep portable. A SQL function would allow you to solve the problem allowing infinite depth, but at the expense of additional complexity and loss of portability.

Assuming there is a theoretical limit of 3 or 4, what would the query look like?

Blindman, thanks! I'll take a look at that and see if I can get it to work.|||I think the trick on your problem is to recurse all member companies. then from there, list the sites.

here's a sample code to use. I guess you get the idea. Sorry if i didnt verify the syntax. My SQL server is down at a the moment.

-----------

-- *** Returns all member companies
CREATE FUNCTION f_COMPANY_CHILDREN (@.PARENT_ID)
OUTPUT TABLE
AS
SELECT A.COMPANY_ID
FROM COMPANY_TABLE A, F_COMPANY_CHILDREN(A.COMPANY_ID) B
WHERE A.COMPANY_ID_PARENT = @.PARENT_ID
GO

-- *** List companies user has access
SELECT B.USER_ID, A.WEBSITE_ID
FROM WEBSITE A, USER_COMPANY B
WHERE A.COMPANY_ID = B.COMPANY_ID
AND A.COMPANY_ID IN (SELECT COMPANY_ID FROM F_COMPANY_CHILDREN(B.COMPANY_ID))
UNION
SELECT B.USER_ID, A.WEBSITE_ID
FROM WEBSITE A, USER_COMPANY B
WHERE A.COMPANY_ID = B.COMPANY_ID
GO|||Sorry, I missed your reply. I haven't tested this, but I'd suggest something like:SELECT
FROM user_table AS u
INNER JOIN User_company_table AS uc
ON (uc.User_ID = u.User_ID)
INNER JOIN (SELECT DISTINCT z4.Company_ID, z1.Company_ID AS zz
FROM user_company_table AS z1
INNER JOIN user_company_table AS z2
ON (z1.Company_ID IN (z2.Company_ID, z2.Company_ID_Parent))
INNER JOIN user_company_table AS z3
ON (z2.Company_ID IN (z3.Company_ID, z3.Company_ID_Parent))
INNER JOIN user_company_table AS z4
ON (z3.Company_ID IN (z4.Company_ID, z4.Company_ID_Parent)) ) AS c
ON (c.zz = uc.Company_ID)
INNER JOIN website_table AS w
ON (w.Company_ID = c.Company_ID)-PatP|||WhileTSQL allows true recursive code, I have found that they are much less efficient than the "Accumulator table" algorithm I gave earlier, and I believe that recursion is limited to something like 36 levels. It was this ceiling on an EDI database application that led me to switch to the accumulator method.|||WhileTSQL allows true recursive code, I have found that they are much less efficient than the "Accumulator table" algorithm I gave earlier, and I believe that recursion is limited to something like 36 levels. It was this ceiling on an EDI database application that led me to switch to the accumulator method.While your solution is more general (it can handle unlimited depth), mine is definitely more portable, and I'd think that mine would perform better too. Each has its benefits, so at least in my mind there isn't a clear cut decision on which one to use.

-PatP|||For a fixed-level of depth, yours is definitely preferable. Simpler to read, easier to code, and probably more efficient as well. I just prefer using an Accumulator table over a recursive algorithm (such as manilaguy's example) for N-level datasets.|||Thanks for all the info. I think for now I have a theoretical limit, so I will go with Pat's suggestion for testing. But as I build the product, I may need to transition to blindman's way of doing it. For some reason I am just hesitant to create temp tables while many concurrent requests are being handled.

Thanks again.|||Temp tables should not be an issue, but you could also use table variables, which exist only within the scope of the procedure.|||Pat, thanks for the query. I don't see where this links into the Company_Table though. Am I missing something? Perhaps I do not understand the query.

Sorry, I missed your reply. I haven't tested this, but I'd suggest something like:SELECT
FROM user_table AS u
INNER JOIN User_company_table AS uc
ON (uc.User_ID = u.User_ID)
INNER JOIN (SELECT DISTINCT z4.Company_ID, z1.Company_ID AS zz
FROM user_company_table AS z1
INNER JOIN user_company_table AS z2
ON (z1.Company_ID IN (z2.Company_ID, z2.Company_ID_Parent))
INNER JOIN user_company_table AS z3
ON (z2.Company_ID IN (z3.Company_ID, z3.Company_ID_Parent))
INNER JOIN user_company_table AS z4
ON (z3.Company_ID IN (z4.Company_ID, z4.Company_ID_Parent)) ) AS c
ON (c.zz = uc.Company_ID)
INNER JOIN website_table AS w
ON (w.Company_ID = c.Company_ID)-PatP|||I tried running an updated version of Pat's query... but I think I'm missing two things.

1) a join on the company_table (as company_parent_id is not found)
2) a a where clause to filter for a particular user_ID.

Any help is appreciated.|||Actually, I think I figured it out... Here is my final query

SELECT DISTINCT w.WebSite_ID
FROM tbl_User u INNER JOIN
tbl_User_Company uc ON uc.User_ID_lkp = u.User_ID
INNER JOIN (SELECT DISTINCT z4.Company_ID, z1.Company_ID_lkp AS zz
FROM tbl_User_Company z1 INNER JOIN
tbl_Company z2 ON z1.Company_ID_lkp IN (z2.Company_ID, z2.Company_ID_prnt) INNER JOIN
tbl_Company z3 ON z2.Company_ID IN (z3.Company_ID, z3.Company_ID_prnt) INNER JOIN
tbl_Company z4 ON z3.Company_ID IN (z4.Company_ID, z4.Company_ID_prnt)) c
ON c.zz = uc.Company_ID_lkp INNER JOIN
tbl_WebSite w ON w.Company_ID_lkp = c.Company_ID
WHERE (u.User_ID = X)

compatibility level

I recently upgraded my database from 6.5 to 2000. To take
advantge of user defined functions I changed the
compatibility level from 65 to 80. the following query in
my code now returns null if the middle name is null. Con
someone explain wht this is happening and how I can fix?
ThanksSorry here is the query
select Client_Name = rtrim(p.Last_Name) +
Case p.Mid_Name
WHEN null then ' '
WHEN ' ' then ' '
Else ' ' + p.Mid_Name + ' '
End
+ ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id
>--Original Message--
>I recently upgraded my database from 6.5 to 2000. To
take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query
in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks
>.
>|||In what query? If I were to guess, most likely you are using string
concatenation to build a name. Concatenate null yields null is off by
default as this is ANSI standard. You can find more info in books
online if you look up CONCAT_NULL_YIELDS_NULL
You can change your query to handle the null values in building the
string - if that's what the issue is.
-Sue
On Mon, 13 Sep 2004 11:32:23 -0700, "steve"
<anonymous@.discussions.microsoft.com> wrote:
>I recently upgraded my database from 6.5 to 2000. To take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks|||As Sue stated in her post, your query will be affected by the
CONCAT_NULL_YIELDS_NULL setting. To avoid that dependency, or write the
query better overall, you would use
select Client_Name = rtrim(p.Last_Name) +
isnull(p.Mid_Name,' ') + ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:173f01c499c1$0b721420$a401280a@.phx.gbl...
> Sorry here is the query
> select Client_Name = rtrim(p.Last_Name) +
> Case p.Mid_Name
> WHEN null then ' '
> WHEN ' ' then ' '
> Else ' ' + p.Mid_Name + ' '
> End
> + ltrim(p.First_Name)
> FROM Client_Info c, Persons p
> WHERE c.Client_no = '0004530184'
> and c.person_id = p.Person_id
> >--Original Message--
> >I recently upgraded my database from 6.5 to 2000. To
> take
> >advantge of user defined functions I changed the
> >compatibility level from 65 to 80. the following query
> in
> >my code now returns null if the middle name is null. Con
> >someone explain wht this is happening and how I can fix?
> >
> >Thanks
> >.
> >

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
JohnCompatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

compatibility level

I recently upgraded my database from 6.5 to 2000. To take
advantge of user defined functions I changed the
compatibility level from 65 to 80. the following query in
my code now returns null if the middle name is null. Con
someone explain wht this is happening and how I can fix?
Thanks
In what query? If I were to guess, most likely you are using string
concatenation to build a name. Concatenate null yields null is off by
default as this is ANSI standard. You can find more info in books
online if you look up CONCAT_NULL_YIELDS_NULL
You can change your query to handle the null values in building the
string - if that's what the issue is.
-Sue
On Mon, 13 Sep 2004 11:32:23 -0700, "steve"
<anonymous@.discussions.microsoft.com> wrote:

>I recently upgraded my database from 6.5 to 2000. To take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
John
Compatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
JohnCompatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

Compatibility issues between SQL 7.0 and SQL 2K

Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
Sundar
Upgrade your code. SUSER_ID is a hold-over from SS 6.5, and before. With
SS 7.0, SIDs replaced SUIDs. SS 7.0 continued to support the function but
SS2K has dropped it.
However, both SS 7.0 and SS2K support SUSER_SID. So, there's your common
point. Make the switch.
Other option is to write your code with path logic using SELECT @.@.VERSION to
tell you which command to execute. However, SUID is an incompatible
attribute with SID. So, it sort of depends on how you are using it.
Sincerely,
Anthony Thomas

"Sundar" <Sundar@.discussions.microsoft.com> wrote in message
news:40344016-CAE0-4437-923A-3852D86CD3C8@.microsoft.com...
Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
Sundar

Compatibility issues between SQL 7.0 and SQL 2K

Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
SundarUpgrade your code. SUSER_ID is a hold-over from SS 6.5, and before. With
SS 7.0, SIDs replaced SUIDs. SS 7.0 continued to support the function but
SS2K has dropped it.
However, both SS 7.0 and SS2K support SUSER_SID. So, there's your common
point. Make the switch.
Other option is to write your code with path logic using SELECT @.@.VERSION to
tell you which command to execute. However, SUID is an incompatible
attribute with SID. So, it sort of depends on how you are using it.
Sincerely,
Anthony Thomas
"Sundar" <Sundar@.discussions.microsoft.com> wrote in message
news:40344016-CAE0-4437-923A-3852D86CD3C8@.microsoft.com...
Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
Sundar