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

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.com...
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,

Get the User Name for a login user

Hi Guys,

How can I get the current loged in UserName in windows in MSSQL?

The problem is:

Users login into their PC (Windows Login)Enter a URL of the intranet applicationThey would then be able to access the web base application (Intranet)The web application has a string connection to make a connection to the databaseI want to have a Trigger for my "products" table so if the table is modified (Insert/Update) all those changes be entered in "products_Audit" tableNow on "products_Audit" table I have a field "ChangedBy" where I want to store the windows loged in User Name (SYSTEM_USER just return the Username for the SQL connection that the web application use)

I would really appreciate all your help and suggestions.

Regards,
Mehdi

Hello,

In case of IntraNet where directory security does not allow anonymous users, then you can retrieve domain user information.

dim user as string = Request.ServerVariables("LOGON_USER")

and pass this variable for your database action query.

get the search term from a URL for reporting purpose

Hi guys,

I have a field called URL in my table. I want to get the SEARCH TERM from a given URL and create a report based on that information. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.

Examples of the URLs from google :

http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables

Does anybody have a sql query or used a CLR functions to get the SEARCH TERM from different search engine (URL).

Thanks in advance.

Here is a blog entry from Chrissy LeMaire that seems to do what you want in both C# and straight SQL:

http://blog.netnerds.net/2007/02/mssql-parse-search-engine-querystrings-for-search-terms/

Larry

|||

10Q Larry, that's what exactly I was looking for.

get the search term from a URL

Hi guys,

I have a field called URL in my table. I want to get the SEARCH TERM from a given URL. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.

Examples of the URLs from google :

http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables

Does anybody have a sql query to get the SEARCH TERM from different search engine (URL).

Thanks in advance.

Sounds like a fairly tricky piece of work that.

All i can really add is that as it's essentially string parsing, you'd be best doing this outside of T-SQL either in your app code or in a CLR function.

|||

RegX (Regular Expressions) is your FRIEND in situations like this.

You really, really want to do this in application code using RegX. It is so much more efficient!!!

If absolutely necessary, you could create a CLR function (in VB or C#), using RegX, and add that CLR function to your server. You could then call it from SQL code.

get the description of a column

Okay guys heres the senario.

I have written a kick butt asp application that allows me to test sql
statements and manage/display all my databases from the web but I have
a feature I want to include that I can't figure out how. In Enterprise
Manager, one of the column editable properties is the Column
Description. I can't find it in sql server itself. only in the
Enterprise Manager. I need to access it using a sql statement so that
it will display in my table definiation view that I create in the asp
app.These descriptions are kept as extended properties in sysproperties.
Look up sp_addextendedproperty in the help file for more information.
To figure out what Enterprise Manager is doing in situations like
these, you can use Profiler to see what SQL code it is sending to the
server.

-Tom.

Get PDF from SQL

Hi guys. I've seen a lot posted about this on various sites and can't come up with a working solution. I've got a PDF saved as a byte array in a SQL image field. I figured out how to save/load with images, and I'm pretty sure it's saving the PDF corectly, but I can't get it to load at all.

Here's what I've got:

byte[] image = (byte[])Com.ExecuteScalar();

stream.Write (image, 0, image.Length);

Response.ContentType = "application/pdf";

Response.OutputStream.Write(image,0,image.Length);

This returns a blank page with some spaces. Now that I think about it, maybe its not saving right then.. Any insight? It'd be greatly appreciated.

Try this:

byte[] image = (byte[])Com.ExecuteScalar();

Response.ClearContent();

Response.ClearHeaders();

Response.ContentType ="application/pdf";

Response.BinaryWrite(image);

Response.Flush();

Response.Close();

|||Hi, thanks. I still got a blank page though. Should that be working, in other words might it not be saving correctly? Is there any way to check? It saves images fine using the same method. Does anyone know any other methods off hand to display it? Thanks for your time.|||

Try saving your byte[] to disk and opening it in Acrobat to see if you are getting valid PDF data out. That code should work i took it straight out of a page that i use.

Martin

|||Make sure it's a separate page that dumps the PDF. If you are doing something silly like setting a session variable that tells a .ASPX page whether to dump the .PDF file, or display a form, you'll confuse the browser. The browser has a tendancy to remember the content type of a specific URL, if the URL remains the same, it expects the content type to remain the same as well.|||

Looks similar to mine:

Dim cmdAsNew SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn)

cmd.Parameters.Add(New SqlParameter("@.ID", SqlDbType.Int))

cmd.Parameters("@.ID").Value = Request.QueryString("ID")

' cmd.Parameters("@.ID").Value = 3

Dim drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader("Content-type", dr("MimeType"))

Response.AddHeader("Content-Disposition","inline; filename=""" & dr("Filename") &"""")

Dim buffer()AsByte = dr("Data")

Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

get path of the database file using a query

Hi guys,
all i want to do is , get the path of the location of my database and log
file using a query of a command i could write down in the query anylyser.
Thanks
Sameer MuzammilTry using:
sp_helpfile
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"sameer" <sameer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi,
You could also use
sp_helpdb <dbname>
Thanks
Hari
SQL Server MVP
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi Sameer,
You can extract info from sysdatabses system table. As Jens replied you
sp_helpfile this system stored procdure also make use of this system table.
select name,filename from sysdatabases
go
Thanks
Syed Zulfiqar
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil

get path of the database file using a query

Hi guys,
all i want to do is , get the path of the location of my database and log
file using a query of a command i could write down in the query anylyser.
Thanks
Sameer Muzammil
Try using:
sp_helpfile
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"sameer" <sameer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil
|||Hi,
You could also use
sp_helpdb <dbname>
Thanks
Hari
SQL Server MVP
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil
|||Hi Sameer,
You can extract info from sysdatabses system table. As Jens replied you
sp_helpfile this system stored procdure also make use of this system table.
select name,filename from sysdatabases
go
Thanks
Syed Zulfiqar
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil

get path of the database file using a query

Hi guys,
all i want to do is , get the path of the location of my database and log
file using a query of a command i could write down in the query anylyser.
Thanks
Sameer MuzammilTry using:
sp_helpfile
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"sameer" <sameer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi,
You could also use
sp_helpdb <dbname>
Thanks
Hari
SQL Server MVP
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil|||Hi Sameer,
You can extract info from sysdatabses system table. As Jens replied you
sp_helpfile this system stored procdure also make use of this system table.
select name,filename from sysdatabases
go
Thanks
Syed Zulfiqar
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:25DDB26C-501D-4DCA-979F-3587514744D0@.microsoft.com...
> Hi guys,
> all i want to do is , get the path of the location of my database and log
> file using a query of a command i could write down in the query anylyser.
> Thanks
> Sameer Muzammil