Hi,
I am doing some resource hungry tasks (some extraction and loading
through DTS), for which each time the SQL Server Log files gets filled
up!
Is there any way to stop the logging (like as during restore)?
Thanks in advance.
-surajit
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 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 RelatedHi All,
I'd like to remotly stop an instance of SQL Server
but this command does not seem to work....
net stop mssql$<server name> (eg. net stop mssql$prod)
...any ideas? I'll want to start this sql server again too.
David.
Is there any way to stop SQL CLR on database apart from using
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
I want to stop this functionality on database...any idea...
Hi All I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new record the id will be 21plese help me in this
View 3 Replies View RelatedI have these two tables and I cant prevent duplicates.
SEARCH
Item
ItemID
Info
CATEGORYDATA
CategoryID
ItemID
SELECT DISTINCT SEARCH.ItemId, SEARCH.Item, CATEGORYDATA.CategoryId
FROM SEARCH
INNER JOIN CATEGORYDATA
ON SEARCH.ItemID = CATEGORYDATA.ItemID
And I get something like:ItemID Item CategoryID
1 item1 1
3 item3 1
1 item1 2 <---duplicate
1 item1 3 <---duplicate
2 item2 3
4 item4 3
Thanks in advance
I have a snapshot replication is running and now I want to stop the replication for a while. Is it possible to do that? If it is then where I can set to stop it? Please help.
Thanks for Help!
Hello,
Does any one know how I can start and stop the sql services with the net use command?
Thanks,
Anthony
I use EM to handle 2 SQL servers. One I can `stop`; the other I can`t. (except I think I used to be able to do so).
When I select the `stop` I get the following message from EM:
"An error 1051 - (A stop control has bee sent to a service which other running services are dependent on) occurred while performing the service operation on the MSSQLServer service."
How do I track down what this other running service is? How do I stop SQL?
All help greatly appreciated.
Judith
I need to remotely start and stop SQL from another machine from a program or command line. Thanks in advance.
View 1 Replies View RelatedI wanted to remove my Northwind database. But that database is currently used for replication. I'll have to stop the replication first before I can remove it.
So how to stop the replication?
Thanks!
amy
Hi,
Can anybody tell me how to stop the execution of a T-SQL statement at once? I have tried Alt+Break but its taking a long time to stop.Whats the reason?Plz suggest....I am dealing with a database containing 24343000 data.
Joydeep
Hey
I have a statement that has been running great for the past hour but now it will not pull the info any longer and just gives me a null
DECLARE @Text VARCHAR(2000)
SELECT @Text = COALESCE(@Text + '', '') + x.memotext
FROM (SELECT TOP 100 PERCENT memotext FROM customermemoheader WHERE memonumber = 'TERMS' and customernumber = '0009' ORDER BY seqnumber) AS x
SELECT @Text AS MemoText
I have verified in the tables that the info is there by running the select statement from within the (). It has worked for 8000 records and now it no longer works. Any help would be much appreciated.
THanks
I have a performance issue with a Cognos report against SQL Server 2005.
The total running time of the report is 1 minute 15, and using SQL Profiler I found out that 1m13 is spent preparing SQL. Execution and generating the report takes up 2 seconds; no problem there.
The SQL is the same every time I run the report, yet SQL Server spends 1m13 preparing it every single time! I'm no DBA, but as far as I understand that's not what's supposed to happen; once prepared, the SQL should execute quickly every time.
Is there a way to stop SQL from preparing the statement every time?
(Cognos 8 against SQL Server 2005 through OLEDB. Oh, and this query takes about a second when run in EM.)
How do you stop Replication?It is causing blocking. URLs....
View 9 Replies View RelatedHey Guys, I have a question. I know you can put a 'STOP' command in VBA code. Is there something like that for SQL Server Stored Procedures?
View 3 Replies View RelatedHi.I need to stop mssql service via command row.How can I do?Tnk,Luca
View 1 Replies View Related
Hello,
i'm desperate! I have a application in my SQL Express and only one database for it. But right now it have 3,97gb, without log file.
i'm already buying a SQL Enterprise to upgrade this, take some days, but my question is: when this size came to 4,00gb my application will be stop working ? my business stop ???
Thanks
Here are some production error messages:
Error: 0xC0047039 at Load work$$MyDataFile from flat file, DTS.Pipeline:
SSIS Error Code DTS_E_THREADCANCELLED.
Thread "WorkThread0" received a shutdown signal and is terminating.
The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load work$$MyDataFile from flat file, DTS.Pipeline:
SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039.
There may be error messages posted before this with more information on why the thread has exited.
I did not request a shutdown!
Someone please give me a global switch to tell SSIS to keep going.
Why should I not load 20,00 records because of one bad record?
I have tediously changed every field on every tool to "Ignore Error".
Why should I still have to do this? I mean why does SSIS default to failure?
Is there some switch I could change in the XML that would change this default behaviour?
The problem with the data in this case: a NewLine character was missing so it looked to the data flow as one very long line.
Hoping for a solution,
IanO
Hi,
In one of my packages, I have a script component to do a transformation and I am inserting the output to table. Within that script component, if the data does not match my requirement, I have to stop the job. For that, I am using 'componentmetadata.fireError' to raise an error. The problem is, now even when an error is raised, the job completes by loading the remaining records and then aborts. But I want to abort the job as soon it raises an error. How to do this?
Thanks.
I create a publication with 1 article with a parameterized filter. Empty table without records. Then i went to create a snapshot through snapshot agent. After snapshot was created, data are inserted with "Insert Into..Select" SQL statement. A total of 20 millions records (10GB) are inserted. When i try to sync with a SQL compact edition, the synchronization % stop at 0 for more than 1 hour. Does that mean it is not working?
Thank you and have a nice day.
Hi,
The following stored procedure (SP) is using a dynamic sql to build the query.
How can this be written using a standard sql. i.e. NOT dynamically being built.
Initially I thought I can have something like the following query but it does not seem to be working for when the where caluse parameters are not passed.
So I ended up using the dynamic sql as it returns the correct data.
Can you see how the SP can be altered please?
Thanks
------------------------this query does not return the correct data where the where parameters are not passed.------------
select
*
from
tbl_Management
where
([Year] is null or [Year] = @Year)
AND
(YearPeriod is null or YearPeriod = @YearPeriod)
AND
(AreaCode is null or AreaCode = @AreaCode)
---------------------------------------------------------------
create procedure usp_PatientManagementAdminGet
@Year int = null,
@YearPeriod int = null,
@AreaCode varchar(3) = null
as
declare @sql varchar(1000)
set @sql = 'select'
set @sql = @sql + ' ID'
set @sql = @sql + ' ,AreaCode'
set @sql = @sql + ' ,Year'
set @sql = @sql + ' ,YearPeriod'
set @sql = @sql + ' ,A1=A2+A3'
set @sql = @sql + ' ,A2,A3'
set @sql = @sql + ' ,B1=B2+B3'
set @sql = @sql + ' ,X1=convert(int, ((B2+B3)*1.0/(A2+A3))*100)'
set @sql = @sql + ' from'
set @sql = @sql + ' tbl_Management'
set @sql = @sql + ' where'
if (@Year > 0)
begin
set @sql = @sql + ' [Year] = ' + convert(varchar(4), @Year)
set @sql = @sql + ' AND'
end
if (@YearPeriod > 0)
begin
set @sql = @sql + ' YearPeriod = ' + convert(varchar(2), @YearPeriod)
set @sql = @sql + ' AND'
end
if (@ProgrammeAreaCode is not null)
begin
set @sql = @sql + ' AreaCode = ''' + convert(varchar(3), @AreaCode) + ''''
set @sql = @sql + ' AND'
end
--trim off the last AND...
set @sql = left(@sql, len(@sql) - 3)
exec sp_sqlexec @sql
We have automated the process of backing up and restoring the databases. But the problem is One database is restoring from several hrs ..though it is supposed to take only a few mins. So there is some issue with it and I want to find out how I can stop the restoring process.
Its not a command am running in query analyser or Enterprise manager, but its automated so i cant see the command to stop it.
Any ideas?
Does UrlEncode have any impact on SQL injection? How would I go about protecting my site?
View 3 Replies View RelatedHi all,
I found some articles online regarding sql injection, but not clear. Can someone give me few examples how to avoid it.
Thanks
Sham
I have a db that tracks clients, payments, and clients codes. A client can be assigned many codes. In my query I'm asking to see any clients that have two selected codes and also of those show me those that have a sum of payments between 1 and 100000 dollars. The query returnes the clients with the selected codes, but the total payments is multiplied by the number of the codes they have of the ones that I select. In this case 'email group' or 'member'
So if a client had a total of $20,000 dollars in payments and they had both of the codes I selected the sum of payments is returned as $40,000.
Any thoughts are appreciated!
Here is a sample example:
SELECT DISTINCT [Salutation], [Client_ID] AS 'Client ID', SUM([dbo].[vw_ClientTotals].[Total Payments]) AS 'Sum(Total Payments)'FROM [dbo].[All_Constituents]INNER JOIN [dbo].[tblClientCodes] ON [dbo].[tblClientCodes].[Client_ID]=[dbo].[All_Constituents].[Client_ID]INNER JOIN [dbo].[tblClientCodeLookUp] ON [dbo].[tblClientCodeLookUp].[ClientCode_ID]=[dbo].[tblClientCodes].[ClientCodeLookup_ID]INNER JOIN [dbo].[vw_ClientGiftTotals] ON [dbo].[vw_ClientGiftTotals].[Client_ID]=[dbo].[All_Constituents].[Client_ID]WHERE ( ([dbo].[tblClientCodeLookUp].[ClientCode] = 'Member') OR ([dbo].[tblClientCodeLookUp].[ClientCode] = 'Email Groups') )GROUP BY [dbo].[All_Constituents].[Salutation], [dbo].[All_Constituents].[Client_ID]HAVING (SUM([dbo].[vw_ClientTotals].[TotalPayments]) BETWEEN '1' AND '100000')
HI,
I am using MS SQL database, i am inserting the data (for a table as tbl_emp) and retrive the data from database and i want to display the data in datagrid
at this time another user want to update or delete the data,now i want to stop that user when i am using insertion,how can i do this.
thanx,
sudha.
I have a weird problem. I started my laptop this morning and was unable to start my SQL Server. Every time I try to start it I get the below error:
A connection could not be established to (local)
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registeration properties.
Any ideas?
Thanks
Hey guys...i've got a problem wif my stored procedures...in which my page keep repeating the same data...so to counter this problem i use SELECT DISTINCT instead of just SELECT..but the problem is when i change SELECT into SELECT DISTINCT...the page will not be display,page error....for your information my stored procures was auto generate from some security sofware...so can guys help me out with my code...
SELECT DISTINCT t.TargetID, 'V' RecordType, t.TargetDNSName [Target DNS Name], t.TargetIPAddress [Target IP], t.TargetIPDisplay [Target IP Display], t.TargetOSName, t.TargetOSRevisionLevel, v.SecChkID, v.Severity, sc.TagName [Tag Name], sc.ChkBriefDesc [Tag Brief Desc], sc.ChkName [Tag Chk Name], CONVERT(NVARCHAR(4000),sc.ChkDetailDesc) [Tag Detail Desc], CONVERT(NVARCHAR(4000),r.RemedyDesc) [Remedy], o.ObjectID, o.ObjectTypeDesc [Object Type Desc], o.ObjectName [Object Name], s.SensorDataID, a.AttributeName, a.AttributeValue, NULL [Port],NULL [Service Name],NULL [Protocol]
FROM #Vulns v INNER JOIN TargetHost t (NOLOCK) ON v.TargetID = t.TargetID INNER JOIN (SecurityChecks sc (NOLOCK) LEFT OUTER JOIN Remedies r (NOLOCK) ON sc.SecChkID = r.SecChkID) ON v.SecChkID = sc.SecChkID INNER JOIN ObjectView o (NOLOCK) ON v.ObjectID = o.ObjectID LEFT OUTER JOIN SensorData1 s WITH (NOLOCK, INDEX(SensorData1_AK3)) ON v.ObservanceID = s.ObservanceID AND s.Cleared = 'n' LEFT OUTER JOIN SensorDataAVP a (NOLOCK) ON s.SensorDataID = a.SensorDataID AND a.AttributeValue IS NOT NULL AND a.AttributeValue != ''UNION ALL
Hi,
I am not an sql server person, but have been asked to stop replication on sql server. Can someone please let me know how to do this.
Thanks,
Jo
Hi friends,
I have quick question. Ihave a job which consists of 9 steps. 9th step is:
Exec xp_cmdshell 'c:mssql.bat'. in this batch file I am stopping and starting SQL services using net stop and net start command. This job is running perfectly but it is not giving the job history when I right click the job and view job history I can't see anything where as if I check the checkbox for Show step details it showing first 8 steps information but it is not showing 9th step information. Even I checked the Sysjobhistory system table in MSDB it shows only 8 steps information.
What is the reason....
Thanks,
Mohammed.