Can I GROUP BY aggregate Function (Like SUM)

Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yetyes, with a derived table, but why would you want to?

please show an example, using sample data to illustrate|||this is my case:

i have a table with a column datetime (I use to record time of calls) , i want to create table that hold data for each hour, so i round the column and want to group by this column in the same time|||you can round a datetime? please show your query|||This is the Query of round datetime

DATEADD(Hour, DATEDIFF(Hour, 0, cdrCallDate), 0)

You can change [hour] to day , minutes , second , etc...|||yes, you can GROUP BY that expression :)

and you cannot use second, it causes an overflow

:)|||thanks man for this information

Can I do Regular Expressions using TSQL in SQL Server2005?

I am running PHP with SQL Server 2005. I have [bold] in some of the values in my result set, which needs to be striped out. I had a function in SQL Server2000 that did that, but the same function does not work in SQL Server2005. Any dieas?

CREATE FUNCTION

dbo.fn_regex(@.pattern varchar(255), @.matchstring varchar(8000))

RETURNS int

AS

BEGIN

declare @.obj int

declare @.res int

declare @.match bit

set @.match=0

exec @.res=sp_OACreate 'VBScript.RegExp',@.obj OUT

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OASetProperty @.obj, 'Pattern', @.pattern

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OASetProperty @.obj, 'IgnoreCase', 1

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OAMethod @.obj, 'Test',@.match OUT, @.matchstring

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OADestroy @.obj

return @.match

END

GO

When you say the functiuon doesn't work in SQL 2005; in what way doesn't it work? I see that you are using sp_OAxxx and friends; you are aware that you need to explicitly enable sp_OAxxx in S2K5, as a server setting?

Also, youwould be much, much, much better off using the CLR integration to do your stuff than sp_OA

Niels
|||Thanks for your response nielsb. Do you know if I can use CLR integration in a TSQL function. I am using PHP with SQL Server, so I do not have the option to use VB.net or C#?

Thanks!|||I don't fully understand the question? But, you can have s function written in C#, VB.NET, that's doing your stuff, and that function can be called by some T-SQL functions.

Niels
|||I cannot write the function in VB.NET or C#, as I am using PHP with SQL Server 2005 (which leave me the only option of write a TSQL function)|||Hmm, I still don't understand why you can not write a VB.NET or C# function. I.e. SQL Server 2005 hosts the CLR. You write a CLR function doing your regex stuff, this function is hosted by SQL Server and exposed by a T-SQL function. Your PHP code then calls the T-SQL function. Or am I totally off-track here?

Niels
|||Thanks for the response Niels! Do you think I can write a C# or VB.NET CLR function and call it within TSQL? Could you maybe give me an example of a CLR function. I have not used this before. your response is very much appreciated!

Can Grow function

Hi folks,

I am a beginner in Crystal report, I have tried to use the Can Grow function and it work fine. But will anyone know how to make/trigger other fields to grow together to the same height when particular field is grown? or is there any function to change the height on the field via formula?

Many thanks,
HaroldI am having the same issue - Did you happen to figure this out?

Can get RDL file name?

Hi,

Is any way can get RDL file name and show on text box?

Any function or something else?

Thanks for any advices!

As the data is tranfered to sql server the report file does not exists anymore as a file. The file that you can download from the web interface is simply composed by reportname + rdl extension.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

The ReportObjectModel exposed to RDL expressions contains several collections (see also: http://msdn2.microsoft.com/en-us/library/ms157274.aspx). One of them is the Globals collections:

Member

Type

Description

ExecutionTime

DateTime

The date and time that the report began to run.

PageNumber

Integer

The current page number. Can be used only in page header and footer.

ReportFolder

String

The full path to the folder containing the report. This does not include the report server URL.

ReportName

String

The name of the report as it is stored in the report server database.

ReportServerUrl

String

The URL of the report server on which the report is being run.

TotalPages

Integer

The total number of pages in the report. Can be used only in page header and footer.

The following expression would show you the path and report name of the current report:

=Globals.ReportFolder & Globals.ReportName

-- Robert

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:

> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>

Can an adapter handle a stored procedures?!

Can someone please help on this issue and tell me how to amend this code:
<WebMethod()> _
Public Function GetRecord(ByVal anyname As String) As DataSet
Dim adapter As New SqlDataAdapter
Dim result As New DataSet
adapter.SelectCommand.Connection = Conn
adapter.SelectCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand.CommandText = "GetSingleName"
adapter.SelectCommand.Parameters.Add("@.myname", SqlDbType.VarChar)
adapter.SelectCommand.Parameters("@.myname").Direction =
ParameterDirection.Input
adapter.SelectCommand.Parameters("@.myname").Value = anyname
adapter.Fill(result, "nabData")
Return result
This code is supposed to resturn dataset filled by an adapter that uses a
stored procedure called "GetSingleName". The Database table is "nabData". Th
e
connection is established through the GUI and is named Conn.
Many thanks in advance.Nab wrote:
> Can someone please help on this issue and tell me how to amend this code:
> <WebMethod()> _
> Public Function GetRecord(ByVal anyname As String) As DataSet
> Dim adapter As New SqlDataAdapter
> Dim result As New DataSet
> adapter.SelectCommand.Connection = Conn
> adapter.SelectCommand.CommandType = CommandType.StoredProcedure
> adapter.SelectCommand.CommandText = "GetSingleName"
> adapter.SelectCommand.Parameters.Add("@.myname", SqlDbType.VarChar)
> adapter.SelectCommand.Parameters("@.myname").Direction =
> ParameterDirection.Input
> adapter.SelectCommand.Parameters("@.myname").Value = anyname
> adapter.Fill(result, "nabData")
> Return result
> This code is supposed to resturn dataset filled by an adapter that uses a
> stored procedure called "GetSingleName". The Database table is "nabData".
The
> connection is established through the GUI and is named Conn.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
This is a question, better answered in a VB.NET newsgroup.
Wrist slapping done...
An example in C# (untested):
// create connection
String connString = "Data Source=(local);Integrated security=SSPI;" +
"Initial Catalog=Northwind;";
SqlConnection conn = new SqlConnection(connString);
// create a Command object based on a stored procedure
String selectSql = "MyStoredProcedure";
SqlCommand selectCmd = new SqlCommand(selectSql, conn);
selectCmd.CommandType = CommandType.StoredProcedure;
// create and set the parameter for the stored procedure
selectCmd.Parameters.Add("@.CustomerID", SqlDbType.NChar, 5);
selectCmd.Parameters["@.CustomerID"].Value = "VINET";
SqlDataAdapter da = new SqlDataAdapter(selecteCmd);
// create a new DataSet to receive the data
DataSet ds = new DataSet();
// read the data from stored procedure & load it into the DataSet
da.Fill(ds);
A good ADO.NET reference is _ADO.NET In a Nutshell_ (pub: O'Reilly).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhjdeoechKqOuFEgEQIg9QCgxmdNXsVA6uhC
wOZGYdYzwpAW+E4An2q8
rXQKtPYE2AAus6bK5zAe4beW
=IcRz
--END PGP SIGNATURE--

Can a stored procedure use the OpenRowSet function to get DB2 data

If so, does anyone have an example?
Background: We have a MSFT Reporting Services report that uses the
OpenRowSet function to get both DB2 and SQL Server data in one SQL statement
.
Since userid and psw are parms to this function, it is not an option to
hard-code them into the OpenRowSet parms.
So our solution is to write a stored procedure that the report will call.
This stored procedure will have the SQL statement, including the OpenRowSet
function WITH the userid and psw hard-coded in, and then the stored procedur
e
will be encrypted.
I'm presently writing the stored procedure and read the following in the
article entitled "External Data and Transact-SQL" (SQL Server books online):
"Stored procedures are supported only against SQL Server data sources."
Here's the stored procedure I'm writing. It doesn't pass the Syntax Checker
yet, so whatever assistance anyone can provide would be great.
Thanks!
========================================
==========
CREATE PROCEDURE SSP_AUTHS_BY_FI_BY_ACCT_TXT
@.inst varchar,
@.stat char(1),
@.ret_cd char(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @.err_cd smallint,
@.rowcount int,
@.error int
SET @.ret_cd = 0
SELECT DISTINCT DB1.EFF_STAT,
DB1.FIN_INST_NM,
DB1.ADDR_LINE_1,
DB1.ADDR_LINE_2,
DB1.ADDR_LINE_3,
DB1.ADDR_CTY,
DB1.ADDR_ST,
DB1.ADDR_POSTAL_CD,
DB1.PHONE_NR,
DB1.CONTACT_NM,
DB1.ACCT_NR,
DB1.ACCT_NM_1,
DB1.ACCT_NM_2,
DB1.PERMVAL_NM,
DB1.EMP_AUTH_NM,
DB1.NE_AUTH_NM,
DB1.NON_EMP_INDIC,
DB1.COUNTRY
FROM OPENROWSET
('IBMDADB2'
,'dsnp_r';'userid';'passwd'
,'''SELECT DISTINCT DBT1.EFF_STAT,
DBT3.FIN_INST_NM,
DBT3.ADDR_LINE_1,
DBT3.ADDR_LINE_2,
DBT3.ADDR_LINE_3,
DBT3.ADDR_CTY,
DBT3.ADDR_ST,
DBT3.ADDR_POSTAL_CD,
DBT3.PHONE_NR,
DBT3.CONTACT_NM,
DBT1.ACCT_NR,
DBT1.ACCT_NM_1,
DBT1.ACCT_NM_2,
AUTH.AUTH_STAT,
AUTH.PERMVAL_NM,
AUTH.NON_EMP_INDIC,
AUTH.EMP_AUTH_NM,
AUTH.NE_AUTH_NM,
DBT50.PERMVAL_NM AS COUNTRY
FROM TRSPROD.CTRS01_ACCOUNT_TB DBT1
INNER JOIN TRSPROD.CTRS03_FININST_TB DBT3
ON DBT1.FIN_INST_ID_NR = DBT3.FIN_INST_ID_NR
LEFT JOIN (SELECT DISTINCT A.ACCT_ID_NR
, A.NON_EMP_INDIC
, C.NAME AS EMP_AUTH_NM
, D.NAME AS NE_AUTH_NM
, A.EFF_STAT AS AUTH_STAT
, B.PERMVAL_NM
FROM TRSPROD.CTRS04_AUTHRITY_TB A
INNER JOIN TRSPROD.CTRS50_PERMVAL_TB B
ON A.AUTH_TYPE_ID_NR = B.PERMVAL_ID_NR
LEFT JOIN BACPROD.PS_PERSONAL_DATA C
ON A.EMPLID = C.EMPLID
LEFT JOIN TRSPROD.CTRS20_NONEMP_TB D
ON A.EMPLID = D.NON_EMPLID
WHERE A.EFF_DT = (SELECT MAX(EFF_DT)
FROM TRSPROD.CTRS04_AUTHRITY_TB E
WHERE A.ACCT_ID_NR = E.ACCT_ID_NR)) AUTH
ON DBT1.ACCT_ID_NR = AUTH.ACCT_ID_NR
LEFT JOIN TRSPROD.CTRS50_PERMVAL_TB DBT50
ON DBT1.CNTRY_ID_NR = DBT50.PERMVAL_ID_NR'') DB1
WHERE (DB1.FIN_INST_NM LIKE ''' + @.INST + '%'')
AND (DB1.AUTH_STAT = ''A'')
AND (DB1.EFF_STAT LIKE ''' + @.STAT + '%'')
ORDER BY DB1.EFF_STAT
, DB1.FIN_INST_NM
, DB1.ACCT_NR
, DB1.PERMVAL_NM''
) DB2')
Select @.error=@.@.error, @.rowcount=@.@.rowcount
IF @.error <> 0
BEGIN
RAISERROR('Read of database failed ',16,1)
SET @.ret_cd = 1
RETURN 1
END
IF @.rowcount = 1
BEGIN
SET @.ret_cd = 0
RETURN 0
END
SET NOCOUNT OFF
RETURN 0
/ ****************************************
***********************************
********************
******************************* End of Procedure SQL
****************************************
***
****************************************
************************************
*******************/
END
GO
========================================
============
--
Joe Palm
Senior Technical Developer
Madison, WICan you add a linked server?, this way you do not have to embed the userid
and password in your query.
Creating a linked server to DB2 using Microsoft OLE DB provider for DB2
http://support.microsoft.com/defaul...kb;en-us;222937
AMB
"Joe Palm" wrote:

> If so, does anyone have an example?
> Background: We have a MSFT Reporting Services report that uses the
> OpenRowSet function to get both DB2 and SQL Server data in one SQL stateme
nt.
> Since userid and psw are parms to this function, it is not an option to
> hard-code them into the OpenRowSet parms.
> So our solution is to write a stored procedure that the report will call.
> This stored procedure will have the SQL statement, including the OpenRowSe
t
> function WITH the userid and psw hard-coded in, and then the stored proced
ure
> will be encrypted.
> I'm presently writing the stored procedure and read the following in the
> article entitled "External Data and Transact-SQL" (SQL Server books online
):
> "Stored procedures are supported only against SQL Server data sources."
> Here's the stored procedure I'm writing. It doesn't pass the Syntax Check
er
> yet, so whatever assistance anyone can provide would be great.
> Thanks!
> ========================================
==========
> CREATE PROCEDURE SSP_AUTHS_BY_FI_BY_ACCT_TXT
> @.inst varchar,
> @.stat char(1),
> @.ret_cd char(1) OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.err_cd smallint,
> @.rowcount int,
> @.error int
> SET @.ret_cd = 0
> SELECT DISTINCT DB1.EFF_STAT,
> DB1.FIN_INST_NM,
> DB1.ADDR_LINE_1,
> DB1.ADDR_LINE_2,
> DB1.ADDR_LINE_3,
> DB1.ADDR_CTY,
> DB1.ADDR_ST,
> DB1.ADDR_POSTAL_CD,
> DB1.PHONE_NR,
> DB1.CONTACT_NM,
> DB1.ACCT_NR,
> DB1.ACCT_NM_1,
> DB1.ACCT_NM_2,
> DB1.PERMVAL_NM,
> DB1.EMP_AUTH_NM,
> DB1.NE_AUTH_NM,
> DB1.NON_EMP_INDIC,
> DB1.COUNTRY
> FROM OPENROWSET
> ('IBMDADB2'
> ,'dsnp_r';'userid';'passwd'
> ,'''SELECT DISTINCT DBT1.EFF_STAT,
> DBT3.FIN_INST_NM,
> DBT3.ADDR_LINE_1,
> DBT3.ADDR_LINE_2,
> DBT3.ADDR_LINE_3,
> DBT3.ADDR_CTY,
> DBT3.ADDR_ST,
> DBT3.ADDR_POSTAL_CD,
> DBT3.PHONE_NR,
> DBT3.CONTACT_NM,
> DBT1.ACCT_NR,
> DBT1.ACCT_NM_1,
> DBT1.ACCT_NM_2,
> AUTH.AUTH_STAT,
> AUTH.PERMVAL_NM,
> AUTH.NON_EMP_INDIC,
> AUTH.EMP_AUTH_NM,
> AUTH.NE_AUTH_NM,
> DBT50.PERMVAL_NM AS COUNTRY
> FROM TRSPROD.CTRS01_ACCOUNT_TB DBT1
> INNER JOIN TRSPROD.CTRS03_FININST_TB DBT3
> ON DBT1.FIN_INST_ID_NR = DBT3.FIN_INST_ID_NR
> LEFT JOIN (SELECT DISTINCT A.ACCT_ID_NR
> , A.NON_EMP_INDIC
> , C.NAME AS EMP_AUTH_NM
> , D.NAME AS NE_AUTH_NM
> , A.EFF_STAT AS AUTH_STAT
> , B.PERMVAL_NM
> FROM TRSPROD.CTRS04_AUTHRITY_TB A
> INNER JOIN TRSPROD.CTRS50_PERMVAL_TB B
> ON A.AUTH_TYPE_ID_NR = B.PERMVAL_ID_NR
> LEFT JOIN BACPROD.PS_PERSONAL_DATA C
> ON A.EMPLID = C.EMPLID
> LEFT JOIN TRSPROD.CTRS20_NONEMP_TB D
> ON A.EMPLID = D.NON_EMPLID
> WHERE A.EFF_DT = (SELECT MAX(EFF_DT)
> FROM TRSPROD.CTRS04_AUTHRITY_TB E
> WHERE A.ACCT_ID_NR = E.ACCT_ID_NR)) AUTH
> ON DBT1.ACCT_ID_NR = AUTH.ACCT_ID_NR
> LEFT JOIN TRSPROD.CTRS50_PERMVAL_TB DBT50
> ON DBT1.CNTRY_ID_NR = DBT50.PERMVAL_ID_NR'') DB1
> WHERE (DB1.FIN_INST_NM LIKE ''' + @.INST + '%'')
> AND (DB1.AUTH_STAT = ''A'')
> AND (DB1.EFF_STAT LIKE ''' + @.STAT + '%'')
> ORDER BY DB1.EFF_STAT
> , DB1.FIN_INST_NM
> , DB1.ACCT_NR
> , DB1.PERMVAL_NM''
> ) DB2')
> Select @.error=@.@.error, @.rowcount=@.@.rowcount
> IF @.error <> 0
> BEGIN
> RAISERROR('Read of database failed ',16,1)
> SET @.ret_cd = 1
> RETURN 1
> END
> IF @.rowcount = 1
> BEGIN
> SET @.ret_cd = 0
> RETURN 0
> END
> SET NOCOUNT OFF
> RETURN 0
> / ****************************************
*********************************
**********************
> ******************************* End of Procedure SQL
> ****************************************
***
> ****************************************
**********************************
*********************/
> END
> GO
> ========================================
============
> --
> Joe Palm
> Senior Technical Developer
> Madison, WI

Can a stored procedure use the OpenRowSet function to get DB2

Linked servers was our first thought, but we don't have that. We'd have to
purchase MSFT Integration Server to get linked servers to work w/our DB2
databases, and we haven't purchased that.
So the linked server route is not an option for us at this time.
Other ideas?
--
Joe Palm
Senior Technical Developer
Madison, WI
"Alejandro Mesa" wrote:
> Can you add a linked server?, this way you do not have to embed the userid
> and password in your query.
> Creating a linked server to DB2 using Microsoft OLE DB provider for DB2
> http://support.microsoft.com/defaul...kb;en-us;222937
>
> AMB
> "Joe Palm" wrote:
>Joe,
The report will pull the data executing a sp in sql server, so that is not
an impediment. The problem with this solution is that encrypting the code of
the sp is not as safe as we think and the userid and password can be taken
from that code.
How can I decrypt a SQL Server stored-procedure?
http://www.mssqlcity.com/FAQ/Devel/DecryptSP.htm
AMB
"Joe Palm" wrote:
> Linked servers was our first thought, but we don't have that. We'd have t
o
> purchase MSFT Integration Server to get linked servers to work w/our DB2
> databases, and we haven't purchased that.
> So the linked server route is not an option for us at this time.
> Other ideas?
> --
> Joe Palm
> Senior Technical Developer
> Madison, WI
>
> "Alejandro Mesa" wrote:
>

Can a Stored Procedure called from an Inline Table-Valued Function

Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

Hi,

you cannot call a stored procedure from a function. however you can call that function from a view.

|||No stored procedure you cannot call inside a query|||Thanks for the reply guys I really appreciate that. But I was just wondering if there is a way I can access the stored proc results from a view.

Can a sql 2005 function return more than a variable

Hi,

I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :

SELECT *, dbo.fn_GetDistance (...) AS Distance

In this function, i have a Latitude and i want this Latitude to be also returned.

It is possible or a function can return only one variable?

If it is possible, what's the syntax of it?

Thanks in advance

You can return a table, something like this:

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEFUNCTION LongLatDistance

(

-- Add the parameters for the function here

@.ZipCode1varchar(10),@.ZipCode2varchar(10)

)

RETURNS @.ResultTableTABLE(LongitudeDecimal(18,6),Latitudedecimal(18,6),Distancedecimal(18,6))

AS

BEGIN

INSERTINTO @.ResultTable (Longitude,Latitude,Distance) SELECT FieldsFROM TableName

RETURN

END

GO

|||

And i call it how from the stored procedure?

|||

SELECT Longitude,Latitude,Distance FROM dbo.LongLatDistance('90210',92630')

I wasnt clear on what exactly else you wanted to return. The sample is only returning the coords of one zip code, you would modify it obviously for your situation.

Can @@ROWCOUNT return NULL?

SQL Server 2000 SP3.

Is it possible for the @.@.ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @.@.ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.

Before doing any further troubleshooting, I would like to rule out the
possibility that @.@.ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.

Any related information would be appreciated.

--
remove a 9 to reply by emailDimitri Furman (dfurman@.cloud99.net) writes:
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT
> to a local variable. That information eventually is passed back to the
> client via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot
> be reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL
> Server 7.0 this could happen in the context of a DML query on a table
> with triggers. This is not the case here - the only DML queries are
> INSERTs into table variables, all other queries in the SP are SELECTs.

I have never heard of a case where @.@.rowcount can return NULL. Books
Online gives one hint when @.@.rowcount is not good: when more than two
milliard rows can be affected. In this case, you should try
rowcount_big(). Could this apply to you?

If not, I would recommend that you start troubleshooting. If it is not
repeatable, it will certainly be difficult...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try removing
SET NOCOUNT ON
from the stored procedure.

GeoSynch

"Dimitri Furman" <dfurman@.cloud99.net> wrote in message
news:Xns96C19F85E9F56dfurmancloud99@.127.0.0.1...
> SQL Server 2000 SP3.
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT to
> a local variable. That information eventually is passed back to the client
> via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot be
> reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL Server
> 7.0 this could happen in the context of a DML query on a table with
> triggers. This is not the case here - the only DML queries are INSERTs into
> table variables, all other queries in the SP are SELECTs.
> Any related information would be appreciated.
> --
> remove a 9 to reply by email|||GeoSynch (SpamSlayed@.Casablanca.com) writes:
> Try removing
> SET NOCOUNT ON
> from the stored procedure.

@.@.rowcount should always return a value even if NOCOUNT is on. This
option controls whether rowcount information is passed to the client.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

callinga web service?

Hi All
can i in call a web service from SP/function
(without using COM SP's) in any way ?
and represent the returned xml as a resultset ?
TIA
Danny
I don't think this is possible in SQL Server 2000 without using the sp_OA
procs or writing an extended stored proc.
In Yukon, you can write a CLR UDF.
Best regards
Michael
"Danny" <danny.ravid@.ness.com> wrote in message
news:eb2d01c43cee$7257dba0$a301280a@.phx.gbl...
> Hi All
> can i in call a web service from SP/function
> (without using COM SP's) in any way ?
> and represent the returned xml as a resultset ?
> TIA
> Danny

Calling WebServices Asynchronously from SQL CLR

I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?

I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.

There is a fundamental flaw in using SQL CLR in this fashion. For starters, the thread you spawn is not visible through any management tools for SQL Server. So if the thread runs amuck, there is no way (that I have found yet) to kill that thread. Of course, you will need to deploy an UNSAFE assembly.

As for asynchronous call from SQL CLR, I would be willing to bet a very small amount of money (since I haven't ried it myself, mind you) that it isn't possible.

So can I ask, what exactly is it that you are trying to do? Perhaps there is another way to find a solution to your issue that doesn't involve the techniques you have already tried doing.

Calling WebServices Asynchronously from SQL CLR

I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?

I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.

There is a fundamental flaw in using SQL CLR in this fashion. For starters, the thread you spawn is not visible through any management tools for SQL Server. So if the thread runs amuck, there is no way (that I have found yet) to kill that thread. Of course, you will need to deploy an UNSAFE assembly.

As for asynchronous call from SQL CLR, I would be willing to bet a very small amount of money (since I haven't ried it myself, mind you) that it isn't possible.

So can I ask, what exactly is it that you are trying to do? Perhaps there is another way to find a solution to your issue that doesn't involve the techniques you have already tried doing.

Calling VBA functions from SQL

I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?Plop the VBA script into an Active-X task within a DTS package, and you should be "good to go". Another choice that folks often overlook is to make the VBA an ActiveX Script step in a SQL Agent job.

-PatP|||Thanks, Pat.

I was thinking along those lines, so I did a little more research while waiting for an answer. It appears that VBScript (and ActiveX??) doesn't like arrays, especially the dynamic variety. I think what I'm trying to do may be misuse of a sproc, but since I want the task to run as part of a rather large string of scheduled tasks, I'm stuck with converting it.

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

You cannot use SET or EXEC with a UDF. You need to use

SELECT @.ReturnVar = [dbo].fnYourFunctionName ( @.InputVar )

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||

I beleive this post belongs in the SS Integration Services Forum.

Alle

|||

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.

HTH
Alle

|||

Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.

The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.

Thanks

Neil

|||Moved to the SQL Server integration services forum :).|||

Did you resolve this? I'm having the same prob. too!

Cheers,

Tamim.

|||

Tamin,

The origianl poster seems to be providing a worng sintax to call the function...

Can you provide the syntax your are using and the error generated?

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||

I beleive this post belongs in the SS Integration Services Forum.

Alle

|||

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.

HTH
Alle

|||

Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.

The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.

Thanks

Neil

|||Moved to the SQL Server integration services forum :).|||

Did you resolve this? I'm having the same prob. too!

Cheers,

Tamim.

|||

Tamin,

The origianl poster seems to be providing a worng sintax to call the function...

Can you provide the syntax your are using and the error generated?

Calling user-defined function without dbo. -- possible?

Is it possible to call a user-defined function without prefixing it
with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
big issue but just a stylistic one for me.

Thanks!

Joel Thornton ~ <groups@.joelpt.eml.cc>You can do so with table function. However, you will have to specify an owner if
you're calling a scalar function. This is to allow sqlserver to distinguish an
udf as opposed to system function.

See if this helps:

create function dbo.scalar()
returns int
as
begin
return(select 123)
end
go
create function dbo.tb()
returns table
as
return(select top 5 * from Northwind..Orders)
go
select 'bad:'+cast(scalar() as varchar)
go
select 'good:'+cast(dbo.scalar() as varchar)
go
select * from tb()
go
select * from dbo.tb()
go
drop function dbo.tb,dbo.scalar
go

--
-oj
http://www.rac4sql.net

"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401091144.40d9f8de@.posting.google.c om...
> Is it possible to call a user-defined function without prefixing it
> with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
> big issue but just a stylistic one for me.
> Thanks!
> Joel Thornton ~ <groups@.joelpt.eml.cc