Failure Details: returns

Showing posts with label returns. Show all posts

Thursday, March 29, 2012

getting a store procedure's result

Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.c om...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
|||Use output parameters for SQL procedure from which you wish to assign the
values to the variables. (More information look for: "Returning Data Using a
Return Code" in SQL books online)
HTH
Saleem@.sqlnt.com
"Silvio" wrote:

> Hey guys,
> I have a store procedure that returns a recorset. Here's an example:
> create procedure ABC as
> --some code here that works with @.x and @.y.. and then the last line
> of the proc:
> SELECT @.x,@.y
> That procedure has been used only in a vb code, so they consume the
> result with no problem. Now I need to call that procedure within a
> different proc, and I need to get back the final values of @.x and @.y.
> Is there a way to get these results back in a variable as I call the
> store proc?
> Thanks,
>

getting a store procedure's result

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

Monday, March 26, 2012

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?
> > >
>

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.sql

GetIsClustered() returns FALSE for Veritas Cluster Server

With both SQL Server 2000 and SQL Server 2005 when running in a Veritas Cluster, the GetIsClustered() API returns FALSE.

Does anyone know if this is working as designed?

Does GetIsClustered() really only mean MSCS clustered or should Veritas be setting that server property?

Thanks

Del Hoobler

Posting the last email thread that we had on the topic.

From: Michiel Wories
Sent: Monday, January 09, 2006 10:34 PM
To: 'Del Hoobler'
Cc: Mark Wistrom
Subject: RE: SQL Server 2005 - "IsClustered" - Is it just for MSCS?

Hi Del, as it seems Veritas clusters do not express their ‘cluster-ness’ through this serverproperty, so you would need to check with them how this is done. This is specific to their implementation so I am afraid I cannot help you with that.

Thanks,

Michiel

From: Del Hoobler
Sent: Monday, January 09, 2006 6:52 PM
To: Michiel Wories
Cc: Mark Wistrom
Subject: RE: SQL Server 2005 - "IsClustered" - Is it just for MSCS?

Hi Michiel,
Yes... this is what gets called for GetIsClustered(): (Here is the profile snippet):
14:29.9 select convert(int, serverproperty(N'isclustered'))
14:29.9 select convert(int, serverproperty(N'isclustered'))
14:29.9 select convert(int, serverproperty(N'isclustered'))
But... it sounds like what Mark is saying is that this field only pertains to MSCS... and we need
another way to determine if the server is clustered. Do you agree?

Thanks,
Del

Wednesday, March 21, 2012

GetDate not working

Hi,
I have a very simple query as follows. When I run, it returns no records. I
know there are records that should be in th result. If I insert today's
date instead of getdate() I get all the records for today's date. Getdate()
works if I use > or < instead of =. Any idea why is this behavior? Or is
there another way of accomplishing this.
Select COURSE_NBR as ItemValue
FROM ED_COURSE_CL_1
Where CLASS_DATE = GETDATE()Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
you think match the exact point in time when you run the query? The way to
do this is to use a range query, as you have already discovered. >= {date}
AND < {date + 1} ...
A
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records.
> I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date.
> Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||It is because datetime include both a date and a time portion. See
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records. I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date. Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||Aaron,
Select getdate() is working good and it returns todays date. For today's
date in my query should return one record, which I can verify it by inserting
today's date instead of getdate(). How can I build a range query to only get
records with today's date?
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
> like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
> you think match the exact point in time when you run the query? The way to
> do this is to use a range query, as you have already discovered. >= {date}
> AND < {date + 1} ...
> A
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> > Hi,
> > I have a very simple query as follows. When I run, it returns no records.
> > I
> > know there are records that should be in th result. If I insert today's
> > date instead of getdate() I get all the records for today's date.
> > Getdate()
> > works if I use > or < instead of =. Any idea why is this behavior? Or is
> > there another way of accomplishing this.
> >
> > Select COURSE_NBR as ItemValue
> > FROM ED_COURSE_CL_1
> > Where CLASS_DATE = GETDATE()
> >
> >
>
>|||> Select getdate() is working good and it returns todays date.
Correction : it returns today's date AND TIME.
> How can I build a range query to only get
> records with today's date?
DECLARE @.today SMALLDATETIME;
SET @.today = DATEDIFF(DAY, 0, GETDATE());
SELECT
...
WHERE DateColumn >= @.today
AND DateColumn < (@.today + 1);|||Thanks Aaron it's working great.
Cheers!!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Select getdate() is working good and it returns todays date.
> Correction : it returns today's date AND TIME.
> > How can I build a range query to only get
> > records with today's date?
> DECLARE @.today SMALLDATETIME;
> SET @.today = DATEDIFF(DAY, 0, GETDATE());
> SELECT
> ...
> WHERE DateColumn >= @.today
> AND DateColumn < (@.today + 1);
>
>
>

GetDate as Parameter for UDF Function returns table

Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > Help!!
> >
> > I am trying to pass GetDate() as a paramter into a function that returns
a
> > table.
> >
> > select * from
> > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> GetDate(),'10/30/2004','Company')
> >
> > This fails but it works if I pass in the date as a string. I need to
use
> > GetDate.
> >
> > I cannot create a local variable, this is a view.
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > Simply CAST it as a varchar..
> >
> > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> >
> > Note,
> >
> > You may wish to parse it out to get only the portions of the date that
you
> > want...
> >
> > You could try casting it as a decimal as well. That may work better for
> > you.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > Help!!
> > >
> > > I am trying to pass GetDate() as a paramter into a function that
returns
> a
> > > table.
> > >
> > > select * from
> > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > GetDate(),'10/30/2004','Company')
> > >
> > > This fails but it works if I pass in the date as a string. I need to
> use
> > > GetDate.
> > >
> > > I cannot create a local variable, this is a view.
> >
> >
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> > Rick
> > Your suggestion does not work
> >
> > CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> > RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> > AS
> > BEGIN
> > INSERT INTO @.Dates VALUES(@.dt)
> > RETURN
> > END
> >
> > --Doesnt work (as you suggested)
> > SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> > --Does work
> > DECLARE @.dt DATETIME
> > SET @.dt=GETDATE()
> > SELECT * from dbo.fn_dates (@.dt)
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > > Simply CAST it as a varchar..
> > >
> > > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> > >
> > > Note,
> > >
> > > You may wish to parse it out to get only the portions of the date that
> you
> > > want...
> > >
> > > You could try casting it as a decimal as well. That may work better
for
> > > you.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> > >
> > > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > > Help!!
> > > >
> > > > I am trying to pass GetDate() as a paramter into a function that
> returns
> > a
> > > > table.
> > > >
> > > > select * from
> > > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > > GetDate(),'10/30/2004','Company')
> > > >
> > > > This fails but it works if I pass in the date as a string. I need
to
> > use
> > > > GetDate.
> > > >
> > > > I cannot create a local variable, this is a view.
> > >
> > >
> >
> >
>|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>sql

GetDate as Parameter for UDF Function returns table

getdate - only want the date

when I am doing a query I want to display just the date.
when I use the getdate function it returns the date and
time from each of the fields...is there a way to just
display the date from the fields and leave out the time?Use CONVERT or CAST. You can find some examples in books online.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||You can use following to display only date.
select convert(char(11),getdate())
If you want to display in different format , then extract
dd, mm, yy from getdate and concatinate it.
Suryakant
>--Original Message--
>when I am doing a query I want to display just the date.
>when I use the getdate function it returns the date and
>time from each of the fields...is there a way to just
>display the date from the fields and leave out the time?
>.
>|||The only way is to convert it to a string. There are several formats so you
should check out CONVERT() in BooksOnLine for the one your after.
--
Andrew J. Kelly
SQL Server MVP
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||worked fine thank you

Monday, March 19, 2012

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

Get Windows NT/XP Logon ID in SQL Server

Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
user logs on as "sa"?
SUSER_SNAME () returns "sa"
Thanks
RonRon
What did you mean by Logon ID ? A process id ?
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1144536622.162777.312290@.u72g2000cwu.googlegroups.com...
> Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
> user logs on as "sa"?
> SUSER_SNAME () returns "sa"
> Thanks
> Ron
>|||RonL (sal_paradise_93@.yahoo.com) writes:
> Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
> user logs on as "sa"?
> SUSER_SNAME () returns "sa"
No. And keep in mind that there may not even be a login ID. The user
could log in from a Unix box.
This a good argument why you should run with Windows Authentication only,
or at least why you should do something like:
DECLARE @.pw char(36)
SELECT @.pw = convert(char(36), newid())
PRINT @.pw
EXEC sp_password @.pw, NULL, sa
Run this script, and without looking at the output, print it, and lock it
into a safe.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm trying to pick up the Windows Logon Id in a Trigger. I can retrieve
the Windows Logon Id in code ASP.NET or Windows but I'm trying to
retrieve it in a Trigger.
I've seen code that at connect time concatenates the UserId with the
Computer Name to create the Workstation ID.
e.g.,
Server = .. Initial Catalog= .. Workstation ID= computerName + userid
Then to retrieve the Userid they execute sp_who and retrieve the value
from the column HOSTNAME.
Unfortunately Windows Authentication is not an option.
Thanks
Ron|||RonL (sal_paradise_93@.yahoo.com) writes:
> I'm trying to pick up the Windows Logon Id in a Trigger. I can retrieve
> the Windows Logon Id in code ASP.NET or Windows but I'm trying to
> retrieve it in a Trigger.
> I've seen code that at connect time concatenates the UserId with the
> Computer Name to create the Workstation ID.
> e.g.,
> Server = .. Initial Catalog= .. Workstation ID= computerName + userid
> Then to retrieve the Userid they execute sp_who and retrieve the value
> from the column HOSTNAME.
Yes, the client can be co-operative and some agreed connection property
to the information you want to pass. WSID or AppName are the choises
that are closest at hand. SET CONTEXT_INFO is also an option.
Running sp_who to get the hostname is a serious overkill. You can use
the built-in function host_name(). (And app_name() if you use the
application name.)
However, there is no way that SQL Server can get this information on
its own, so if SQL Server cannot trust the client, you are out of luck.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If NT authentication is not an option, use SQL Server authentcation, but
never allow users to connect as SA. Create as many SQL user roles as needed
then create as many database users and put them in appropriate roles. Only
give the users the privileges they need to execute individual business tasks
- why would they need the power to drop/create/alter SQL objects etc.?
ML
http://milambda.blogspot.com/

Monday, March 12, 2012

Get the value of a testbox

Hello, I built a report which returns some calculated fields and I would like to know if it's possible to use the returned values in another field.

Exemple:
textbox10 = Fields!MyColumn.Value
textbox11 = Fields!MyOtherColumn.Value

I would like to know if there is a way to do something like this:
textbox12 = textbox10 + textbox11

Has anyone any idea? Thanks in advance for your help

Use the ReportItems collection i.e.

Code Snippet

textbox12 = ReportItems!textbox10.Value + ReportItems!textbox11.Value

Wednesday, March 7, 2012

Get Table Rows Priority Wise

I have a table.

Highlight
----

Name

Detail

StartDate

EndDate

Priority

I want to make a query which returns 1 Highlight in the current date.

But remember I have already set the Hightlight Priority 1 to 5. And I want that Hight Priority rows select more times than Low Priority Rows.

Not sure I understand the question, but to return a single row, you can use TOP 1 and for your priority, use an ORDER BY. So for example:

SELECT TOP 1 * FROM [Hightlight] WHERE [StartDate] = GETDATE() ORDER BY [Priority]

-Damien

|||

I need that.

Every time I select top 1 row different Highlight will select.

But the hightlight which has high priority select more times then low priority.

|||

Are you trying to get a random result? Basically the statement I provided will only ever return 1 result with the highest priority.

Not sure I understand what you are trying to do...

-Damien

|||

I think this is waht you need:

1SELECT TOP 1 *2FROM [Hightlight]3WHERE [StartDate] =Convert(varchar(20),GETDATE(),111)4ORDER BY [Priority],[StartDate], [EndDate]

I suggest you to have another column (to act as a flag) for the FinishedTasks (IsFinished or IsDone column).

So, you can list the top 1 high priority that assign to you first and not finished yet and it has the closest deadline (EndDate), by using the following example:

1SELECT TOP 1 *2FROM [Hightlight]3WHERE [StartDate] =Convert(varchar(20),GETDATE(),111)4AND [IsFinished] = 1-- (0:Finished and 1:Not Finished)5ORDER BY [Priority],[EndDate]6

Good luck.|||

But I don't want this.

I want that row will select randomly and the row which has high priority shows many time as compare to low priority hightlight.

But in your prescribed condition High Priority row will select till it expires and other will wait to expire.

|||

Hi,

If you want to show random values, you can try Order By NewID() .

Try the following codes (I suppose Max(Priority) will have the most priority. Or you can use MIN() )

1 SELECT TOP 1 *
2 FROM [Hightlight]
3 WHERE [Priority]=select Max(Priority)
4 ORDER BY NewID()

OR:

select Top1 *

from [hightlight]

order by Priority,NewId()

Hope my suggestion helps

|||

But Sir,

From your prescribed solution.

Random Rows of Same Priority will select till that priority will expires.

But I want random rows from random priorities.

And records of low priority will select lower then high priority

|||

zeeshanuddinkhan@.hotmail.com:
But Sir,
From your prescribed solution.
Random Rows of Same Priority will select till that priority will expires.
But I want random rows from random priorities.
And records of low priority will select lower then high priority

Here it is:

1SELECT TOP 1 * 2FROM Hightlight3WHERE [Priority]in 4 (5select Priority6From Hightlight7Order byNewID()-- for random pripority8 )9Order byNewID()-- for random rows

Good luck

|||

Can you tell me?

How can this query take care that the high priority will select more time as compare to low priority?

Because after trying your query every priority has equal chance to come.

|||

And by the way.

First try the query on your own end.

Because your mentioned query not fits as I ask.

And Second it has following error.

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

|||

zeeshanuddinkhan@.hotmail.com:
Can you tell me?
How can this query take care that the high priority will select more time as compare to low priority?
Because after trying your query every priority has equal chance to come.

Here it is:

1SELECT TOP 1 * 2FROM Hightlight3WHERE [Priority]in 4 (5select Priority6From Hightlight7Order byNewID(), Priority-- for random pripority8 )9Order byNewID()-- for random rows

Now the chances for high priority will be more for the random rows.

Good luck.

|||

Please check it.

This query has an error.

|||

zeeshanuddinkhan@.hotmail.com:
Please check it.
This query has an error.

Sorry for the small mistake, I forgot to use top.. here is the correction and I am sure it will work with you,

11SELECT TOP 1 *22FROM Hightlight33WHERE [Priority]in44 (55select TOP 1 Priority66From Hightlight77Order byNewID(), Priority-- for random pripority88 )99Order byNewID()-- for random rows10

Good luck.

|||

SELECT *

FROM Highlight

WHERE GetDate() BETWEEN StartDate AND EndDate

ORDER BY rand(cast(cast(newid() as binary(4)) as int))*Priority

That will return all the records that have a startdate before now, and a enddate later than now in a random order weighted by priority. (Priority 1 will be more likely to be near the top than a priority 5, etc). Just add TOP 1 if you only want the first record after you are done verifying that the results are what you want. If you wanted priority 5 to be selected more than priority 1, then add " DESC" to the end of the query.

NewId by itself IS NOT random. It is unique on every call, but it's predictable. You can however, use it as a seed to the rand function, which will make it random (or more random).

Sunday, February 26, 2012

get resulset from stored procedure

Hi all!
I have one stored procedure than returns a resulset (select). I want to
execute this in other stored procedure and get the resulset... How can i
get it?
TIA
Alfonsohttp://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi,
CREATE TABLE #TEMP
(
COL1 INT NOT NULL
)
INSERT INTO #TEMP EXEC SP
Also seen on:
http://groups.google.de/groups?hl=d...r />
40phx.gbl
%26rnum%3D8
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Alfonso" <aNOSPAMparedes@.rtspain.com> schrieb im Newsbeitrag
news:%234DizOAQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi all!
> I have one stored procedure than returns a resulset (select). I want to
> execute this in other stored procedure and get the resulset... How can i
> get it?
> TIA
> Alfonso
>|||
Thanks David & Jens for your answers
I got it :-)))

Thursday, March 29, 2012

getting a store procedure's result

getting a store procedure's result

Getting a Return value from a Function.

Monday, March 26, 2012

GetProperties returns undeterminable timeout value

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

GetIsClustered() returns FALSE for Veritas Cluster Server

Wednesday, March 21, 2012

GetDate not working

GetDate as Parameter for UDF Function returns table

GetDate as Parameter for UDF Function returns table

GetDate as Parameter for UDF Function returns table

getdate - only want the date

Monday, March 19, 2012

Get XML from SQL Server 2000

Get Windows NT/XP Logon ID in SQL Server

Monday, March 12, 2012

Get the value of a testbox

Wednesday, March 7, 2012

Get Table Rows Priority Wise

Sunday, February 26, 2012

get resulset from stored procedure

Failure Details

Blog Archive

About Me