Return Array from Function

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.
Option Base 1
Function FileSearch(Directory As String, Criteria As String)
Dim aFiles()
With Application.FileSearch
.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute
ReDim aFiles(.FoundFiles.Count)
For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i
End With
'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End Function
Does this have something do to with SQL Server? If not, you should consider
posting this in a VB forum.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"j...greig...davis@.gmail.com" <jgreigdavis@.gmail.com> wrote in message
news:1136905451.257413.287960@.g49g2000cwa.googlegr oups.com...
>I have the following function that works fine, unless I add arguements.
> When I add the arguments I get a Run-Time error '13': Type mismatch.
> Any help would be greatly appreciated.
> Option Base 1
> Function FileSearch(Directory As String, Criteria As String)
> Dim aFiles()
>
> With Application.FileSearch
> .NewSearch
> .LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
> 2) 'Directory
> .SearchSubFolders = True
> .Filename = Criteria '
> ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
> .Execute
> ReDim aFiles(.FoundFiles.Count)
> For i = LBound(aFiles) To UBound(aFiles)
> aFiles(i) = .FoundFiles(i)
> Debug.Print i, aFiles(i)
> Next i
> End With
> 'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
> FileSearch = aFiles
> End Function
>

Return Array from Function

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.
Option Base 1
Function FileSearch(Directory As String, Criteria As String)
Dim aFiles()
With Application.FileSearch
.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute
ReDim aFiles(.FoundFiles.Count)
For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i
End With
'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End FunctionDoes this have something do to with SQL Server? If not, you should consider
posting this in a VB forum.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"j...greig...davis@.gmail.com" <jgreigdavis@.gmail.com> wrote in message
news:1136905451.257413.287960@.g49g2000cwa.googlegroups.com...
>I have the following function that works fine, unless I add arguements.
> When I add the arguments I get a Run-Time error '13': Type mismatch.
> Any help would be greatly appreciated.
> Option Base 1
> Function FileSearch(Directory As String, Criteria As String)
> Dim aFiles()
>
> With Application.FileSearch
> .NewSearch
> .LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
> 2) 'Directory
> .SearchSubFolders = True
> .Filename = Criteria '
> ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
> .Execute
> ReDim aFiles(.FoundFiles.Count)
> For i = LBound(aFiles) To UBound(aFiles)
> aFiles(i) = .FoundFiles(i)
> Debug.Print i, aFiles(i)
> Next i
> End With
> 'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
> FileSearch = aFiles
> End Function
>

Return Array from Function

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.
Option Base 1
Function FileSearch(Directory As String, Criteria As String)
Dim aFiles()
With Application.FileSearch
.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute
ReDim aFiles(.FoundFiles.Count)
For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i
End With
'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End FunctionDoes this have something do to with SQL Server? If not, you should consider
posting this in a VB forum.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"j...greig...davis@.gmail.com" <jgreigdavis@.gmail.com> wrote in message
news:1136905451.257413.287960@.g49g2000cwa.googlegroups.com...
>I have the following function that works fine, unless I add arguements.
> When I add the arguments I get a Run-Time error '13': Type mismatch.
> Any help would be greatly appreciated.
> Option Base 1
> Function FileSearch(Directory As String, Criteria As String)
> Dim aFiles()
>
> With Application.FileSearch
> .NewSearch
> .LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
> 2) 'Directory
> .SearchSubFolders = True
> .Filename = Criteria '
> ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
> .Execute
> ReDim aFiles(.FoundFiles.Count)
> For i = LBound(aFiles) To UBound(aFiles)
> aFiles(i) = .FoundFiles(i)
> Debug.Print i, aFiles(i)
> Next i
> End With
> 'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
> FileSearch = aFiles
> End Function
>sql

return all rows function

in sql 2000 there is the return all rows function.. where is it in sql 2005 express or does it have that function?

thanksI'm a bit confused by your post; can you explain where this function is and how you use it? I have a feeling you're referring to a feature by a different name than is normally used. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <erd@.discussions.microsoft.com> wrote in message news:ddefbe4d-f12b-489e-9ccf-7422d416a908@.discussions.microsoft.com...in sql 2000 there is the return all rows function.. where is it in sql 2005 express or does it have that function?thanks|||Something like this function in Enterprise Manager for SQL Server 2000 should be available to you in Management Studio Express. Failing that, just run the query, SELECT * FROM table? It's really not that much more work than right-click, select... A wrote in message news:ddefbe4d-f12b-489e-9ccf-7422d416a908@.discussions.microsoft.com...
> in sql 2000 there is the return all rows function.. where is it in sql
> 2005 express or does it have that function? >
> thanks
>|||You can right click on your table or view in Object Explorer and select Open Table/View in the context menu. This open a query editor instance with all the data in your table or view.

Is this what you were looking for?|||yes.. thats the one.. thank you

i have another problem..

we are currently using an sql 2000 in server and installed sql server 2005 express on one client but whenever we right click the database diagram in of of our client machine we are having an error.

The system cannot find the specified path exception from HRESULT 0x800700002 (Microsoft.VisualStudio.VSHelp80)|||

Which server is the diagram on and which client is doing the clicking? Are you clicking on a particular diagram, or on the diagrams folder?

The database designer in SQL Server 2000 doesn't understand SQL Server 2005 diagrams. Conversely, the database designer in SQL Server 2005 can't work with diagrams on SQL Server 2000 servers.

|||

where is the import/export function in sql server 2005 express edition?

trying to import an access file or .mdb file to sql 2005 express but couldnt find how to import it.. and also the export file..

thanks

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

Return a todays Date query?

Hello , i want to writ a query that returns ruslts for today's date only,How to do it? i tried to filter the results using Now() function but it did`t work, any help please?

hi leonardo ,

try this

select columns from tablename where convert(varchar, column_with_date_datatype, 103) = convert(varchar,getdate(),103)

hope it helps

regards,

satish

|||

Hi Leo,

U can also use the split function to receive the date.

I think the now.tostring gives date<space>time

so,

dim dat() as string

dat=split(now.tostring)

Msgbox(dat(0).tostring)

dat(1)-->time

-PSK

|||

Thanks for the help

but what i want is to build up a query in the SQL Server that returns today's date in SQL Statment

Select JoinDate

From Employee

Where **********;

how to have a result for a today's date , a week before and a month before ...

|||

you could use

dateadd() function in that case, for more help refer to sql books online.

thanks,

satish.

|||

You could get the start of the day like so DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)..

This means you would do something like...

SELECT * FROM table WHERE Created >= DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)

Enjoy, Steve

return a TABLE

hello all

I need a function with a return value TABLE...

this is not the problem..

but i need the returned table dynamic..

example:

i call the function getTable(schema_name, TableName)

the 1st value is the schema. the 2nd is the table i need...

now i must have a return value like this

select * from schema_name.TableName

is this possible? can i build by return value dynamically?

i tried a lot but nothing worked..

thx

greg

Give a look to CREATE FUNCTION in books online and you will find that in functions two things that you are not allowed to do are:

EXEC ( ' (any SQL statement)' ) EXEC aStoredProceduresql

Return a Field from a User Function ?

Hello,
I am using SQL Server 2000 and I am wondering if it possible to create a
user fonction that return a field so I can use the return of the function in
a WHERE .
My original query I someting like this:
'=======================================
===
SELECT * FROM Table1
WHERE
case @.Workgroup
WHEN 1 THEN Table1.RouteQuart1
WHEN 2 THEN Table1.RouteQuart2
WHEN 3 THEN Table1.RouteQuart3
END = @.NumRoute
'=======================================
====
I want to create a function to remplace the CASE. This function would return
a field. And My new query would be :
'=======================================
===
SELECT * FROM Table1
WHERE
MyNewUserFunction = @.NumRoute
'=======================================
====
Is there a way to do this ? I the query will be more optimized ' If not
possible how to make my original query the most efficient?
Regards,
Gilles LabelleGilles Labelle,
Write three sps and call them from the main one.
create procedure dbo.p1
@.RouteQuart1 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart1 = @.RouteQuart1
return @.@.error
go
create procedure dbo.p2
@.RouteQuart2 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart2 = @.RouteQuart2
return @.@.error
go
create procedure dbo.p3
@.RouteQuart3 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart3 = @.RouteQuart3
return @.@.error
go
create procedure dbo.p4
@.Workgroup int,
@.NumRoute int
as
set nocount on
declare @.rv int
declare @.error int
if @.Workgroup = 1
begin
exec @.rv = dbo.p1 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 2
begin
exec @.rv = dbo.p2 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 3
begin
exec @.rv = dbo.p3 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
-- handle when the value of @.Workgroup is not 1, 2,3
end
end
end
return @.error
go
AMB
"Gilles Labelle" wrote:

> Hello,
> I am using SQL Server 2000 and I am wondering if it possible to create a
> user fonction that return a field so I can use the return of the function
in
> a WHERE .
> My original query I someting like this:
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> case @.Workgroup
> WHEN 1 THEN Table1.RouteQuart1
> WHEN 2 THEN Table1.RouteQuart2
> WHEN 3 THEN Table1.RouteQuart3
> END = @.NumRoute
> '=======================================
====
> I want to create a function to remplace the CASE. This function would retu
rn
> a field. And My new query would be :
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> MyNewUserFunction = @.NumRoute
> '=======================================
====
>
> Is there a way to do this ? I the query will be more optimized ' If not
> possible how to make my original query the most efficient?
>
> Regards,
> Gilles Labelle
>
>
>

return @@identity for another function

What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @.@.identity for the image upload function which will name the image like this
image_23.jpg (23 being the @.@.identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

CREATE PROCEDURE SP_Insertad
(
@.catid int,
@.subcatid int,
@.areaid int,
@.uid int,
@.adtitle varchar(255),
@.addescription varchar(1000)
)

AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@.adtitle,@.addescription,@.areaid, @.uid, 0,convert(varchar, GETUTCDATE(), 101), @.catid, @.subcatid, 1)

select @.@.identity
return
GO

I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@.adtitle", adtitle)
SQLCommander.Parameters.add("@.addescription", addescription)
SQLCommander.Parameters.add("@.catid", catid)
SQLCommander.Parameters.add("@.subcatid", subcatid)
SQLCommander.Parameters.add("@.uid", uid)
SQLCommander.Parameters.add("@.areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imputYour existing code will work by making the following changes:

1) Take out the following lines:

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

2) Make the following change to the SQLCommander.ExecuteNonQuery() line:

Dim returnVal As Integer = SQLCommander.ExecuteScalar()

See the following link about the ExecuteScalar() method:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassexecutescalartopic.asp|||you should say "return @.@.ID..." rather than "select"|||i changed it to
...
AS
Insert Into Tbl_Ads
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@.adtitle,@.addescription,@.areaid, @.uid, 0,convert(varchar, GETUTCDATE(), 101), @.catid, @.subcatid, 1)

return @.@.identity
GO

I think the break down is here -
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

it's not writing to page. Stumped|||ghost opz ,
that worked, I dont know why or how but That did the trick.

thanks a million!

striker

Retrieving XML data using OpenXML

Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int --document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.Hello shriram2977,

> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:

> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Retrieving XML data using OpenXML

Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int--document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.
Hello shriram2977,

> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:

> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Retrieving NEWSEQUENTIALID

Hi,
We are thinking of changing the primary key of our table from GUID type to
squential id using the new NEWSEQUENTIALID function. But the issue we are
having is how to efficiently retrieve the generated value because we need to
pass this information back to the client app. With GUID we have a stored
procedure that calls NEWID and then we assign the generated value to the
primary key column. But with NEWSEQUENTIALID it can only be used as DEFAULT
constraint. We came up with a solution to retreive the generated id but the
performance is very slow.
Has anyone come up with a better strategy in doing this?
Thanks.Roy
Read this article in the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e06d2cab-f1ff-42f1-8550-
6aaec57be36f.htm
I think , as the BOL suggests to use a scalar UDF to rertieve the value
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> I think , as the BOL suggests to use a scalar UDF to rertieve the value
Uri,
I'm not sure if we have a different rev of BOL (I'm looking at the
December version), but mine seems to indicate that you CANNOT use a UDF on
NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
CREATE TABLE x1
(
col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
)
GO
INSERT x1
OUTPUT inserted.col
DEFAULT VALUES
GO
DROP TABLE x1
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>|||DOH!!!! Thanks Adam. I did not read it properly , ( I need to freshen up
myself in the morning)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OUOE$tzmGHA.3440@.TK2MSFTNGP03.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Another approach is to assign the uniqueidentifier value in application code
rather than in SQL Server. That way, you don't need to retrieve the
assigned value at all because you already know it. To get a sequential GUID
value in application code, call the UuidCreateSequential RPC function.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||Thanks the response.
However, this is the solution we tried before but we saw a performance
degradation (during lots of inserts) compare to the old way of using GUIDs.
Roy
"Adam Machanic" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
>|||Thanks Dan.
We haven't tried this approach yet. We will it give it a try.
Roy
"Dan Guzman" wrote:

> Another approach is to assign the uniqueidentifier value in application co
de
> rather than in SQL Server. That way, you don't need to retrieve the
> assigned value at all because you already know it. To get a sequential GU
ID
> value in application code, call the UuidCreateSequential RPC function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>
>

Retrieving Data

Hi

I've got a module that contains the following function.
Imports System.Data.SqlClient

Module SQL_Sprocs
Function ListUsers()

Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("DBConn"))

Dim cmd As SqlCommand = New SqlCommand("list_users", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()

Dim dr As SqlDataReader

dr = cmd.ExecuteReader

ListUsers = dr

dr.Close()
conn.Close()

End Function
End Module

I then want to call this function from my webform. So I'm using the following

Dim dr As SqlClient.SqlDataReader

dr = Timetracker.SQLProcs.ListUsers()

Do While dr.Read
Label1.Text &= dr("first_name") & " " & dr("last_name") & ", "
Loop

dr.Close()

But it's not working. I want to have a module that contains all my sprocs and be able to call them from the individual webpages.

What am I doing wrong?

LbobIt's also private.

You're returning a closed data reader.

dr = cmd.ExecuteReader

ListUsers = dr

dr.Close()
conn.Close()

-------

and you should never break apart the datalayer, and require a reader object to be return back to the primary worker process.

ghetto code.|||Can someone tell me then what I should do with stored procedures that get called from multiple places? In our current app we have a sql_procs.asp which contains all of them, we then use include on the required page and return the recordset.

Any ideas??|||You should never return a data reader to a presentation component, it keeps the connection open until you close, as you've just discovered. If you're looking for a simple solution I'd used a DataSet instead.

Retrieving an integer value from a database function via PHP

I am trying to retrieve an integer value that is returned from an MS SQL function that our DBA wrote and is saved in a table in the database. Using the ODBC_EXEC command I am able to successfully call and execute the function. All I am able to get back is the Resource id. This is the PHP code:

$sqlquery = "dbo.fn_gcc_total_applied '123456'";
$result = odbc_exec($sqlconnect, $sqlquery);
if (!$result) {
exit("Error in SQL 3");
}

Is there a way of capturing the actual integer value that is being returned by the function?

This is an actual function not a stored procedure. The actual MS SQL code is as follows:

declare @.ret_value int

set @.ret_value = dbo.fn_gcc_total_applied ('123456')
print 'Total Applied: ' + cast(@.ret_value as varchar(3))

Thank you in advance for any help you can offer.

Fred BernsteinIn my opinion this has more to do with the MS SQL function and the way the query is setup then with PHP. I therefore think you'll get better support when this thread is moved to the MS SQL forum. Good luck!

Ronald :cool:|||

Quote:

Originally Posted by ronverdonk

In my opinion this has more to do with the MS SQL function and the way the query is setup then with PHP. I therefore think you'll get better support when this thread is moved to the MS SQL forum. Good luck!

Ronald :cool:

Thank you very much for pointing me in the right direction.

Fred Bernstein

Retrieve the version of SQL Server from within a user defined function

EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.

I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?

have you tried

select serverproperty('ProductVersion') as character_value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can use @.@.Version within a user defined function. I tested the following code in both SQL 2000 and SQL 2005. Hope this helps.

Alter Function dbo.VersionNumber()

Returns int

As

Begin

Declare @.Temp VarChar(1000)

Declare @.Output Int

Select @.Temp = @.@.Version

Set @.Temp = Replace(Left(@.Temp, CharIndex('-', @.Temp)-1), 'Microsoft SQL Server', '')

If IsNumeric(@.Temp) =1

Set @.Output = Convert(int, @.Temp)

Else

Set @.Output = 0

Return @.Output

End

go

Select dbo.VersionNumber()

|||

serverproperty works very nicely, thanks.

I had tried @.@.VERSION, which worked, but not pleasant. Now changed to serverproperty

retrieve popular search items from table - problems

I've been using SQL for a while but I'm kinda stumped as to where to start with this one. I have a search function on my ecommerce site and whatever anyone searches for is being stored in a database.

I need a script that will look at my search entries table and return a list of the most popular search terms.

So go to the table and produce result like

Search Term (count)
Harry Potter (6)
Sherlock Holmes (4)
Garfield (2)

But like I say, I'm a little stumped as to where to even begin with this one.

You can use a GROUP BY and ORDER BY clause in your SQL statement e,g,

DECLARE @.MYTABLETABLE (idint IDENTITY(1,1), SearchTermvarchar(10))INSERT @.MYTABLEVALUES('ASP.NET')INSERT @.MYTABLEVALUES('ASP.NET')INSERT @.MYTABLEVALUES('Something')INSERT @.MYTABLEVALUES('Nothing')INSERT @.MYTABLEVALUES('Anything')INSERT @.MYTABLEVALUES('Other')SELECT SearchTerm,COUNT(id)AS SearchesFROM @.MyTableGROUP BY SearchTermORDER BYCOUNT(id)DESC

Retrieve name of running stored procedure

Hello everybody,

I would like to know if there is a function to retrieve the name of the running stored procedure, like the built-in function "DB_NAME()" to retrieve the name of the database.

I need to retrieve the name for logging possibilities.

Thank you in advance!

Bastiaan Molsbeck.

Bastiaan:

There is no direct way to do this; however, if what you have in mind is to be able to get the name of an executing stored procedure in a trigger so that you can retain this information you can (1) use SET CONTEXT_INFO in the stored procedure to store the name of the calling proc and then (2) use the CONTEXT_INFO() function from the trigger to retrieve this information. Keep in mind that CONTEXT_INFO is BINARY(128) so you will need to convert the name to binary to execute SET CONTEXT_INFO and will need to translate from BINARY back to VARCHAR when using the CONTEXT_INFO() function. Take the time to look up these two topics in books online. Here are a couple of previous threads in which there was a similar discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=485119&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1234752&SiteID=1

You will need to include something like this in the procedure:

declare @.contextBinary varbinary (128)
set @.contextBinary = ( select convert(varbinary(128), 'MyProcedureName') )

set context_info @.contextBinary

and something like this in the trigger:

declare @.callingProcedure varchar(128)

set @.callingProcedure = ( select convert(varchar(128), context_info()) )

|||

Hm, I was trying to find a way to accomplish that WITHOUT having to write the name of the stored procedure in it.

But thanks anyway. :-)

|||

If youi are talking about retrieving it from within the executing stored procedure, use object_name(@.@.proc_id) as in:

create procedure test

as

select object_name(@.@.procid)

go

exec test

This will return:

test

|||

Yes, that's exactly what I need.

Many thanks!

Retrieve length of image or BLOB

Check out the DATALENGTH function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroup
s.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>I use the data type image to store BLOB's on the database. Now I would
like to Query the length of the BLOB with T-SQL to calculate the used
disk-space for this item.
Has some one a good idea?|||Check out the DATALENGTH function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroup
s.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>