How to get the whole DDL command from EVENT_INSTANCE

Hello,
I try to save the current DDL in a table using the trigger on database ddl
events.
As usual,
DECLARE @.data XML
SET @.data = EVENTDATA()
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
How can I extract more then 2000 chars? Should I use a system table or
function to retrieve all the DDL command? I have SPs whith tons of chars...
Thanks,
CatalinHow about, for instance:
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
> Hello,
> I try to save the current DDL in a table using the trigger on database ddl
> events.
> As usual,
> DECLARE @.data XML
> SET @.data = EVENTDATA()
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
> How can I extract more then 2000 chars? Should I use a system table or
> function to retrieve all the DDL command? I have SPs whith tons of chars..
.
> Thanks,
> Catalin|||Thanks, but i have SPs with probably 40k chars or more... Neither varchar
(8000) is enough...
"Tibor Karaszi" wrote:

> How about, for instance:
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in messag
e
> news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
>|||Did you try nvarchar(max)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:989EED5F-D2F0-44EB-A43E-A2FB23BB9B5B@.microsoft.com...
> Thanks, but i have SPs with probably 40k chars or more... Neither varchar
> (8000) is enough...
> "Tibor Karaszi" wrote:
>|||Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'
t
tell me that it was also available on SQL 2000...)
I am so deceived about me... After 8 years of SQL I will have to start again
from ABC... Sometimes I am so busy to find complex solutions and I am not
able to see the simplest one.
Thanks again|||> Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'ted">
> tell me that it was also available on SQL 2000...)
The max datatypes are indeed new to 2005. Consider them as replacements for
the less than user
friendly text, ntext and image datatypes.

> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
This happens to all of us. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:01DD8998-1C93-43E2-AA9A-F82694049866@.microsoft.com...
> Thank you, I had no ideea about (max) implementation on 2K5... (Please, do
n't
> tell me that it was also available on SQL 2000...)
> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
> Thanks again

How to Get the Output Column in OLE DB Command Transformation

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @.OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = ?

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

|||

It sounds as tho you are using the wrong component. To source stuff use the OLE DB Source Adapter, not the OLE DB Command.

-Jamie

|||

Dear Jamie,

Thanks for such a quick reply.

U Mean OLEDB Source From DataFlow Sources.

Actually the My dataflow task contains one OLEDB source component which is having connection to one table, from that table i am getting the orderID column, Then i am passing this OrderID column values to the query Which will get the serialnumber in the SerialNumbers table based on this OrderID. And my problem is i cant able to get this selected serialnumber column in the output column tree view,so i that column is not accessable for futher transformations.

Please give me some solution.

Thanks in advance.

- Dhivya

|||

You need the LOOKUP transform. That s exactly what it does.

-Jamie

|||

Dear Jamie,

That also i tried,the table contains multiple values(for same OrderID multiple serial numbers) and the lookup transform will take only the first value and map the same to the others.

-Dhivya

|||

So its a many-to-many?

Then you should use the MERGE JOIN component!

-Jamie

|||

Good advice, Jamie.

Dhivya, remember that the Merge Join needs a sorted input, so you'll also need to use sort components. Alternatively, use ORDER BY in the source queries, and set the IsSorted property of the source adapter output to True.

Donald

|||

I tried merge join, the problem is order ID is not unique in my source table and in transaction table. so if i put some inner or left joins i am not getting the values what i want.

-Dhivya

|||

Merge Join worked for me. I did right outer join.

Thank u Jamie and donald.

But still my question is, we cant get the output columns in OLE DB Command Component if we use select command?

|||

No. That's not what its for!

-Jamie

|||

OK. Thanks a lot

-Dhivya

How to get the log file with /logger option

Hi,

I want to get the log for the below command.

DTExec /FILE "C:\Test.dtsx" /CONNECTION DestinationConnectionFlatFile;"c:\test.csv" /CONNECTION SourceConnectionOLEDB;"Data Source=test;User ID=user;Initial Catalog=DATA_CONV;Provider=SQLNCLI;Auto Translate=false;PASSWORD=password" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /logger "DTS.LogProviderTextFile;log.txt" /Set "\package.Connections[log.txt].Properties[ConnectionString];c:\log.txt"

But I got the error as below. Please advise.

Error: 2006-10-10 09:57:21.07
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by C
onnections collection when the specific connection element is not found.
End Error
Error: 2006-10-10 09:57:21.10
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2006-10-10 09:57:21.14
Code: 0x8001F02F
Source: Test
Description: Cannot resolve a package path to an object in the package ".Connections[log.txt].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2006-10-10 09:57:21.17
Code: 0x80012017
Source: Test
Description: The package path referenced an object that cannot be found: "\package.Connections[log.txt].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \package.Connections[log.txt].Properties[ConnectionString]
value to c:\log.txt.

Are you using dtexecui.exe to build the command line? If not, please do that, as it will build properly formed dtexec parameter set.

If you want to add a logger from the command line, it must reference a pre-existent connection manager in the package. So add a file connection manager to the package.

Now if you want a logger without a pre-existent connection manager, and you don't mind coding, create your own LogProvider by creating a subclass of LogProviderBase and overriding of meaning of the ConfigString, when generally points to a connection manager in stock loggers.

Two custom log providers with source code are available in the SQL 2005 samples available at http://www.microsoft.com/downloads/details.aspx?FamilyID=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
FrankFrank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>|||Hi Frank
Try (for SPID 52):
CREATE TABLE #inputbuffer (
EventType varchar(20),
parameter INT,
Eventinfo varchar(80)
)
-- Execute the command, putting the results in the table
INSERT INTO #inputbuffer
EXEC ('DBCC INPUTBUFFER (52) WITH NO_INFOMSGS')
-- Display the results
SELECT *
FROM #inputbuffer
GO
John
"Frank" wrote:
> hi,
> I want to get all the server's processes last command in a stored procedure.
> What I know now is that I could get the last command of one specific process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>
>|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Frank
> > but I could not insert the result into a table
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> > hi,
> > I want to get all the server's processes last command in a stored
> > procedure.
> > What I know now is that I could get the last command of one specific
> > process
> > by using DBCC Inputbuffer, but I could not insert the result into a
table.
> > Any one has other ways to get the last command and could save in a
table?
> > I am using SQL Server 2000.
> >
> > Thanks in advance
> > Frank
> >
> >
>|||John,
Thanks for your information
Frank
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86A3B320-1959-48B7-90A0-6BCCC77793BA@.microsoft.com...
> Hi Frank
> Try (for SPID 52):
> CREATE TABLE #inputbuffer (
> EventType varchar(20),
> parameter INT,
> Eventinfo varchar(80)
> )
> -- Execute the command, putting the results in the table
> INSERT INTO #inputbuffer
> EXEC ('DBCC INPUTBUFFER (52) WITH NO_INFOMSGS')
> -- Display the results
> SELECT *
> FROM #inputbuffer
> GO
> John
> "Frank" wrote:
> > hi,
> > I want to get all the server's processes last command in a stored
procedure.
> > What I know now is that I could get the last command of one specific
process
> > by using DBCC Inputbuffer, but I could not insert the result into a
table.
> > Any one has other ways to get the last command and could save in a
table?
> > I am using SQL Server 2000.
> >
> > Thanks in advance
> > Frank
> >
> >
> >

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
FrankFrank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Frank
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
table.[vbcol=seagreen]
table?[vbcol=seagreen]
>

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
Frank
Frank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>
|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Frank
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
table.[vbcol=seagreen]
table?
>

how to get table structure?

Hi,
There is a command Describe in Oracle to get the table structure (column
names, types, etc. ).
Is there any similar command in SQL server?
Thanks,
Guangmingsp_help tablename
Word 2003 memory Leakage wrote:
> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming|||Following will give you list of columns, data type, size etc.
sp_columns tablename
For more information, please have a look at:
http://www.aspfaq.com/show.asp?id=2177
"Word 2003 memory Leakage" wrote:

> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming|||Both are working. sp_help returns more infor than sp_columns.
It seems they are much slower than describ in Oracle.
but it works.
Thanks,
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Following will give you list of columns, data type, size etc.
> sp_columns tablename
> For more information, please have a look at:
> http://www.aspfaq.com/show.asp?id=2177
> "Word 2003 memory Leakage" wrote:
>sql

how to get table structure?

Hi,
There is a command Describe in Oracle to get the table structure (column
names, types, etc. ).
Is there any similar command in SQL server?
Thanks,
Guangmingsp_help tablename
Word 2003 memory Leakage wrote:
> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming|||Following will give you list of columns, data type, size etc.
sp_columns tablename
For more information, please have a look at:
http://www.aspfaq.com/show.asp?id=2177
"Word 2003 memory Leakage" wrote:
> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming|||Both are working. sp_help returns more infor than sp_columns.
It seems they are much slower than describ in Oracle.
but it works.
Thanks,
"Absar Ahmad" wrote:
> Following will give you list of columns, data type, size etc.
> sp_columns tablename
> For more information, please have a look at:
> http://www.aspfaq.com/show.asp?id=2177
> "Word 2003 memory Leakage" wrote:
> > Hi,
> >
> > There is a command Describe in Oracle to get the table structure (column
> > names, types, etc. ).
> >
> > Is there any similar command in SQL server?
> >
> > Thanks,
> >
> > Guangming

how to get table structure?

Hi,
There is a command Describe in Oracle to get the table structure (column
names, types, etc. ).
Is there any similar command in SQL server?
Thanks,
Guangming
sp_help tablename
Word 2003 memory Leakage wrote:
> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming
|||Following will give you list of columns, data type, size etc.
sp_columns tablename
For more information, please have a look at:
http://www.aspfaq.com/show.asp?id=2177
"Word 2003 memory Leakage" wrote:

> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming
|||Both are working. sp_help returns more infor than sp_columns.
It seems they are much slower than describ in Oracle.
but it works.
Thanks,
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Following will give you list of columns, data type, size etc.
> sp_columns tablename
> For more information, please have a look at:
> http://www.aspfaq.com/show.asp?id=2177
> "Word 2003 memory Leakage" wrote:

How to get server name and databases name from a server using SQL Command...

can anyone tell me
How to get server name and databases name from a server using SQL Command...

i m using sql server 2000(T-SQL)

Use @.@.SERVERNAME to find the server name -
select @.@.SERVERNAME

& this to find the current database name -
select db_name()

|||

thanks....

How to get return value for the number of rows affected by update command

Hi,

i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?

Below is the normal way i did in vb.net, but how to check for the return value. Please help.

========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========

Thank you.you can add either of these statements to the SQL being called
[BOL} @.@.rowcount
[BOL] Rowcount_big

the difference is in the datatypes rowcount _big returns a bigint
and @.@.rowcount returns int

if you have over 2 billion rows user rowcount_big|||Hi Ruprect, thanks for your reply. My sql statement is a very simple insert query without using any parameters just like the one below:

sql = "INSERT INTO [Subscriber] ([SubID], [SubName], [SubEmail], [Status], [MailID], [SubscribeDate]) VALUES (SubID, SubName, SubEmail, 'Pending', MailID ,getDate())"

I'm unsure of how to include the " [BOL} @.@.rowcount ". Do you mean that i should add a parameter to return @.@.rowcount or there is other way to do it? I'm new to this, would you please give me an example.

Thanks for your time.|||@.@.Rowcount stored the number of records affected by the immediately prior statement. The value is lost as soon as another statement is executed, so you must either use it immediately or store it in a procedure variable:

declare @.RecordsAffected Int
.
.
.
.
.
Update/Select/Delete some records from somewhere...
set @.RecordsAffected = @.@.RowCount

Look up @.@.Rowcount in Books Online for more details.|||thanks BLIND MAN
i didnt getthis until late
[BOL] stands for Books Online it's the sql server help file
i was giving you the article title

and since blindman got it exactly i've no need to reiterate
good luck.|||see if there is something like mycommand.rowsaffected property.|||Thanks Blindman and Thanks Ruprect. I'll study BOL ;) for details of @.@.rowcount.|||You should also follow ms_sql_dba's suggestion to see if there is a method to return the value via VB.

It might be more appropriate if you are going to use the value in your VB code.|||Hi ms_sql_dba, there isn't any rowsaffected property, however there is this UpdatedRowSource and others ..

Thanks for your suggestion, although i'm unsure of their usage, i'll look into it and see if i can find something which stores the value of number of rows affected!|||Sure Blindman, i'll study both ways and see which one is more applicable for my situation. You have a great day.|||Hi Everyone,

I managed to find another solution to my question. Just simply assign the value like this line:-

rowsAffected = myCommand.ExecuteNonQuery()|||see, it was simple!|||Yea. Lesson learned! Cheers!!!

How to get query result in XML format using osql.exe (command line)

Hi.
I'm using MSDE 2000. Is there a way to get a query result in a 'readabe'
XML format from command line?
I found the option 'FOR XML', however this produced something that is
not really XML. E.g.:
SELECT * FROM dbo.master FOR XML
Thanks and best regards,
Dezo
You can execute SQL queries to return results as XML rather than standard
rowsets. These queries can be executed directly or from within stored
procedures. To retrieve results directly, you use the FOR XML clause of the
SELECT statement, and within the FOR XML clause you specify an XML mode:
RAW, AUTO, or EXPLICIT.
For example, this SELECT statement retrieves information from Customers and
Orders table in the Northwind database. This query specifies the AUTO mode
in the FOR XML clause:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
The output will be in the xml format .
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get output of sql command in columns

Hi,
I am working with Informix db in Digital Unix.
When I try to give any select commands and try to retrieve more than 5 columns in the same sql command, the output comes in rows instead of columns.
Is there a way to force it to come in columns?

i just use a simple format,
select column1 ,column2 ,column3 ,column4 ,column5 from tableyou should be getting 5 columns per record in the DB

column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5

how do you want the layout and why?

How to get invalid stored procedures

Hi, All,
Is there any script or command that can get all invalid stored procedures in
SQL Server 7.0/2000. Thanks
JieWhat do you mean by invalid ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"J Gao" <jie.gao@.tequilasoftware.com> wrote in message
news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
Hi, All,
Is there any script or command that can get all invalid stored procedures in
SQL Server 7.0/2000. Thanks
Jie|||Invalid means there is syntax error in the stored procedures. I would like
to have query to get the all stored procedures that have syntax error with
it instead of going to every stored procedure to check sybtax. Thanks
Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OJO%23aJvSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> What do you mean by invalid ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "J Gao" <jie.gao@.tequilasoftware.com> wrote in message
> news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Hi, All,
> Is there any script or command that can get all invalid stored procedures
in
> SQL Server 7.0/2000. Thanks
>
> Jie
>
>|||A stored procedure cannot be submitted to SQL Server if it is invalid, with
the exception of delayed name resolution. That could be a problem. Or, if
objects that the procedure uses are dropped, then that would invalidate,
although not delete, the procedure.
There is not a way to test this directly that I know of.
What you could do is:
1. Script out all of the stored procedures using DROP / CREATE.
2. Run that script from OSQL using an output file.
3. Examine the output file for errors.
Any procedures with serious syntax errors will not recreate and will now be
missing from your database. You can examine the errors to see what is wrong
should any of them need fixing.
The downside of this is that you don't want to do it during a productive
work period.
Russell Fields
"J Gao" <jie.gao@.tequilasoftware.com> wrote in message
news:uNzatTvSDHA.3188@.tk2msftngp13.phx.gbl...
> Invalid means there is syntax error in the stored procedures. I would like
> to have query to get the all stored procedures that have syntax error with
> it instead of going to every stored procedure to check sybtax. Thanks
> Jasper.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OJO%23aJvSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > What do you mean by invalid ?
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "J Gao" <jie.gao@.tequilasoftware.com> wrote in message
> > news:%23GbjhauSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> > Hi, All,
> > Is there any script or command that can get all invalid stored
procedures
> in
> > SQL Server 7.0/2000. Thanks
> >
> >
> > Jie
> >
> >
> >
>|||the only way you can do this is to build a new database
using the scripts created by scripting out your database.
Then you have to worry about dependencies. DB Ghost can do
all of this for you, displaying any syntax errors that may
exist in your database code. It will also show you
dependancy errors that may exist in your code. Both syntax
and dependancy errors can exist in the database code
simply because of inadequate change management. For
example a developer changes a column definition on a table
using an alter statement on that table. However there is a
stored procedure that relies on the original specification
of that column. You now have an error which will only
manifest itself when the stored procedure is next used.
This happens all the time, that's why we use DB Ghost for
our change management as it guarentees the source and the
deployement. Check out www.dbghost.com - it makes a good
read and the app comes with a full help manual.
Mark Baekdal
>--Original Message--
>Hi, All,
>Is there any script or command that can get all invalid
stored procedures in
>SQL Server 7.0/2000. Thanks
>
>Jie
>
>.
>

how to get hostname via SQL command?

Hi!
Can something tell me how I can obtain the machine name (either IP address or DNS name) on which SQL Server is running via a SQL query?
select @.@.version returns a lot of useful information, however it doesn't return the host name.
thanks in advance
- GarryI haven't tested this, and it requires sysadmin privleges to run, but couldn't you use:EXECUTE master.dbo.xp_cmdshell 'SET COMPUTERNAME'-PatP

How to get executing sql and pids via sql from a command line....

To all,
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--sean
Hello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net ...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>

How to get executing sql and pids via sql from a command line....

To all,
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
----
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--seanHello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>

How to Get Error Output from and OLE DB Command Destination

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

To get rows down the error output you change the ErrorRowDisposition property for the input to be redirect row. Have you done this? If not go the last page of the Advanced Editor, select the Input, and change the ErrorRowDisposition property.|||

I reviewed your suggestion of changing the ErrorRowDisposition value to RD_RedirectRow and that is where the issue is. I view the Advanced Editor for the OLE DB Command destination object and expand the Input Columns under OLE DB Command Input and see several input columns. However, the problem is every one of those columns has an ErrorRowDisposition=RD_NotUsed and the field is greyed out so I am unable to change the setting. Would I need to change any settings in the source or data conversion objects to allow these values to be editable?

|||Select the input and stop there, don't expand the columns. The setting is on the input which is in effect the parent for the columns.