how to run this proc in SQL Analyzer?

hi, guys

I have a stored procedure, like that:

CREATE PROCEDURE [dbo].[ViewTitles]
@.UID int,
@.DateStart datetime,
@.DateEnd datetime,
........

When I run this query in SQL Analyzer like this:
ViewTitles 6165, '2006-01-29 10:00:00', '2006-02-29 10:00:00'

It alwasy shows:
Error converting data type varchar to datetime.

If I run it in code, asp.net, or report, no problem at all.
What is wrong with that?

Thanks.

What is the dateformat of the session when logged in through ISQLW? You can verify this by looking at output of DBCC USEROPTIONS. Look for set option "dateformat". In addition to this, you should one of the ISO 8601 formats for datetime literals that should be interpreted correctly irrespective of the language or dateformat settings. Specify the value instead like '2006-01-29T10:00:00'.

How to run DTS from stored proc

I am running a DTS package from stored proc as,

Exec [master].[dbo].[xp_cmdshell] "dtsrun /S Server /U User /P Pass /N Package Name"

But i am getting an error :

The system can not find the path specified.

But i am able to run the same from DTS Design Wizard.

Please anyone of you help me out.set @.str varchar(8000)
set @.dtsname varchar(8000)
set @.dtsname=physical path and name of your dts package
SET @.str='DtsRun '+ '/F ' + @.DTSNAME

EXEC master..xp_Cmdshell @.str


I believe it will help you

Subhasish Ray

subhasishray@.sify.com

How to run a DTS as a trusted user in a stored proc

I get an error when i try to run a dts
but when i create a new dts and select the right server and user
everything went well.
Can someone help me?What is the error you receive ?

This is a cut from a microsoft article which may address your problem:
"Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run. "

Here is the article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;q269074)sql

How to Return a subset of resultset of some stored proc

Hi,

I want to write a query to select some of the columns from result of stored proc,

My expected code will be like that, but its not allowed,

Select first_Name, Last_Name, Last_Used_date from (Execute sp_reportNumber15 '9555')

I have around 30 different stored proc but I know some of the columns are in resultset of every stored proc, So I want to write a generalize stored proc to whom I will just pass stored proc name and it will return me the subset of its result.

Thanks,

Imran.

Imran:

I would first try to convince you to take another approach. Reports -- especially heavy reports -- can be very resource intensive. And to take the results of some 30 different reports and the massage these results and take small subsets of each report and the presenting the results to an end user sounds like doing a lot of work to simplify the coding of a developer. To me this is putting the work in the wrong place.

When I implement a database one of my goals always is to provide service to each request as fast as possible. The implementation you propose does not aim at that goal. To me, the implementation you propose aims at taking a round-about path to gathering the data and hoping that the response to the request might be fast enough. This brings to me visions of dozens of tables being table scanned when the data from much fewer tables is needed. If it were my server I would view this as abuse.

Please, rethink this before you go forward.

Dave

|||

Hi Dave,

I have not written that I want to compile resultset of 30 reports at the same time. The scenario is like as,

One enduser want sometimes 5 specific columns from resultset of sp_report1, sometime he needs same 5 columns from resultset of sp_report2, in actual the number of columns returned in resultsets is different of sp_report1 and sp_report2 but same 5 columns are present in resultset of every sp, like sp_report1 return 30 columns, and sp_report4 returns 15 columns.

I want to write a new stored proc for him, say for example sp_getData 'sp_report1'. By this kind of stored proc he doesnt need to fetch full resultset of every sp, this will reduce network load also,

when user passes the parameter value as sp_report1, then I will return 5 columns from resultset of report1, and when user passes parameter value as 'sp_report15' then I will return 5 columns from resultset of report15

My question is only that, if you have a vision to write this kind of statement, conceptually my statement will be as,

Select Col1, Col4, Col6, Col7, Col9 from (Exec sp_getData @.repnumber)

but syntactically it is wrong, if there is some possibility then just reply otherwise dont waste your time as well as my time.

thanks,

Imran.

|||

convert your sp into function..

so you can do Select Col1, Col4, Col6, Col7, Col9 from fn_getData (@.repnumber) as Data

|||

Thank you so much ... :D

This is exactly what I want...

so nice of you

How to return a partial string based on a particular character?

Hi,
I am looking through books on-line but an not finding what I am looking for.
In my stored proc, I am being passed a varchar field, 20 long. It looks
something like, '103098-1'
I need to split the characters on the left side of the '-' into one field,
and the characters on the right side of the '-' into another field.
How do I do this?
Thanks,
Steve
This is how I did it, does this make sense, or is there an easier way?
Declare @.strOrder varchar(20)
set @.strOrder = '38372-1'
set @.charIndex = CHARINDEX('-', @.strOrder)
set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
@.charIndex))
Thanks again.
"SteveInBeloit" wrote:

> Hi,
> I am looking through books on-line but an not finding what I am looking for.
> In my stored proc, I am being passed a varchar field, 20 long. It looks
> something like, '103098-1'
> I need to split the characters on the left side of the '-' into one field,
> and the characters on the right side of the '-' into another field.
> How do I do this?
> Thanks,
> Steve
|||yes, it could be done in a single line though.
Declare @.strOrder varchar(20)
declare @.left varchar(10)
declare @.right varchar(10)
set @.strOrder = '38372-1'
select @.left = left(@.strOrder, CHARINDEX('-', @.strOrder)-1),
@.right=substring(@.strOrder,
charindex('-',@.strOrder)+1,len(@.strOrder)-charindex('-',@.strOrder)+1)
print @.left
print @.right
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:A9247AFD-686A-46BE-AE7B-225E813FBCA1@.microsoft.com...[vbcol=seagreen]
> This is how I did it, does this make sense, or is there an easier way?
> Declare @.strOrder varchar(20)
> set @.strOrder = '38372-1'
> set @.charIndex = CHARINDEX('-', @.strOrder)
> set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
> Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
> @.charIndex))
> Thanks again.
> "SteveInBeloit" wrote:
for.[vbcol=seagreen]
looks[vbcol=seagreen]
field,[vbcol=seagreen]

how to retun results from Stored Proc

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

Quote:

Originally Posted by Talghagin

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

try a function instead|||

Quote:

Originally Posted by Talghagin

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

I think if you take out the Return at the end then stored procedure will return the result of the query.

How to retrieve @@Rowcount variable

I'm trying to output the number of rows that were effected by my stored proc.

Here is the stored proc:

ALTER Proc Update_IndividualMoves_GTPhone_NCOAPhone_Differ
AS
Update Results
SET Results.home_phone = Results.NEWPhone,
Results.Address1 = Results.NCOAADDRESS1,
Results.CITY = Results.NCOACITY,
Results.ST = Results.NCOAST,
Results.ZIP_OUT = Results.NCOAZIP5,
Results.ZIP4_OUT = Results.NCOAZ4
Where AddressServiceStatus = 'I' AND home_phone IS NOT NULL AND NEWPhone IS NOT NULL AND home_phone <> NEWPhone
Return @.@.Rowcount

Here is the code from the DAL class that I'm calling the stored procedure from (I'm using the SQL Helper Class.)

 Public Shared Function GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ()
 Dim Rowcount As Integer
 Dim GlobalConnString As String = AppSettings("ConnectionString")
 ''Put proc in that gets this data out for household moves that have both
 ''GTPro and NCOA update phone numbers however they differ. Does not apply
 ''to DRC donors / < 12 month donors. Update to latest and greatest phone number
 ''from NCOA listing.
 Try
 Return ExecuteDataset(GlobalConnString, CommandType.StoredProcedure, "Update_HouseholdMoves_GTPhone_NCOAPhone_Differ", New SqlParameter("@.@.Rowcount", Rowcount))
 Catch ex As Exception
 Throw New ApplicationException("An error occured when calling this stored proc out Update_HouseholdMoves_GTPhone_NCOAPhone_Differ")
 End Try
 End Function

I want to post how many rows were effected in a label that is located on my aspx page through referencing the function above:

What I'm doing is activating the function through an asp:button control and then I want to display the @.@.Rowcount result in the label next to it.

Here is what I have now:

<code
Private Sub cmdHouseholdMove2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHouseholdMove2.Click
'Dim rowcount As Integer
GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ(New SqlParameter("@.@.rowcount", lblHouseholdMoves2.Text))

End Sub

If anyone knows how to do this please let me know:

Thanks in advance everyone.

Regards,
RByou can use an OUTPUT parameter to return the rowcount...check books on line for sample code.
some sample code for retreiving the output parameter from asp.net..

dim result as integer
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)
result = convert.toint16(mycommand.Parameters("@.result").Value))

hth