Need table of words/glossary/etc.

Hi!

I have a client who wants to have a function to have a system create a validation system for users who register for a system. It would email them a registration code, which they want to be two or three random words strewn together.

So, I'm looking for a big table of words - random, glossary terms, etc. Does anyone have anything like this? (A flat file that I can import would be fine.)

Thanks!I have a dictionary of words in an access db,... by why would you do this, why not generate some random sequence based on something like the data/time they subscribed??|||I have a dictionary of words in an access db,... by why would you do this, why not generate some random sequence based on something like the data/time they subscribed??

Because that would be logical and clients don't operate on logic :)

(They're afraid that random words would be too difficult for the users of the system, most of which are senior citizens.)|||A smart person would suggest sending them a validation link in the email that they can click and autovalidates... but there you go. ;)

So do you want this db? I think it is about 4 megs but I can't be sure...|||This is completely out of my hands. I've made my arguments and I've been shot down. Other people have offered alternatives, they've shot them down too. So, this is what I have to go with.

If you could provide the table, that'd be great.

Thanks!|||how do you want it? http download from somewhere? I can upload it in the next few hours (once I get home).|||Either post it somewhere or email it to rflagg@.gmail.com

Thanks!|||should be in your gmail|||Thanks! That table is great (and very extensive). Much appreciated!|||...sounds like a "lazy hacker" looking for a quick and dirty dictionary attack...maybe wrong though ;)|||And I just helped him,.. great,... ah well, if that is the case then people should protect themselves against such attacks anyway...|||I'm in a similar situation. Could you possible send me a copy of the db ?

[My Email] (laasunde@.online.no)

Thank you.|||Any way you can prove to me that it's not for dictionary attacks?|||Don't know how I could prove that over the Internet.

Need the db for an assignment at uni..|||I promise you that's not what it's for. I'm a professional, I do ASP.NET and SQL development for a living as full-time work, as contracts on the side, and I maintain two websites that I wrote myself for two non-profit animal rescue groups dedicated to the saving and placement of homeless and stray animals. One of which is Animal Allies (http://www.animalallies.com) which services the Washington D.C. metro area (where I live). If you'd like more evidence of this, you can email me at tarkon@.animalallies.com and I'll respond.

Besides that, there's very little I can do to assure you this is for honest and legitimate purposes other than my personal guarantee (and the fact that I don't even know what a dictionary attack is).

And lastly, it's been my experience most hackers are Linux people and not SQL Server 2000 developers.|||heheh no worries Tarkon, I wasn't that concerned to be honest,... just messing with your head. ;)sql

need something like replace but not quite

I need a function that does the following:
SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z', 'The
Letter Z', ...)
Where if the input value is 'X' then 'The Letter X' is returned and so on. I
could write a function for this of course but I was hoping one exists in SQL
2k. I looked and couldn't find such an animal.
Thanks,
KeithKeith,
Try:
DECLARE @.LETTER CHAR(1)
SET @.LETTER = 'X'
SELECT 'The Letter ' + @.LETTER
--OR IN A FUNCTION
CREATE FUNCTION UFN_LETTER (@.LETTER CHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @.STRING VARCHAR(20)
SET @.STRING = 'The Letter ' + @.LETTER
RETURN(@.STRING)
END
SELECT DBO.UFN_LETTER('X')
HTH
Jerry
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:elEXtN4zFHA.2008@.TK2MSFTNGP10.phx.gbl...
>I need a function that does the following:
> SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z',
> 'The
> Letter Z', ...)
> Where if the input value is 'X' then 'The Letter X' is returned and so on.
> I
> could write a function for this of course but I was hoping one exists in
> SQL
> 2k. I looked and couldn't find such an animal.
> Thanks,
> Keith
>|||"Keith G Hicks" wrote:

>I need a function that does the following:
> SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z',
> 'The
> Letter Z', ...)
> Where if the input value is 'X' then 'The Letter X' is returned and so on.
> I
> could write a function for this of course but I was hoping one exists in
> SQL
> 2k. I looked and couldn't find such an animal.
> Thanks,
> Keith
What about CASE ?
CASE InputValue
WHEN 'X' THEN 'The Letter X'
WHEN 'Y' THEN 'The Letter Y'
WHEN 'Z' THEN 'The Letter Z'
ELSE 'No Letter'
END
Craig|||I think he's looking for REPLACE with an unlimited arg list, like COALESCE
... sorry Keith, no can do. You could write your own function with a lot of
parameters though but you have to pass at least DEFAULT for every single one
even if they have a default value.
Another alternative would be to accept a specially formatted string that
contained your find & replace values, parse the string and use REPLACE in a
loop.
Good luck
"Jerry Spivey" wrote:

> Keith,
> Try:
> DECLARE @.LETTER CHAR(1)
> SET @.LETTER = 'X'
> SELECT 'The Letter ' + @.LETTER
> --OR IN A FUNCTION
> CREATE FUNCTION UFN_LETTER (@.LETTER CHAR(1))
> RETURNS VARCHAR(20)
> AS
> BEGIN
> DECLARE @.STRING VARCHAR(20)
> SET @.STRING = 'The Letter ' + @.LETTER
> RETURN(@.STRING)
> END
> SELECT DBO.UFN_LETTER('X')
> HTH
> Jerry
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:elEXtN4zFHA.2008@.TK2MSFTNGP10.phx.gbl...
>
>|||KH is correct. That is what I was looking for (my example was a bit too
simplistic
I guess). Thanks for the confirmation. -keith
"KH" <KH@.discussions.microsoft.com> wrote in message
news:87791D9A-7D88-4EF3-8713-C007B7476267@.microsoft.com...
I think he's looking for REPLACE with an unlimited arg list, like COALESCE
... sorry Keith, no can do. You could write your own function with a lot of
parameters though but you have to pass at least DEFAULT for every single one
even if they have a default value.
Another alternative would be to accept a specially formatted string that
contained your find & replace values, parse the string and use REPLACE in a
loop.
Good luck
"Jerry Spivey" wrote:

> Keith,
> Try:
> DECLARE @.LETTER CHAR(1)
> SET @.LETTER = 'X'
> SELECT 'The Letter ' + @.LETTER
> --OR IN A FUNCTION
> CREATE FUNCTION UFN_LETTER (@.LETTER CHAR(1))
> RETURNS VARCHAR(20)
> AS
> BEGIN
> DECLARE @.STRING VARCHAR(20)
> SET @.STRING = 'The Letter ' + @.LETTER
> RETURN(@.STRING)
> END
> SELECT DBO.UFN_LETTER('X')
> HTH
> Jerry
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:elEXtN4zFHA.2008@.TK2MSFTNGP10.phx.gbl...
on.
>
>|||On Wed, 12 Oct 2005 19:19:33 -0400, Keith G Hicks wrote:

>I need a function that does the following:
>SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z', 'Th
e
>Letter Z', ...)
>Where if the input value is 'X' then 'The Letter X' is returned and so on.
I
>could write a function for this of course but I was hoping one exists in SQ
L
>2k. I looked and couldn't find such an animal.
Hi Keith,
You could use nested REPLACE:
REPLACE(REPLACE(REPLACE(InputValue, 'X', 'TheLetterX'), 'Y',
'TheLetterY'), 'Z', 'TheLetterZ')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Need some help to understand T-SQL vs 'official' SQL standards

Hello,

I would like to know if a certain T-SQL function is actually in an official standard (today the ISNULL function, but in the future other functions as well). Is there a chart somewhere or another way to look this up?

Thank you!

The best resource I know of is the Mimer SQL validator ( http://developer.mimer.com/validator/parser200x/index.tml ). You can put your query there to see if there are any standards-compliance issues.

-Ryan / Kardax

|||Did you try a Google? search of, oh, I don't know, perhaps: "ansi AND sql AND functions"?|||Yes, but I was looking more for a resource where you can immediately see that:
function x is in tsql, but it's not in sql92
Best would be a page with all functions. But the concept of Google is not lost on me.
|||I assume you are asking this question because you want to write "SQL standard code".

WARNING: There is NO SUCH THING. Every SQL server implementation has its own "enhancements" and "work arounds" and even differences of "interpetation" of the so-called SQL standard.

If you change database engines, you will have to check all, and probably rewrite many, SQL statements to conform to the new engine. And you will probably want to do this anyway to benefit from the enhancements of the new engine.

The so-called SQL92 standard boils down to: There will be the statement "SELECT" followed by more stuff to return records.

|||

I think that 'owning' a copy of the O'Reilly book would be a good idea. (Fortunately, ANSI standards don't change very often.)

Chapter 4 (SQL Functions) is even available free online.

http://www.oreilly.com/catalog/sqlnut2/chapter/ch04.pdf

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......|||Thank you|||Excellent solution my friend
Thanks for sharing the code

Need replacement for sybase list aggregate

Sybase has an aggregate function called list that concatenates the
results into a scalar.
sybase eg:
select dept,list(description) from parts group by dept
This query would return one row for each department. Each row would
contain all the description text for that department.
Is there any way to do the same in TSQL without using a cursor? I'd
like to do this in a subquery.
TIAThere is none. There are certain workarounds though; see:
http://groups.google.com/group/micr...3e?dmode=source
In SQL 2005 you can use some complex methods using XML with CROSS APPLY, but
the most reliable way is to use the server to return the tabular resultset
and use a client side programming language to get concatenate/ format the
way you need. In certain situations, you might be able to use the PIVOT
operator as well.
Anith|||Consider 'type' is your department and 'name' is the descriptions then this
should do what you want...
SQL Server 2005 only...
select type,
(
select name + ',' as [text()]
from sys.objects soi
where soi.type = t.type
order by name
for xml path( '' ), root( 'sysobjects' ), type
)
from ( select distinct type from sys.objects ) as t
Will give output like this...
D <sysobjects>DF__spt_value__statu__436BFEE3,</sysobjects>
IT
<sysobjects> queue_messages_1003150619,queue_messages
_1035150733,queue_messag
es_1067150847,</sysobjects>
P <sysobjects> sp_MSrepl_startup,sp_MScleanupmergepubli
sher,</sysobjects>
S
<sysobjects> sysrowsetcolumns,sysrowsets,sysallocunit
s,sysfiles1,syshobtcolum
ns,</sysobjects>
SQ
<sysobjects> QueryNotificationErrorsQueue,EventNotifi
cationErrorsQueue,Servic
eBrokerQueue,</sysobjects>
U
<sysobjects> spt_fallback_db,spt_fallback_dev,spt_fal
lback_usg,spt_monitor,sp
t_values,</sysobjects>
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Darren" <darren@.nospam.com> wrote in message
news:uGD%23BWc%23FHA.2608@.tk2msftngp13.phx.gbl...
> Sybase has an aggregate function called list that concatenates the results
> into a scalar.
> sybase eg:
> select dept,list(description) from parts group by dept
> This query would return one row for each department. Each row would
> contain all the description text for that department.
> Is there any way to do the same in TSQL without using a cursor? I'd like
> to do this in a subquery.
> TIA|||Have you seen the FOR XML extension that will allow you to do this in the
engine without the need of sending more data to the client.
Its one of the examples I now use of when, for scalability and complexity
reduction this should be done in engine rather than client side.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:O8YqVbc%23FHA.3608@.TK2MSFTNGP09.phx.gbl...
> There is none. There are certain workarounds though; see:
> http://groups.google.com/group/micr...3e?dmode=source
> In SQL 2005 you can use some complex methods using XML with CROSS APPLY,
> but the most reliable way is to use the server to return the tabular
> resultset and use a client side programming language to get concatenate/
> format the way you need. In certain situations, you might be able to use
> the PIVOT operator as well.
> --
> Anith
>|||I know, but I am not sure if the OP is using SQL 2005 or not.
Anith

Need information on createDrillThroughContext function

In our application, we want to implement createDrillThroughContext function.

Would anyone tell me what that function does, how to use it, where I can use it, and any other related information.

The information on the RDL spec is not very helpful.

Hi Jinlin-

You can find more information about DrillThroughContext at: http://msdn2.microsoft.com/en-us/library/ms159112.aspx

The CreateDrillThroughContext() method is used by report builder for autogeneration of drillthrough reports. It produces a DrillThroughContext which is passed back to the model. Given the model and the DrillThroughConext, the report server will auto create a report.

In general, if you which to create custom model based drillthrough reports, use report builder to create a report and set the "Drillthough" property under the report properties menu. You then can bind that report to a specific model item using Management Studio.

Thanks, Jon

need help-how to call a function by date in SQL server

I am writing a project in .NET.
I want to check every minute two fields in the data base:
time and start date
time end end date
if in the check the date and time arrives I want to call a function automaticly.
what is the best way to do it in windows service?
maybe directly from the database?
I would like to get sugestions to do it
thanksWell, you can fire a trigger in the db when a new record is added to the table. In that trigger you could shell out to run a .NET app that fires an event that the Windows service is watching for. Or you could poll the db from the Windows service every few seconds to see if there is new data.

Any of these work for you?

Don|||Well, you could also just add a job to the database that starts a stored procedure every x minutes.

In this particular case, the sp_add_job system procedure is agood place to start.

Need help with User Defined Function

I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.

To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.

The access function is as follows:

Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long

If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If

End Function

The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.

I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.

Any direction would be greatly appreciated!

I still cannot get this proceedure set up so it will work. I have been playing with it all day and have gotten to the following point:

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATE, @.PODate DATE)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATE
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)
-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())
Where @.IFS < @.ThisDay + 14
RETURN @.IFS - 3 - @.PO
Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
RETURN @.IFS - 7 - @.PO
Where @.IFS < @.ThisDay + 28
RETURN @.IFS - 10 - @.PO
End

It gives me the following errors:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 15
Invalid use of 'getdate' within a function.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 18
Incorrect syntax near the keyword 'Where'.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 20
Incorrect syntax near the keyword 'Where'.

I am really trying to understand this and get it to work right. If someone could just give me an example of a similarly structured function, I am sure I should be able to alter this one so that it works.

Calling the function from the view, actually, I guess I should just use the function in the INSERT code for the DTS package, might still drive me nuts when I try and use this, but I do really want to get this done right.

Thanks again!

|||

Hopefully this will help. We do not have a DATE datatype in SQL SERVER. We have a DATETIME datatype.

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATETIME, @.PODate DATETIME)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATETIME
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)

-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())

if (@.IFS < @.ThisDay + 14)
RETURN @.IFS - 3 - @.PO
else if ((@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29))
RETURN @.IFS - 7 - @.PO
else if (@.IFS < @.ThisDay + 28)
RETURN @.IFS - 10 - @.PO

RETURN -1 --trying to indicate a failure

--Where @.IFS < @.ThisDay + 14
-- RETURN @.IFS - 3 - @.PO
--Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
--RETURN @.IFS - 7 - @.PO
--Where @.IFS < @.ThisDay + 28
-- RETURN @.IFS - 10 - @.PO
end
GO

|||

Thanks Asvin!

That is getting it very close, the only error I now get is:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 16
Invalid use of 'getdate' within a function.

Not sure how else I can get today's date in this thing, but I keep trying. I am open to just getting the date as a variable, but when I tried that, I still go the error.

Thanks again!

|||

In 2000 you can't use the getdate function in a function (say that a few times fast) but you can in 2005.

The workaround is to add a parameter and pass in the date and time like:

create function functionName
(
@.parm1 datatype,
@.getdate datetime

Then add that to the call.

Another trick which I have never actually used is to create a view:

create view dateView
as
select getdate() as getdate

Then just add this to your queries that need the current date (using a cross join works nicely):

create view dateView
as
select getdate() as getdate
GO

select * from dateView
GO

Returns:

getdate
--
2006-04-07 23:58:30.217

select dateview.getdate, sysobjects.name
from sysobjects
cross join dateview

getdate name
--
2006-04-07 23:58:30.247 sysrowsetcolumns
2006-04-07 23:58:30.247 sysrowsets
2006-04-07 23:58:30.247 sysallocunits
.......

And so on.

|||

Thanks Louis,

I didn't actually use the ideas you put forth, but you did get me thinking. I realized that I could put GetDate() in the view that was used to make the table for the cube. I then do some alterations on the table based on what data is missing in some fields of the table. There are times that there are no dates in the fields I need to calculate the BuyerDeltas that this whole exercise is designed to calculate so I need to put dates in these fields before I can call the function anyway. After those updates, I can call the function and put the value I wanted calculated in the appropriate field of the table. There is probably a simpler and faster way to do it, but this works and the boss is breathing down my neck to get this finished.

Thanks again to all that helped, using a little bit from all of the responses has let me finally beat this thing.

Need Help with UDF's Please

I need a scalar function that I can call from a select statement (with other
criteria) that will dynamically return all the columns (And sometimes their
datatypes with a mode option), from a table or view from any database on tha
t
server. The function needs to work in both SQL 2000 and SQL 2005. I have
listed the function I have tried to create below (using the master database
trying to make a system object but 2005 will not allow me to update system
tables with the UID of 4 - sys in 2005 and system_schema_function in
2000...yes I already know I am a bad developer for trying to cheat). Any
help on this would be greatly appreciated!
Please excuse the formating in advance (was created in VS sql 2005).
Thank You
Jared
/ ****************************************
******************/
/* Name : fn_getcolumns
*/
/* Version : 1.0
*/
/* Author : Jared Kirkpatrick
*/
/* Date : 2006-01-01
*/
/* Description : Retrives column information about an object
*/
/ ****************************************
*****************/
/* Date : Version: Who: Description
*/
/ ****************************************
*****************/
/* 2006-01-01 : 1.0 : JSK : Initial Release.
*/
/ ****************************************
*****************/
USE [master];
GO
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'fn_getcolumns')
BEGIN
DROP FUNCTION [dbo].[fn_getcolumns]
END;
GO
CREATE FUNCTION [dbo].[fn_getcolumns]
(
@.p_DB [NVARCHAR](100)
,@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
,@.v_Result [NVARCHAR](4000)
SELECT @.v_SQL =
'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
+ CHAR(13) + CHAR(10) +
' '' ,['' + [Column_Name] + ''] '' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN @.d_Mode = 1 '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
+ CHAR(13) + CHAR(10) +
' ''['' + UPPER([Data_Type]) + '']'' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''CHAR'''
+ CHAR(13) + CHAR(10) +
' ,''VARCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NVARCHAR'''
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Character_Maximum_Length])
+ '') '' ' + CHAR(13) + CHAR(10) +
' WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''DECIMAL'' '
+ CHAR(13) + CHAR(10) +
' ,''NUMERIC'' '
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
'' + ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') '' '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END + CHAR(13) + CHAR(10) '
+ CHAR(13) + CHAR(10) +
' FROM [INFORMATION_SCHEMA].[COLUMNS] '
+ CHAR(13) + CHAR(10) +
' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
+ CHAR(13) + CHAR(10) +
' AND [Table_Name] = ''' + @.p_Object + ''' '
+ CHAR(13) + CHAR(10) +
' ORDER BY [Ordinal_Position] '
+ CHAR(13) + CHAR(10) +
' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
EXEC sp_executesql @.v_SQL,
N'@.d_Result [NVARCHAR](4000) OUTPUT',
N'@.d_Mode [INT]' ,
@.v_Result OUTPUT ,
@.d_Mode = @.p_Mode
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)Thats an easy one, you can=B4t use execute statements or DML (and
non-temp tables) in functions. Therefore you have to use stored
procedures.
HTH, Jens Suessmeyer.|||The RETURN Statement at the end should be:
RETURN @.v_Result
NOT
RETURN @.v_SQL
Excuse me I was trobleshooting.
"kirk1880" wrote:

> I need a scalar function that I can call from a select statement (with oth
er
> criteria) that will dynamically return all the columns (And sometimes thei
r
> datatypes with a mode option), from a table or view from any database on t
hat
> server. The function needs to work in both SQL 2000 and SQL 2005. I have
> listed the function I have tried to create below (using the master databas
e
> trying to make a system object but 2005 will not allow me to update system
> tables with the UID of 4 - sys in 2005 and system_schema_function in
> 2000...yes I already know I am a bad developer for trying to cheat). Any
> help on this would be greatly appreciated!
> Please excuse the formating in advance (was created in VS sql 2005).
> Thank You
> Jared
> / ****************************************
******************/
> /* Name : fn_getcolumns
> */
> /* Version : 1.0
> */
> /* Author : Jared Kirkpatrick
> */
> /* Date : 2006-01-01
> */
> /* Description : Retrives column information about an object
*/
> / ****************************************
*****************/
> /* Date : Version: Who: Description
> */
> / ****************************************
*****************/
> /* 2006-01-01 : 1.0 : JSK : Initial Release.
> */
> / ****************************************
*****************/
> USE [master];
> GO
> IF EXISTS(SELECT 1
> FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
> WHERE [SPECIFIC_SCHEMA] = 'dbo'
> AND [SPECIFIC_NAME] = 'fn_getcolumns')
> BEGIN
> DROP FUNCTION [dbo].[fn_getcolumns]
> END;
> GO
> CREATE FUNCTION [dbo].[fn_getcolumns]
> (
> @.p_DB [NVARCHAR](100)
> ,@.p_Owner [NVARCHAR](100)
> ,@.p_Object [NVARCHAR](100)
> ,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
> )
> RETURNS [NVARCHAR](4000)
> WITH ENCRYPTION
> AS
> BEGIN -- Begin function
> /* Declare Variables */
> DECLARE @.v_SQL [NVARCHAR](4000)
> ,@.v_Result [NVARCHAR](4000)
> SELECT @.v_SQL =
> 'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
> + CHAR(13) + CHAR(10) +
> ' '' ,['' + [Column_Name] + ''] '' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN @.d_Mode = 1 '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
> + CHAR(13) + CHAR(10) +
> ' ''['' + UPPER([Data_Type]) + '']'' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''CHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''VARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NVARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Character_Maximum_Length
])
> + '') '' ' + CHAR(13) + CHAR(10) +
> ' WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''DECIMAL'' '
> + CHAR(13) + CHAR(10) +
> ' ,''NUMERIC'' '
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
> '' + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') ''
'
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END '
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END + CHAR(13) + CHAR(10) '
> + CHAR(13) + CHAR(10) +
> ' FROM [INFORMATION_SCHEMA].[COLUMNS] '
> + CHAR(13) + CHAR(10) +
> ' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
> + CHAR(13) + CHAR(10) +
> ' AND [Table_Name] = ''' + @.p_Object + ''' '
> + CHAR(13) + CHAR(10) +
> ' ORDER BY [Ordinal_Position] '
> + CHAR(13) + CHAR(10) +
> ' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
> EXEC sp_executesql @.v_SQL,
> N'@.d_Result [NVARCHAR](4000) OUTPUT',
> N'@.d_Mode [INT]' ,
> @.v_Result OUTPUT ,
> @.d_Mode = @.p_Mode
> RETURN @.v_SQL;
> END; -- Begin function
> GO
> --SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)|||Jens,
First: I really appreciate the feedback
Second: I understand that you cannot execute statements in UDF's which is
why I am looking for help. A sp will not help me in this situation because
I
need the information within another dynamic statement, and you cannot call a
n
sp within inline SQL. I could use the code that makes this UDF and place it
in every instance I need within the sp I call but we are talking about 87
diffrent calls to date and more coming around the corner. I would really
like to find a solution that will allow me to somehow do the following by
making a system function or I will need to start making an xp that can be
called from a UDF (Of cource that calls for me to register the xp and have
code outside of T-SQL that makes things harder to manage).
"Jens" wrote:

> Thats an easy one, you can′t use execute statements or DML (and
> non-temp tables) in functions. Therefore you have to use stored
> procedures.
> HTH, Jens Suessmeyer.
>|||> why I am looking for help. A sp will not help me in this situation
> because I
> need the information within another dynamic statement, and you cannot call
> an
> sp within inline SQL.
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dynamic_sql.html|||kirk1880 (kirk1880@.discussions.microsoft.com) writes:
> Second: I understand that you cannot execute statements in UDF's which
> is why I am looking for help. A sp will not help me in this situation
> because I need the information within another dynamic statement, and you
> cannot call an sp within inline SQL. I could use the code that makes
> this UDF and place it in every instance I need within the sp I call but
> we are talking about 87 diffrent calls to date and more coming around
> the corner. I would really like to find a solution that will allow me
> to somehow do the following by making a system function or I will need
> to start making an xp that can be called from a UDF (Of cource that
> calls for me to register the xp and have code outside of T-SQL that
> makes things harder to manage).
There is a way: you can call extended stored procedures from UDFs. Thus
you can call xp_cmdshell which in it's turns invokes to OSQL and
runs the query. The query would have to insert the data into a table,
and then you would pick up the data from the table in the UDF.
Now, the design of UDFs is that they are not supposed to change database
state, which is why all those restrictions with them are there. This is
a loophole, and there is not really any guarantees that it will work
as expected - if you get something work now, it could break SQL 2005
or in the next service pack.
Thus, I strongly discourage you from trying this. It's a fun thing to
do at parties, but is unsuitable for production code.
What you really should do is to go back to your original business
problem and analyse why you ended up with all these 87 calls. The best
I can recommend at this point is to bounce data over a temp table, or
a process-keyed table, see http://www.sommarskog.se/share_data.html
for details. But I suspect that you need a complete redesign.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also not preferable would be to use OPENQUERY which is able to execute
a Storedprocedure as a source of a result set, but this has many
mantrap and is not suggestable.
HTH, Jens Suessmeyer.|||Thank you everyone that replied to this post. The solution is actually
simplified (I was trying to make it way to hard). I hold a master schema
table of all the user databases that are will be using dynamic table/view
creates and dynamic insert/update/delete. So my original code/function that
is not dynamic (listed below) works great but only in the database that the
original objects reside...so the solution is to use OSQL and have a system
wrapper that compiles the same function in every database that is flagged,
that way the dynamic procedure that is already in whatever database that is
flagged will have access to return the results I need. Kinda of a kluge to
have multiple objects with diffrent id's in each database, but it works.
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'FSU_GetColumns')
BEGIN
DROP FUNCTION [dbo].[FSU_GetColumns]
END;
GO
CREATE FUNCTION [dbo].[FSU_GetColumns]
(
@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
SELECT @.v_SQL = ISNULL(@.v_SQL, '') + ' ' +
' ,[' + [Column_Name] + '] ' +
CASE WHEN @.p_Mode = 1
THEN REPLICATE(' ', (25 - LEN([Column_Name]))) +
'[' + UPPER([Data_Type]) + ']' +
CASE WHEN [Data_Type] IN (
'CHAR'
,'VARCHAR'
,'NCHAR'
,'NVARCHAR'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Character_Maximum_Length]) +
') '
WHEN [Data_Type] IN (
'DECIMAL'
,'NUMERIC'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Numeric_Precision]) + ', ' +
CONVERT(NVARCHAR(10), [Numeric_Scale]) + ') '
ELSE ''
END
ELSE ''
END + CHAR(13) + CHAR(10)
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [Table_Schema] = @.p_Owner
AND [Table_Name] = @.p_Object
ORDER BY [Ordinal_Position]
SELECT @.v_SQL = STUFF(@.v_SQL, 1, 3, ' ');
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[FSU_GetColumns]('dbo', 'TU_Registry', 1);

Need help with UDF useage. Trying to get away without using cursor.

I have a Function (say X) that takes 2 parameters and returns back a table result of multiple records.

And I have a query (say Q) that return rows of 2 columns that I need to feed Function X.

They way I do it right now is I have a cursor that loops through the result of Query Q
and call Function X as I pass the 2 values the the function.

As Function X return with the result set, I load it into a temporary table.

At the end of the cursor processing, I query the temporary table to return the complete result set.

Is there a way do this without using a cursor?

Here is my Function X top part:

alter FUNCTION ReturnItem
(
@.tableName varchar(50),
@.ItemID int
)
returns @.returnTable table
(
ItemName varchar(50),
ItemValue varchar(50),
[Timestamp] datetime
)

JB..

You want to use the CROSS APPLY capability of SS2k5 to "apply" the rows of one table to a UDF. Here is an example:

CREATE TABLE QuerySource
(
c1 INT,
c2 INT
)

INSERT QuerySource VALUES (1,1)
INSERT QuerySource VALUES (2,3)
INSERT QuerySource VALUES (10,15)
INSERT QuerySource VALUES (16,13)

CREATE FUNCTION ReturnItem
(
@.p1 INT,
@.p2 int
)
returns @.returnTable table
(
AddResult int,
SubtractResult int,
TimesResult int,
DivideResult int
)
AS
BEGIN
INSERT @.returnTable SELECT @.p1+@.p2, @.p1-@.p2,@.p1*@.p2,@.p1/@.p2
RETURN
END

SELECT *
FROM QuerySource qs CROSS APPLY dbo.ReturnItem(qs.c1, qs.c2)

|||Hello. Thank you very much for you help. That is exactly what I wanted to do. I never knew such feature exist. Anyway, I was afraid that when the number of data being passed becomes really big, the cursor will slow things down hence, I have to find this solution.

Thank you again.

JB..