retrieve Datetime

Friday, March 30, 2012

Return Code 62309

I'm using SQL Lightspeed for Log shipping and I keep getting the following r
eturncode when calling their xp_backup_database procedure.
The error message says it's a sql returncode. Is this a syntax returncode?
Any know?
ThanksI havent worked with sql litespeed, but given that transaction log backups
are going on, pls check the sql error logs on both the Primary and
Secondary to see if there is a problem with the tran logs.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Return Code 62309

I'm using SQL Lightspeed for Log shipping and I keep getting the following returncode when calling their xp_backup_database procedure.
The error message says it's a sql returncode. Is this a syntax returncode? Any know?
ThanksI havent worked with sql litespeed, but given that transaction log backups
are going on, pls check the sql error logs on both the Primary and
Secondary to see if there is a problem with the tran logs.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Return Code 62309

I'm using SQL Lightspeed for Log shipping and I keep getting the following returncode when calling their xp_backup_database procedure.
The error message says it's a sql returncode. Is this a syntax returncode? Any know?
Thanks
I havent worked with sql litespeed, but given that transaction log backups
are going on, pls check the sql error logs on both the Primary and
Secondary to see if there is a problem with the tran logs.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
sql

Return bottom 10 rows not working.

Ok so this is more then likely very easy for most. But for me it's hard. I have a stored proc that I've been able to get to retun just the top 10 rows and have been able to order it asending. But it will only return the top 10 and I need to bottom 10 of the selection.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[proj_ListFiles](@.project_id int)
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id

How can I make it return to bottom 10. On a side note about this, when it returns the recordset they all have the 'id' of the 'project_id' that I selected. Not that the side note really bothers me as it will not be returning the ID's to be visible.

Any help would be great,
Thanks
Tim

Hi,

To do this, you need to add an ORDER BY clause to your select statement and set it to be DESCENDING.

ALTER proc [dbo].[proj_ListFiles](@.project_id int)
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id
order by project_id desc

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Hi,

in addition to Geert, be aware that TOP makes no sense without any order clause unless you want to get a set of (possible) random rows out of the database.

HTH; Jens K. Suessmeyer.

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

Ok I've added the order by project_id desc to the statement, but it's still only returning the same 10 rows is ascending order, it'a also changing the ID to the value I queried in this case 12. So it's sorting all of the as 12. I was looking at another and added something and it made it so my results flip around and sort ascending but are returning rows 2-11 not 1-10.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
ALTER proc [dbo].[proj_ListFiles](@.project_id int = 0 )
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id
or @.project_id = 0
order by project_id desc

Any ideas, what i'm trying to do is limit the results so that way only the last 10 entries of that same project_id will return and not all/the first 10. It will make for easier viewing for me.

By the way thanks for the help.
Tim

|||

Hi,

Since project_id is the same for all the records, you need to sort on a different field then that. For example if you want to have the 10 largest filenames, you perform an ORDER BY length DESC.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Well I figured it out with a little help from a co worker. Not sure if it needs it or not but I just set rowcount to 10 and removed the (@.project_id) from the top 10 command and it works like a dream (a really weird nerdy dream).

ALTER proc proj_ListFiles(@.project_id int)

set rowcount 10

select top 10 id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

Thanks for the help.

Tim

|||

The procedure mentioned above was wrong, it took the projectId as a static expression leading to the fact that it produced 10 time the number you put in as project id, e.g.

SELECT TOP 10(10) From sys.sysobjects

10
10
10
10
10
10
10
10
10
10

YOu will not have to set SET ROWCOUNT, just use the Top with either the syntax

select top 10 id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

(Which is only supported by SQL Server 2005 as backward compatibility)

select top (10) id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

Which is new in SQL Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you very much for this reply, and yes you are correct I didn't have to use 'set rowcount 10' it was removed within minutes of my previous post. Another coworker that had helped me on it yesterday showed me that you did not have to use it. So the second I got home (10 minutes ago) I updated the stored proc and it worked perfect. Now I just hope the rest of them go easier than that.

Thanks again every one.
Tim

Return BIGINT OUTPUT param to VB!?

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage bigint OUTPUT
AS
...
...
SELECT @.WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)
FROM
TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND [PDF].[WD-WE] = @.InWeekDay
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.Recordset
Dim objAdoRecordset As ADODB.Recordset
Dim objAdoCommand As ADODB.Command
Dim lngCtr As Long
On Error GoTo RunCommand_Error

' Create cmd object
Set objAdoCommand = New ADODB.Command
Set objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.CommandText = v_strStoredProcName
objAdoCommand.CommandType = adCmdStoredProc
Call objAdoCommand.Parameters.Refresh
'Stop
For lngCtr = 0 To UBound(r_varParamValues)
If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then
objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr)
End If
Next

Set objAdoRecordset = New ADODB.Recordset
objAdoRecordset.CursorLocation = adUseClient

Set objAdoRecordset = objAdoCommand.Execute
'Stop
For lngCtr = 0 To objAdoCommand.Parameters.Count - 1
If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then
r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value
End If
Next
Set RunProcedure = objAdoRecordset
RunCommand_Exit:
' Collect your garbage here
Exit Function
RunCommand_Error:
' Collect your garbage here
Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")
End Function

PLEASE HELP.

Regards,

Sander

Grrrr...If I add the following line (before the DROP) to the sp it returns the value!

SET @.WeightedAverage = 9587558855

?! Que Pasa ?!

return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a limit
>> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
>> stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>>I try to build a VB.NET class with a Store proc. I specify the name of
>>the table to my SP then it return's a string. If I Copy/Paste the string
>>in a VB file, I have a fully fonctional class that represent my table. So,
>>I have writen a template that I use in my SP. My template has 38000
>>characters. In some place, in my template, there are speacials words that
>>are going to be replace when the SP is execute. When I execute the SP
>>without parameters, my template is return entirely but with no
>>modification in the template. When I execute the SP with a parameter, only
>>the 8000 first characters are return with the modification. I don't run
>>the SP in Query analyser but in a VB.NET programm that I did. How can I
>>return a big string with parameters?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message
>> de news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a
>> limit of 8000 bytes per column. And you should avoid using sp_ as a
>> prefix to stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>>
>

retrieve Datetime

Friday, March 30, 2012

Return Code 62309

Return Code 62309

Return Code 62309

Return bottom 10 rows not working.

Return BIGINT OUTPUT param to VB!?

return big string

return big string

Followers

Blog Archive

About Me