Database

Save Query Result as XML File in SQL Server 2005

19-Jan-09 6:52

You're welcome

The need to optimize rises from a bad design.My articles[^]

Abhijit Jana18-Jan-09 23:22

Abhijit Jana

18-Jan-09 23:22

Hi,

My stored procedure returning XML data (I am using FOR XML Auto ) . I want to save the same as XML File from that procedure itself . I am using SQL Server 2005 .
Any Idea to implement  Smile | :)

Thanks in advance !

cheers,
Abhijit
CodeProject MVP
My Recent Article : Exploring Session in ASP.Net

Done !!!

Abhijit Jana19-Jan-09 2:04

Abhijit Jana

19-Jan-09 2:04

I have used bcp and execute it by xp_cmdshell Smile | :)

cheers,
Abhijit
CodeProject MVP
My Recent Article : Exploring Session in ASP.Net

Re: Done !!!

Wendelius19-Jan-09 8:50

How to get leaf nodes of a particular Parent using sql Query

19-Jan-09 8:50

Just as an alternative because xp_cmdshell has some security issues, I think you could create a linked server against ODBC DSN and use ODBC Text Driver. Using that, you could write to the linked server as it was a table.

The need to optimize rises from a bad design.My articles[^]

vidhyap18-Jan-09 23:10

vidhyap

18-Jan-09 23:10

Hi,

I have to query the table,which have more than 3 or 4 level of hierarchy.
I need to get all the leaf nodes of particular Parent.i tried Querying like this-select * from Hierarchy where UniqId not in (select Parent from Hierarchy).but it returns leaf nodes of all the parent.what i need is to get leaf nodes of particular Parent.

can anybbody please help me to solve this issue,its realy very important.

Thanks and regards
Vidhya

Re: How to get leaf nodes of a particular Parent using sql Query

kingindra18-Jan-09 23:16

kingindra

18-Jan-09 23:16

can you please let me know the table structure?

Re: How to get leaf nodes of a particular Parent using sql Query

Wendelius19-Jan-09 2:09

19-Jan-09 2:09

You could use CTE (Common Table Expression) recursively possibly in an inline view and then restrict based on level (meaning there are no children under current node). Have a look at: http://msdn.microsoft.com/en-us/library/ms186243.aspx[^]

The need to optimize rises from a bad design.My articles[^]

Insert Stmt returns -1

AB777118-Jan-09 19:43

18-Jan-09 19:43

Hi all,
I have written an insert query in a Stored Proc,
It works fine and inserts an record in the table, but
it returns -1 when i execute it thru C# Code.

Can anyone tell me why this happens?

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

Reza Raad18-Jan-09 20:30

Reza Raad

18-Jan-09 20:30

first paste your stored procedure code here.
do you return any thing in your procedure code?

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

AB777118-Jan-09 20:39

18-Jan-09 20:39

i dont return any thing from the proc, but the default value returned from an insert proc is 1,
in my case donno y it returns -1.
I have done some calculations into some variables and a simple insert statement is used to
insert the record in the table,
I have also used a transaction for this and
after the insert statment i have checked if the @intErrorCode is not 0 then rollback transaction,
The insert works fine but it returns -1.

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

Reza Raad18-Jan-09 21:21

Reza Raad

18-Jan-09 21:21

if you call another stored procedure for insert into this procedure?
if this is true:
call to another procedures into one procedure cause returning -1

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

Aman Bhullar18-Jan-09 20:45

Aman Bhullar

18-Jan-09 20:45

Which function you are using
ExecuteScaler, executenonquery, etc

Please specify

AB777118-Jan-09 20:52

18-Jan-09 20:52

dbcommand.ExecuteNonQuery();

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

N a v a n e e t h18-Jan-09 21:33

N a v a n e e t h

18-Jan-09 21:33

Do you mean ExecuteNonQuery returns -1? If yes, see what the documentation says

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Navaneeth

How to use google | Ask smart questions

AB777118-Jan-09 22:35

18-Jan-09 22:35

yes, tht's a problem, i can the values in the table that means the insert statement was executed, and if an error has occured the insert should be rolledback rite, but this rollback is not happening and m not able to find what the error is.

wht r the ways to find the error that has caused the rollback?

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

Wendelius19-Jan-09 2:13

19-Jan-09 2:13

In your stored procedure you can use RAISERROR[^] to generate an error and to inform client what has happened.

The need to optimize rises from a bad design.My articles[^]

N a v a n e e t h19-Jan-09 14:53

N a v a n e e t h

19-Jan-09 14:53

Since you are using Stored procedures, you can handle the errors inside the procedure and rollback if necessary. Add an output parameter which can be used to inform the client program about the process status. Something like,

CREATE PROCEDURE Foo
   @FooColumn int = NULL
   @ProcessStatus varchar(100) = NULL
AS
   // your insert query
   IF @@ERROR <>0
   BEGIN
      ROLLBACK
      SET @ProcessStatus = 'Rollbacked'
   END
   ELSE
   BEGIN
      SET @ProcessStatus = 'Completed'
   END

Navaneeth

How to use google | Ask smart questions

AB777119-Jan-09 22:58

Creating relation between two tables in VS2008 & SQL Express ?

19-Jan-09 22:58

Thanks guys for all you suggestions,
I found the problem with my stored proc,
I had written SET NOCOUNT ON;
which was causing the -1 to return.
Now i have switched oFF that and it returns me 1.

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

Mohammad Dayyan18-Jan-09 2:18

Mohammad Dayyan

18-Jan-09 2:18

Hi experts Smile | :)

I've created a database ( named database1.sdf )
Then , I've created two tables in it. ( date[^] & text[^] )
Now I'm going to create a relation between them with VS2008 SP1.

But this error[^] has occurred Frown | :(

What's wrong with it ?
How can I resolve it ?

Thank you in advance

Re: Creating relation between two tables in VS2008 & SQL Express ?

Wendelius18-Jan-09 7:25

Re: Creating relation between two tables in VS2008 & SQL Express ?

18-Jan-09 7:25

Most likely you have a mismatch in the code behind of xsd itself. Click DateID column in your dataset designer and check the DataType property from properties window.

The need to optimize rises from a bad design.My articles[^]

Mohammad Dayyan18-Jan-09 8:45

Mohammad Dayyan

18-Jan-09 8:45

Wow, thanks Smile | :)

Re: Creating relation between two tables in VS2008 & SQL Express ?

Wendelius18-Jan-09 8:57

18-Jan-09 8:57

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

trigger problem

monuSaini16-Jan-09 20:05

monuSaini

16-Jan-09 20:05

Hello Friends,

I have create an trigger on an table insert and update event :

CREATE TRIGGER sp_Kp1 ON [dbo].[OEORDD]
for insert, update

AS
DECLARE @x DECIMAL(18,2);
DECLARE @y varchar(10);
DECLARE @z DECIMAL(18,2);

SELECT @x = xx FROM inserted;
SELECT @y = yy FROM inserted;
SELECT @z = zz FROM table1 where xx= @x and yy = @y;

INSERT INTO table2 (tx,ty,tz) VALUES @x,@y,@z);

This trigger is working fine only if one row is updated
but it fails if i update more than one row using update query.

Thanks n regards,

rahul saini

Re: trigger problem

Wendelius16-Jan-09 21:52