Creating Triggers

Can someone please help me with the following problem:

SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;

Create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

Originally posted by Byrd24
Can someone please help me with the following problem:

SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;

Create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.|||Originally posted by dmmac
Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id)
10 );
And update the student w/ id of 1000.|||Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END

Originally posted by Byrd24
I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id)
10 );
And update the student w/ id of 1000.|||Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/

Originally posted by dmmac
Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END|||Try putting a semi-colon at the end of the UPDATE statement.

Originally posted by Byrd24
Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/|||Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??|||Change if to IF(:new.student_id = 1000) THEN

Originally posted by Byrd24
Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??

creating temporary table with SELECT INTO

Hi

Dose any body know why a temporary table gets deleted after querying it the
first time (using SELECT INTO)?
When I run the code bellow I'm getting an error message when open the temp
table for the second time.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name '#testtable'.

-----------------------
-----
cnn.Execute("SELECT category, product INTO #testtable FROM properties")
'--creating temporary TestTable and populate it with values from another
table

SET rst_testt = cnn.Execute("SELECT * from #testtable") '-- opening
the temporary TestTable
SET rst_testt2 = cnn.Execute("SELECT * from #testtable") '-- ERROR
opening the temporary TestTable for the second time (that where the error
occurred)

rst_testt2.Close '-- closing table connection
SET rst_testt2 = nothing

rst_testt.Close '-- closing table connection
SET rst_testt = nothing

cnn.Execute("DROP TABLE #testtable") '-- dropping the temporary
TestTable
'-----------------------
-----

But when I create the temp table first and then INSERT INTO that table some
values then it is working fine.
'-----------------------
-----
cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), product
VARCHAR(3))")
cnn.Execute("INSERT INTO #testtable VALUES('5','4')")

SET rst_testt = cnn.Execute("SELECT * from #testtable") '-- opening
the temporary TestTable
SET rst_testt2 = cnn.Execute("SELECT * from #testtable") '-- opening
the temporary TestTable for the second time

rst_testt2.Close '-- closing table connection
SET rst_testt2 = nothing

rst_testt.Close '-- closing table connection
SET rst_testt = nothing

cnn.Execute("DROP TABLE #testtable") '-- dropping the temporary
TestTable
'-----------------------
-----

Does any body know why the first code (SELECT INTO) is not working where the
second code it working?

regards,
goznal[posted and mailed, please reply in news]

gonzal (k2net@.dodo.com.au) writes:
> Dose any body know why a temporary table gets deleted after querying it
> the first time (using SELECT INTO)?
> When I run the code bellow I'm getting an error message when open the temp
> table for the second time.
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E37)
> Invalid object name '#testtable'.
> -----------------------
> cnn.Execute("SELECT category, product INTO #testtable FROM properties")
> '--creating temporary TestTable and populate it with values from another
> table
> SET rst_testt = cnn.Execute("SELECT * from #testtable")
> '-- opening the temporary TestTable
> SET rst_testt2 = cnn.Execute("SELECT * from #testtable")
> '-- ERROR opening the temporary TestTable for the second time (that
> where the error occurred)

What's happening is that ADO is opening a second connection behind your
back. This has really not anything to do with how you created the table.

The reason that ADO opens an extra connection, is because there are
rows waiting to be fetched on the first connection, so ADO cannot
submit a query on that connection. I cannot really say why it only happened
in one place, but it may be related to the type of cursor you picked,
and the fact that in the second example, there is only one row in
the table.

Use a static client-side cursor. I would expect this to resovle the
problem.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland
You were right.
ADO is opening an extra connection thats why it is not working and the
second example is working because only one record was inserted. If I
change to second code to:

cnn.Execute("INSERT INTO #testtable VALUES('5','4')")
cnn.Execute("INSERT INTO #testtable VALUES('2','3')") -- inserting
second row

Then it is not working, same as the first example.

As you have suggested in your previous email to use a static client-side
cursor, I have change to code to:

set rst_tre = Server.CreateObject("ADODB.RecordSet"
rst_tre.CursorLocation = 1 '-- adUseClient
rst_tre.CursorType = 3 '-- adOpenStatic
rst_tre.LockType = 2 -- adLockOptimistic
sql_tre = "SELECT category, product INTO #testtable FROM products"
rst_tre.Open sql_tre,cnn
SET rst_tre = nothing

But it is still not working.
Any suggestion why?

I head a look at some logs from SQL:

SQL:BatchCompleted; SELECT category, product INTO #testtable FROM
products
SQL:BatchCompleted; SELECT * from #testtable
Audit Login -- network protocol:
SQL:BatchCompleted SELECT * from #testtable
Audit Logout

Any suggestions why ADO opens another connection after opening the
#testtable for the first time?

Another think I have noticed is that when I open the #testtable only
once it is working fine.

Thanks,
Gonzal

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||gonzal (nospam@.abc.abc) writes:
>
> set rst_tre = Server.CreateObject("ADODB.RecordSet"
> rst_tre.CursorLocation = 1 '-- adUseClient
> rst_tre.CursorType = 3 '-- adOpenStatic
> rst_tre.LockType = 2 -- adLockOptimistic
> sql_tre = "SELECT category, product INTO #testtable FROM products"
> rst_tre.Open sql_tre,cnn
> SET rst_tre = nothing
>
> But it is still not working.
> Any suggestion why?

I have to admit that I'm no ADO guru, so I cannot really say what is
happening. However I did a quick test, and this did not give me a
second connection:

Dim cnn As ADODB.Connection
Dim cnnErr As ADODB.Error
Set cnn = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

On Error GoTo errHandle

Dim oCommand As ADODB.Command

cnn.ConnectionString = "Provider=" & Trim$(Provider.Text) & ";" & _
"Data Source=" & Trim$(DataSource.Text) & ";" & _
"User Id=" & Trim$(User.Text) & ";" & _
"Password=" & Trim$(Password.Text) & ";" & _
"Initial Catalog=" & Trim$(Database.Text) & ";" & _
"Use Procedure for Prepare=0"
cnn.ConnectionTimeout = 5
cnn.CursorLocation = adUseClient
cnn.Open

Set oCommand = CreateObject("ADODB.Command")
Set oCommand.ActiveConnection = cnn

oCommand.CommandType = adCmdText
oCommand.CommandText = "select name into #temp from sysobjects"
oCommand.Execute

rs.Open "SELECT * FROM #temp", cnn
rs2.Open "SELECT * FROM #temp", cnn

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try to make the temporary table a global variable by using two #
characters like: ##testtable. This probably will solve your problem.

Greetings Sjaak

Creating stored procedure

I have an inline sql query which i want to convert to a stored procedure.
The query is simplified as follows:

string sql = "SELECT * FROM property_property WHERE Location = " + location;
if(state != null) sql += " AND State = " + state;
if(cost !=null) sql += " AND Cost = " + cost;

The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc

I know some of the basics and managed to convert every other query except this.

Any help would be appreciatedcheck out BOL for sp_executesql.

hth|||Here is a way - probably not the most elegant but:

CREATE PROCEDURE MyStoredProcedure
@.Location NVARCHAR(50),
@.State NVARCHAR(50) = NULL,
@.Cost NVARCHAR(50) = NULL

AS

DECLARE @.Query NVARCHAR(2000)
SET @.Query = 'SELECT * FROM property_property WHERE Location = ''' + @.Location + ''''

IF NOT @.State IS NULL
BEGIN
SET @.Query = @.Query + ' AND State = ''' + @.State + ''''
END

IF NOT @.Cost IS NULL
BEGIN
SET @.Query = @.Query + ' AND City = ''' + @.City + ''''
END

EXEC sp_executesql @.Query

GO

For debugging purposes, you might replace the "EXEC sp_executesql @.Query" with "PRINT @.Query" just to make sure it is building the correct SQL statement.

Hope that helps,
Ian|||

CREATE PROCEDURE dbo.ReadMoreBooks
 @.State varchar(50), -- change datatypes to match sql's
 @.Cost varchar(50), -- change datatypes to match sql's
 @.Location varchar(50) -- change datatypes to match sql's
AS
SELECT
 *, -- i hate using *. List all the fields
 StateSelect = (
 CASE
 WHEN @.State IS NOT NULL THEN
 @.State
 WHEN @.State IS NULL THEN
 Null
 END
 ),
 CostSelect = (
 CASE
 WHEN @.Cost IS NOT NULL THEN
 @.Cost
 WHEN @.Cost IS NULL THEN
 Null
 END
 )
FROM
 property_property -- weird table name, but ok.
WHERE
 Location = @.Location
AND StateSelect = @.State
AND CostSelect = @.Cost

That's one way. Didn't try, but maybe.

Creating SQL Statements at run-time

Okay, guess a few questions rolled into one post here.

On my site, I have a drop down list where users can select different columns from their database, and then once they select any given field, all the unique values that the field contains are brought up in a CheckBoxList for the user to select which ones they want to search for. (Note: that part of the site is already done--this next part is what I need help with) I want to create a SQL statement based on what the user checked. So like, if from FieldX they checked Item1, Item3, and Item8, the SQL statement created should be something along the lines of:

SELECT * FROM Orders Where FieldX='Item1' OR FieldX='Item3' OR FieldX='Item8'

This is going to be in an intranet, so I'm not too worried about SQL Injection attacks, which I've heard of, but don't really know what they are particulary. Although I guess it would be better to be safe rather than sorry.

Also, as far as creating the SQL statement, some items from the database will be text and others will be numbers, so I guess I also need to know how to find out whether an item in question is a string or a number of some type so that I can know whether to enclose that item in single quotes within the SQL statement.

Okay, I think that's it for now.

Thanks in advance.

Welcome to the forums.

This question has been answered a few times in these forums. Search in these forums and if you still dont find any possible solutio post back.

Creating select statement with seperate columns for different valu

I've been trying to figure out a select statement that would list a seperate
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?

Creating primary key on a view

i'm connecting a view to microsoft mappoint using a .UDL. when i link the
data i need to select the primary key so i choose the customer number, but
there are a few shops that have to shops but one customer number so neither
go in to the map because they are duplicates. so i was wondering if i could
create a primary key in the view that is sort of like the autonumber in
access. so each shop would be given a number when the view is called and tha
t
could be my primary key. the number wouldn't even have to match up with the
same number every time i would just need it to get every shop into mappoint.
if anyone has any suggestions i would really appreciate it. thank you.
jb
Message posted via http://www.webservertalk.comjbennett via webservertalk.com wrote:
> i'm connecting a view to microsoft mappoint using a .UDL. when i link the
> data i need to select the primary key so i choose the customer number, but
> there are a few shops that have to shops but one customer number so neithe
r
> go in to the map because they are duplicates. so i was wondering if i coul
d
> create a primary key in the view that is sort of like the autonumber in
> access. so each shop would be given a number when the view is called and t
hat
> could be my primary key. the number wouldn't even have to match up with th
e
> same number every time i would just need it to get every shop into mappoin
t.
> if anyone has any suggestions i would really appreciate it. thank you.
> jb
> --
> Message posted via http://www.webservertalk.com
Adding an "auto-number" (the similar feature is called IDENTITY in SQL
Server) doesn't seem to make much sense for the situation you
described. For future reference, it's a reliable rule of thumb that
doing anything "like Access" in SQL Server is often going to be a big
mistake ;-)
Instead, create a table called Shops. Shops obviously isn't the same as
Customers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>
>Instead, create a table called Shops. Shops obviously isn't the same as
>Customers.
Thank you for your reply, i'll keep in mind not to try stuff in SQL that i
can do in access:) we already have a customer table. the problem is that we
ship ups and some of our shops have po boxes for address so we need a ship t
o
address which is stored in another table. so i created a view that pulls in
the customer info with the shipping address. after i did that i came across
a
problem that some shops had 2 stores under one customer number creating a
duplicate primary key, because in mappoint i had to use custno as the primar
y
key. we have shops added wly and inactivated wly so i would have to
update a table all the time, that is why i used a view. if there is a way to
create a table that automatically updates that would be great. thanks again
for all your help.
jb
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Creating Optional Parameter dropdown lists

I have a parameter that acted as optional before I changed it to a dropdown
populated by a query. Now the report forces me to select something in spite
of the fact that I have selected ALLOW BLANK and have tried to use blanks
spaces or other things as a default values. It seems like it won't recognize
a default value when the parameter is a dropdown.
Is this by design, is something acting in error, or am I doing something
wrong?You will need to also select "Allow Null" for the parameter and it should
then default to NULL and auto execute the report.
You may also need to pass in a NULL value from your data set that populates
the optional dropdown list. You can do this by using a UNION statment in the
dataset that will return the NULL value, such as:
select MyID, MyDescrtiption from MyTable
UNION
Select Null,Null
I hope this helps.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"John Marsh" <John Marsh@.discussions.microsoft.com> wrote in message
news:6065AC4F-F402-450B-AFEA-A977CFE152AC@.microsoft.com...
>I have a parameter that acted as optional before I changed it to a dropdown
> populated by a query. Now the report forces me to select something in
> spite
> of the fact that I have selected ALLOW BLANK and have tried to use blanks
> spaces or other things as a default values. It seems like it won't
> recognize
> a default value when the parameter is a dropdown.
> Is this by design, is something acting in error, or am I doing something
> wrong?

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating new table from existing table

Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)you can use SELECT INTO

select * into <table name2> from <table name1 >|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH

Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >|||Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >
Duh - yeah - thanks Mallier - copy and paste error :o

creating N xml documents from N rows

kudos to anyone who can answer this one;

I've got a table and I want to select rows in it so that the result contains an XML column. So going from

ID Parent Name Date

1 null Foo 2005-04-03

2 1 Bar 2005-04-03

to

ID Parent XML

1 null <element name="Foo" date="2005-04-03" />

2 1 <element name="Bar" date="2005-04-03" />

And for the life of me I can't find a method for creating the XML in an elegant way.

The 'FOR XML EXPLICIT' clause gives me all the formatting I could want, but forces you to munge all the XML into a single, xml-only result. That doesn't seem to be a help. Is there a clean way to do this?

If all else fails, though I shudder even to suggest it, is there a function to xml-escape strings so I can do something like

select

ID, Parent,

convert(xml, '<element name="' + quote(name) + '" date="' + quote(tostring(date)) + '" />")

from Elements

If you SQL Server 2005 you can use the following query,

Code Snippet

Create Table #data (

[ID] int ,

[Parent] Varchar(100) ,

[Name] Varchar(100) ,

[Date] datetime

);

Insert Into #data Values('1',NULL,'Foo','2005-04-03');

Insert Into #data Values('2','1','Bar','2005-04-03');

Select

Id

,Parent

,(Select [Name],[Date] from #Data element Where element.Id=Main.ID For XML AUTO) as XML

From

#Data Main

|||Wow -- thanks. That's absolutely perfect. Thanks so much.|||

this one also will work:

select
ID,
Parent,
(
select
quote(name) as "@.name",
quote(tostring(date)) as "@.daae"
for xml path('element'), type
) xml
from Elements

Creating Duplicate Table

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

It's amazing what a little searching can find.

Check out this thread from just yesterday. It tells you all you need to know.

|||

I have created one, but not tested 100%.. try this code..

Code Snippet

Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid

Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder

Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;

Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;

--Print @.SQL

Exec(@.SQL);
Print 'Table Structered Copied'

If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End

Go

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End

return @.IndexedCols;
End

Go

exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0

|||

Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...

|||

I fixed the bugs.. check now

|||

Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.

|||

Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...

|||Which db you are using SQL Server 2000/2005?

|||Hi Manivannan..we are using Sql server 2000.|||

I fixed the bug again.. .

Can you check now?

|||

Hi Manivannan... Now i am able to Create. Thank u Very Much...

Creating Duplicate Table

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

It's amazing what a little searching can find.

Check out this thread from just yesterday. It tells you all you need to know.

|||

I have created one, but not tested 100%.. try this code..

Code Snippet

Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid

Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder

Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;

Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;

--Print @.SQL

Exec(@.SQL);
Print 'Table Structered Copied'

If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End

Go

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End

return @.IndexedCols;
End

Go

exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0

|||

Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...

|||

I fixed the bugs.. check now

|||

Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.

|||

Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...

|||Which db you are using SQL Server 2000/2005?

|||Hi Manivannan..we are using Sql server 2000.|||

I fixed the bug again.. .

Can you check now?

|||

Hi Manivannan... Now i am able to Create. Thank u Very Much...

Creating Cursor from Stored Procedure

Hi guys!
i want to create one cursor in the t-sql. the problem is i want to use
stored procedure instead of select command in cursor.

can anyone tell me how can i use stored procedure's o/p to create
cursor?

i'm using sql 2000 and .net 2.0

thanks,

LuckyFirst, try to rewrite your app so you don't use cursors.

Second, if you must use a cursor, you can create a temp table to hold
the output of your stored procedure, and then build a cursor from that;
e.g.:

CREATE TABLE #splat (columnlist)

INSERT INTO #splat
exec myproc

DROP TABLE #splat

Stu

Lucky wrote:
> Hi guys!
> i want to create one cursor in the t-sql. the problem is i want to use
> stored procedure instead of select command in cursor.
> can anyone tell me how can i use stored procedure's o/p to create
> cursor?
> i'm using sql 2000 and .net 2.0
> thanks,
> Lucky|||Post your exact requirement. There can be better method of what you are
trying to do now

Madhivanan

Lucky wrote:
> Hi guys!
> i want to create one cursor in the t-sql. the problem is i want to use
> stored procedure instead of select command in cursor.
> can anyone tell me how can i use stored procedure's o/p to create
> cursor?
> i'm using sql 2000 and .net 2.0
> thanks,
> Lucky|||Stu wrote:
> First, try to rewrite your app so you don't use cursors.

Second, Seriously. Try to rewrite your app so you don't use cursors.

You might also consider dropping the guts of your stored procedure into
a User Defined Function that returns a table. Then you can use that
UDF for both the Stored Procedure and your sketchy thing that uses
Cursors.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/|||Hi ,
Here i'm pasting the sql code that i want to run. the code is ment to
fetch datbase list and update each database with some specific business
logic.

DECLARE authors_cursor CURSOR FOR
SELECT sp_databases

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @.Database_name, @.database_size, @.Remarks

WHILE @.@.FETCH_STATUS = 0
BEGIN

print 'database : ' + @.Database_name
print 'some business logic'

FETCH NEXT FROM authors_cursor
INTO @.au_id, @.au_fname, @.au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

NOTE:

please notice the use of procedure to get list of the database in the
select statement of the DECLARING CURSOR. i want to use stored
procedure's o/p to iterate through the rows returned by the procedure.

Please let me know if you know how to do this.

thanks

Madhivanan wrote:
> Post your exact requirement. There can be better method of what you are
> trying to do now
> Madhivanan
>
> Lucky wrote:
> > Hi guys!
> > i want to create one cursor in the t-sql. the problem is i want to use
> > stored procedure instead of select command in cursor.
> > can anyone tell me how can i use stored procedure's o/p to create
> > cursor?
> > i'm using sql 2000 and .net 2.0
> > thanks,
> > Lucky|||Lucky (tushar.n.patel@.gmail.com) writes:
> Here i'm pasting the sql code that i want to run. the code is ment to
> fetch datbase list and update each database with some specific business
> logic.
> DECLARE authors_cursor CURSOR FOR
> SELECT sp_databases

There is no table?

> OPEN authors_cursor
> FETCH NEXT FROM authors_cursor
> INTO @.Database_name, @.database_size, @.Remarks
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print 'database : ' + @.Database_name
> print 'some business logic'
>
> FETCH NEXT FROM authors_cursor
> INTO @.au_id, @.au_fname, @.au_lname
> END
> CLOSE authors_cursor
> DEALLOCATE authors_cursor
> NOTE:
> please notice the use of procedure to get list of the database in the
> select statement of the DECLARING CURSOR. i want to use stored
> procedure's o/p to iterate through the rows returned by the procedure.

What does "o/p" mean?

It would be interesting to know what "some business logic" contains.

It's possible that you could use sp_MSforeachdb:

EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'

This procedure is undocumented and not supported from Microsoft, so
you would have to look into the source code for the gory details on
how it works. But basically it iterates over all databases, and
run as the SQL statement once for each database. ? works as placeholder
for the database name.

--
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|||yeah there is no table. its just a database list that i need. and the
business logic is very very big to paste here. i need some different
tables in different DB and the idea of using the foreachloop is
interesting but i dont know wether it will work with more than 250
lines of PL SQL code.

the o/p mean OUTPUT.

let me know if you want to know anything else.

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > Here i'm pasting the sql code that i want to run. the code is ment to
> > fetch datbase list and update each database with some specific business
> > logic.
> > DECLARE authors_cursor CURSOR FOR
> > SELECT sp_databases
> There is no table?
> > OPEN authors_cursor
> > FETCH NEXT FROM authors_cursor
> > INTO @.Database_name, @.database_size, @.Remarks
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > print 'database : ' + @.Database_name
> > print 'some business logic'
> > FETCH NEXT FROM authors_cursor
> > INTO @.au_id, @.au_fname, @.au_lname
> > END
> > CLOSE authors_cursor
> > DEALLOCATE authors_cursor
> > NOTE:
> > please notice the use of procedure to get list of the database in the
> > select statement of the DECLARING CURSOR. i want to use stored
> > procedure's o/p to iterate through the rows returned by the procedure.
> What does "o/p" mean?
> It would be interesting to know what "some business logic" contains.
> It's possible that you could use sp_MSforeachdb:
> EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'
> This procedure is undocumented and not supported from Microsoft, so
> you would have to look into the source code for the gory details on
> how it works. But basically it iterates over all databases, and
> run as the SQL statement once for each database. ? works as placeholder
> for the database name.
>
> --
> 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|||Lucky (tushar.n.patel@.gmail.com) writes:
> yeah there is no table. its just a database list that i need.

So how does the actual cursor declaration look like? The code you
posted was incorrect, as it referred to a non-existing column. It's
very difficult to assist when I don't really know what you are trying
to do.

> and the business logic is very very big to paste here. i need some
> different tables in different DB and the idea of using the foreachloop
> is interesting but i dont know wether it will work with more than 250
> lines of PL SQL code.

PL/SQL? What are you using? MS SQL Server or Oracle?

To me it sounds very funny of wanting to run 250 lines of business logic
in multiple databases. I can envision situations where this may be
necessary, but I can also see this as a result of a poor design.

If you explained what your are actually trying to achieve in business
terms, it may be easier to suggest a good solution.

For a general discussion on multiple databases, this section in my
article on dynamic SQL may give some ideas:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB.

--
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 quite dissappointed with your Questions. what i wanted to do so far
is to use output of the stored procedure in the select statement of the
Declaring CURSOR. but you have diverted the conversation on the
different track.

-- first i clearly said in my first post that i'm using MS SQL Server
2000 and .NET 2.0

-- for your convinience i gave you expamle of declaring cursor that i
had copied form the ms sql help but instead of understanding the
problem you complained about the syntaxt though that example was for to
understand the problem but you missed the target.

-- PL SQL is of course in Oracle to write some custom business logic.
the same way you can do in SQL Server the name used in here is T-SQL.
it shouldn't be hard for you to understand.

-- As far as i know, nobody ever asked me what kind of business logic i
want to use. we always disscus problems here and asked for the
solution.

-- what kind of businees logic i'm using and why i'm using and what
should be the size of the logic. these all depends on the requirements
and the scererios. i didn't ask your opinion on that.

We have streached the conversation to far and i dont want to continue
it further more.

thanks for nothing. and by the way i found what i was looking for.

Lucky

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > yeah there is no table. its just a database list that i need.
> So how does the actual cursor declaration look like? The code you
> posted was incorrect, as it referred to a non-existing column. It's
> very difficult to assist when I don't really know what you are trying
> to do.
> > and the business logic is very very big to paste here. i need some
> > different tables in different DB and the idea of using the foreachloop
> > is interesting but i dont know wether it will work with more than 250
> > lines of PL SQL code.
> PL/SQL? What are you using? MS SQL Server or Oracle?
> To me it sounds very funny of wanting to run 250 lines of business logic
> in multiple databases. I can envision situations where this may be
> necessary, but I can also see this as a result of a poor design.
> If you explained what your are actually trying to achieve in business
> terms, it may be easier to suggest a good solution.
> For a general discussion on multiple databases, this section in my
> article on dynamic SQL may give some ideas:
> http://www.sommarskog.se/dynamic_sql.html#Dyn_DB.
> --
> 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|||Lucky (tushar.n.patel@.gmail.com) writes:
> I'm quite dissappointed with your Questions. what i wanted to do so far
> is to use output of the stored procedure in the select statement of the
> Declaring CURSOR. but you have diverted the conversation on the
> different track.

Yes, I want to help you to solve the real problem.

I've been following technical newsgroups on Usenet for many years, and I
early made the observation that when people asked "funny questions" was
that they were trying to get from A to B, but instead they were asking
of how to get from C ro D, because they the way from A to C and from
D to B and now they were standing at deep ravine and not being able to
cross. While there in fact there was a straight motorway from A to B,
which was easy to point to, once the real problem had been uncovered.

> -- for your convinience i gave you expamle of declaring cursor that i
> had copied form the ms sql help but instead of understanding the
> problem you complained about the syntaxt though that example was for to
> understand the problem but you missed the target.

I'm afraid that those are the rules. If you cannot make yourself clear
what you are asking for, then you will not get very good answers. I'm
sorry, but while I'm good at SQL, I am not good reading other people's
thoughts.

> -- PL SQL is of course in Oracle to write some custom business logic.
> the same way you can do in SQL Server the name used in here is T-SQL.
> it shouldn't be hard for you to understand.

It happens frequently enough that people who use Oracle, MySQL or some
other engine post to this newsgroup, that I felt obliged to rule out this
possibility.

--
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 didn't get you. what do you mean by make myself clear? didn't i gave
you the example? check my post before. and i clearly said what i wanted
to know. in the same post. but instead of targeting the problem you
said there is a syntaxt mistake in the code.

The problem is quite simple to understand. and tha is HOW TO USE OUTPUT
OF THE PROCEDURE TO CREATE CURSOR.

is it very hard to understand? i didnt know the syntaxt and all i
wanted to know was the syntaxt.

but instead telling me that, you asked me what kind of business logic i
want to use. do it really matter to know how the cursor can be created
from the output of the procedure?

and if you are member of the group for years than you should at least
be experienced by now to understand what one is asking.

as far as i know. the example i've posted was of MS SQL SERVER wasn't
from Oracle. but you cared to know wether i want to use PL/SQL or
T-SQL? i didn't asked to optimize some code.

ALL I ASKED IS JUST ONE DEFINATION OF CREATING CURSOR.

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > I'm quite dissappointed with your Questions. what i wanted to do so far
> > is to use output of the stored procedure in the select statement of the
> > Declaring CURSOR. but you have diverted the conversation on the
> > different track.
> Yes, I want to help you to solve the real problem.
> I've been following technical newsgroups on Usenet for many years, and I
> early made the observation that when people asked "funny questions" was
> that they were trying to get from A to B, but instead they were asking
> of how to get from C ro D, because they the way from A to C and from
> D to B and now they were standing at deep ravine and not being able to
> cross. While there in fact there was a straight motorway from A to B,
> which was easy to point to, once the real problem had been uncovered.
> > -- for your convinience i gave you expamle of declaring cursor that i
> > had copied form the ms sql help but instead of understanding the
> > problem you complained about the syntaxt though that example was for to
> > understand the problem but you missed the target.
> I'm afraid that those are the rules. If you cannot make yourself clear
> what you are asking for, then you will not get very good answers. I'm
> sorry, but while I'm good at SQL, I am not good reading other people's
> thoughts.
> > -- PL SQL is of course in Oracle to write some custom business logic.
> > the same way you can do in SQL Server the name used in here is T-SQL.
> > it shouldn't be hard for you to understand.
> It happens frequently enough that people who use Oracle, MySQL or some
> other engine post to this newsgroup, that I felt obliged to rule out this
> possibility.
>
> --
> 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|||Lucky (tushar.n.patel@.gmail.com) writes:
> i didn't get you. what do you mean by make myself clear?

That I did not understand what you was looking for. And I am sorry,
to that end I am the sole judge. You may know what you were looking
for, but that does not mean that you manage to convey that message.

> The problem is quite simple to understand. and tha is HOW TO USE OUTPUT
> OF THE PROCEDURE TO CREATE CURSOR.

And that is a such a strange thing to, thar there is all reason to ask
what you want really want to do. In fact, any question that involves a
cursor will be met with the suspicion that the cursor may not be needed.

But there is also one more reason to ask what you really want to do:
there may be several options, and which is the best one, depends on
your actual business problem.

Finally, please remember that on Usenet you never get less help than
you pay for.

--
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 agree with you on avoiding CURSORs and i always welcome suggestions
on doing things other ways.
the foreach loop you suggested me was a good suggestion. i already
tried that that to avoid cursor but the problem was with the bunch of
line to modify tables,procedures,views and all i wanted to do is to
write some logic to update all database rather than doing it manually.

i found the way out and i did it.

but you know when you are on job you have immense pressure on you and
that time you can't wait to explain everything. if it would be
something that i needed for more then 1 time then i would have
discussed the problem in more detail and of course also might welcomed
your suggestions.

i trully appriciate the help i get from groups and that is why i always
prefer groups then tutorials and books. Learning from others experience
is always better then anything.

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > i didn't get you. what do you mean by make myself clear?
> That I did not understand what you was looking for. And I am sorry,
> to that end I am the sole judge. You may know what you were looking
> for, but that does not mean that you manage to convey that message.
> > The problem is quite simple to understand. and tha is HOW TO USE OUTPUT
> > OF THE PROCEDURE TO CREATE CURSOR.
> And that is a such a strange thing to, thar there is all reason to ask
> what you want really want to do. In fact, any question that involves a
> cursor will be met with the suspicion that the cursor may not be needed.
> But there is also one more reason to ask what you really want to do:
> there may be several options, and which is the best one, depends on
> your actual business problem.
> Finally, please remember that on Usenet you never get less help than
> you pay for.
> --
> 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