How To Script Jobs In SQL Server ?

How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...

msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX

soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.

How to rung SQL 2005 db on SQL 2000 server - no unique SQL 2005 st

I converted a database from Sql 2000 to Sql 2005 - no problems. Added
function to the database. I did not use any new functions unique to Sql
2005, as far as I know. I need to allw this database to run on both SQL 2000
and SQL 2005. When I attempted to load the database on SQL 2000 local
server, I received the following message:
Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
Run DBCC checktable on SysIndex.
I have never run this and I do not know if this is an on going problem. Any
information would be appreciated.
Thank You.
Jack
LitePipe ManagementYou cannot restore or attach a 2005 database to 2000, 2005 added stuff to the database file formats
that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to go the script,
export/import route (using BCP, DTS, SSIS etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
>I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> function to the database. I did not use any new functions unique to Sql
> 2005, as far as I know. I need to allw this database to run on both SQL 2000
> and SQL 2005. When I attempted to load the database on SQL 2000 local
> server, I received the following message:
> Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
> Run DBCC checktable on SysIndex.
> I have never run this and I do not know if this is an on going problem. Any
> information would be appreciated.
> Thank You.
> Jack
> LitePipe Management|||Thank you, I appreciate you taking the time to answer my question.
Jack Leach
"Tibor Karaszi" wrote:
> You cannot restore or attach a 2005 database to 2000, 2005 added stuff to the database file formats
> that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to go the script,
> export/import route (using BCP, DTS, SSIS etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
> news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
> >I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> > function to the database. I did not use any new functions unique to Sql
> > 2005, as far as I know. I need to allw this database to run on both SQL 2000
> > and SQL 2005. When I attempted to load the database on SQL 2000 local
> > server, I received the following message:
> > Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
> > Run DBCC checktable on SysIndex.
> >
> > I have never run this and I do not know if this is an on going problem. Any
> > information would be appreciated.
> >
> > Thank You.
> >
> > Jack
> > LitePipe Management
>|||Hi,
If you select the option of scripting the SQL 2005 database tobe
compatible with SQL 2000.
There is a bug in SQL 2005 were it won't create the correct script
which can be used with SQL 2000.
May be you will have to wait for next SP or need to modify the script
so it can run on SQL 2k.
Thanks
Ajay Rengunthwar
MCTS
LitePipe wrote:
> Thank you, I appreciate you taking the time to answer my question.
> Jack Leach
> "Tibor Karaszi" wrote:
> > You cannot restore or attach a 2005 database to 2000, 2005 added stuff to the database file formats
> > that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to go the script,
> > export/import route (using BCP, DTS, SSIS etc).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
> > news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
> > >I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> > > function to the database. I did not use any new functions unique to Sql
> > > 2005, as far as I know. I need to allw this database to run on both SQL 2000
> > > and SQL 2005. When I attempted to load the database on SQL 2000 local
> > > server, I received the following message:
> > > Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
> > > Run DBCC checktable on SysIndex.
> > >
> > > I have never run this and I do not know if this is an on going problem. Any
> > > information would be appreciated.
> > >
> > > Thank You.
> > >
> > > Jack
> > > LitePipe Management
> >
> >

How to run user defined function on sql server 2005

Dear all

I wants to run sql server user defined function when linked two server.

I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?

Regards

Jerminxxx

You can call the function with [ServerName] .[DatabaseName].[User/Schema Name].[FunctionName].

|||

Just use the select ServerName.DatabaseName.OwnerName.Functionname

i.e Like select IFoundd10.Employee.dbo.getenc()

__________________________________________________________

Don't forget to click "Mark as Answer" on the post that helped you.

|||

Thanks for ur reply.

when i try to use this technique i got some error like .

"contains more than the maximum number of prefixes."

Can u help me

|||

Which version of SQL Server are you using ? Can you post some of the details of the 2 servers that you've linked, the function and from where are you trying to execute the function ?

|||

I'm using sql server 2005.

For Example:

We have two sql server named as server1 and server2. These two servers are already linked.

On server1 we have some user defined function.Amoung the function one function called getencf().

What i want .I wants to run function getencf() on server 2 environment.

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

CAn you help me

Regards

Jerminxxx

|||

jerminxxx:

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

Are you getting the same error ("contains more than the maximum number of prefixes.") which you posted in one of your posts ?

Are you able to run the function from server1 ?

|||

it will e hard due to prefixes number, if your function returns single value try to replace it( or add extra ) stored procedure on the server which will call you function. If your function returns results set try to use OPENQuery to execute it on remote server.

How to run TSQL from vb net 2003

Hi All
I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
In vb6 I called a function...
retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
which ran the TSQL script against OSQL. The script basically created the
Database in MSDE then imports a heap of tables from Access 2000.
Private Function ExecCmd(cmdline$)
'used for MSDE to launch osql
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret&
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
'Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
What is the preferred method of running a TSQL script from VB Net 2003 using
sqlclient class?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
> In vb6 I called a function...
> retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
> which ran the TSQL script against OSQL. The script basically created
> the Database in MSDE then imports a heap of tables from Access 2000.
> Private Function ExecCmd(cmdline$)
> 'used for MSDE to launch osql
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim ret&
> ' Initialize the STARTUPINFO structure:
> start.cb = Len(start)
> ' Start the shelled application:
> ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
> NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
> 'Wait for the shelled application to finish:
> ret& = WaitForSingleObject(proc.hProcess, INFINITE)
> Call GetExitCodeProcess(proc.hProcess, ret&)
> Call CloseHandle(proc.hThread)
> Call CloseHandle(proc.hProcess)
> ExecCmd = ret&
> End Function
> What is the preferred method of running a TSQL script from VB Net
> 2003 using sqlclient class?
http://www.absistemi.it/permalink=tn169.ashx
this will use the very same feature with .Net..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to Reuse functions in script tasks ?

Hello

I have some common functions that i use in several script tasks. How du i store a function globaly so that i can use it from different projects and still only have to edit it one place ?

I think one way is to create a dll of the function and import in each of your script tasks. To Import the dll into each of the script you need to place the dll into <root>\Windows\Microsoft .Net\Framework\<Latest Version> and gac it.

I think this will help...

Thanks

Dharmbir

|||But when i import it, it will become "local" ? So if i want to change something in the function i'll have to import it again in all those scripts ?|||

Correct.

I am not able to think of any other way to do the same.

Because each script task is different Identity(different project), if you create a function into one ...the other script task will not be aware of it.

How do you share a global function b/w two different projects?

Thanks

Dharmbir

|||

You don't have to import in each of your script...

once u do changes copy to the <root>\microsoft.net\.... folder again and just gac it...

It should work

How to return varchar(MAX) from a CLR function?

Hi,

I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.

How can I solve this problem and return varchar(MAX)?

Thanks

? The Visual Studio deployment tool maps SqlString to VARCHAR(8000). Use SqlChars instead -- it gets mapped to VARCHAR(MAX). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bill YU@.discussions.microsoft.com> wrote in message news:2a21db0a-f54b-42e2-88b9-fb4cab26d356@.discussions.microsoft.com... Hi, I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters. How can I solve this problem and return varchar(MAX)? Thanks|||Thanks Adam, it works.|||Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!
|||? Visual Studio deployment unfortunately has a lot of quirks. In case you're interested, I discuss another one in the following blog post: http://www.amazon.com/gp/plog/post.html/ref=cm_blog_pl/104-6385614-0075127?%5Fencoding=UTF8&pt=personalBlog&aid=PlogMyCustomersAgent&ot=customer&pd=1147803274.225&pid=PMCAALIINL2LPJELat1147801908&iid=AALIINL2LPJEL -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bo416@.discussions.microsoft.com> wrote in message news:2343e9db-ce1c-4f2a-9b47-2d471a0ddb44@.discussions.microsoft.com...Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!

How to return the result of an EXEC from a function

Hi,

I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.

This is my function:

ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@.TypeID int,
@.CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1
BEGIN
SET @.RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END

FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND P.CaseID = ' + CAST(@.CaseID as varchar)
END
ELSE
BEGIN
SET @.RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND CaseID = ' + CAST(@.CaseID as varchar)
END

RETURN @.RTN

END

Each time I try " RETURN EXEC(@.RTN) " or something similar I get an error.

I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.

Anyone with any ideas?

You need to give a look to the CREATE FUNCTION article in books online. There are a number of constraints on the DML that can be used inside of a function. In this case you are being stopped by the constraint that does not allow use of the EXEC ( @.anSQLString ) inside of a function. You are also not allowed to execute stored procedures from inside the body of a function. I would suggest that if you want to run an EXEC ( @.someKindOfString ) that you really ought to use a stored procedure instead of a function. Since the problem is dynamic SQL you might want to first see if you can eliminate the dynamic SQL.

Kent

|||

Following on from Kent, in looking at your query do you actually need to build a dynamic string at all?

Code Snippet

CREATE FUNCTION [dbo].[ReturnPickItemValue]

(

-- Add the parameters for the function here

@.TypeID int,

@.CaseID int

)

RETURNS varchar(max)

AS

BEGIN

-- Declare the return variable here

DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1

BEGIN

SET @.Rtn = (SELECT I.PickItem +

CASE D.IsStartDateEstimated

WHEN 0 THEN CAST(StartDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)

END +

CASE D.IsEndDateEstimated

WHEN 0 THEN CAST(EndDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)

END

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID

LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID

WHERE L.PickTypeID = @.TypeID

AND P.CaseID = @.CaseID)

END

ELSE

BEGIN

SET @.Rtn = ( SELECT I.PickItem

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID

WHERE L.PickTypeID = @.TypeID

AND CaseID = @.CaseID)

END

RETURN @.Rtn

END

HTH!

|||cheers richbrownesq

That works a treat!!

How to Return SqlDataReader and return value (page count) from SPROC

This is my function, it returns SQLDataReader to DATALIST control. Howto return page number with the SQLDataReader set ? sql server 2005,asp.net 2.0

Function get_all_events() As SqlDataReader
Dim myConnection As NewSqlConnection(ConfigurationManager.AppSettings("........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterState As New SqlParameter("@.State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@.pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Variable Pages is global integer.

This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

please any help ? finally following code works without error, but still not returning any return value (pagecount) along with the list of records.

Dim MyReader As SqlDataReader
Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("Dx918Aveb8ax81"))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterregion As New SqlParameter("@.Region", SqlDbType.VarChar, 5)
parameterregion.Value = Request.Params("reg")
myCommand.Parameters.Add(parameterregion)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 10
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
pages = myCommand.Parameters("@.pagecount").Value
DataList1.DataSource = MyReader
DataList1.DataBind()
myCommand.Dispose()
myConnection.Dispose()
Response.Write(pages)

|||Output parameters and return values are not available until after aDataReader is closed (which you should add after your DataBind()).|||thank you. I will try that today!!

how to return name of day 'Monday' from a date?

Hello,
Does sql Server (2k) have a function for returning the name of a day like in
.Net
date.DayOfW.ToString
returns say Monday. Or do I have to use a Case Statement with Datepart?
Thanks,
RichThere's a DATENAME function that's similar to DATEPART but returns names
rather than numbers.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:972DE410-FE73-4248-B1CF-2D65F94B6465@.microsoft.com...
Hello,
Does sql Server (2k) have a function for returning the name of a day like in
.Net
date.DayOfW.ToString
returns say Monday. Or do I have to use a Case Statement with Datepart?
Thanks,
Rich|||Thanks. I knew there was a function. I used it once a ways back. Now need
to use it again. Just couldn't remember.
Thanks.
"Keith G Hicks" wrote:

> There's a DATENAME function that's similar to DATEPART but returns names
> rather than numbers.
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:972DE410-FE73-4248-B1CF-2D65F94B6465@.microsoft.com...
> Hello,
> Does sql Server (2k) have a function for returning the name of a day like
in
> ..Net
> date.DayOfW.ToString
> returns say Monday. Or do I have to use a Case Statement with Datepart?
> Thanks,
> Rich
>
>

How To Return Multiple Table Values From A Function

Hi,
We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.

Create Function F_clusters()
Returns @.ki Table(names Nvarchar(200),total Int),
As
Begin
Insert @.ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Return
End

This Works Fine :-
And Gives The Reqd. Results.

But,

If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.

Create Function F_clusters()
Returns @.ki Table(names Nvarchar(200),total Int),@.k2 Table(names Nvarchar(200),total Int)
As
Begin
Declare @.cnt Int
Set @.cnt = 1
While @.cnt <= 2
If @.cnt =1
Begin
Insert @.ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Set @.cnt = @.cnt + 1
End
If @.cnt =2
Begin
Insert @.k2
Select @.naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
On A.intcustomerid = B.intcustomerid
Where Chremail <> ' ' And Chremail Is Not Null
And Intpreferenceid In (6,7,2,3,12,10)
Set @.cnt2 = @.cnt2 + 1
End
End
Return
End

Can We Return Two Tables Or Is It Not Possible ?
Pls Chk Into This And Tell Me.

Thanks.I don't believe it's possible, and if it is then it's not a good idea.

One of the big advantages of UDF table functions is that you can join them directly into SQL statements.

select * from my table inner join myfunction on mytable.pk = myfunction.pk

Now, what would happen to this if myfunction returned more than one data set?

On a side note, it's nOT nECSSARY tO cAPITALIZE tHE fIRST lETTER oF eACH wORD. In English, just capitalize the first letter of each sentence, and all proper nouns. ;)|||HI,

THANKS FOR THE TIP ON ENGLISH, BUT IS IT POSSIBLE TO GET MORE THAN 1 DATA SET FROM A FUNCTION ... AS I AM REALLY HAVING A PROBLEM WITH IT ?.

THANKS FOR THE HELP ON FUNCTIONS

BUT COULD YOU TELL ME ABOUT HOW TO GET DIFFERENT SET OF DATA FROM THE SAME FUNTIONS.

LETS'S A SIMPLE ONE :-

Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Not Null

Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Null

)

I WILL GET IT RIGHT IF I GIVE THE FIRST SELECT STATEMENT ONLY

BUT IF I USE THE SECOND SELECT STATEMENTS IN THE FUNCTION IT WILL NOT EXECUTE.

SO,IS IT POSSIBLE TO GET TWO DIFFERENT SETS OF DATA FROM A FUNCTION.

IF YES,HOW ?

CAN WE DECLARE TWO TABLES WHICH CAN RETURN VALUES ?
IF YES,HOW?|||//What about a subselect?
//The subselect returns two columns, one with the Null count
//and one with the Not Null count

Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct c1.Chremail) As CNTS
(Select count(*)
//The disctinct does not work with a Null Column, its like counting nothing
From CLEANCustomer As c2
Where c2.Chremail Is Null) As CNTSNull

From CLEANCustomer As c1
Where c1.Chremail Is Not Null
)

//A second way is the Union:
//The Result of the Union are two rows with two fields, one as the count,
//one as the the type
Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct Chremail) As CNTS,
'Not Null' As Nulltype
From CLEANCustomer
Where Chremail Is Not Null
UNION
Select count(*)
'Not Null'
From CLEANCustomer
Where Chremail Is Null
)

/*
This is only SQL, try to take some SQL lessons

Sneaky Pie
*/|||No. You can only return one dataset from a function. Rewrite you code as a stored procedure, which can return multiple datasets:

Create Procedure F_clusters()
AS
Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Not Null

Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Null

...but this sort of thing is not much use in application development. You rethink what you are doing.|||HI,

THANKS FOR THE HELP BY BOTH OF YOU,BUT STILL FACING THE PROBLEM ,

THE subselect FUNCTION IS NOT WORKING :-
I TRIED SEVERAL PERMUTATIONS AND COMBINATIONS

Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct c1.Chremail) As CNTS
(Select count(*)
//The disctinct does not work with a Null Column, its like counting nothing
From CLEANCustomer As c2
Where c2.Chremail Is Null) As CNTSNull

From CLEANCustomer As c1
Where c1.Chremail Is Not Null
)

AND YES YOU ARE RIGHT ABOUT USING STORED PROCS TO GET MULTIPLE DATASETS :-

BUT YOU ARE FORGETTING THOUGH WE HAVE PROCS WHICH GIVES US THE RESULTS , WE ARE TRYING TO CONVERT THEM INTO FUNCTIONS

AND THEREFORE FACING PROBLEMS WITH MULTIPLE DATASETS,

FUNCTION CAN RETURN A TABLE , BUT NOT TWO MULTIPLE DATA SETS
IS THERE A WAYOUT OF IT ?

OR ELSE WE MIGHT HAVE TO WRITE SEVERAL FUNCTIONS TO REPLICATE WHAT 1 PROC RESULTS WAS REFLECTING WHICH WOULD BE A REAL PAIN.

IS THERE A WAY OUT ?|||Why are you so hot to convert your stored procs into functions? Functions are not necessarily better than stored procedures.

I can't shake the feeling that there are underlying problems with your data architecture, application design, or project goals, and unless you resolve these you are not going to be any better off than you were.|||Hi,

We Are Trying To Convert Procs Into Funtions ,so That We Can Invoke Them In Any Other Functions And Use Them As An I/p To Other Procs Or Functions.

But,if We Are To Use Them As Procs Then We Have The Limitation Of Not Using Insert..exec Statement More Than Once.

So, There Fore Functions Are Necessary ,but It Now Seems That They Cannot Give Up More Than 1 Datasets ,which Is Again Causing Us Problems.

Out We Are Trying To Automate The Process.

Can You Give Something Better Option To Work ?

As , It Seems We Are Working Round And Round .|||sorry
I have no idea|||I think that the problem lies in the fact that the routine (stored procedure or function) is trying to return more than one result set.

Any given routine should do exactly one thing. If a routine manipulates data (INSERT/UPDATE/DELETE) it should do that one thing, and nothing more. You may have a few stored procedures that are "wrappers" that call a sequence of other routines, but even they do only one thing, wrap the other routines. Designing your code so that a routine intentionally does a dozen things is a receipe for disaster in my opinion. Any complex thing that works is only a collection of simple things that work!

Your biggest problem in my opinion is that you need to adopt a more disciplined methodology. It appears that you are used to writing "spaghetti code", and are having a hard time breaking that mindset. Although it is considered "old fashioned" by internet standards, I think the simplest way out of the problems you are facing right now is to embrace "structured programming", although you need to keep in mind that there are many other methodologies that offer even more benefits at the cost of more complexity.

I'd suggest that you read Software Tools (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=ZYw1fKKpGI&isbn=020103669X&itm=1) and try to adopt its principles as best you can.

-PatP|||The definition of a FUNCTION is to return ONE THING. That thing can be a value (scalar) or a table (inline or table-valued). Data connectivity (ODBC API's and SQLOLEDB) for SQL offer the ability to retrieve multiple resultsets from a statement execution against a connection object/handle. This is a flexibility feature, not a standard.

We all understand that you "HAVE TO CONVERT BLAH-BLAH-BLAH...", but you have to understand that what everybody tries to offer here is tricks and techniques that are based on specific well known rules that IT industry lives by. Complexity and/or brilliance of solutions varies, but the concept remains. Unless in the future there will be a break-through in technology that would alow sending rockets to the moon with a simple SELECT, or returning more than "ONE THING" from a function, - you'll have to learn how to abide by these industry rules.

And one last thing, - no matter how many more times you choose to post the same question (HOW TO MAKE A FUNCTION RETURN MORE THAN ONE RESULTSET), you will NOT get any more elaborate answers, other than "YOU CAN'T!"|||"We Are Trying To Convert Procs Into Funtions ,so That We Can Invoke Them In Any Other Functions" - A tautological argument

"... And Use Them As An I/p To Other Procs Or Functions." - And how would you use them as input if they returned more than one result set? The problem here is with your development process, and not due to a limitation of SQL.|||Hi,
Thanks Pat ,BLINDMAN & RDJABAROV FOR THE HELP. and HAS YOU SAID ALL ,I Think I Have To Bring In More Discipline In My Programming
AND YES MAYBE GET BACK TO STRUCTURED PROGRAMMING

And Ok !!! Think,it's Time To Break Down Most Of The Routines Into Simple Steps.

BUT, I STILL HOPED THERE WAS A WAY OUT OF IT !!!!!.|||BUT, I STILL HOPED THERE WAS A WAY OUT OF IT !!!!!.If you find a way out, please publish it. The book will make you a very rich person, and the rest of us very happy people!

-PatP

How to return 0 instead of null when using a sum function?

Hi,

I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).

Is there a common way to avoid this?

Thanx

Interesting you should ask. I was looking at the same problemyesterday. My results are displayed in a DataGrid and thankfully itdoesn't barf on the NULL values. It displays -1 instead. Of coursethat's still not desirable as the correct answer for SUM(nothing)should be 0 IMO.
Let's hope someone has a suggestion.
|||You could use the ISNULL function to calculate a 0 whenever the SUM contains a NULL:

SELECT ISNULL(SUM(myColumn,0)) FROM myTable WHERE 0=1

|||

Or, as a slight variation, use ISNULL to replace the result of the SUM for instances where no rows result in an answer of NULL:

SELECT ISNULL(SUM(myColumn),0) FROM myTable WHERE 0 = 1

(Note the different parenthesis placement: tmorton's will replace any NULL value with zero BEFORE aggregation; mine will replace theentire result with 0 AFTER aggregation in the event of a NULL sum).

|||

pjmcb wrote:

Or, as a slight variation, use ISNULL to replace the result of the SUM for instances where no rows result in an answer of NULL:
SELECT ISNULL(SUM(myColumn),0) FROM myTable WHERE 0 = 1
(Note the different parenthesis placement: tmorton's will replace anyNULL value with zero BEFORE aggregation; mine will replace the entireresult with 0 AFTER aggregation in the event of a NULL sum).

Thank you for catching my typo pjmcb! What I actually posted is not even syntactically correct. :-)
I should have copied-and-pasted from Query Analyzer instead of retyping.



|||I didn't think you could do that, but I was too lazy to open up QA to test it out...

how to retrive a SP table inside a SP?

Hello, inside of my SP i want to execute another SP, something like:

EXEC

[dbo].[Forum_DeleteBoard] @.BoardID= @.DelBoardID

this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not
(it returns just one row with 3 columns).

Columns it returns:
QResult , Threads , Answers

SELECT @.isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @.BoardID= @.DelBoardID ?

or how do you get it?

Patrick

you can try to do it by creating temp table and next by do insert from your stored procedure into this temp table. Temp table have to have the same layout as returned table. and you can select value you need from this table.

If you can modify your second procedure try to modify it to return output parameters instead of table result set. It will be simple and much faster.

You can find information about both solutions in SQL help, but if you have a problem with it just post again.

Thanks

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren