Is this T-SQL dangerous/not reliable

We are trying to create a string of column names for a
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)

Is this possible?

Hey guys,

I wrote a T-SQL query to retrieve all errors name and count the error for each error name based on some conditions. Assume that errors_name is a column name in Error table and the error_count is a calculated field. Here is my question; when I run the query, obviously, it displays the list of errors name and the corresponding errors count which only meets the where condition . However, what I need is to list all the errors name (even if they don't meet the condition) and assign a zero value to the error count column for those errors name that do not fulfill the where condition.

Can anybody assist me?

Appreciate your help.

Sincerely,

Amde

given:

create table ErrorSpec(Id, Message)|||

Yeah, just use a left outer join, and then sum on non-null values (hopefully this matches your need. If not, post table structures and data :):

SET NOCOUNT ON
GO
CREATE TABLE errorType
(
errorTypeId varchar(10) PRIMARY KEY
)
CREATE TABLE errorOccurence
(
errorOccurence int IDENTITY PRIMARY KEY,
occurred datetime DEFAULT (getdate()),
errorTypeId varchar(10) REFERENCES errorType(errorTypeId)
)
GO

INSERT INTO errorType
SELECT 'Type1'
UNION ALL
SELECT 'Type2'
UNION ALL
SELECT 'Type3'

GO

INSERT INTO errorOccurence (errorTypeId)
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type1'
UNION ALL
SELECT 'Type3'
go

SELECT errorType.errorTypeId,
sum(case WHEN errorOccurence.errorTypeId IS NOT NULL THEN 1 ELSE 0 end)
FROM errorType
LEFT OUTER JOIN errorOccurence
ON errorType.errorTypeId = errorOccurence.errorTypeId
GROUP BY errorType.errorTypeId

|||

oops. . . yeah I meant left outer join

should have read:

given:

create table ErrorSpec(Id, Message) create table ErrorOccurance(ErrorSpec_Id, DateOfError)|||

Hey guys,

Thank you for your assistance. I already did what you suggested, however adding the left outer join clause doesn't solve my problem.

Here is the code that I wrote:

--

SELECT

e.eventtypename,m.eventtypeid,Count(m.eventtypeid) as [error count],Count(I.instanceid) as [session count]

FROM

eventtypes e

LEFT OUTER JOIN

messages m

ON

e.eventtypeid = m.eventtypeid

LEFT OUTER JOIN

instances i

ON

m.instanceid = i.instanceid

LEFT OUTER JOIN

sessions s

ON

i.instanceid = s.instanceid

LEFT OUTER JOIN

applications a

ON

s.applicationid = a.applicationid

WHERE

m.instanceid IN(SELECT s.instanceid FROM sessions

WHERE(s.[timestamp] between '2006-02-25 01:23:52.883' and '2006-02-29 01:29:15.513')

AND(a.applicationid = 1002))

AND(I.rootinstanceid = 0)

GROUP BY e.eventtypename,m.eventtypeid

GO

--

The output of this query is as follow:

eventtypename eventtypeid error count session count

Trace 4 492 492

--

So obviously only the data which fulfill the where condition will be displayed. That is true! however, I need also to display those eventtypename which doesn's fulfill the where condition and assign a zero value. For instance, the following table shows the output that I need.

eventtypename eventtypeid error count session count

Trace 4 492 492

Debug failur 2 0 0

Trace warning 3 0 0

Warning event 1 0 0

and so on......

--

I think I am now clear.

Please let me know if you need more explanation?

Sincerely,

Amde

---

|||

remove the timstamp criteria from the where clause and immediately after the "group by" put:

having s.[timestamp] between '2006-02-25 01:23:52.883' and '2006-02-29 01:29:15.513'

the where is applied at the join point.

the Having statement applies a filter on the grouping

|||

Dear Blair,

I tried it, but it didn't solve my problem.

Let me know if you have another idea.

Sincerely,

Amde

|||post a simple script that makes and populates a similar set of tables.|||

I believe when you create your Left Joins, you should add your filter criteria to the ON clause. Putting your filter criteria in the WHERE will filter out all rows that do not meet the criteria, so when there would be no rows whose COUNT == 0, because the WHERE would filter those rows out. Putting the criteria in the ON clause will at least keep the left portion of the row.

You can do this either by:
1. putting the filter criteria one of the LEFT JOIN clause's. (This looks like it might be more work.)
2. LEFT JOINing the eventtypes table with a subquery that filters the messages table.

It may be easier for you to diagnose if you remove the GROUP BY and COUNTS until you get your rowset looking like:

eventtypename eventtypeid m.eventtypeid I.instanceid
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
Trace 4 <value> <value>
<....492 times>
Debug failur 2 NULL NULL
Trace warning 3 NULL NULL
Warning event 1 NULL NULL

then you can add your COUNTs and GROUP BY back and you should have the calculation you are looking for.

Also with representative data we could give more specific assistance.

|||

First. . . you need all your instances where rootinstanceID = 0 with its associated sessions.

then that is used in the left join with messages/events -

try this:

SELECT e.eventtypename, m.eventtypeid,
Count(m.eventtypeid) AS [error count],
Count(temp.instanceid) AS [session count]
FROM eventtypes e LEFT OUTER JOIN messages m ON e.eventtypeid = m.eventtypeid
LEFT OUTER JOIN
(
SELECT i.instanceid
FROM instances INNER JOIN sessions s ON i.instanceid = s.instanceid
INNER JOIN applications a ON a.applicationid = s.applicationid
WHERE rootinstanceid = 0
AND s.[timestamp] BETWEEN '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513'
) temp
ON m.instanceid = temp.instanceid
GROUP BY e.eventtypename,m.eventtypeid

|||

Thank you guys, both Blair and Todd.

I got what I want. Now it's working

Appreciate it!

Sincerely,

Amde

|||

Dear Blair and Todd,

As I told you earlier, everything works fine, however, I want to bind the error count column with the s.timestamp condition. In other words, I want to count those errors which occured under the given date.

Do you have any idea to implement this functionality with out affecting the previous logic?

Sincerely,

Amde

|||

The other technique to try would be to embed a nested sub-query within one of your columns. This will work better if the relationship between your applications and sessions is not 1-to-n. Without seeing the data, I don't know if this is what you are looking for, but it will allow you to count "error count" and "session count" independently if this is what you need to do. Here is an example syntax with Customers counting Orders:

select
c.CustomerId,
(SELECT count(*) from Orders o where o.CustomerId = c.CustomerId) as o_count
FROM
Customers c

This will work as long as the SELECT statement returns at most 1 row and 1 column.

The sub-select is nested in the column selector, which would allow you to count "applications" and "messages" seperately.

|||

the inline select count is going to be a little slow.

I dont know why my suggestion didnt work. . .

try:

SELECT e.eventtypename, m.eventtypeid,
Count(m.eventtypeid) AS [error count],
Count(i.instanceid) AS [session count]
FROM eventtypes e LEFT OUTER JOIN messages m ON e.eventtypeid = m.eventtypeid
LEFT OUTER JOIN instances i on m.instanceid = temp.instanceid
LEFT OUTER JOIN session s ON i.instanceid = s.instanceid
LEFT OUTER JOIN applications a ON a.applicationid = s.applicationid
GROUP BY e.eventtypename,m.eventtypeid
HAVING isNull(rootinstanceid, 0) = 0
AND IsNull(s.[timestamp], '2006-02-25 01:23:52.883')
BETWEEN '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513'

Note that 'Having' will not take into account indexes.

Again, I think my nested select should work.

Post some create table/insert statements so I can build a dummy to work with if that doesnt work.

cheers

|||

Dear Blair,

I am not saying your suggestion doesn't work. It is perfect. And your nested select statement works fine. That is really what I wanted. So here is the thing; in the previous code(nested query), the [session count] is filtered based on the s.timestamp value. That is great. Similarly, I need also to filter the [error count] based on the s.timestamp value, so that it will count those errors in the specified timestamp and return a zero value if the given date is not with in the timestamp. In other words, we have to assign a zero value to [error count] if s.timestamp is not between '2006-02-25 01:23:52.883' AND '2006-02-29 01:29:15.513' . Make sure that this modification should not affect the previous functionality.

I appreciate your willingness to assist me.

Amde

Is this possible?

Hi,

I want to write some t-sql that selects data from a different database from which the tsql is being written in, some like this:

there are 2 databases A and B

I want to select the unit price from database A where the ID feild of database A is equal to the ID feild of database B

kinda something like this pseudo code:

select UNIT_PRICE

FROM Prices

WHERE Prices.Product_ID = (DATABASE B)Prices.Product_ID

can somebody please give me an example of how this can be done

Many Thanks

Try:

select unit_price

from dbo.prices as a

where product_id in (select b.product_id from another_db.dbo.prices as b)

-- or

select unit_price

from dbo.prices as a

where exists (select * from another_db.dbo.prices.product_id as b where b.product_id = a.product_id)

-- or

select a.unit_price

from dbo.prices as a inner join another_db.dbo.prices as b

on a.product_id = b.product_id

AMB

|||

But before executing the query you have to setup those remote database server as linked server in your current database.

here the sample code to setup the linked server..

Code Snippet

EXEC sp_addlinkedserver

@.server = 'ServerBAliasName',

@.provider = 'SQLOLEDB.1',

@.srvproduct = '',

@.provstr = 'Privider=SQLOLEDB.1;Data Source=ServerB;Initial Catalog=Database'

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'ServerBAliasName',

@.useself = true,

@.locallogin = null,

@.rmtuser = 'Userid',

@.rmtpassword = 'Password'

--Later you can use any of the above query to get the result

|||

Good point Manivannan, but just if they reside in a different server.

AMB

|||Yes... if it is on different servers then we should have the Linked Server|||Thanks for you help guys. It is much appreciated.

Is this possible?

Hi guys,

Is there any mechanism or tool to convert T-SQL query to its corresponding MDX query?

Please let me know.

Sincerely,

Amde

That begs the question: is there necessarily a corresponding MDX query? So if you could explain the context, or what problem you're trying to solve, that would help.|||

Hi,

The thing is I have a T-SQL query which works perfectly in a relational database. And I want to implement the same functionality in my Cube. So I am curious to know if I could achieve this thing.

Sincerely,

Amde

|||

Hi Amde,

Can you give an idea of the cube and how it is built from relational data? Also, what values does the T-SQL query return, or what does the query look like?

|||

hi,

Basically, I am working on repoting service, and I don't know how the cube is built. All I know is that I have the dimesions, measures, levels, members and so on to generat the report. But, I can tell you about the T-sql query and you can give whether I can achieve the same functionality using MDX query?

The t-sql query do some calculation on the date information and keep on storing the values in the temporary table. Finally, these value will be used in the report.

Sincerely,

Amde

|||Well, knowing the T-SQL would help (sounds like it's more than just queries, if temp tables are inolved). But it would be hard to figure out the MDX without knowing something about the cube design - how do the dimensions and measures relate to the report you're trying to generate, for example?|||

By the way, is it possible to create a temporary table in cubes?

|||Not exactly, but depends on what overall problem you're trying to solve - I think it's useful to understand the capabilities of Analysis Services OLAP in its own right, versus just drawing detailed comparisons to relational concepts.|||

Okay, assume the following scenario:

I have a date dimension, which stores date info. Assume I want to select the members of the dimension , for example, based on this condition: [Dates].[Date].&[2006-02-25T00:00:00]:[Dates].[Date].&[2006-06-24T00:00:00]. And I want the output in the following format;i.e. on a monthly basis.

From 2006-02-25 to 2006-03-24

From 2006-03-25 to 2006-04-24

From 2006-04-25 to 2006-05-24

From 2006-05-25 to 2006-06-24

How can I achieve this scenario?

Sincerely,

Amde

|||

A couple of questions:

Is 24th the end of the fiscal or reporting month - in which case create a separate "fiscal month"?|||

Hi,

Here is some answer inline:

-"Fiscal month" is not necessary for my report, because of the fact that the report is not financial related.

-Yes there will be a measure which counts some values on each date range.

Sincerely,

Amde

sql

Is this end of T SQL / SQL ?

With LINQ becoming the language for developement in SQL Server 2008 ,

is Microsoft going to gradually come out of SQL .

Any enhancements to T-SQL in Katami ?

Thanks

Perhaps I'm being too naive, but I just can't imagine that they'd phase SQL/T-SQL out of a product named "SQL Server". :-)
|||

LINQ is not a replacement of T-SQL as such imho.

Some improvements in T-SQL are the MERGE statement, OVER() PARTITION BY for aggregate functions, multiple value inserts ...

LINQ may replace T-SQL on the application tier but I don't think T-SQL will ever dissapear as a whole ;-)

|||There are and will be mutliple additions to TSQL, but in terms of LINQ it won′t replace TSQL as e.g. the provider for SQL Server in LINQ to SQL uses TSQL under the covers, it is the native conversation language for this tier with SQL Server.

Jens K. Suessmeyer.

http://www.sqlserver2008.de
|||I actually see LINQ as a new opportunity of getting more performance tuning consulting, and thus I wish it welcome with all my heart I've never seen any "abstraction" of SQL that actually works well, and are able to create well optimized code. What bothers me is the idea of even more software developers with little or no skills in databases.|||

Hehe, right on ;-)

But lets give Microsoft the benefit of the doubt. There is a possibility to work with stored procedures from LINQ too.

|||Sure, it will be possible to create use procs with LINQ. But what about today? Developers don't know how to build procs today, and I see it even less likely that they will when LINQ is out |||

SQL Server continues to invest in Transact-SQL as a server-side programming language, with full support for the language on the client-side. There are a number of enhancements to T-SQL in SQL Server 2008, including the new date and time data types and related built-in functions, table-valued parameters, the MERGE statement, grouping sets, INSERT with nested DML, and many more. Most of the above enhancements are available in the SQL Server 2008 June CTP and are documented in SQL Server 2008 June CTP Books Online. Keep a lookout for more T-SQL enhancements in future CTPs.

|||

LINQ is the result of a longstanding (read: decades) observation that there is a huge impedence mismatch between the primary language (read: C#, etc.) and the Data Sublanguage (read: SQL these days) that makes it very difficult for programmers to access databases. One has to go back to the Codasyl days (early to mid 1970s), where the data sublanguage was actually defined by the same people who defined Cobol and some Cobol compilers had built in support for it, to find a point in time where this was not a major issue. Database folks have tried to address this in limited ways in the past. For example, embedding SQL in other languages via subcompilers. SQL is particularly bad for that because it is a non-procedural language. DEC Rdb had something variously called RDO or RDML (referring to the utilities rather than the unnamed language...internally we called it RDML most of the time) that fit into 3GLs better, but it lost out to SQL. So the purpose of LINQ is to give C# (and other) programmers a way to access data that is natural to THEM as opposed to making them learn a rather foreign data sublanguage and try to integrate the two. Only time will tell how well it succeeds.

LINQ (and some other things in SQL Server 2008) reflect the patterns that developers often use to write applications. For example, they tend to deal with entities rather than tables. Oh, there is code that deals with tables but what it does is construct entities (e.g., orders versus separate order headers and line items) for use in other parts of the application. And if you look at a lot of that code, it constructs the most brain dead bad SQL queries imaginable. So why not materialize and manipulate the entities directly? Does that mean no one will ever want to use SQL's ability to transform one or more tables into a new table (which is what every SQL query does)? Of course not. It means for some set of scenarios there is an access pattern that is better tied to how application programers work.

SQL will be around "forever" both because it is the universal language for talking to databases and because the DBA community (and other serious database programmers) will continue to embrace it no matter what other communities prefer. LINQ will hopefully be a big success in bringing more developers into the data fold and lead to the creation of more innovative and higher quality applications. But any specific outcome in 5 years, 10 years, 20 years, etc. is impossible to predict and no one will even attempt it. It's just not productive.

Hal

|||

In addition to what Hal and Sara already mentioned:

LINQ per se is the ability to provide a declarative programming style in the context of a procedural programming environment. It's family spans a couple of specific extensions for non-CLR data models, among then LINQ on XML for operating on in-memory XML documents, LINQ on SQL that provides an easier integration of mid-tier data programming to SQL, and LINQ on Entities that provides that over the new EDM model.

Today, LINQ on SQL is a mid-tier programming model that can run inside SQL CLR functions but is not meant to be replacing T-SQL. Tomorrow, our thinking is going more towards enabling users to chose the programming environment that they feel comfortable in and enable them to be productive quickly, effectively and efficently. This means T-SQL as well as potentially other programming paradigms (and, no I don't see PL/SQL anytime soon ).

Note also that there is a difference between SQL the declarative query language and T-SQL the procedural extensions. SQL as the query language is an ISO/ANSI standard and as such, it is unlikely to disappear.

Best regards

Michael

Is there any examples of inserting/create a namespace in sql

Is there any examples of inserting/create a namespace in t-sql...so my
stored procedure return a <root xmlns="http://ns"></root>
thanks a billion!!!Hello mm,
m> Is there any examples of inserting/create a namespace in t-sql...so
m> my stored procedure return a <root xmlns="http://ns"></root>
In SQL Server 2005, you can use the new "with xmlnamespaces" option to do
that. In SQL Server 2000, no such luck, there's not a good way (at least
that I can recall) to get namespaced XML from for xml query.
with xmlnamespaces(default 'http://ns')
select 'a' as 'text()'
for xml path('v'),root('root'),type
See the topic "Adding Namespaces Using WITH XMLNAMESPACES " in BOL for more
information
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thanks for answer...now i can move on :)
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74176758c83649594b73f0@.news.microsoft.com...
> Hello mm,
> m> Is there any examples of inserting/create a namespace in t-sql...so
> m> my stored procedure return a <root xmlns="http://ns"></root>
> In SQL Server 2005, you can use the new "with xmlnamespaces" option to do
> that. In SQL Server 2000, no such luck, there's not a good way (at least
> that I can recall) to get namespaced XML from for xml query.
> with xmlnamespaces(default 'http://ns')
> select 'a' as 'text()'
> for xml path('v'),root('root'),type
> See the topic "Adding Namespaces Using WITH XMLNAMESPACES " in BOL for
> more information
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Is there any chance to execute or call PL/SQL SP in SQl Server 2000 T-sql

HI Group,

Is there any chance to execute or call PL/SQL Sp in SQL Server 2000 T-Sql ?
If any chance, please kindly sent steps .
Regards
ravi Shankar.

You can call a PL/SQL SP indirectly using an Oracle package wrapper procedure that returns a PL/SQL table as output which can be called via OPENQUERY. I have posted lot of examples that demonstrate this technique 3-4 years back in the public newsgroups. Use link below:

http://groups.google.com/group/microsoft.public.sqlserver.programming/search?group=microsoft.public.sqlserver.programming&q=oracle+stored+procedure+call+umachandar&qt_g=1

You will find more examples if you search for my name and appropriate keywords (oracle, linked servers, stored procedure etc…).