Hi Guys,
I have a bulk process. During the processing incase of any error I need to log error to the SQL Server Error Log.
I checked the help and found the external SP(Srv_Message_Handler) for this purpose.
I am not clear on the usage of this SP. Pls Help.
Vineet
SQL Server 2000 BE, Access 2002 FE.I want to write a stored procedure, that will among other things logerrors to a table, I want to be able to report a summary of work doneand errors to the user and let them decide whether to commit orrollback. I so far foresee a couple of problems.1. Inside a transaction, I write to the error log table, will I be ableto read it back again before commit/rollback?2. If I rollback, my error log gets rolled back too.Is it possible to log the errors outside of the current transaction?
View 2 Replies View RelatedI'm receiving these two errors again and again:
Windows cannot load extensible counter DLL MSSQL$MSFW, the first DWORD in data section is the Windows error code.
Windows cannot load extensible counter DLL MSSQL$SBSMONITORING, the first DWORD in data section is the Windows error code.
What might be generating them and how can I cure it?
The system is SBS 2003 R2 Premium.
Thanks.
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
I have various SSIS packages running on my SQL Server 2005, they are mostly schadule to perform data updations from different locations over remote network.
In every task there is an email object added in so that it can notify about sucessful completion of SSIS package.
Here I want my email should contain some information about how many records updated in system ... or how long it take to complete the job.
Can i include sort of information into email object? if yes ... then how to do it?
Thanks
MIZ
Hello All,
I am trying to do a complex ordering of my query results. Hopefully I can explain what I am trying to do. To simplify the explanation I have a table with two fields in it.
Table name: SPACE
Column names: SPACE_TYPE_ID, OWNER_ID
What I would like to to is order the results in this exact way:
SPACE_TYPE_ID = 2
SPACE_TYPE_ID = 5
SPACE_TYPE_ID = 6
OWNER_ID Alphabetically (if SPACE_TYPE_ID = 3,4,9)
SPACE_TYPE_ID = 1
SPACE_TYPE_ID = 7
SPACE_TYPE_ID = 8
Basically it will be ordered by the id unless the id is 3,4 or 9. I thought I could possibly use a UNION with three select statements but I believe I can't have three order by statements with a UNION. I also was told about temp tables in the database but have no idea how to use them. Is this a possible solution.
Any help on how to achieve this customized order would be greatly appreciated.
Thanks
jlmoshier
I am using SSIS to load a lot of Excel, csv files. Some of the files will fail for various formating/validation reason. Is it a good way to capture the error and generate a nice error report so the provider can read it easily and correct the data files?
The error log of the package is difficult to read.
I need a sales report that would display weekly amounts either sold or forecasted in matrix (pivot) form.
The data used for the report is like following (INV is sold and FC is forecasted):
rtype
region
week
wgt
INV
EU
1
150
INV
US
2
200
FC
US
2
400
FC
US
3
1000
FC
EU
2
100
I want the report to show data like this:
WEEK 1
WEEK 2
WEEK 3
TOTAL
INV
INV
FC
FC
INV
FC
EU
150
100
150
100
US
200
400
1000
200
1400
So I put region as rows, week and rtype as columns, and wgt as data field. Everything works fine except that there will be no grand totals for INV/FC. What I get is:
WEEK 1
WEEK 2
WEEK 3
TOTAL
INV
INV
FC
FC
EU
150
100
250
US
200
400
1000
1600
How can I get my totals? I know I could tailor my data to get INV/FC values into different columns to show as data fields in matrix report. But in this case, every week would always display 2 columns, which is certainly not what I want.
I also know I could add another matrix report to create a simulated total columns, but I wasn't able to "join" these 2 reports properly, there is always some space between them and the report looks unprofessionally. So I am looking for a way to do that with a single matrix.
Second question: how can I paint entire columns into different colors, so that INV is always green and FC is always blue? I tried customizing background color, but it only paints cells with values inside, leaving empty cells white. Is it possible to have the INV columns green, entirely?
Thank you in advance,
Wapper
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma ',' and minus '-'. Take a look on the example what I exactly want to do is
I have a main table name `tbl_main` in SQL
AS_ID KWD
1 Man,Businessman,Business,Office,confidence,arms crossed
2 Man,Businessman,Business,Office,laptop,corridor,waiting
3 man,business,mobile phone,mobile,phone
4 Welcome,Greeting,beautiful,bride,celebration,wedding,woman,happiness
5 beautiful,bride,wedding,woman,happiness,mobile phone,talking
6 woman,girl,Digital Tablet,working,sitting,online
7 woman,girl,Digital Tablet,working,smiling,happiness,hand on chin
If serch text is = Man,Businessman then result AS_ID is =1,2
If serch text is = Man,-Businessman then result AS_ID is =3
If serch text is = woman,girl,-Working then result AS_ID is =4,5
What is the best why to do this?
Has anyone customized the parameters area to pretty it up and make it more configurable like control number of columns etc?
Or can anyone direct me to a third party component?
Thanks.
Hi...
I am creating an application where I need to export the reportviewer data into formatted excel sheet.
Ex excel sheet should have formatting-
1-Column width set to 8.3
2-Wrap text turned OFF
3-Left Justify all fields
4-Landscape default for orientation.
If I apply these formatting into the .rdl files then it is going to be applicable for all other formats
like csv,xml etc but I don't want to apply these fomatting except the excel report.
I think if I can trap any event when export button is clicked when excel is selected from dropdown menu
then it is going to solve the problem a little bit.
I need this solution as soon as possible.
Regards
Chinmay Chandra Dey
(Software Engineer)
I don't want show the DBMS error to my clients. I want show a simple error instead of SQL error.
View 3 Replies View RelatedI have a dataset which is like:
Month, Day, Location, TotalSales
Jan 1 A 100
Jan 1 B 200
Jan 14 A 120
Feb 2 A 130
Mar 5 B 150
I want to transform the dataset using sql query into the following format:
Month, Day, LocationATotalSales, LocationBTotalSales, TotalSales
Jan 1 100 200 300
Jan 14 120 0 120
Feb 2 130 0 130
Mar 5 0 150 150
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies View RelatedWe have Microsoft SQL Server Management Studio Version 10.50.2500.0.I change to "Results To Text", and "Display Results in a Separate Tab". Then I restart SQL Server Management Studio; and my settings are gone.Is there a way to save my settings?
View 3 Replies View RelatedHi!
I have created a setup in visual studio and set the sql server as a pre-requsites.
now when the setup is run from
setup.exe
it checks whether sql server is installed... if installed then continues otherwise install sql server express edition, now this installation is silent,
I need to set the sa password during installation of sql server . i have no option to set the password during installation, can any one help
Hi all,
I am developing a program to connect to SQL 2005 Express. I don't know if I need any CAL license in order to make the connection. If it is not necessary for the Express Edition case, how about if my client upgrate to SQL 2005 in the future? Do I need CAL license for SQL 2005?
Thanks for any advice given.
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
Is this an intrinsic issue?
How can I check from database username and password? It doesent need any special authentication, just a lookup through the database and if the user exist than continue with the next page.Thanks
View 2 Replies View RelatedI have a web application accessing a SQL Server database (the ususal stuff).
I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?
The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed
Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?
Jags
Is there a way to produce a log of all SQL statements hitting a database in a given range of time by a specific SPID? Obviously the SQL Server activity logs do not go into that much detail, except when errors are produced or a change is made to a system table. Is there a setting to add more detail, or to log a specific SPID's actions, or maybe a third party software that will give me what I am looking for?
Thanks. JT
does SQL 7.0 have any built in logging capabilities to identify row level actions by operator. For instance, can it tell me that a particular user deleted or inserted a row? How would I tell it who the operator is?
View 2 Replies View RelatedIs there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?
View 2 Replies View RelatedHi,
is there any chance to log all incoming SQL-statements with date/time and duration ?
Thx for help.
dajm
Is there anyway to log every incoming command, INSERT/UPDATE/SELECT/DELETE to a seperate database table?
Thanks
I've been asked to write a trigger that will basically log changes to certain fields in certain tables, then create a front-end application where the user will be able to review the info. The front-end app. is not a problem for me - the trigger is. I have found example of how to do this on Update when it's a complete row you want to log, but not a specific field. In addition, I also need to know if someone is attempting to read certain data and who that user is. If the user is not someone that is allowed to read the data, then I need to send an email alert.
I believe it's possible to do the above (despite my lack of knowledge :) -
Does anyone know where I can get more information on how to accomplish the above - or where to start looking?
Thanks to any who can guide me in the right direction.
Hi,I´m currently playing around with ASP.NET.Is there a way to log all Queries that are send to the SQL-Server? Something like the query.log of a Mysql.
View 3 Replies View RelatedI have a problem acessing MSDE. My server runs at "NT AUTHORITY/NETWORK SERVICE" so it is not allowed to connect through the windows authentication feature. It seems that the password for the "sa" user account was changed during setup, after reading the logs from the setup I can see that it was changed but I cannot see what it was changed to. How would I set up a new account that I could use to access the server though SQL Authentication???
David Legg
Lets say I have version 1 of a database - DB1. I am creating the second database, DB2.
What I need is a log of all the SQL statements that where used to
change DB1 into DB2. This means recording both what happened in the GUI
and in the SQL Query Analyser.
Is there a way I can do this? I know SQL Server has a transaction log
somewhere. Is there a way to set this to output all the changes made
from a set date on a database into a SQL log file?
Thanks in advance for any help.
Jagdip