I'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.
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 RelatedHi 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
Hi,
i just started working on a new project, a RIA with a backend on SQL 2005.
The question that I'm asking - about the databases architecture - is it possible:
a) (this is the preferred solution) to have up to 5 000 (five thousands) databases inside a SQL 2005 instance (each client with his own small db)
b) would it be better to have one large database with tables eventually having near a billion of rows ?
c) one single database with thousands of small tables.
Thank You All.
Edit :
The clients databases will be identical in structure.
The motivaion for having each client with his own database is to ease management (backups, restores, roll-backs) and tools in this direction will be created (scripts, automated procedures, scheduled management plans etc). Each client is proprietary, and to have things separated seems more legal.
The hardware is not a problem, the db server will be a blade center runing a clustered MS Windows Server.
Code Snippet
(07:51:49) him: i haven't followed sql recently but let me see (seems difficult question)
(07:52:36) him: what is the motivation to have different datbases for each client?
(07:52:46) me: (the hardware is not a problem, probably ibm blade center, about the management - tools will be created to ease)
(07:52:52) him: it is it the size/efficiency or you want to separate them
(07:53:02) me: for the back-ups and restores and roll-backs
(07:53:13) me: the db-s r identical
(07:53:30) him: identical in design or data inside them
(07:53:33) me: yes, design
(08:02:12) him: so the limitation is Databases per instance of SQL Server 32,767
(08:02:21) him: so it seems that in that respect you are going to be fine
(08:02:45) me: yes, i've seen the specs
(08:02:50) him: yep
(08:03:03) him: in terms of design
(08:03:13) him: managing 5000 databases would be a nightmare
(08:03:22) him: unless there is some automatic way to do it
(08:04:18) me: that's for shure, and that's what is intented
(08:04:29) me: automadet procedures, scripts etc
(08:05:27) him: so again what is the motivation to partition each client in different database
(08:06:05) me: there is a need to keep their data separate
(08:06:19) me: each client is proprietary
(08:06:44) me: and to have things separated is more legal
(08:07:25) him: well you try with several databases and see how it scales
(08:08:37) him: one thing that would also prevent confusing databases connections and may be helpful if you decide to change the to a single database model is to design the database as if it would be used with many clients but use it just for one
(08:09:09) him: then, if you decide to merge the databases in the future it would be relatively easy
(08:09:16) him: unless this would create too much overhead
(08:09:41) me: yes, this is allready the case
(08:09:58) me: so, i go for a test u say
(08:10:12) me: and see how it's working
(08:10:16) him: ok. then it seems that the issue is maintaince then anything else
(08:10:29) him: yeah. i don't think the sql server would be a problem
(08:10:44) him: there would be some overhead of the different databases but should be small
(08:11:12) me: :)
(08:11:24) me: k, thank you verry much
(08:12:53) him: http://www.thescripts.com/forum/thread503521.html
(08:13:09) him: i think either way has plus and minuses
(08:16:56) him: also read the forum link i sent you
(08:17:00) me: ty, i'm on it
(08:17:16) him: there are some interesting considerations i did not think of
(08:17:58) me: i just saw about caching maintainance plans, stored procs
How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())
increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.
Anyone has an idea?
mipo
I have a query that returns raw tick data from a table. Unfortunately aftereven a few days there are hundreds of thousands of rows so the followingquery is not efficient.SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? ANDRawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' ANDContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE Status= 'I' AND ContractCode = ?))The most obvious solution then is to get all tick data with status ='I'(Imported) for a contract, process it and then move it to another table forarchiving. I am faced with a problem however: After selecting all data for acontract with status='I' the application was updating these records to a newstatus of 'P' (processed). Unfortunately another application is continuningto feed in live data and so it is possible that we will inadvertantly updateunprocessed data to 'P'.Question: Is it possible to select all records with status 'I' (from abovequery) and update their status to 'P' in one sequence?I am not a programmer, but if this is possible I should be able to implementthe query.Many thanks.Steve
View 3 Replies View RelatedGreetings,
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
Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records. Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download. When I
save the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks
Format number thousands separator with point
I have this number in my database output result of query: 1013473
I need this output: 1.013.473
I have a custom application that on occasion requires thousands of TSQLfiles (on the file system) to be compiled to the database.What is the quickest way to accomplish this?We currently have a small vbs script that gets a list of all the files,the loops around a call to "osql". each call to osql opens/closes aconnection to the destination database (currently across the network).
View 5 Replies View Related
I would like to select a BIGINT type and get a formatted result with commas. Anyone have ideas?
declare @i bigint
set @i = 123456789
select @i
--Would like to get
123,456,789
I have a report that I'd like to involve in delivering tons of PDFs for each of our customers for a billing cycle. There is a table that the report reads from that gives it all its data necessary for the report (which is a customer bill) and the table also has a column that has the file name for the PDF for that particular customers bill for the report. Basically each table row represents one output report (bill) and each table row has its name nicely formated for me. Writing the report is not a problem. Figuring out how to run through thousands of rows generating a PDF for each with the file name from the table is my challenge.
I would like to create either a SP or a SSIS package (that is scheduled) that can run a report for each line of data in my table and spit out a PDF file to a UNC path. It might have to generate thousands of PDFs to a UNC path that has plenty of space. There will be another SSIS package that moves the PDFs later to their proper directory.
So, is there someone who has done this before? Any suggestions? Is there a quick path to doing this, would it take a ton of time? Any tutorials out there?
Thanks,
Keith
p.s. My preference is to do this in SP's or SSIS and if I need a .NET language to do that in VB.NET, but I'd rather avoid that if I can.
Hi guys,
I have this problem with reporting services wherein my report contains an Area Chart. My plot points is about 100,000 records. The problem is sometimes the Chart is displaying right and sometimes its not. The users need to refresh the report again for the chart to display right. This doesn't happen if my records to plot contains around 10,000 records.
Does RS have a limation in plotting records in an Area Chart? Any solutions?
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 RelatedI 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"
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
Other than doing bulk insert is there a way to insert in SQL 7 without logging the transaction in order to speed up the process?
If so what command(s) allow this?
If you use the import feature of DTS to import data into various tables and your database is setup with truncate log on checkpoint set to false, will SQL Server log those transactions?
Thanks,
Veronica
I have (amongst many) an overnight SQL stored procedure that occasionally takes over an hour to run compared to its usual 2 minutes. It does the same each night, as does everything around it. (N.B. The job truncates and reinserts data into a table.)
I'm just wondering if a user request during the day that reads the table perhaps leave the PID open and thus a lock open with it. Then, perhaps my SQL job comes along and waits for the lock to release before it performs the truncate.
Couple of questions:
1. Could this happen?
2. If so, is there a log anywhere to suggest that a deadlock situation arose and PID x was the loser?
Thx in advance.
Andy Richardson
Hello everybody.
1. I have 28 Gig database with 4 tables above 4 Gig each with very bad
fragmenataion, each table has between 3 and 5 indexes
2. Database set for full recovery and I use custom log Shipping to restore
db on stand by server every 15 min.
I tried to run DBCC INDEXDEFRAG on one index on 4Gig table .
following took place
1. It took 4 hrs to complite DBCC INDEXDEFRAG
2. log shipping fail.
3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited
I tried to run drop and create clustered index on table it create same
problem - log growing, log shipping fails
(if log shipping fails and stand by database get suspended it will take 6- 8
hrs to restore it from backup and apply all logs)
So my question is
What would be best way to rebuild- reindex - defragment 28 Gig database
when it set to fully recovery and log shipping
Thank you
Alex