How can I get value of XML element from XML column

I have one simple question. How can I get scalar value of en XML element from XML column?

...

<tag> value <tag/>

...

I wont to get a result: "value"

this runs against a XML variable, but afaik, it should work in a similar manner:

set nocount on
DECLARE @.xVar XML
declare @.docHandle int
SET @.xVar =
'<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<FirstName>Michael</FirstName>
<LastName>Howard</LastName>
</author>
<author>
<FirstName>David</FirstName>
<LastName>LeBlanc</LastName>
</author>
<price>39.99</price>
</book>'

SELECT
nref.value('FirstName[1]', 'nvarchar(50)') FirstName,
nref.value('LastName[1]', 'nvarchar(50)') LastName
FROM @.xVar.nodes('/book/author') AS R(nref)
|||or you could simple do this..seems more concise to me

declare @.xml nvarchar(100)
declare @.id int

set @.xml = '<tag> value </tag>'

exec sp_xml_preparedocument @.id output, @.xml
select tagVal from openxml(@.id, N'//tag') with (tagVal varchar(100) 'text()')|||

Don't forget the :

exec sp_xml_removedocument @.Id

It is an alternative. I'm currently looking at code to try and determine whether the .nodes methods available are faster/the same/slower than OPENXML.

|||

The nodes() method on an indexed column is faster/same than doing server side cursors. It also scales better w.r.t. memory usage.

OpenXML is faster on parameters/variables than nodes(), but less scalable.

Best regards

Michael

How can I get value from data flow control?

How can I get value from data flow control when it returns to the Control flow?

I want to use values in the data flow to decide what I'm doing next - using an expression on the 'line' (i.e. @.Step == 10, 20 , 30 ...)
@.Step - is user defined variable.

How can I set @.Step according to the data flow ?

It seems to be that I can't change @.Step in the data flow.
Its not clear what you mean by set @.step according to the data flow. According to what? In any event most components do no set variable values in the dataflow they just look at them. The exceptions are row count and script (there could be another but I don't recall it off the top of my head). Row count only sets a variable at post execute. The script component can set a variable whenever it wants but in order to do so it can not use the variables defined on the ReadWrite variables line as those can only be set during post execute. In order to set a variable during execution you need to use the variable dispenser to lock it and then you can set it to whatever you would like. Remember to unlock it or else anything else trying to lock it will fail.

Thanks,
Matt|||

The reason I need this is because I want to define a table of tasks and that the process will continue according to the next step on the table (not as a fix path).

I don't see a script control in the data flow - so how can I change the variable ?

Thanks,

Yossi.

|||

Where is your problem?

1. Define your variable (In this example it is named Vari )

2. Drag a Script-Component (transformation) to your DataFlow-Task.

3. Select theScript Tab

4. Enter the name of the variable (Vari ) as the value of the ReadWriteVariables.

5. Click Design Script.

7. Edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

End Sub
Public Overrides Sub PostExecute()
Me.Variables.Vari = 1234
End Sub

End Class

Notice, there is no code in the ProcessInputRow. You can delete it.

|||

Thanks, it is helpful.

I want to change the variable according to a data source.

How can I access the data source which was entering to the Script-Component within the script?

.

|||

Check the columns you need in the Input Columns.

and then edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim Max As Integer

Public Overrides Sub PreExecute()
Max = 0
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
If Row.InputColumn > Max Then Max = CInt(Row.InputColumn)
End Sub

Public Overrides Sub PostExecute()
Me.Variables.Vari = Max
End Sub
End Class

|||

I'm geting this msg:

"sub 'Input_ProcessInputRow' can not be declared 'override' because it dose not override a sub in base class"

|||

Seems like you are editing Script Task instead of Data Flow Script Transform (or maybe Data Flow Script Component, but Source instead of Transform).

Of course, it is possible the script task (or existing task, e.g. Execute SQL Task) is what you really need - depending on your needs and what you mean by "change the variable according to a data source". Could you describe it in more detail?

I also suggest reading SQL Books Online to get general understanding of SSIS architecture, the difference between tasks and transforms, etc.

How can I get value from data flow control?

How can I get value from data flow control when it returns to the Control flow?

I want to use values in the data flow to decide what I'm doing next - using an expression on the 'line' (i.e. @.Step == 10, 20 , 30 ...)
@.Step - is user defined variable.

How can I set @.Step according to the data flow ?

It seems to be that I can't change @.Step in the data flow.
Its not clear what you mean by set @.step according to the data flow. According to what? In any event most components do no set variable values in the dataflow they just look at them. The exceptions are row count and script (there could be another but I don't recall it off the top of my head). Row count only sets a variable at post execute. The script component can set a variable whenever it wants but in order to do so it can not use the variables defined on the ReadWrite variables line as those can only be set during post execute. In order to set a variable during execution you need to use the variable dispenser to lock it and then you can set it to whatever you would like. Remember to unlock it or else anything else trying to lock it will fail.

Thanks,
Matt|||

The reason I need this is because I want to define a table of tasks and that the process will continue according to the next step on the table (not as a fix path).

I don't see a script control in the data flow - so how can I change the variable ?

Thanks,

Yossi.

|||

Where is your problem?

1. Define your variable (In this example it is named Vari )

2. Drag a Script-Component (transformation) to your DataFlow-Task.

3. Select theScript Tab

4. Enter the name of the variable (Vari ) as the value of the ReadWriteVariables.

5. Click Design Script.

7. Edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

End Sub
Public Overrides Sub PostExecute()
Me.Variables.Vari = 1234
End Sub

End Class

Notice, there is no code in the ProcessInputRow. You can delete it.

|||

Thanks, it is helpful.

I want to change the variable according to a data source.

How can I access the data source which was entering to the Script-Component within the script?

.

|||

Check the columns you need in the Input Columns.

and then edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim Max As Integer

Public Overrides Sub PreExecute()
Max = 0
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
If Row.InputColumn > Max Then Max = CInt(Row.InputColumn)
End Sub

Public Overrides Sub PostExecute()
Me.Variables.Vari = Max
End Sub
End Class

|||

I'm geting this msg:

"sub 'Input_ProcessInputRow' can not be declared 'override' because it dose not override a sub in base class"

|||

Seems like you are editing Script Task instead of Data Flow Script Transform (or maybe Data Flow Script Component, but Source instead of Transform).

Of course, it is possible the script task (or existing task, e.g. Execute SQL Task) is what you really need - depending on your needs and what you mean by "change the variable according to a data source". Could you describe it in more detail?

I also suggest reading SQL Books Online to get general understanding of SSIS architecture, the difference between tasks and transforms, etc.

How can I get Transactional Republishing to work?

I'm starting a new thread to keep things clean and simple. This is a transactional republishing issue.

In my simplest scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. (And C will need to transactionally replicate to D, but ignore that for now). All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C distribution doesn't work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I've gotten everything working except this. Who out there has done this and what is the secret?

We have not had any problems doing this in SQL 2000 with 36 replicated tables. In an earlier test, I had SQL 2000 Replication running on this box too, but I completely removed SQL 2000 from this box (except for my Virtual PC which is not running). I don't want to convert transactional to merge. I am running merge replication on 13 tables at the same time as the transactional replication, but am replicating different tables in each. I have no desire to increase the number of merge replicated tables.

Thanks for any advice, including any information about why this is an issue with 2005 and wasn't in 2000.

Paul

And fyi -

I followed all the rules specified here:

http://msdn2.microsoft.com/en-us/library/ms152553(SQL.90).aspx

They did not help.

|||

Hi PDav,

How do you create DatabaseC? Is it created from scratch (use CREATE DATABASE) or restored from other database? Have you ever used it for any other replication purpose? As I said in another thread, replication thinks the table is published so it issues this error. So I want to know why replication think the table is published?

Could you try this query on DatabaseC and let me know the result?

select name, category from sysobjects where name = 'MyReplTable'

Thanks,

-Peng

|||

Peng,

Sure, but at what point-in-time? Without implementing any replication? Without implementing replication from BtoC? With replication in both?

Oh and yes, all databases are copied from backups.

|||

PDav,

With replication in both and without implenting any replication. This is just to know if this is introduced when configuring tran republishing.

So DatabaseC is copied from backups. From which backups? 80 server or other database? And do you use UI to do the backup or T-SQL?

Peng

|||

-- So DatabaseC is copied from backups. From which backups? 80 server or other database? And do you use UI to do the backup or T-SQL?

The backups for the 3 databases were created by me using script from SQL 2000 (sp4) DBs and restored with script.
Databases came from replication working SQL 2000 environment.

Example BackkupScript follows:

BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyDirectory\MyDatabase.bak'
WITH INIT,STATS

--Executed query: SELECT name, category from sysobjects where name = 'MyReplTable'

-- With merge replication implemented successfully AtoB and BtoC (using entirely different tables than tran replication)
-- With tran replication implemented successfull AtoB only. BtoC distribution fails with error ("Cannot drop the table...")

-- Run at database A
name category
MyReplTable 32

-- Run at B
name category
MyReplTable 32

-- Run at C
name category
MyReplTable 0

--After removing all replication from all levels

-- Run at database A
name category
MyReplTable 0

-- Run at B
name category
MyReplTable 0

-- Run at C
name category
MyReplTable 0

|||

Sorry, PDav. Looks like sysobjects table doesn't give us enough information to do troubleshooting. Could you use the following query again:

select * from sys.objects where name = 'MyReplTable'

Also, can you post a sample of your restore database command?

Thanks,

|||

Example Scripts follows:

BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyDirectory\MyDatabase.bak'
WITH INIT,STATS
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\MyDirectory\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\MyDirectory\data\MyDatabase.mdf',
MOVE 'MyDatabaseLog' TO 'C:\MyDirectory\log\MyDatabaseLog.ldf',
REPLACE,STATS

--Executed query: select * from sys.objects where name = 'MyReplTable'

-- With tran replication implemented successfull AtoB only. BtoC distribution fails with error ("Cannot drop the table...")

-- Run at database A

(to make readable you can use Regular expressions to replace tabs with newlines)
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 1419152101 NULL 1 0 U USER_TABLE 2003-10-23 17:38:30.417 2006-03-20 14:49:19.340 0 1 0

-- Run at B
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 981356498 NULL 1 0 U USER_TABLE 2006-03-20 14:49:38.560 2006-03-20 14:52:25.333 0 1 0

-- Run at C
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 468118100 NULL 1 0 U USER_TABLE 2006-02-16 17:27:13.717 2006-03-01 10:07:52.900 0 0 0

--After removing all replication from all levels

-- Run at database A
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 1419152101 NULL 1 0 U USER_TABLE 2003-10-23 17:38:30.417 2006-03-20 15:51:22.943 0 0 0

-- Run at B
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 981356498 NULL 1 0 U USER_TABLE 2006-03-20 14:49:38.560 2006-03-20 15:51:21.990 0 0 0

-- Run at C
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 468118100 NULL 1 0 U USER_TABLE 2006-02-16 17:27:13.717 2006-03-01 10:07:52.900 0 0 0

|||I also have a highly abbreviated outline of my build script if that is useful. (280 lines with about half of that space lines)|||

From the result, looks like the error message is not caused by table is published by tran replication. The only other possiblity I can think of is server thinks the table is published by merge replication.

I know you have merge replication, but publish different table. Just want to make sure, could you run the following query at DatabaseC?

select * from sys.tables where name='MyReplTable'

Thanks,

|||

Wow. You are correct! Here are results of 3 of the 36 tables. All 36 have the same results. So why do they have 1 for "is_merged_published" and how can I fix that? We don't include them in any merge publications, anywhere. They are only transactionally replicated.

Results for 3 tables (I removed table name column)

object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row

982215941 NULL 1 0 U USER_TABLE 2006-02-16 17:27:40.137 2006-03-01 10:08:02.540 0 0 0 0 NULL 17 0 1 0 0 1 0 0 0 0

978739931 NULL 1 0 U USER_TABLE 2006-02-16 17:27:43.043 2006-03-01 10:08:02.353 0 0 0 0 NULL 7 0 1 0 0 1 0 0 0 0

944643800 NULL 1 0 U USER_TABLE 2006-02-16 17:27:22.137 2006-03-01 10:08:02.180 0 0 0 0 NULL 6 0 1 0 0 1 0 0 0 0

|||

Great. The bad news is I am not sure how to fix it. I will see if I can get someone who knows how to do it. The apparent solution is to drop DatabaseC and recreate it from scratch though.

I am also curious how this "is_merged_published" flag is introduced. But the answer the following questions may be helpful (also to the people who knows who to fix it).

1) You have merge replication. Is it on DatabaseA/B/C but publish different tables, or it is just on some totally different DBs?

2) Have you create any merge publications on the database (in SQL2000) from which DatabaseC is restored?

Thanks,

|||

Answers:

1) You have merge replication. Is it on DatabaseA/B/C but publish different tables, or it is just on some totally different DBs?

It is on the same databases (A,B,C) but totally different tables. I define and start my merge publications first. Then I define and start my transactional publications. At the moment, I put in a 3 minute "waitfor" between the AtoB and the BtoC publications in both merge and trans, to ensure that the jobs are properly setup, in sequence, to allow for republishing.

2) Have you create any merge publications on the database (in SQL2000) from which DatabaseC is restored?

I may have taken the backups from "actively" replicating SQL 2000 databases in all cases (however no actual replication would have been occuring because it was from my test server, used only by me). It is also possible that I removed replication first, can't remember. In any case, the source databases are setup exactly as I have described here, same db names, same table names, same publication names, same replication job names.

|||

I'm going to restore Database C from a backup that has no replication. Then I'll run the command to show if tables are marked for merge replication. Then I'll run my script. Then I'll check to see if they are marked for merge replication.

Peng, thanks very much for your help. This gives me a way to proceed. I'll update this thread with my results.

|||

PDav,

Could you send me your build script? Maybe I can try and see if I can repro it on my own server? If you don't want to post it on this thread, you can send it to my email (psongbox@.hotmail.com)

Thanks,

how can i get to rows five to ten in my table?

I need to pull out rows five to ten with my sqlDataSource. is this possible?

If you are using SQL Server (Express) 2005, you can use theRow_Number function

|||

How do i use this. is it easy? CAN I WRITE sql DIRECT TO THE SQLDATASOURCE?

|||

Here is a sample:

<asp:SqlDataSourceID="SqlDataSource1"Runat="server"SelectCommand="SELECT ProductID, ProductName, CategoryID FROM (SELECT ProductID, ProductName, CategoryID, ROW_NUMBER() OVER(ORDER BY [ProductID] ASC) as rowNum FROM Products) t WHERE rowNum>4 and RowNum<=10"ConnectionString="<%$ ConnectionStrings:NWConnectionString %>">

</asp:SqlDataSource>

<asp:GridViewID="GridView1"runat="server"DataSourceID="SqlDataSource1"AutoGenerateColumns="False"DataKeyNames="ProductID">

<Columns>

<asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"

ReadOnly="True"SortExpression="ProductID"/>

<asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/>

<asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/>

</Columns>

</asp:GridView>

|||

Thanks. That worked!!

How can i get time?

There is a field which type is datetime in my table, when the filed is '2006-06-03 23:00:00', how can i get '2006-06-03' from the field?

TRY THIS

SELECT CONVERT(VARCHAR(10), '2006-06-03 23:00:00')

REGARDS

ANAS

|||

This only works if the field is a varchar. You would need to set a different style or you will get "Jun 3 200". See below...

IF OBJECT_ID('test', 'U') IS NOT NULL

DROP TABLE dbo.test

GO

CREATE TABLE dbo.test

(

testDate datetime

)

INSERT dbo.test (testDate) VALUES ('2006-06-03 23:00:00')

--This returns "Jun 3 200"

SELECT CONVERT(VARCHAR(10), testDate) FROM dbo.test

--This returns 2006-06-03

SELECT REPLACE(CONVERT(VARCHAR(10), testDate, 102), '.', '-') FROM dbo.test

how can i get this query

Hi can anybody give me a suggestion to get result like

Student ComputerGeographySocial

ID

Name

ID

SEM

Marks

ID

SEM

Marks

ID

SEM

Marks

1

ABC

1

1

80

1

2

35

1

3

88

2

CDE

2

1

60

3

2

65

2

3

45

3

XYZ

2

2

55

3

1

67

3

1

75

and I want OUTPUT as below

Name

Computers Total

Geography Total

Social Total

ABC

80

35

88

CDE

115

45

XYZ

67

65

75

how can i write a query to get it? Thanx in help

Thanx-Chinni

WHat about:

SELECT
SUM(c.Marks) AS [ComputerTotal],SUM(G.Marks) AS [Geography Total],Sum(So.Marks) AS [SocialTotal]
FROM Student S
LEFT JOIN Computer C
ON s.ID = C.ID
LEFT JOIN GeoGraphy G
ON S.ID = C.ID
LEFT JOIN Social So
ON S.ID = So.ID

Does that work for you ?

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Oh thanx its working now. But the problem is I executed this query in sql server management studio but how can I execute the same query using ado.net with c#.

I tried like this

SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter("our query string ", myConnection);

Dataset _dataSet = new DataSet("student");

_sqlDataAdapter.Fill(_dataSet,"student");

But it shows error

Thanx-Nagu

|||

Oh sorry I did a small mistake and now its working any how thank you very much

Nagu

How can I get this function be working?

How can I get this function be working?

CREATE FUNCTION MyFunc

(

@.MyDate as datetime,

@.MyTableName varchar(50),

)

RETURNS TABLE

AS

RETURN

SELECT * FROM @.MyTableName Where myDate=@.MyDate

? You can't -- passing a table name dynamically would require dynamic SQL, and dynamic SQL is not supported in UDFs... Why do you want to pass a table name dynamically, anyway? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:d565e7f4-4b5a-4e7f-989d-3e6299fbda2c@.discussions.microsoft.com... How can I get this function be working? CREATE FUNCTION MyFunc ( @.MyDate as datetime, @.MyTableName varchar(50), ) RETURNS TABLE AS RETURN SELECT * FROM @.MyTableName Where myDate=@.MyDate|||

Ok. Thanks for the reply.

I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?

EXECUTE sp_executesql @.SQLSelectString

-- forward the result set in a table so that other procedures can read it.

|||? There are a few methods. I recommend that you read the following article: http://www.sommarskog.se/share_data.html -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:f9f7fbde-732a-43bf-a322-001a6f668f95@.discussions.microsoft.com... Ok. Thanks for the reply. I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it? EXECUTE sp_executesql @.SQLSelectString -- forward the result set in a table so that other procedures can read it.

How can I get this function be working?

How can I get this function be working?

CREATE FUNCTION MyFunc

(

@.MyDate as datetime,

@.MyTableName varchar(50),

)

RETURNS TABLE

AS

RETURN

SELECT * FROM @.MyTableName Where myDate=@.MyDate

Can you describeexactly what your problem is andexactly where you are trying to use the function. Are you getting error messages? If so, show them.

My guess is that the problem is you are trying to use a parameter as the table name. You cannot do that.

|||

Ok. Thanks for the reply.

I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?

EXECUTE sp_executesql @.SQLSelectString

-- forward the result set in a table so that other procedures can read it.

How can I get the value from Product_id in Buy that is primarykey and insert it in Product_id in

How can I get the value from Product_id in Buy that is primarykey and insert it in Product_id in Product that is ForeignKey?

My tables look like the following.

table Buy
buy_id identity int PK
invoicenumber int

table Product
Price int
Articlenumber int
Description varchar
Product_id PK
buy_id FK

my code-

CREATE PROCEDURE anotherBuy
AS
BEGIN
INSERT INTO Buy(InvoiceNumber) VALUES (50);
/*Get Product_id*/
INSERT INTO Product(Price, ArticleNumber, Description, Buy_id) VALUES (50, 12, 'tv Sony 50' /*, Product_id*/);
END

CREATE function nyttK?p()
RETURNS int
BEGIN
DECLARE @.buyId as int;
SET @.buyId = 1;
INSERT INTO K?p(Fakturanummer) VALUES (@.buyId);
RETURN /*K?p_id*/ 1
END;*/

/*DECLARE @.buyId as int;*/
/*SET @.buyId = nyttK?p();*/

/*INSERT INTO Produkt (Pris, Artikelnummer, beskrivning) VALUES (30, 101, 'tv');*/

KE

The following query may help you to get idea, In function you can't do any insert/update/delete operations..

Code Snippet

Create table Buy

(

buy_id int identity(1,1) Primary Key,

invoicenumber int

)

Create table Product

(

Product_id int Identity(1,1) Primary Key,

buy_id int references Buy(buy_id),

Price int,

Articlenumber int,

Description varchar(100)

)

Go

Declare @.Buy_id int;

Insert Into Buy Values(50);

--Store the Inserted Buy id in variable

Set @.Buy_id = Scope_Identity();

--Reuse the variable on other quires

Insert Into Product Values(@.Buy_id, 100, 8736, 'First Product');

Insert Into Product Values(@.Buy_id, 1000, 8746, 'Secods Product');

Insert Into Product Values(@.Buy_id, 1000, 4346, 'Third Product');

select * from Buy

Select * from Product

|||

Thank you for that answer.

You helped me with the funktion Scope_Identity();.

My problem is now that I want to execute a trigger that insert the value from the primarykey. The problem is that if I write

DECLARE @.buyId int;

SET @.buyId = Scope_Identity();

outside the trigger. the compiler says that I must declare @.buyId. If I write inside the trigger, like the code below, Scope_Identity(); will return null.

NSERT INTO Buy(InvoiceNumber) VALUES (80);

SELECT InvoiceNumber, Buy_id FROM Buy;

DROP TRIGGER BuyTrigger

CREATE TRIGGER BuyTrigger

ON Buy

FOR INSERT AS

BEGIN

DECLARE @.buyId int;

SET @.buyId = Scope_Identity();

SELECT @.buyId

INSERT INTO Product (Price, ArticleNumber, Description, Buy_id) VALUES (50, 12, 'tv Sony 50', 50);

END

Is it possible to solv with a trigger, or?

|||

Yes. Once the new/next insert statement called on the current scope the value on the Scope_Identity() will be reset.

Can you tell more about your requirement. I can't able to understand what exactly you are trying to do.. pls explain bit more..

|||

Hello!

I have solved the problem with a procedure (with a refrence variable) instead. Look below.

You seem to have very good knowledge Manivannan.D.Sekaran. Can I ask you where you work?

-Write to table Product and get Primary key value --

CreateProcedure insert_buy (@.buy_id int OUT)

AS

DECLARE @.buyId int;

BEGIN

INSERTINTO Buy(InvoiceNumber)VALUES(8);

SELECT InvoiceNumber, Buy_id FROM Buy

SET @.buy_id =Scope_Identity();

END

Write to Product -

CREATEPROCEDURE insert_Product(@.buy_id int)

AS

BEGIN

INSERTINTO Product(Price, ArticleNumber, Description, Buy_id)VALUES(50, 12,'tv Sony 50', @.buy_id);

SELECT Price, ArticleNumber, Description, Buy_id FROM Product;

END

-- Start a buy --

DECLARE @.x int

execute insert_buy @.x OUT;

IF @.x > 0

BEGIN

execute insert_Product @.x;

END

ELSE

BEGIN

PRINT'buyID is 0';

END

How can I get the username from an application?

Hi everybody,

Here the environment is application in 3 layers.
So the user in his client machine, access the application in an application server that sends the request to my sql server database server.
My problem is: when I run sp_who from Query Analyzer, I see the host name that is pre-defined inside code application. But, actually, I need to know the user who is requesting the transactio so I can know right the person who is slowing my database performance...
Do you know, any function, procedure or anything else that I can use in the application code in order to provide the user or the host name from the client machine???
I hope some answers...

Thank you all,Look up NILADIC functions in Books Online.|||host_name()|||Hi all,

I have this doubt: may I apply SP3a in my SQL Server production database while it is operational?
I mean, may I do this version upgrade while the databases are online and users continue accessing the databases normally?
My actual version is 8.00.194 (no SP applied), and I am planning to apply SP3a.

Thank you in advance,|||No, when applying sp3a to a server that is currently at RTM (what you load from the CD) will require a reboot of Windows. There will be a brief service interruption while the machine reboots.

-PatP|||host_name()Will host_name() help in a three tier environment? I would expect it to return the name of the middleware server.

-PatP|||So this function host_name() doesn't help me, because I need to see the client host name during sp_who...
Does someone can explain a solution for this?

Thanks,|||The easiest way is to have your middleware set the hostname (http://support.microsoft.com/default.aspx?scid=kb;en-us;140301) equal to however it identifies the caller (user name, account number, etc). There are other ways to do this, but they get ugly very quickly and each solution is VERY specific to the installation/application involved.

-PatP

How can I get the Texbox Action's to diplay a report in a new browser.

Hello,
I would like to click on a report's textbox and it opens up another
report in a new browser? I have the URL and it parameter. I placed them in
the textbox's ACTION attribute but the new report displays in the same
browser.
Any suggestions?
Thanks,
SteveHere is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"steve kwon" <steven,kwon@.lmco.com> wrote in message
news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I would like to click on a report's textbox and it opens up another
> report in a new browser? I have the URL and it parameter. I placed them
> in the textbox's ACTION attribute but the new report displays in the same
> browser.
> Any suggestions?
> Thanks,
> Steve
>|||Thanks, I went that route too. But I will try it again since you were
successful.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Here is an example of a Jump to URL link I use. This causes Excel to come
> up with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "steve kwon" <steven,kwon@.lmco.com> wrote in message
> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I would like to click on a report's textbox and it opens up another
>> report in a new browser? I have the URL and it parameter. I placed them
>> in the textbox's ACTION attribute but the new report displays in the same
>> browser.
>> Any suggestions?
>> Thanks,
>> Steve
>|||You have to have either SP1 or SP2 installed (it was introduced with SP1).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"steve kwon" <steven,kwon@.lmco.com> wrote in message
news:%23jh%23BSukFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Thanks, I went that route too. But I will try it again since you were
> successful.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "steve kwon" <steven,kwon@.lmco.com> wrote in message
>> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I would like to click on a report's textbox and it opens up another
>> report in a new browser? I have the URL and it parameter. I placed
>> them in the textbox's ACTION attribute but the new report displays in
>> the same browser.
>> Any suggestions?
>> Thanks,
>> Steve
>>
>|||Thanks Bruce. It worked.
<Action>
<Hyperlink>="javascript:void(window.open('http://www.google.com',
'_blank'))"</Hyperlink>
</Action>
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23te2YkukFHA.3144@.TK2MSFTNGP12.phx.gbl...
> You have to have either SP1 or SP2 installed (it was introduced with SP1).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "steve kwon" <steven,kwon@.lmco.com> wrote in message
> news:%23jh%23BSukFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Thanks, I went that route too. But I will try it again since you were
>> successful.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "steve kwon" <steven,kwon@.lmco.com> wrote in message
>> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I would like to click on a report's textbox and it opens up another
>> report in a new browser? I have the URL and it parameter. I placed
>> them in the textbox's ACTION attribute but the new report displays in
>> the same browser.
>> Any suggestions?
>> Thanks,
>> Steve
>>
>>
>|||I have been trying this and it is not working for me. I actually have it
embedded in an Iif statement, but even plain navigation JUMP TO URL
expression it is not working. Do I need to add a reference to something else
to get the javascript working?
Here's my code from the expression:
=Iif (Fields!STATUS.Value <>
"6","javascript:void(window.open('"http://<servername/<subdir>/<filename>?FN"
& code.displaylink(Fields!SigFullPath.Value) &
"&PD=','_blank'))","mailto:scconops@.mydomain.com?Subject=Restore%20Request%20For%20Archived%20Document&Body=Please%20restore%20the%20following%20document.%0A%0ACustomer%20Name:%20"&
Fields!DOCNAME.Value & "%0A%0AAcct#:%20"& Fields!ACCOUNTNO.value &
"%0A%0AObligor#:%20" & Fields!OBLIGORNO.value & "%0A%0ASystem:%20"&
Fields!LIBRARY.Value & "%0A%0ASSN:%20"& fields!SSNO1.Value &"&CC="&
(Right(User!UserID,5)) & "@.mydomain.com")
"steve kwon" wrote:
> Thanks Bruce. It worked.
> <Action>
> <Hyperlink>="javascript:void(window.open('http://www.google.com',
> '_blank'))"</Hyperlink>
> </Action>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23te2YkukFHA.3144@.TK2MSFTNGP12.phx.gbl...
> > You have to have either SP1 or SP2 installed (it was introduced with SP1).
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "steve kwon" <steven,kwon@.lmco.com> wrote in message
> > news:%23jh%23BSukFHA.1444@.TK2MSFTNGP10.phx.gbl...
> >> Thanks, I went that route too. But I will try it again since you were
> >> successful.
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
> >> Here is an example of a Jump to URL link I use. This causes Excel to
> >> come up with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "steve kwon" <steven,kwon@.lmco.com> wrote in message
> >> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
> >> Hello,
> >>
> >> I would like to click on a report's textbox and it opens up another
> >> report in a new browser? I have the URL and it parameter. I placed
> >> them in the textbox's ACTION attribute but the new report displays in
> >> the same browser.
> >>
> >> Any suggestions?
> >>
> >> Thanks,
> >> Steve
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

How can I get the table size

Hi, everyone
Could you please tell me how to get the size of a table(including data) in
SQL Sever 2000?have a look in BOL for sp_spaceused, eg
USE pubs
EXEC sp_spaceused 'titles'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||but how can reclaim the unused table size ?

How can I get the table size

Hi, everyone
Could you please tell me how to get the size of a table(including data) in
SQL Sever 2000?
have a look in BOL for sp_spaceused, eg
USE pubs
EXEC sp_spaceused 'titles'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||but how can reclaim the unused table size ?

How can I get the table size

Hi, everyone
Could you please tell me how to get the size of a table(including data) in
SQL Sever 2000?have a look in BOL for sp_spaceused, eg
USE pubs
EXEC sp_spaceused 'titles'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||but how can reclaim the unused table size ?

How can I get the table schema using Entreprise Library DAAB ?

Hello people,

i'm using the DAAB (Enterprise Library) to access the data in my system. But I need to use the schema from the tables i'm reading....

when I was using the default DataAdapter, I have used the FillSchema() method, but now, when I use de DAAB to fill de data, I couldn't get the primary keys columns, unique columns neither autoincrement columns...

This is the code that i'm using to get the data with DAAB:

===========================

Database db = DatabaseFactory.CreateDatabase();

DBCommandWrapper cmd = db.GetSqlStringCommandWrapper("SELECT * FROM Customer");

ds = db.ExecuteDataSet(cmd);

===========================

But this code don't return the schema from the "Customer" table.

Do you have any tip to do it?

thanks people

Andr

Since this is not a Data Mining question, can you try posting to one of these forums?:

.NET Framework Data Access and Storage (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=45)

SQL Server Data Access (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=87)

Raman Iyer
SQL Server Data Mining
http://www.sqlserverdatamining.com

How can I get the sum of hours stored in a datetime datatype

I need to get (In a select statement, for a stored procedure) the SUM of hours stored in a datetime column (column name TaskHours). For example; (1/1/1900 3:30:00 AM, 1/1/1900 5:15:00 AM, 1/1/1900 4:45:00 AM) (3:30 + 5:15 + 4:45) this should sum to 13.5 hours.

(not 12.90)

I appreciate your help!

try this logic:

print

(datediff(minute,convert(datetime,'1/1/1900',101),convert(datetime,'1/1/1900 3:30:00 AM',101))+
datediff(minute,convert(datetime,'1/1/1900',101),convert(datetime,'1/1/1900 4:45:00 AM',101))+
datediff(minute,convert(datetime,'1/1/1900',101),convert(datetime,'1/1/1900 5:15:00 AM',101)))/60.00

thanks

|||

That came up with the correct total. But how would I sum a column of these, not hard coding them?

tblVTasks (TaskHours)

|||

try this:

createTable #time( userIDint,

tt

datetime)

insert

Into #timevalues(1,'1/1/1900 3:30:00 AM')

insert

Into #timevalues(1,'1/1/1900 4:45:00 AM')

insert

Into #timevalues(1,'1/1/1900 5:15:00 AM')

insert

Into #timevalues(2,'1/1/1900 1:30:00 AM')

insert

Into #timevalues(2,'1/1/1900 2:45:00 AM')

insert

Into #timevalues(2,'1/1/1900 2:15:00 AM')

select

userID,ttfrom #time

select

userID,datediff(minute,convert(datetime,'1/1/1900',101),tt)from #time

select

userID,SUM(datediff(minute,convert(datetime,'1/1/1900',101),tt))/60.00from #time

group

by userID

drop

Table #time

I hope that it should be all you need.

Good luck

How can I get the SQL Server 2000 Column Description

How can I get the SQL Server 2000 Column Description within ADO.NET 2.0?

BR / Chris

Hi

The following lines steer U.

Dim _Con As New SqlConnection("...")

Dim _Com As New SqlCommand("Select * from ...", _Con)

_con.Open()

Dim _DTable As DataTable = _Com.ExecuteReader.GetSchemaTable()

DataGrid1.DataSource = _DTable

Pandian S

|||

ColumnName ColumnOrdinal ColumnSize NumericPrecision NumericScale IsUnique IsKey BaseServerName BaseCatalogName BaseColumnName BaseSchemaName BaseTableName AllowDBNull ProviderType IsAliased IsExpression IsIdentity IsAutoIncrement IsRowVersion IsHidden IsLong IsReadOnly DataTypeName XmlSchemaCollectionDatabase XmlSchemaCollectionOwningSchema XmlSchemaCollectionName UdtAssemblyQualifiedName NonVersionedProviderType unit_id 0 8 19 255 unit_id 0 bigint 0

Using the GetSchemaTable method does not work. Above is a listing of what I retrieved for a database table, where a description for unit_id should exist.

/Steffan

How can I get the SQL Server 2000 Column Description

How can I get the SQL Server 2000 Column Description within ADO.NET 2.0?

BR / Chris

Hi

The following lines steer U.

Dim _Con As New SqlConnection("...")

Dim _Com As New SqlCommand("Select * from ...", _Con)

_con.Open()

Dim _DTable As DataTable = _Com.ExecuteReader.GetSchemaTable()

DataGrid1.DataSource = _DTable

Pandian S

|||

ColumnName

ColumnOrdinal

ColumnSize

NumericPrecision

NumericScale

IsUnique

IsKey

BaseServerName

BaseCatalogName

BaseColumnName

BaseSchemaName

BaseTableName

AllowDBNull

ProviderType

IsAliased

IsExpression

IsIdentity

IsAutoIncrement

IsRowVersion

IsHidden

IsLong

IsReadOnly

DataTypeName

XmlSchemaCollectionDatabase

XmlSchemaCollectionOwningSchema

XmlSchemaCollectionName

UdtAssemblyQualifiedName

NonVersionedProviderType

unit_id

0

8

19

255

unit_id

0

bigint

0

Using the GetSchemaTable method does not work. Above is a listing of what I retrieved for a database table, where a description for unit_id should exist.

/Steffan

how can I get the row number(error row) while constraint failure at the OLE DB destination?

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).

What you CAN do is divert the erroring rows elsewhere for examination later.

-Jamie

|||

thank you.

it saves me a lot of time.

|||What good reasons ?

how can I get the row number(error row) while constraint failure at the OLE DB destination?

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).

What you CAN do is divert the erroring rows elsewhere for examination later.

-Jamie

|||

thank you.

it saves me a lot of time.

|||What good reasons ?

How can I get the resultset from a stored procedure

I have a stored procedure which will output a table.
How can I use a sql statement to get the result of the sp?
Thanks a lot>I have a stored procedure which will output a table.
A stored procedure cannot output a table!
> How can I use a sql statement to get the result of the sp?
Get the result to where?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Vincent" <aaa@.aaa.com> wrote in message
news:u0SfhLYGFHA.2824@.tk2msftngp13.phx.gbl...
>I have a stored procedure which will output a table.
> How can I use a sql statement to get the result of the sp?
> Thanks a lot
>|||CREATE #TABLE (
define here the column according to the SP output format
)
INSERT INTO #TABLE EXEC my_sp
Chris
--
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Vincent" <aaa@.aaa.com> wrote in message
news:u0SfhLYGFHA.2824@.tk2msftngp13.phx.gbl...
> I have a stored procedure which will output a table.
> How can I use a sql statement to get the result of the sp?
> Thanks a lot
>|||As a sample :
create table ##Test
(
SPID int,
ecid int,
status varchar(60),
loginame varchar(90),
hostname varchar (20),
blk int,
dbname varchar(30),
cmd varchar(100)
)
insert into ##test exec master.dbo.sp_who
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Vincent" <aaa@.aaa.com> wrote in message
news:u0SfhLYGFHA.2824@.tk2msftngp13.phx.gbl...
> I have a stored procedure which will output a table.
> How can I use a sql statement to get the result of the sp?
> Thanks a lot
>|||Thank you very much
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:eCbEYbYGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> As a sample :
> create table ##Test
> (
> SPID int,
> ecid int,
> status varchar(60),
> loginame varchar(90),
> hostname varchar (20),
> blk int,
> dbname varchar(30),
> cmd varchar(100)
> )
> insert into ##test exec master.dbo.sp_who
>
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "Vincent" <aaa@.aaa.com> wrote in message
> news:u0SfhLYGFHA.2824@.tk2msftngp13.phx.gbl...
>

How can I get the result of SP_Help myTable

Hi,
Since "Sp_help myTable" will give different rows of result, so I could not
run like
"insert into aTable exec sp_help myTable" to get the result of sp_help for
analysing. How could I get the result and analysing it?
And also, I remember that in the past, I could check the code of sp_help to
get some help for doing the same task that sp_help does, but now, I could
not find the definition of sp_help.
Thanks for any advice
Frank
Hi Frank
The definitions of the system stored procedures are all available in the
master database and can be viewed using sp_helptext.
USE master
EXEC sp_helptext sp_help
Since sp_help returns multiple results sets, it will be difficult to put the
results all into one table.
You could take the code of sp_help and create a new proc that takes each
result set and puts it into a separate table.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Frank" <wangping@.lucent.com> wrote in message
news:Obn7NgwPFHA.2652@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Since "Sp_help myTable" will give different rows of result, so I could not
> run like
> "insert into aTable exec sp_help myTable" to get the result of sp_help for
> analysing. How could I get the result and analysing it?
> And also, I remember that in the past, I could check the code of sp_help
> to
> get some help for doing the same task that sp_help does, but now, I could
> not find the definition of sp_help.
> Thanks for any advice
> Frank
>
|||Thanks Kalen,
That's what I want.
B/R
Frank
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ehcxCjwPFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi Frank
> The definitions of the system stored procedures are all available in the
> master database and can be viewed using sp_helptext.
>
> USE master
> EXEC sp_helptext sp_help
> Since sp_help returns multiple results sets, it will be difficult to put
the[vbcol=seagreen]
> results all into one table.
> You could take the code of sp_help and create a new proc that takes each
> result set and puts it into a separate table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:Obn7NgwPFHA.2652@.TK2MSFTNGP10.phx.gbl...
not[vbcol=seagreen]
for[vbcol=seagreen]
could
>

How can I get the result of SP_Help myTable

Hi,
Since "Sp_help myTable" will give different rows of result, so I could not
run like
"insert into aTable exec sp_help myTable" to get the result of sp_help for
analysing. How could I get the result and analysing it?
And also, I remember that in the past, I could check the code of sp_help to
get some help for doing the same task that sp_help does, but now, I could
not find the definition of sp_help.
Thanks for any advice
FrankHi Frank
The definitions of the system stored procedures are all available in the
master database and can be viewed using sp_helptext.
USE master
EXEC sp_helptext sp_help
Since sp_help returns multiple results sets, it will be difficult to put the
results all into one table.
You could take the code of sp_help and create a new proc that takes each
result set and puts it into a separate table.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Frank" <wangping@.lucent.com> wrote in message
news:Obn7NgwPFHA.2652@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Since "Sp_help myTable" will give different rows of result, so I could not
> run like
> "insert into aTable exec sp_help myTable" to get the result of sp_help for
> analysing. How could I get the result and analysing it?
> And also, I remember that in the past, I could check the code of sp_help
> to
> get some help for doing the same task that sp_help does, but now, I could
> not find the definition of sp_help.
> Thanks for any advice
> Frank
>|||Thanks Kalen,
That's what I want.
B/R
Frank
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ehcxCjwPFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi Frank
> The definitions of the system stored procedures are all available in the
> master database and can be viewed using sp_helptext.
>
> USE master
> EXEC sp_helptext sp_help
> Since sp_help returns multiple results sets, it will be difficult to put
the
> results all into one table.
> You could take the code of sp_help and create a new proc that takes each
> result set and puts it into a separate table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:Obn7NgwPFHA.2652@.TK2MSFTNGP10.phx.gbl...
not[vbcol=seagreen]
for[vbcol=seagreen]
could[vbcol=seagreen]
>

How can I get the result of SP_Help myTable

Hi,
Since "Sp_help myTable" will give different rows of result, so I could not
run like
"insert into aTable exec sp_help myTable" to get the result of sp_help for
analysing. How could I get the result and analysing it?
And also, I remember that in the past, I could check the code of sp_help to
get some help for doing the same task that sp_help does, but now, I could
not find the definition of sp_help.
Thanks for any advice
FrankHi Frank
The definitions of the system stored procedures are all available in the
master database and can be viewed using sp_helptext.
USE master
EXEC sp_helptext sp_help
Since sp_help returns multiple results sets, it will be difficult to put the
results all into one table.
You could take the code of sp_help and create a new proc that takes each
result set and puts it into a separate table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Frank" <wangping@.lucent.com> wrote in message
news:Obn7NgwPFHA.2652@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Since "Sp_help myTable" will give different rows of result, so I could not
> run like
> "insert into aTable exec sp_help myTable" to get the result of sp_help for
> analysing. How could I get the result and analysing it?
> And also, I remember that in the past, I could check the code of sp_help
> to
> get some help for doing the same task that sp_help does, but now, I could
> not find the definition of sp_help.
> Thanks for any advice
> Frank
>|||Thanks Kalen,
That's what I want.
B/R
Frank
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ehcxCjwPFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi Frank
> The definitions of the system stored procedures are all available in the
> master database and can be viewed using sp_helptext.
>
> USE master
> EXEC sp_helptext sp_help
> Since sp_help returns multiple results sets, it will be difficult to put
the
> results all into one table.
> You could take the code of sp_help and create a new proc that takes each
> result set and puts it into a separate table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:Obn7NgwPFHA.2652@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Since "Sp_help myTable" will give different rows of result, so I could
not
> > run like
> > "insert into aTable exec sp_help myTable" to get the result of sp_help
for
> > analysing. How could I get the result and analysing it?
> >
> > And also, I remember that in the past, I could check the code of sp_help
> > to
> > get some help for doing the same task that sp_help does, but now, I
could
> > not find the definition of sp_help.
> >
> > Thanks for any advice
> > Frank
> >
> >
>

How can I get the result of a SQL PRINT Statement

I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.

I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.

I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.If you have the SQL Client tools then you can use Profiler to catch the statement that is sent as D-SQL. This is the best way to extract the D-SQLs that are getting executed rather than the Print statement ...

You can on the contrary use a Select and pass this D-SQL statement and catch it in your recordset returned ...

These are some of the options I can think of ...|||Where would I get the SQL Client tools?|||The simplest answer might be to create a DebugLog table and insert your dynamic SQL statement into it.

First, create the table to log your SQL statement:

CREATE TABLE
DebugLog
(
SQLStatement varchar(8000),
AddDate datetime DEFAULT GETDATE()
)

Next, add code to your stored procedure to insert your statement into the log file:

DECLARE @.SQLStatement Varchar(8000)
SET @.SQLStatement = 'SELECT * FROM test'
INSERT INTO DebugLog (SQLStatement) VALUES (@.SQLStatement)
EXEC(@.SQLStatement)

Next, take a look at the SQL statements that were executed (the most recent statement will be on top):

SELECT * FROM DebugLog ORDER BY AddDate DESC

Alternately, you could create an OUTPUT parameter in your sproc and pass the value of @.SQLStatement back to it and display it on your ASP.NET page.

Terri|||Thanks for the help. I'll try that. In point of fact the dynamic sql I'm trying to debug is using sp_executesql with an output parameter. I am trying to retrieve the recordset count based on the criteria parameters I'm passing to it from my asp.net application. In the application, the value returned is zero, which I know cannot be correct. Once I verify the proper assembly of the query, I can concentrate on the syntax of sp_executesql.|||Hi Terri,

When I first saw this post, I was going to respond that PRINT only writes to Query Analyzer. But upon researching it, I found that BOL has this to say about it in the Using PRINT topic:

"The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement."

This suggests that one could capture the error and do something with the PRINT text. But there can be multiple PRINT statements in a sproc, so I assume this would be an SqlException with nested exceptions.

Anyone ever done anything with this? Could be an interesting thing to explore.

Don|||yes, in my data access layer the error handling is like so.

try
{
oCmdExecute.ExecuteNonQuery(); // or ExecuteReader etc...
}
catch(SqlException ex)
{
foreach(SqlError err in ex.Errors)
{
strErrorString = strErrorString + "SqlError: #" + err.Number.ToString () + "\n" + err.Message;
}
strErrorString = strErrorString + "\n\nStored proc: " + sSql + "\n";
}

the SqlError collection will contain all the print statements, of course this only when the stored procedure fails, so you would have to call RAISERROR(' test error ', 16, 1) to force it into the exception block.

Note: that is RAISERROR not RAISEERROR go figure?|||Cool.

Note: that is RAISERROR not RAISEERROR go figure?

Yeah, that's been something odd since the statement was first added to T-SQL.

Don

How can I get the ProcessID of a give stored procedure call?

Hello!

How can I get the ID of the process a given stored procedure call belongs to? I want to make some like this:

create procedure P1

as

declare ProcID numeric
set ProcID=SomeFunction()

insert into T1 values (ProcID, ...)

GO

Thanks a lot.Try @.@.spid

How can i get the process detais using TSQL instructions ?

Hi Friends,
How can i get the process detais using TSQL instructions ?
i want the same information that i get when i execute the
following procedures
"Expand Management, and then expand Current Activity.
Click Process Info.
The current server activity is displayed in the details
pane."
Thank You
Johnny
Hi
You can use profiler to see what EM is doing and it would have come up with
he undocumented procedure:
EXEC sp_MSget_current_activity 51,1
John
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>
|||Hi,
To get information of a SPID then go with this:-
From Query Analyzer, Execute the below system procedure to get the SPID for
the user.
sp_who
After getting the sp_id use the below command to get the process detailsfor
the SPID
DBCC INPUTBUFFER(SPID)
Thanks
Hari
MCDBA
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>
|||select *
from master..sysprocesses
-Sue
On Fri, 18 Jun 2004 12:50:37 -0700, "Johnny Silvestre"
<johnny_silvestre@.yahoo.de> wrote:

>Hi Friends,
>How can i get the process detais using TSQL instructions ?
>i want the same information that i get when i execute the
>following procedures
>"Expand Management, and then expand Current Activity.
>Click Process Info.
>The current server activity is displayed in the details
>pane."
>Thank You
>Johnny

How can i get the process detais using TSQL instructions ?

Hi Friends,
How can i get the process detais using TSQL instructions ?
i want the same information that i get when i execute the
following procedures
"Expand Management, and then expand Current Activity.
Click Process Info.
The current server activity is displayed in the details
pane."
Thank You
JohnnyHi
You can use profiler to see what EM is doing and it would have come up with
he undocumented procedure:
EXEC sp_MSget_current_activity 51,1
John
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||Hi,
To get information of a SPID then go with this:-
From Query Analyzer, Execute the below system procedure to get the SPID for
the user.
sp_who
After getting the sp_id use the below command to get the process detailsfor
the SPID
DBCC INPUTBUFFER(SPID)
Thanks
Hari
MCDBA
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:1e84e01c4556d$86ab8380$a401280a@.phx.gbl...
> Hi Friends,
> How can i get the process detais using TSQL instructions ?
> i want the same information that i get when i execute the
> following procedures
> "Expand Management, and then expand Current Activity.
> Click Process Info.
> The current server activity is displayed in the details
> pane."
> Thank You
> Johnny
>|||select *
from master..sysprocesses
-Sue
On Fri, 18 Jun 2004 12:50:37 -0700, "Johnny Silvestre"
<johnny_silvestre@.yahoo.de> wrote:
>Hi Friends,
>How can i get the process detais using TSQL instructions ?
>i want the same information that i get when i execute the
>following procedures
>"Expand Management, and then expand Current Activity.
>Click Process Info.
>The current server activity is displayed in the details
>pane."
>Thank You
>Johnny