Showing posts with label run. Show all posts
Showing posts with label run. Show all posts

Friday, March 30, 2012

How to audit data flow runs

What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.

There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.

-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.

http://markiehillmsis.blogspot.com/

Thanks
Sutha

How to audit data flow runs

What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.

There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.

-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.

http://markiehillmsis.blogspot.com/

Thanks
Sutha

Monday, March 26, 2012

How to apply style sheet to report... help me

hai...

i have designed a static rdl file which contains 10 columns.. now when i run the report, how to apply style sheet to the report...i want to apply style sheet at run time.. how to do that..? help me..

Thanks in advance..

If you are using rdlc in Visual Studio 2005, In my observations I think there's no way that you can apply style sheets to rdlc but you can apply CSS in Report Viewer control that contains your rdlc.

How to analyze SQLProfiler trace result ?

Hi All,
I plan to run SQL Profiler because my SQL Server 2000 get the users
timed out after running some time, and happen every day.
(see
[url]http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thr
ead/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b[/u
rl])
The question is :
After I get the trace recorded using Profiler, how can I check which
query that cause the problem ?
Or what I see any clue of the problem on the SQL Profiler result ?
Thank you very much,
xtantoA timeout is called "Attention" in Profiler. So make sure you catch that eve
nt. You can now go
backward for a SPID, from the Attention to the SQL:BatchStarted (or similar)
event to see what query
was timed out.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<krislioe@.gmail.com> wrote in message news:1155353854.603424.64300@.74g2000cwt.googlegroups.c
om...
> Hi All,
> I plan to run SQL Profiler because my SQL Server 2000 get the users
> timed out after running some time, and happen every day.
> (see
> [url]http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_t
hread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b[
/url])
> The question is :
> After I get the trace recorded using Profiler, how can I check which
> query that cause the problem ?
> Or what I see any clue of the problem on the SQL Profiler result ?
> Thank you very much,
> xtanto
>

How to analyze SQLProfiler trace result ?

Hi All,
I plan to run SQL Profiler because my SQL Server 2000 get the users
timed out after running some time, and happen every day.
(see
http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b)
The question is :
After I get the trace recorded using Profiler, how can I check which
query that cause the problem ?
Or what I see any clue of the problem on the SQL Profiler result ?
Thank you very much,
xtantoA timeout is called "Attention" in Profiler. So make sure you catch that event. You can now go
backward for a SPID, from the Attention to the SQL:BatchStarted (or similar) event to see what query
was timed out.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<krislioe@.gmail.com> wrote in message news:1155353854.603424.64300@.74g2000cwt.googlegroups.com...
> Hi All,
> I plan to run SQL Profiler because my SQL Server 2000 get the users
> timed out after running some time, and happen every day.
> (see
> http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b)
> The question is :
> After I get the trace recorded using Profiler, how can I check which
> query that cause the problem ?
> Or what I see any clue of the problem on the SQL Profiler result ?
> Thank you very much,
> xtanto
>|||Hi Tibor,
Thanks for your answer.
Actually what I want to find with Profiler is not the query that was
timed out, because when the "specific condition" met, "any queries" to
the server, from "any user" will get timed out.
So my goal is to find What Has caused this "specific condition" ?
Thank you very much,
xtanto
Tibor Karaszi menuliskan:
> A timeout is called "Attention" in Profiler. So make sure you catch that event. You can now go
> backward for a SPID, from the Attention to the SQL:BatchStarted (or similar) event to see what query
> was timed out.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <krislioe@.gmail.com> wrote in message news:1155353854.603424.64300@.74g2000cwt.googlegroups.com...
> > Hi All,
> >
> > I plan to run SQL Profiler because my SQL Server 2000 get the users
> > timed out after running some time, and happen every day.
> > (see
> > http://groups.google.co.id/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc287e2b9eb78b92/a05b0496982d258b?lnk=st&q=&rnum=24#a05b0496982d258b)
> >
> > The question is :
> > After I get the trace recorded using Profiler, how can I check which
> > query that cause the problem ?
> > Or what I see any clue of the problem on the SQL Profiler result ?
> >
> > Thank you very much,
> > xtanto
> >

Monday, March 19, 2012

How to add sproc to multiple databases

Hi all. Lets say I had a script to run (create sproc) and I needed to
run it on several different databases on one sql server. Can someone
give me an idea how I could do that short of changing the "use databse"
line each time?
ThanksSee if this helps:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm
But be warned that this is an undocumented and unsupported system procedure.
ML
http://milambda.blogspot.com/|||You can also consider instead of having multiple copies of the proc,
just put one copy in the master database... make sure it is prefixed
with "sp_" so it can be executed from any database on that server.
I haven't tried this out myself. Be careful of any name collisions
from future service packs or upgrades.|||Personally, while I have done this in the past, I am starting to shy away
from this practice. It does work, but the problem here is the same problem
as DLL hell. So I have a utility procedure named sp_stringParse, or
whatever. I use this in three databases, and I really think it needs to be
improved for a task in database 3. So do I have a sp_stringParse_version1,
sp_stringParse_version2? Maybe, but instead I just put utility procedures
in the database and upgrade them as required in each system. It also saves
me in that if I don't use the new version immediately, I don't have to test
the code in the other databases until I upgrade it.
It is essential however that you use some sort of versioning process with
SourceSafe, CVS, or even just naming the files differently so you can apply
the latest versions as you discover a need to upgrade the proc in another
database.
Just my $.03 worth :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<InfoSponge3000@.gmail.com> wrote in message
news:1137783245.156785.87200@.g43g2000cwa.googlegroups.com...
> You can also consider instead of having multiple copies of the proc,
> just put one copy in the master database... make sure it is prefixed
> with "sp_" so it can be executed from any database on that server.
> I haven't tried this out myself. Be careful of any name collisions
> from future service packs or upgrades.
>

How to add speed when run sql execute command?

Hi,
the problem i'm facing when i run the sql is when i run
the command- executenonquery or executescalar(), the
system needs a long time to process before come out a
result.
i'm using Intel P4 1.5GHz, 256 RAM.
Can anyone tell me that how to add speed when execute the
sql command'?
thanks.There can be many reasons why the command executes slowly. DDL statements
(Cretae Table etc.), demo data (Insert statements) and the command you re
sending would definitely help solving the problem. You can use Query
Analyzer to anayze the T-SQL query you are sending - you can see the
execution plan and find worst operations, like table scans, sorts...
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:10a6401c3f435$2e416eb0$a001280a@.phx.gbl...
> Hi,
> the problem i'm facing when i run the sql is when i run
> the command- executenonquery or executescalar(), the
> system needs a long time to process before come out a
> result.
> i'm using Intel P4 1.5GHz, 256 RAM.
> Can anyone tell me that how to add speed when execute the
> sql command'?
> thanks.|||What sort of command are you executing? It will be a good idea to have a
look at the actual query.
--
With Regards,
Deepak
[I code, therefore I am]
http://deepakictim.blogspot.com
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:10a6401c3f435$2e416eb0$a001280a@.phx.gbl...
> Hi,
> the problem i'm facing when i run the sql is when i run
> the command- executenonquery or executescalar(), the
> system needs a long time to process before come out a
> result.
> i'm using Intel P4 1.5GHz, 256 RAM.
> Can anyone tell me that how to add speed when execute the
> sql command'?
> thanks.

How to add speed when run sql execute command?

Hi,
the problem i'm facing when i run the sql is when i run
the command- executenonquery or executescalar(), the
system needs a long time to process before come out a
result.
i'm using Intel P4 1.5GHz, 256 RAM.
Can anyone tell me that how to add speed when execute the
sql command'?
thanks.There can be many reasons why the command executes slowly. DDL statements
(Cretae Table etc.), demo data (Insert statements) and the command you re
sending would definitely help solving the problem. You can use Query
Analyzer to anayze the T-SQL query you are sending - you can see the
execution plan and find worst operations, like table scans, sorts...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:10a6401c3f435$2e416eb0$a001280a@.phx
.gbl...
> Hi,
> the problem i'm facing when i run the sql is when i run
> the command- executenonquery or executescalar(), the
> system needs a long time to process before come out a
> result.
> i'm using Intel P4 1.5GHz, 256 RAM.
> Can anyone tell me that how to add speed when execute the
> sql command'?
> thanks.|||What sort of command are you executing? It will be a good idea to have a
look at the actual query.
--
With Regards,
Deepak
[I code, therefore I am]
http://deepakictim.blogspot.com
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:10a6401c3f435$2e416eb0$a001280a@.phx
.gbl...
> Hi,
> the problem i'm facing when i run the sql is when i run
> the command- executenonquery or executescalar(), the
> system needs a long time to process before come out a
> result.
> i'm using Intel P4 1.5GHz, 256 RAM.
> Can anyone tell me that how to add speed when execute the
> sql command'?
> thanks.

Friday, March 9, 2012

How to add DTS

How can I Add a DTS by Script?
I run a batch script with osql or Query Analyser...
:D
ThanksYou can use SQL-DMO either with VB/VBScript or through sp_OAxxx

How to Add Columns and Use Them at Run Time using VB 2005

In order to allow my customers to use their existing data and install updated application, I want to be able to add columns to existing CE tables at startup time, and allow the user to enter data into the new column(s) immediately. I used the ALTER TABLE command as shown in the example below:

cmd.CommandText = "ALTER TABLE tblStores ADD dtDemoDate datetime, dtDemoMode int"

cmdexe = cmd.ExecuteNonQuery()

To add the column, But this does not set the columns in the attached dataset. The program sees the columns and data is able to be added, but is not then in the underlying table? What else do I need to do?

Thank you - b2bMan

You must update the dataset to fit the new schema. Distribute an application update together with the code to update the schema.

Sunday, February 19, 2012

How to access SQL Server 2005 with Query Analyzer (SQL 2000)

I'm not able to run queries on a MS SQL Server 2005 using the Query Analyzer
from an XP Pro machine running MS SQL 2000. I'm able to connect to the SQL
2005 server and display all objects in the Object Browser but can't run a
simple query. Any ideas?
By the way, I'm connecting with a new SQL account I created in SQL 2005 with
full rights.
What error message(s) are you getting?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:9BCEACF4-3E33-48B2-AB00-70AF2D087F5D@.microsoft.com...
> I'm not able to run queries on a MS SQL Server 2005 using the Query
> Analyzer
> from an XP Pro machine running MS SQL 2000. I'm able to connect to the
> SQL
> 2005 server and display all objects in the Object Browser but can't run a
> simple query. Any ideas?
> By the way, I'm connecting with a new SQL account I created in SQL 2005
> with
> full rights.
|||I'm running the following select statement to get a table does not exist error:
SELECT * FROM Address
"Gail Erickson [MS]" wrote:

> What error message(s) are you getting?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
> news:9BCEACF4-3E33-48B2-AB00-70AF2D087F5D@.microsoft.com...
>
>
|||Sal Young wrote:
> I'm running the following select statement to get a table does not
> exist error:
> SELECT * FROM Address
>
I believe this was already addressed in another post (unless we're
dealing with two identifical posts from two users).
SQL Server databases use the concept of schemas. That is, objects may or
may not be owned by "dbo" - the general default from prior SQL versions.
Because of this, the schema must be supplied so SQL Server knows where
to look. The Address table in the AdventureWorks database is contained
in the Person schema. So try:
Select * from Person.Address
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Query Analyzer does not fully support new SQL Server 2005 features. For
example, it does not recognize schema names in the Object Browser. So when
you look at the AdventureWorks objects in the Object Browser of QA, they all
appear to belong to dbo even though very few objects actually do. If you
want to continue to use QA for 2005 databases, you'll need to keep that in
mind. The SQL Server 2005 Books Online topic "AdventureWorks Data
Dictionary" lists all the tables and the schemas they are contained in.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%234sqZta1FHA.4032@.TK2MSFTNGP15.phx.gbl...
> Sal Young wrote:
> I believe this was already addressed in another post (unless we're dealing
> with two identifical posts from two users).
> SQL Server databases use the concept of schemas. That is, objects may or
> may not be owned by "dbo" - the general default from prior SQL versions.
> Because of this, the schema must be supplied so SQL Server knows where to
> look. The Address table in the AdventureWorks database is contained in the
> Person schema. So try:
> Select * from Person.Address
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||I may just be me but I honestly think the SQL Server Management Studio is one
of the worst tool I have ever used. I wish they still had enterprise manager
and query analyzer for 2005. The new tools are so slow an bloated.
"Gail Erickson [MS]" wrote:

> Query Analyzer does not fully support new SQL Server 2005 features. For
> example, it does not recognize schema names in the Object Browser. So when
> you look at the AdventureWorks objects in the Object Browser of QA, they all
> appear to belong to dbo even though very few objects actually do. If you
> want to continue to use QA for 2005 databases, you'll need to keep that in
> mind. The SQL Server 2005 Books Online topic "AdventureWorks Data
> Dictionary" lists all the tables and the schemas they are contained in.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%234sqZta1FHA.4032@.TK2MSFTNGP15.phx.gbl...
>
>