Getting a stored procedures return value -- URGENT !

We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>

Getting a SQL Database Error...any ideas SQL experts?

I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer...

Here is the code I am using:

<%@. Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.Configuration" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server">

<script runat="server">

Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)

Dim CWZ As CreateUserWizard
CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard)

CreateUserProfile(CWZ.UserName)

Private Sub CreateUserProfile(ByVal UserName As String)

Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@.UserID)", con)
cmd.Parameters.AddWithValue("@.UserID", UserName)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using

End Sub

</script>

...and here is the error and stack trace (the offending Line 49 is in bold):

Server Error in '/Site_Dev' Application.
------------------------

An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 47: cmd.Parameters.AddWithValue("@.UserID", UserName)
Line 48: Using con
Line 49: con.Open()
Line 50: cmd.ExecuteNonQuery()
Line 51: End Using

Source File: C:\Users\mdcragg\Documents\Website\Site_Dev\User_Create.aspx Line: 49

Stack Trace:

[SqlException (0x80131904): An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ASP.user_create_aspx.CreateUserProfile(String UserName) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:49
ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:30
System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

Looking around potential solutions seem to be:

1. Open SQL Server Management Studio and attach the .mdf file manually to the SQL Server. (Select the 'Databases' node, Right click and select 'Attach')

Now change your web.config and change the connection string to point to the pre attached database instead of auto attach.

2. SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again.

3. The windows account "ASPNET" must have write permission on the folder. If it is a Windows 2003 machine, provide write access to the account "NETWORK SERVICE" on the folder, instead of account ASPNET.

4. You may want to disable the "User Instance=true" and try.

5. Are you using beta versions of VWD, Visual Studio 2005 or SQL Server 2005 Express? Uninstall them andinstall the released versions.

6. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode. (Select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select the node 'Security' to view/change the authentication mode)

7. Make sure your connection string is correct. It must be something like below:

"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"

When you useIntegrated Secuiryt=SSPI in the connection string, make sure you are using<identity impersonate="true" /> in the<system.web> section of the web.config and also make sure to specify a valid windows account for the "Anonymous User" access section in IIS.

Getting a single value from SqlDataSource

Hi,

I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1.

DDL1 Selected data field todisplay is ProductName
DDL1 Selected data field tovalue is ProductCost

I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1.

Happy Coding

Hi

You will be able to obtain only the values that you bind to the drop down list. if you want non bound value then you will have to fire another query based on what you select from the drop down.

For example,

select productid from Table_name where product name = 'prodname you selected from ddl' and productcost= cost you got from ddl.

Then use productid as you need

HTH

Getting a return value from a Stored Procedure

Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...

DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst

The SP that you are calling should contain an OUTPUT parameter.
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards

|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst

Getting a Return value from a Function.

Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID:Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)
I cant seem to accomplish this. It returns nothing. Please help.
TIA,
Stue
<code>
Function Get_AttendID(ByVal strAttendIDAsString)As SqlDataReader
Dim connStringAsString = ConfigurationSettings.AppSettings("ClassDB")
Dim sqlConnAsNew SqlConnection(connString)
Dim sqlCmdAs SqlCommand
Dim drAs SqlDataReader

sqlConn.Open()
Dim strSQLAsString = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'"

sqlCmd =New SqlCommand(strSQL, sqlConn)

sqlCmd.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.FirstName").Value = tbFirstName.Text
sqlCmd.Parameters.Add("@.LastName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.LastName").Value = tbLastName.Text
sqlCmd.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8)
sqlCmd.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString
dr = sqlCmd.ExecuteReader()
dr.Close()
sqlConn.Close()

Return dr

EndFunction
</code>

Why are you returning a datareader if all you want is the attend id and why are you even using the datareader at all when all you are looking for is one value.
The best way would be to use executescalar method and return the value. excuse the sample code because it is C#

publicstring AttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();

return myCommand.ExecuteScalar().ToString();
}

|||Thanks Mansoorl! I tried that and it worked. In response to your question about the datareader, the reason I went this route is because I have another function wich requires pulling 2 values. So i was in that mindset. I didnt know about the ExecuteScalar though so thanks for educating me.
Do you mind explaining how i might go about returning 3 values via the data reader if:
Select FirstName, LastName, Company from TBClassSurvey Where AttendID=@.AttendID and SchedID=@.SchedID and survey = '0'";

Thanks again,
Stue
|||publicvoidAttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();

SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();
string FirstName = myReader["FirstName"].ToString();
string LastName = myReader["LastName"].ToString();
string Company = myReader["Company"].ToString();
myReader.Close();
myConnection.Close();
}
The above code assumes you got something back in result of the query. If there is a possiblity for blank records make sure you use if (myReader.Read()) constuct.
Cheers,|||Thanks again mansoorl! I appretiate you educating me.
Take care,
Stue

Getting a permission denied - but not using that user?

In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Con
nection=False"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.

Getting a permission denied - but not using that user?

In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql

getting a constraint value from query in a trigger?

say i have a query like:

UPDATE table SET status = 1 WHERE id = 1000

if i have a trigger on that table, is there anyway i can get the id ?

i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?

thankshmm, looks like i can't even get the id if i try to update since it's an identity column|||How about:SELECT id FROM inserted-PatP|||inserted AND deleted are full blown copies of the table that's affected for the rows that are be modified or added.

You [Id] should be there

What are you trying to do?|||oops, actually i just realized SELECT id from inserted works fine even though id isn't updated

thanks

Getting #Error with aggregate function

Why doesn't this return a value?
=Sum(IIf(Fields!GroupCode.Value = 10, Fields!Rating.Value, 0))
I am getting #Error as the value. This however gives me a value:
=Sum(IIf(Fields!GroupCode.Value = 10, 1, 0))
However, I need the above to work...I need it to sum the rating if it's
part of a particular group.Here are some additional findings...
The following code works:
=Sum(IIf(Fields!GroupCode.Value = 10, CInt(Fields!Rating.Value), 0))
The following code fails:
=Sum(IIf(Fields!GroupCode.Value = 10, 1.20, 0))
=Sum(IIf(Fields!GroupCode.Value = 10, CDbl(Fields!Rating.Value), 0))
Why is it that it can only sum up intergers?|||I figured it out!!
The following code works:
=Sum(IIf(Fields!GroupCode.Value = 10, 1.20, 0.0))
Both the true and false values need to be of the same type. By return
0 as my false condition value, and 1.2 as my true condition, it caused
it to fail because it's returning different data types base on the
different condition.
This is bad, MS needs to fix this.

GetReportParameters - How can I get the default value of the parameter?

I'm using the GetReportParameters method to get the parameters of my report.
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>

GetProperties returns undeterminable timeout value

I have the following code:
// Get timeout
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[1] = itemName;
props[1] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
Problem I'm seeing is that this code returns no output array member
for the timeout if either the system default is being used or if there
is no timeout. How can I figure out which one it is then?Whoops - I tried to edit the code to take out some stuff that didn't
pertain to the problem. The code is actually good, looking like this:
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[0] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> Derek,
> There is a problem with your code. In .NET all arrays are 0 based. So an
> array with one element should be arrayname[0]. So your code should look like
> this:
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(_path, props);
>
> "Derek Knudsen" wrote:
> > I have the following code:
> >
> > // Get timeout
> > Property itemTimeout = new Property();
> > itemTimeout.Name = "ReportTimeout";
> >
> > Property[] props = new Property[1];
> > props[1] = itemName;
> > props[1] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(path_, props);
> >
> > Problem I'm seeing is that this code returns no output array member
> > for the timeout if either the system default is being used or if there
> > is no timeout. How can I figure out which one it is then?
> >|||More on this:
The GetProperty works fine, with -1 being returned if there is no timeout
and no value being returned if it uses the default setting. Problem was with
setting it to the default setting, which I had thought you could do by
setting the value to "0" (I can't find where I saw this). This throws an
"invalid value" exception. Anyway, solution was to not specify a value and
then it gets reset to "use default setting". Thanks.
"Derek Knudsen" wrote:
> Whoops - I tried to edit the code to take out some stuff that didn't
> pertain to the problem. The code is actually good, looking like this:
> Property itemTimeout = new Property();
> itemTimeout.Name = "ReportTimeout";
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(path_, props);
> bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> > Derek,
> >
> > There is a problem with your code. In .NET all arrays are 0 based. So an
> > array with one element should be arrayname[0]. So your code should look like
> > this:
> >
> > Property[] props = new Property[1];
> > props[0] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(_path, props);
> >
> >
> > "Derek Knudsen" wrote:
> >
> > > I have the following code:
> > >
> > > // Get timeout
> > > Property itemTimeout = new Property();
> > > itemTimeout.Name = "ReportTimeout";
> > >
> > > Property[] props = new Property[1];
> > > props[1] = itemName;
> > > props[1] = itemTimeout;
> > >
> > > Property[] itemProps = _rs.GetProperties(path_, props);
> > >
> > > Problem I'm seeing is that this code returns no output array member
> > > for the timeout if either the system default is being used or if there
> > > is no timeout. How can I figure out which one it is then?
> > >
>

geting value from binary

hi,
is it possible to get value as follows in SQL server
I will have values in 101, 111 like in binary , I want to get its decimal values
I want to get 5 for 101, 7 for 111 ...
please let me know ...
RamSo, you have a string containing a kind of binary number?

Consider to solve this in a user-defined function. Loop through your string, and compute the power of 2 if applicable.

geting the UNIQUENAME fom a string

Does anyone have an idea how to get the UNIQUENAME of a member from ther dimesion date given a determined value.

For example in Adventure Works

Val: 2004

UNIQUENAME: [Date].[Calendar].[Calendar Year].&[2004]

Thanks!!

Here is an example showing how to retrieve the unique member name or the key value associated with the current member:

WITH

MEMBER MEASURES.[UniqueName] AS

[Date].Calendar.CurrentMember.UniqueName

MEMBER MEASURES.[KeyValue] AS

[Date].Calendar.CurrentMember.Properties("Key")

SELECT

{[Date].[Calendar].[Calendar Year].&[2004]} ON COLUMNS,

{MEASURES.[UniqueName],MEASURES.[KeyValue]} ON ROWS

FROM [Adventure Works]

HTH,

- Steve

geting money type from sql

hi

i am retriving value from sql server database like

select cast(round(12345674.8658,2,0) as decimal(20,2))

output is 12345674.87
but i want to get like 12,345,674.87
any function is there?do that in your code. Or cast to Money not decimal.

getdate() not returning a value

I have a strange problem occurring when I am using getdate() in a stored proc. I want to get some data from a table within a date range using getdate.
I have a begin and end dates on a table and want to retrieve a guid and some other information based on the current date. So, wherever today's date falls between the begin date and the end date, I want the information from that row.

For example,

select * from polldates
where (pollbegindate >= getdate() and pollenddate <= getdate())

This works fine Monday through Saturday. I get a value returned from getdate() correctly and am able to retrieve the information that I need. However, on Sunday, getdate returns nothing when I run the stored procedure. Any clues? Am I just crazy or has anyone else seen this type of thing happen?

Any help would be greatly appreciated!I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP|||If you are sure that get date is returning a correct value but I am not getting anything back from my query can you suggest how to improve the query?

For example, the begin date is 9/5/04 and the end date is 9/11/04.

Thanks!|||Is it safe to assume pollbegindate and pollenddate are datetime datatypes in the table? Please post the enitre proc. There may be another problem.|||I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP

Well that was CERTAINLY helpful...

Dude

Do SELECT GetDate()...what do you see?

Ahh microseconds...

USE DATEDIFF

But the logic doesn't make sense...

You want all begin dates that are today and greater but all end dates that are less that or equal today...which means...

And day where the start and end are equal and it's TODAY

Johhny...tell him what he's won.....|||Maybe we all need to read. Now I feel like an idiot (well, I almost always feel like an idiot, but that's another matter).SELECT *
FROM polldates
WHERE pollbegindate <= getdate()
AND pollenddate >= getdate()The previous code was looking for rows where the begindate was greater than the enddate!

-PatP|||Even with the screwed up logic why does it return records everyday but Sunday?|||Me no know.

Without seeing the real query and the underlying data, I can offer a gazillion guesses, but no hard facts.

-PatP

Getdate() default value does not get created

I have a table in which a field named DateCreated has the default value
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:

>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?
Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegro ups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>
|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession@.nField1 int = 0,
@.nField2int = 0,
@.nField3int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking(fldField1,
fldField2,
fldField3,
fldField4)
VALUES (@.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction@.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.
|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession @.nField1 int = 0,
@.nField2 int = 0,
@.nField3 int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking( fldField1,
fldField2,
fldField3,
fldField4)
VALUES ( @.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction @.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql

getdate() - 5 hrs

How can i return the getdate value minus 5 hrs
So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
ThanksSELECT DATEADD(hh, -5, GETDATE())
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23gQMybEtFHA.1252@.TK2MSFTNGP09.phx.gbl...
How can i return the getdate value minus 5 hrs
So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
Thanks|||SELECT DATEADD(hour, -5, CURRENT_TIMESTAMP)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:%23gQMybEtFHA.1252@.TK2MSFTNGP09.phx.
gbl...
> How can i return the getdate value minus 5 hrs
> So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
> Thanks
>|||Or
SELECT CONVERT(CHAR(10),GETDATE(),121)+
REVERSE(LEFT(STUFF(REVERSE(
CONVERT(varchar,getdate(),9)),3,4,SPACE(
0)),11))
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23gQMybEtFHA.1252@.TK2MSFTNGP09.phx.gbl...
> How can i return the getdate value minus 5 hrs
> So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
> Thanks
>