What are some sound solutions to capture the query runtime via a
script/proc? The info returned from Statistics time is hard to process.
The goal is to run a statement(s) and capture and track the execution
time to check on the availability of SQL Server. The data can be
analyzed to determine when perf is high and low. But the main reason is
to check db availability.
Thanks,
Mike
--
Posted via http://dbforums.com
Hi All,
Is it possible to capture the runtime metadata relating to the number of rows processed by a graph, the number of rows inserted/updated in the target blah blah using SSIS.
I have only seen metadata extensions for starttime, machine name etc. but not for this
THanks
Manish
I am trying to acces an SQLDatasource in the code page, I have the following code but get the error as below, any one help please
The SQLDataSource returns 1 value named [ShippingRegion], I think that has somethjing to do with it ??!!
dsShippingRegion.Select(DataSourceSelectArguments.Empty)Dim myReader As Data.IDataReader = CType(dsShippingRegion.Select(DataSourceSelectArguments.Empty), Data.IDataReader)If myReader.Read Then If Convert.IsDBNull(myReader("ShippingRegion")) Then Beep() Else Beep() End IfEnd If
System.InvalidCastException was unhandled by user code Message="Unable to cast object of type 'System.Data.DataView' to type 'System.Data.IDataReader'." Source="App_Web_rd5quiy1" StackTrace: at admin_administer_shop_productaddnew.Page_Load(Object sender, EventArgs e) in E:Web DevelopmentWebSitesAJAX_sirs2hersadminadminister_shopproductaddnew.aspx.vb:line 25 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
dsShippingRegion.Select(DataSourceSelectArguments.Empty)
We've developed several reports where you can type in / select variables and run them, affecting their outcomes.
We'd like to change this so if the user clicks on a link from our Intranet, this takes them straight to the report with the appropriate parameter(s) - rather like the "jump to report" function within a report - i.e. the url for that function...
Is this possible?
Steve
I have been told to use the following Microsoft.SqlServer.Dts methods to create a maintenance plan programatically. Can I use C# to program a maintenance plan? If so, how could I use DTS methods to create a maintenance plan? Or, moreover, what would be a good first step to create this?
-Kyle
I'm trying to organize this SQL query from a packet capture and I'm more of a network/application guy, not so much of a DBA. To me it looks like they are using variables in their query and the "@" is a delimiter.
S E L E C T [ t 0 ] . [ S E C T I O N N A M E ] , [ t 0 ] . [ P A R A M E T E R N A M E ] , [ t
0 ] . [ I N T V A L U E ] , [ t 0 ] . [ S T R I N G V A L U E ] , [ t 0 ] . [ D A T E V A L U E ] , [ t 0 ] . [ I N F R A S T R U C T U R A L ]
, [ t 0 ] . [ S i t e P a r a m e t e r s I d ] F R O M [ d b o ] . [ S I T E _ P A R A M E T E R S ] A S [ t 0 ] W H E R E ( [ t 0
] . [ S E C T I O N N A M E ] = @ p 0 ) A N D ( [ t 0 ] . [ P A R A M E T E R N A M E ] = @ p 1 ) @ 4@ @ p 0 n v a r c h a r ( 3
) , @ p 1 n v a r c h a r ( 1 0 ) @ p 0 4 W e b @ p 1 4 M a x Q u e r i e s
[code]...
I'm new to sql server. Now I'm re-writing an application without any source code. I want to find out how the data is retrieved from the database. Can anyone give me any detail instructions or link to the documentation that tells this? There's no DBA here either.
Thanks.
Hi guys,
A column in my table is being updated and I would like to find out what process, host, user, etc. is performing the update.
I have added a trigger to the table so that when an update occurs I am able capture the session details into a table, however the session details that are being returned are those of the actual trigger rather than those of the original query.
The statement that I am using to capture the session details is...
select * from sys.dm_exec_connections cross apply sys.dm_exec_sql_text(most_recent_sql_handle) where session_id = @@spid
There is a parent_session_id returned in the above which I hoped might give me some pointers but this is always NULL.
I have been working my way through the system dynamic views in the hope that one of them will provide the information I am looking for but so far I've had no luck.
Because the system I am troubleshooting is web based the actual server and user details will be those configured as service accounts for the application but if I am able to show the developers the original query that was used for the update I'm sure they will be able to provide valuable information as to which component it is coming from. I imagine SQL will also be able to tell me which host is sending the query. With these two bits of info I'm sure I'll be able to track it down.
Our suspicion is that an old component is running somewhere and I'd like to be able to track it down. Perhaps I've spent too much time looking at this but I'm coming to the conclusion that it may not be possible from the SQL back end, which I am very surprised at.
I have tried capturing every session that was running at the time of the update but there's simply too much info to deal with. I even searched through these results but there's so many different possible combinations that an update statement might take form that it quickly became a worthless exercise.
Perhaps one of you can resolve this for me. Any ideas?
Regards,
Joe
I want to capture an error through dynamic query. I have got a link server. I will execute a procedure in database a which will insert data into a table of database b. If while inserting into the table if database b generates an error I have to catch that error in database a and show it.
Please help.
I have 2 tables, table one with 772 pieces of compliant data. Table 2 has 435 pieces of data that meet another criteria (all the columns are identical it was just passes through an additional filter). I need to capture the values that are excluded from table 2.
Example Table 1
ID some value
1 x
2 x
3 x
4 x
5 x
Table 2
ID some value
2 x
3 x
5 x
I need to capture the data from ID 1 and 4 and assign a new value to it, it is extra compliant data. Thanks!
Hi,
Currently im using SQL queries in combination with ASP.NET..
I have currently only one table('Requests'), which has all the user info of name,id,studyrequested etc.. A person can request for any number of studies.
Now the prob is that from now on whenever a user requests for a new study not only should his details be stored in the Request table but also in a new Users table. In the Users table that particular users details must only be loaded once.. i.e. if the same user enrolls for another study his details should only be updated in the requests table and not in the Users table, since his details have already been loaded once in the Users Table..
While this works fine for new users, but what bout the old users already stored in the Requests table before the Users table existed.
Is there any way i could run a sql script only once at runtime, so that all the old legacy users are loaded once in to the Users Table.
Thanks,
Nitin
I get Invalid object name 'bstr'. when I try to run this querySelect distinct c0.oid, c1.Value, c2.Value, c3.ValueFrom(SELECT oid FROM dbo.COREAttributeWHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}','{1449DB2D-DB97-11D6-A551-00B0D021E10A}','{1449DB2F-DB97-11D6-A551-00B0D021E10A}','{1449DB31-DB97-11D6-A551-00B0D021E10A}','{1449DB33-DB97-11D6-A551-00B0D021E10A}','{1449DB35-DB97-11D6-A551-00B0D021E10A}','{1449DB37-DB97-11D6-A551-00B0D021E10A}','{1449DB39-DB97-11D6-A551-00B0D021E10A}','{1449DB3B-DB97-11D6-A551-00B0D021E10A}','{1449DB3D-DB97-11D6-A551-00B0D021E10A}','{1449DB3F-DB97-11D6-A551-00B0D021E10A}','{1449DB43-DB97-11D6-A551-00B0D021E10A}','{1449DB45-DB97-11D6-A551-00B0D021E10A}','{1449DB47-DB97-11D6-A551-00B0D021E10A}','{1449DB49-DB97-11D6-A551-00B0D021E10A}','{1449DB4B-DB97-11D6-A551-00B0D021E10A}','{1449DB4D-DB97-11D6-A551-00B0D021E10A}','{1449DB51-DB97-11D6-A551-00B0D021E10A}','{DAA598D9-E7B5-4155-ABB7-0C2C24466740}','{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}','{128F17D4-2014-480A-96C6-370599F32F67}','{9F3A64C9-28F3-440B-B694-3E341471ED8E}','{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}','{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}','{2BB836D3-2DC1-4899-9406-6A495ED395C3}','{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}','{E18E470B-B297-43D2-B9CD-71AF65654970}','{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}','{0E91AC62-7929-4B42-B771-7A6399A9E3B0}','{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}','{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}','{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}','{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}','{19082B90-EF02-45CC-B037-AFD0CF91D69E}','{6F76CEF7-EBC0-48C6-8B78-C5330324C019}','{18492042-B22A-4370-BFA3-D0481800BBC7}','{A71343AD-CC09-4033-A224-D2D8C300904A}','{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}','{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}','{A923226F-B920-4CFA-9B0D-F422D1C36902}','{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}','{C31DB61A-5221-42CF-9A73-FE76D5158647}')) AS c0 ,(select oid, dispid, valueFROM dbo.COREBSTRAttributeWHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}') As bstrLEFT JOIN bstr AS c1ON (c0.oid = c1.oid)AND c1.dispid = 28LEFT JOIN bstr AS c2ON (c0.oid = c2.oid)AND c2.dispid = 112LEFT JOIN bstr AS c3ON (c0.oid = c3.oid)AND c3.dispid = 192thanksSunit
View 1 Replies View RelatedI am trying to load all the MDX queries that run on a Analysis Server instance into a database for further analysis. A SQL Profiler is setup which captures the MDX queries, and when I am loading the Profiler info to database, some of the queries are not coming up in full length.The TextData field doestn't show full MDX query. When loading to the database, the field is next data type. Is there any workaround to get the complete MDX query?
View 2 Replies View RelatedHi all,
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
Thanks for your time.
Pace
"Impossible is Nothing"
Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.
The error is
Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B
I pieced together this code from the examples in the online books, but I am not sure what to do.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Public Class ScriptMain
Public Sub Main()
'
Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
app.LoadPackage("c:systimeExcelOutExcelOutExcelOutDo.dtsx", Nothing)
Dim pkgVars As Variables = package.Variables
Dim gsVar As Variable = pkgVars("User::gsExcelFile")
Dim currVars As Variables = Dts.Variables
Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)
gsVar.Value = Dts.Variables("User::gsExcelFile").Value
pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value
pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value
Dim e As Executable = package.Executables("ExcelOutTask")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Get the source component.
Dim SourceComponent As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection("Local Source")
Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()
srcDesignTime.ProvideComponentProperties()
' Reinitialize the metadata.
srcDesignTime.AcquireConnections(vbNull)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
' Get the destination component.
Dim destination As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection("Excel Destination")
Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()
destDesignTime.ProvideComponentProperties()
' Create the path.
dataFlowTask.PathCollection.RemoveAll()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _
destination.InputCollection(0))
'Console.WriteLine(dataFlowTask.PathCollection.Count)
Dim ret As DTSExecResult
ret = package.Execute()
Console.WriteLine(ret.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Hi all,
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
Thanks for your time.
Barry Andrew
Hi all,
I have the following query...
SELECT Count(*)
FROM Incidents I
WHERE (Priority = 1)
AND (Time_First_Unit_On_Scene IS NOT NULL)
AND (DateDiff(s, Time_ClockStart, Time_First_Unit_On_Scene) <= 480)
AND (Response_Date BETWEEN '1-Apr-2004')
AND ('31-Mar-2005 23:59:59')
AND (I.Disposition_ID <> 9 )
...and I get the following error message...
System.Data.OleDb.OleDbException: Difference of two datetime columns caused overflow at runtime.
... any one know what it could be?
Thanks
Tryst
Hi Friends,
I want to have solution for one of the problem.
The requirement is like this :
I want to write stored procedure or function which will take parameter as SQL Server name, DB name, UserName and passwod.
This Stored proc will connect to Remote server using these parameters and will get the count of the rows in one of the table.
I created the connection using the linked server
EXEC sp_addlinkedserver @SerevrName,N'SQL Server'
EXEC sp_addlinkedsrvlogin @SerevrName, False, Null, @ServerUserName,@SerevrPws
Now I am trying to get count using following query :
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
But the question is that the execution goes this way :
exec(@SQLQuery)
Now how to assign this count value to some variable so that I can use it later ...?
Going forword I want to use cursor and get the rows in these table using cursor ...?
How can I assign values returned from any runtime query to temporary variable or table ...?
I tried another approach also:
I put remote connection and query execution in inner stored proc called usp_GetTableRowCount
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
exec(@SQLQuery)
and in outer stored proc : referenced the inner stored proc like this
exec @AFSDataRowCount = dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>>
The execution of dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> gives me exact no of rows
but when I see value of AFSDataRowCount, I get 0.
Kindly help me out whereever I am making mistake or else pls tell me any other approach to follow.
Thanks in advance.
Hi,
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination.
I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Thanks
Radhika
Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??
View 3 Replies View RelatedI have to create DDL trigger for audit to capture the object definition before and after the changes.
Like If any user running the Alter table Statement, i need to capture the Object definition before and after changes..
Hi,
We would like to capture events in our system. There seem to be
three obvious capture points for us - application, triggers, transaction
log. The latter seems to be the most attractive, since we`re looking
for a solution with minimal performance impact. In general, our
problem is similar to populating data warehouses from on-line databases.
Can anyone proffer some advice? In particular, being quite new to
SQL Server, I am not sure how difficult/possible it is to read the
transaction log in order to cull events. Some direction here would
be greatly appreciated.
Thanks,
Karl
Hi all,
Is there anyway to capture the SQL Server Error and act accordingly?
I donot want sql server to raise an error when a Primary key violation has occured. Instead i want to capture that error(number,description etc) and act
accordingly.
Whats happening is, from the application we are trapping this sql error
and raising it. Instead, if somebody inserts a record which already exists, then we want to trap that error from the sqlprocedure itself and then do an update to that record.
thanks for the help
Hi All.
I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.
If anyone knows how to do this, please help.
Thanks,
ODaniels
I was wondering how you go about capturing text as it is entere into a textarea? i want it captured exactly as it is typed, with carriage returns and everything. is there an easy way to do this?
View 14 Replies View RelatedAccess front-end, SQL Server 2005 backend.I have users connected to SQL Server via a Microsoft Access user-interface.Connection is via NT login.I want to log users' activities to the database with their userid.How can I capture their NT User ID (via VBA in Access)?Thanks,Bubbles
View 1 Replies View RelatedHi,
Please help me how can i capture I/O operation per database SQLServer2005.
Regards
Sufian
Hi,
in sql server 2005, i was using update trigger with BEFORE but it gives error that
"'BEFORE' is not a recognized trigger."
My requirement is that i want to capture the old value as well as new value of
a column of the updated row in a trigger.
How can i perform this.
thanks in advance
Hi All!
SQL Server 2000
I've situation where I've to capture a DML executing against let say Table1 and later may at the end of the day or week I want to be able to extract all the DML executed against Table1 and execute them against similar tables on different sql server to synchronize the data. I don't want to use profiler as this is quite expensive resource for my problem niether can use any third party tool.
Is it possible to capture sql statement in the trigger?
I hope I made my question clear. Urgent help will be highly appreciated.
--Sohail.
how can I capture delta in SQL Server 2005 to refresh base tables in a data warehouse?
View 8 Replies View RelatedI have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.blumonde
View 6 Replies View RelatedIn my BLL I have a method that adds a new row to a table in the database...
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public bool AddContact(string firstname, string lastname, string middleinit, bool active, Guid uid, bool newsletter) { ContactsDAL.tblContactsDataTable contacts = new ContactsDAL.tblContactsDataTable(); ContactsDAL.tblContactsRow contact = contacts.NewtblContactsRow();
contact.FirstName = firstname; contact.LastName = lastname; contact.MiddleName = middleinit; contact.Active = active; contact.UserID = uid; contact.Newletter = newsletter;
contacts.AddtblContactsRow(contact); int rowsAffected = Adapter.Update(contact);
return rowsAffected == 1; }
The primary key in this table is a BigInt set as an identity column....How do I capture the value of the primary key that gets created when the new row is added?
I've a textbox that displays the current time in this format "hh:mm:ss tt" but when it is save into the database it'll display the date and time together. So how do I save only the time into the database? My codes is as shown below:
txtTime.Text = DateTime.Now.ToLongTimeString()
Dim parameterDate As SqlParameter = New SqlParameter("@Date_5",SqlDbType.DateTime)
parameterDate.Value = txtDate.Text
objCommand.Parameters.Add(parameterDate)
I've tried using Format() but it still get the same results. Can someone help me out? Thanks!