can I create a VIEW on the sesult of stored procedure ?

I have a store procedure which select/calculate fields from multiple tables,

can I create a VIEW on the sesult of the stored procedure ?

u can create a view using a select statement containing ur sp converted to a function....
but why would u want to do such a thing....do u want to permanently keep these values..//refer them just then...some more info may get u a better suggestion buddy...|||I want create a cube fact that is base on the result of a stored procedure, but the cube cannot accept SP as a source, so I want to create a view that can be the source of cube fact|||

In this case I think your best bet would probably be to simply create and populate a table with the results of your stored procedure. If you don't need to keep these results then you can truncate and/or drop the table when you don't need it anymore.

I don't think it's possible to create a view based on the output of a SP.

sql

Can i call Tigger in store procedure or vice versa and How?

Can i call Tigger in store procedure or vice versa in sql server 2000.

and How it implement ?

Your question has been replied in anthor post, please check it:

http://forums.asp.net/1318011/ShowPost.aspx

To call stored procedure in trigger, just call it like anywhere else you call it:

EXEC sp_mysp

sql

Can i call tigger in store procdure and vice versa?(urgent)

Can i call tigger in store procdure and vice versa??

if yes how it ???

I don't think you can. If you need the trigger functionality you could always copy it into a stored procedure and call that instead.|||you can not call trigger in stored procedure. but you do can call stored procedure from trigger.

Can datetime type store milliseconds

Hi,

I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...

Anyone knows how to store datetime value with milliseconds in the SQL database?

Thanks

All --

FYI, I found the issue that was causing me trouble.

It is a simple matter of display in Enterprise Manager.

When viewing data in Enterprise Manager using the GUI by right-clicking on a Table name and choosing "Return all rows" we see the data...

11/09/2006 15:38:14

...but, when we view the same data using Query Analyzer we see the data...

2006-11-09 15:38:13.907

...which is something quite different.

So, the value is stored with milliseconds but the value is NOT displayed in the same way using the GUI table-browser and Query Analyzer.

That was the crux of my issue.

(Imagine my surprise when I found out my code was actually working.)

:-)

Thank you.

-- Mark Kamoski

|||

If you want to store milliseconds you cannot use SmallDateTime because of limited resolution. The link below should take you in the right direction. Post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187819.aspx

|||

Hi Caddre,

The datatype i am using is datetime and It's doesn't seem to allow me to add milliseconds to it. I am using sql 2000, but i don't think this have to do with SQL version.

|||

There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.

http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp

|||

thanks,

I managed to store milliseconds

INSERT INTO Table VALUES ({ts '2003-11-05 13:02:43.296'})

|||

The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.

|||

Motley:

The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.

I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?

I know that a SQL Sever 2000 datetime column can store 3 places of time accuracy, such as 12/30/2006 12:01:03.123, as noted here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

However, what is not clear is how to get a VS.NET 2003 DateTime variable value to insert into SQL Server datetime column with 3-places of accuracy.

I have tried several things, such as...

DateTime myDateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));

...but SQL Server always seems to trim the milliseconds.

What is the way to accomplish this?

Please advise.

Thank you.

-- Mark Kamoski

|||

Caddre:

There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.

http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp

I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?

The .NET data type DateTime has milliseconds but after I insert them into SQL Server the datetime column does not contain the milliseconds.

I am using ADO.NET, and OLEDB DataAdapter, and simply call Update passing a DataTable.Therefore, I just set the value in the DataTable as a .NET DateTime datatype with high millisecond precision and then call update. Unfortunately, the milliseconds are trimmed.

How can this be done?

|||

It is in the same area with the link you gave Motley. Read carefully and spend time with Katy kam’s blog she does this stuff for the BCL(base class library) team. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_1ckz.asp

http://blogs.msdn.com/kathykam/archive/2006/09/29/773041.aspx

|||

Mark,

If you tried the first post the link is to the general topic because the urls did not change, I have the SQL Server 2005 version but run a search for the topic in SQL Server 2000 BOL(books online) . Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190234.aspx

|||

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

|||

Motley:

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

Motley --

OK. that might work.

I appreciate the clarification.

However, now that I think about it, I want to have as much resolution as possible to the date-time value being stored.

As such, if I want to store the full 7-digits to the right of the decimal point, (the resolution that .NET provides), then I think that I need to store the date as an nvarchar in some known format that can easily be parsed. When retrieving such values, I will have to cast from the SqlServer nvarchar to a .NET DateTime. That will make SQL-based comparisons/mining tricky. However, once the data is in-memory, in .NET code, and properly cast back to a DateTime, such comparison/mining will be simple. That seems to be a decent solution, at least in my case. And so on.

If anyone has a better solution, then please post it.

Thank you.

-- Mark Kamoski

|||

Motley:

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

Please help.

When I go to SQL Server 2000 Enterprise Manager, select a table, and choose "Return All Rows", and then try to enter...

01/01/2001 01:01:01.123

...I get the following error...

The value you entered is not consistent with the data type or length of the column, or over grid buffer limit

...which is strange.

I can enter the data...

01/01/2001 01:01:01.123

...but it will NOT allow entry of milliseconds.

Why?

Oddly enough, it will allow...

update ProgramLog set DateTimeLogged = '01/01/2001 01:02:03.456'

...and it enters the milliseconds but (another interesting point) it rounded-up ".456" to ".457".

SideBar -- Note that SQL Server 2000 Books Online says: "datetime - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table."

Please advise.

Thank you.

-- Mark Kamoski

can an sql procedure start printing a report ?

How can i start printing a report from a trigger activated store procedure ?It's possible.
Basic process:
1. Set up printer delivery extension as described in the books online
(and test that it works nicely)
2. Set up subscription to send required report to printer extension.
3. Go to SQL Agent and find the newly created job. You'll see that the
actual step is just a stored procedure that launches an even in the Report
Server database. Copy --> Paste the step details into the relevant stored
procedure.
"elbert chavarro" wrote:
> How can i start printing a report from a trigger activated store procedure ?

Can Add/update/drop column sql statements in one store procedure?

I have a batch sql statments to add column NewCol into TableA, then update
NewCol at this TableA, then drop column NewCol from TableA. I have no proble
m
to run them as individual sql statment with GO after each statment. But when
I tried to put these sql statements into one store procedure, each time when
compile or run the store procedure, I got error 'Invalid column name'. I
guess column NewCol is not there at beginning. So Can I put Add/update/drop
column in one store procedure?
alter procedure xash
AS
-- other sql regarding to this TableA --
--alter add column--
alter table TableA add NewCol varchar(1) null
-- update new column--
Update TableA
set NewCol= 'Y'
--other operation related this table and new column--
--drop column --
alter table TableA drop COLUMN NewCol
GOYou have to commit the work at each step before the next step can see
it. There is lots of overhead for this.
I have to ask why you are doing this. Instead of having physical
storage hold your computed values, wouldn't it be better to simply have
a VIEW with the computed column in it? Also why do you want to use
VARCHAR(1); think about it.
CREATE VIEW FilteredFoobar (..)
AS
SELECT ..., CASE WHEN <<test here>> THEN 'Y' ELSE 'N' END AS new-col
FROM Foobar, ..
WHERE ..;|||On 13 Apr 2005 16:18:15 -0700, --CELKO-- wrote:

> Also why do you want to use VARCHAR(1); think about it.
You know, it would be nice if SQL implementations could just throw an error
message if you try to create a VARCHAR(1) column. :)

Can a stored procedure be executed from within a select statement?

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.No, not within a select statment. Well, maybe with OPENQUERY, but even if that did work I would never use it.|||So convert it into a function...

Is there a question here?

calling the same store procedure repeatly, but only work in the first time

I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong?

My original version that calls a store procedure

Public

SharedFunction getPartId(ByVal part_supplierserialnumberAsString)AsStringDim mySqlCommandAsNew SqlCommandDim mySqlConnectionAs SqlConnection =New SqlConnection(GetERATSConnectionString())Dim myPart_idAsString

mySqlCommand.CommandType = CommandType.StoredProcedure

mySqlCommand.CommandText = "getPartId"

mySqlCommand.Connection = mySqlConnection

mySqlCommand.Parameters.Add(

New SqlParameter("@.part_supplierserialnumber", part_supplierserialnumber))Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch exAs Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

EndTryReturn myPart_idEndFunction

My Store procedure

create procedure getPartId
@.part_supplierserialnumber as nvarchar(50)
as

select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @.part_supplierserialnumber order by part_datecreated desc

GO

The new version I tried which happen the same thing

PublicSharedFunction getPartId(ByVal part_supplierserialnumberAsString)AsStringDim myPart_idAsStringDim strSqlAsString = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc"Dim mySqlConnectionAs SqlConnection =New SqlConnection(GetERATSConnectionString())Dim mySqlCommandAsNew SqlCommand(strSql, mySqlConnection)Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch exAs Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

EndTryReturn myPart_idEndFunction

I tested your stored procedure. It works.

Here is my test code on the page load event. It retrieves partid for the for loop.

--code

Dim mystring1, mystring2 As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim a(3) As String
a(0) = "abc"
a(1) = "xyz"
a(2) = "hij"
Dim i As Integer = 0
For i = 0 To 2
mystring1 = Convert.ToString(getPartId(a(i)))
mystring2 = mystring2 + mystring1 + "+"
Label1.Text = mystring2

Next
End Sub

--code

But your new version should work with the parameterized query. Not this one with misplaced single quotas. It didn't run.

This one works:

.....

Dim strSql As String = "SELECT top 1 part_id FROM tblPtSingapore where part_supplierserialnumber=@.part_supplierserialnumber order by part_datecreated desc"
Dim mySqlConnection As SqlConnection = New SqlConnection(connString)
Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection)
mySqlCommand.Parameters.Add(New SqlParameter("@.part_supplierserialnumber", part_supplierserialnumber))

......

Check your test data and the for loop too.

|||

I have check the for loop and test data, it seems all rite.. I tried your query too..

I have try calling that store procedure getPartId from another store procedure, but it just doesn't assign the variable with the part id I got from the getPartId, though it does return a part id if I call getPartId in the sql query analyzer.

CREATE PROCEDURE PRVtoRIP
@.part_supplierserialnumber NVARCHAR(30)
AS

declare @.newpart_id as nvarchar(35)
/* get the unique PK for the record, cos at application lvl doesn't work */
Execute @.newpart_id = getPartId @.part_supplierserialnumber

UPDATE tblPtSingapore
SET part_status = 'RIP',
part_datemodified = getDate()
WHERE part_id = @.newpart_id
and part_status = 'PRV'

select @.newpart_id as new_part_id
GO