Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it
to
> be function and return all fields from all tables, BUT this one do not wan
t
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 = Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:
> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it to
> be function and return all fields from all tables, BUT this one do not want
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 => Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>> Following sample function. This one works in query analyzer, as well as
>> works as stored procedure. Because of the nature of the program I need it
>> to
>> be function and return all fields from all tables, BUT this one do not
>> want
>> to compile because it has fileds with the same names in different tables.
>> How to fix it?
>> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
>> RETURNS TABLE
>> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
>> column names on different tables to be displayed
>> FROM Table1 INNER JOIN (((Table5 AS uu
>> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
>> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
>> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =>> Table4.Filed5
>> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>>
>> --
>> Tamir Khason
>> "The computer is no better
>> than its program." [Elting Elmore Morison]
>> http://www.dotnet.us/
>>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
>> Thank you for response, BUT there are 10 tables with about 100
>> columns each one. It's seemed me rather supid to specified 1000
>> columns for such function.
>>
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.comsql

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it to
> be function and return all fields from all tables, BUT this one do not want
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>
|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com
|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Retriving data from SQL text field

I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?Use a parameter.|||SQL QA does not allow declaring local variables with text type. I am trying to pull the SQL text filed's all the text. Declaring or converting to varchar will limit it to 8000 characters only.

An example will be very handy..

Thxsql

Retrieve Query

Hi Friends,
I had written a complex SQL query through SQL Query Analyzer, and had
executed the same by pressing F5 button. Before I could save that query on my
PC, my computer switched off.
By any chance is it possible to retrieve the complex query executed by me ?
If yes then how ?
Thanks and Regards,
AmitUnfortunately, you will have to use your skills to recreate that query. QA
does not have to ability, like the browser, to 'remember' the last thing we
typed in.
Be sure that you have that auto-shut down mechanism turned off next time. I
would absolutely hate it if "my computer switched off" without my telling to
it to do so.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Amit20" <Amit20@.discussions.microsoft.com> wrote in message
news:B7C8A4E4-3B96-4541-AA99-0F1EF2828B77@.microsoft.com...
> Hi Friends,
> I had written a complex SQL query through SQL Query Analyzer, and had
> executed the same by pressing F5 button. Before I could save that query on
> my
> PC, my computer switched off.
> By any chance is it possible to retrieve the complex query executed by me
> ?
> If yes then how ?
> Thanks and Regards,
> Amit|||You can not get the query back once it's done running. But let's say while
the query was running your computer got shut down and you had an option to
connect through another machine to same SQL instance then you could use the
following to get the query:
DBCC InputBuffer(spid)
It displays only first 255 characters of your query...
Bob
"Arnie Rowland" wrote:
> Unfortunately, you will have to use your skills to recreate that query. QA
> does not have to ability, like the browser, to 'remember' the last thing we
> typed in.
> Be sure that you have that auto-shut down mechanism turned off next time. I
> would absolutely hate it if "my computer switched off" without my telling to
> it to do so.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Amit20" <Amit20@.discussions.microsoft.com> wrote in message
> news:B7C8A4E4-3B96-4541-AA99-0F1EF2828B77@.microsoft.com...
> > Hi Friends,
> >
> > I had written a complex SQL query through SQL Query Analyzer, and had
> > executed the same by pressing F5 button. Before I could save that query on
> > my
> > PC, my computer switched off.
> >
> > By any chance is it possible to retrieve the complex query executed by me
> > ?
> > If yes then how ?
> >
> > Thanks and Regards,
> > Amit
>
>|||Or profiler was running, or ...
But for all practical purposes, the OP is SOL.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:DC9E8CBA-5BD2-480C-8B53-E332F1CB3FEE@.microsoft.com...
> You can not get the query back once it's done running. But let's say while
> the query was running your computer got shut down and you had an option to
> connect through another machine to same SQL instance then you could use
> the
> following to get the query:
> DBCC InputBuffer(spid)
> It displays only first 255 characters of your query...
>
> Bob
> "Arnie Rowland" wrote:
>> Unfortunately, you will have to use your skills to recreate that query.
>> QA
>> does not have to ability, like the browser, to 'remember' the last thing
>> we
>> typed in.
>> Be sure that you have that auto-shut down mechanism turned off next time.
>> I
>> would absolutely hate it if "my computer switched off" without my telling
>> to
>> it to do so.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Amit20" <Amit20@.discussions.microsoft.com> wrote in message
>> news:B7C8A4E4-3B96-4541-AA99-0F1EF2828B77@.microsoft.com...
>> > Hi Friends,
>> >
>> > I had written a complex SQL query through SQL Query Analyzer, and had
>> > executed the same by pressing F5 button. Before I could save that query
>> > on
>> > my
>> > PC, my computer switched off.
>> >
>> > By any chance is it possible to retrieve the complex query executed by
>> > me
>> > ?
>> > If yes then how ?
>> >
>> > Thanks and Regards,
>> > Amit
>>

Retrieve Query

Hi Friends,
I had written a complex SQL query through SQL Query Analyzer, and had
executed the same by pressing F5 button. Before I could save that query on m
y
PC, my computer switched off.
By any chance is it possible to retrieve the complex query executed by me ?
If yes then how ?
Thanks and Regards,
AmitUnfortunately, you will have to use your skills to recreate that query. QA
does not have to ability, like the browser, to 'remember' the last thing we
typed in.
Be sure that you have that auto-shut down mechanism turned off next time. I
would absolutely hate it if "my computer switched off" without my telling to
it to do so.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Amit20" <Amit20@.discussions.microsoft.com> wrote in message
news:B7C8A4E4-3B96-4541-AA99-0F1EF2828B77@.microsoft.com...
> Hi Friends,
> I had written a complex SQL query through SQL Query Analyzer, and had
> executed the same by pressing F5 button. Before I could save that query on
> my
> PC, my computer switched off.
> By any chance is it possible to retrieve the complex query executed by me
> ?
> If yes then how ?
> Thanks and Regards,
> Amit|||You can not get the query back once it's done running. But let's say while
the query was running your computer got shut down and you had an option to
connect through another machine to same SQL instance then you could use the
following to get the query:
DBCC InputBuffer(spid)
It displays only first 255 characters of your query...
Bob
"Arnie Rowland" wrote:

> Unfortunately, you will have to use your skills to recreate that query. QA
> does not have to ability, like the browser, to 'remember' the last thing w
e
> typed in.
> Be sure that you have that auto-shut down mechanism turned off next time.
I
> would absolutely hate it if "my computer switched off" without my telling
to
> it to do so.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Amit20" <Amit20@.discussions.microsoft.com> wrote in message
> news:B7C8A4E4-3B96-4541-AA99-0F1EF2828B77@.microsoft.com...
>
>|||Or profiler was running, or ...
But for all practical purposes, the OP is SOL.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:DC9E8CBA-5BD2-480C-8B53-E332F1CB3FEE@.microsoft.com...[vbcol=seagreen]
> You can not get the query back once it's done running. But let's say while
> the query was running your computer got shut down and you had an option to
> connect through another machine to same SQL instance then you could use
> the
> following to get the query:
> DBCC InputBuffer(spid)
> It displays only first 255 characters of your query...
>
> Bob
> "Arnie Rowland" wrote:
>