I have a script or SP that takes a very long time to perform
multiple tasks, and after each one there is a PRINT statement
that shows the time and what was just accomplished, to help me
monitor what's happening and estimate how long it will take.
In a script, I can put a GO after each PRINT to cause the output
to appear immediately, but that's not possible inside an SP.
Instead, it seems the output goes to a buffer, and the buffer
is only output when a PRINT causes the buffer to become full.
Sometimes there is a long delay before the buffer fills. Is
there a way other than GO to cause immediate printing? (I
guess I could just use longer messages, and fill up the
buffer with every one, but that's not very elegant.)
So I have been asked by our sustainability person to create report from our printing data that actually shows the number of pieces of paper used. This is easy enough for single-sided printing, but when printing in duplex the software does not take into account that 3 printing pages actually equates to 2 pieces of paper. I know this sounds simple, but say I have a print job record that looks like this:
This is a print jobs that if done correctly is actually 21 pages( duplex printing). If the job is say total_pages =5 I cant just divide by 2 because its actually using 3 pieces of paper ( yes they are wanting this data don't ask why). How can I adjust some sql to accurately depict 5 pages, front and back, as 3 pieces of paper?
I have deployed a report that is configured for landscape printing. It does print in landscape, however, only the first seven columns appear on the first page and the other five columns appear on the next page. Is there a method, like in print preview setup in MS Excel, where we can scale down the print (like to 80%) so all columns appear on each page?
I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use. Is there a Possibility to make a "force detach" or something?
Forgive my ignorance. I have been told you cannot run a force restore without SQL Agent running? Is that so?
Also, is it possible to execute a force restore from a command line. If so, how would you go about do this.
I ask these questions because recently our database and MSBD files became corrupted and couldn't restore them in the normal way. Had to manually rebuild MSDB file.
I've a complex stored procedure, that makes a lot of insert, update,delete and so on.I would like to make some commits durint this sp, but of course theyare not "real" commit because who call the sp could decide for arollback.But I know that this commit has to be real. In fact, the transactionlog grows really too much during the execution.Is there a way to force a commit durint a sp ?thank you very much!
I set up odbc to link to sql express from access 2003, using sql authentication to force a login, and did not click 'save password'. when i open up access again, and open table, it goes right in without a login. How can I set so it does a login, at least each time Access opens?
I want to configure SQL 2k5 Express so that all connections are encrypted. I've found the documentation that gives the How-To steps, but I don't see the SSL option in the Network Configuration.
Is this possible with the Express edition?
Do I need to do something special with the installation?
My data flow has several transformations: 1. Search an employee, if the employee already exists, update it, otherwise insert it. 2. Once the new employee is created, i have to get its id (with another search transformation )to update another table with it. This id is an autonumeric , thats the reason i have to get it once the record is inserted.
At this momment this second search transformation to get the assigned id for the new reacord doesnt find any employee... i suppose its because these new data is not commited in the database....
I have a report that has multiple graphs on it. I would like to force a page break (new page) in between the different reports causing a report to be generated on it own page. Im assuming that Im missing something here because it seems like a easy/usable feature. Any help would be great.
I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?
I would like to know if there would be any special way to force drop a database from an ASP.NET page.
When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout. In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.
I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?
I have a DTS package in which I would like it to fail if the table is empty. I would like to create the first task as this check (i.e. select @cnt = count(*) ... if @count = 0 then fail the task (to prevent further DTS Task processing.
Does anyone have an example or suggestion on how to set this particular Execute SQL Task to fail?
Does anyone know a way to force out all users from a particular database? I need to script this to perform some maintenance at a particular time every day.
I created a view from a table with out any where clause. All the rows from the table will be in the view with some extra info.
The table has a few indexes.
In my stored procedure I am storing two columns from the table ( IdHi , IdLo - primary key ) into a temp table and joining the temp to the view. Here the query is taking too much time and not using the index. Can I force the primary key index on View?
Does anyone know how to force a SSIS step to fail? I don’t want to export a file if no records were written.
I found this but not quite sure how to implement it: To do this, double-click on the connector line joining 2 of your ExecuteSQL tasks and change the "Evaluation operation:" to any of the options that include "Expression". This enables you to enter a boolean expression that must evaluate to true for the path to be followed. Assuming you've got an int parameter called "sp1rtnvalue" which must evaluate to 1 for the flow to continue; your expression would be: @sp1rtnvalue == 1
Am I suppose to define a @sp1rtnvalue in the stored procedure that creates the file I want to monitor? Do a record count in the stored procedure. In my pkg I enter the expression @sp1rtnvalue == 1 when I run that step the expression will evaluate to false and fail?
If so where exactly do I enter the Boolean expression (@sp1rtnvalue == 1) , what tab in what dialog/wizard??
I have a complex query (16 table join) that until five days ago took 30sec to run. It now takes eight hours. I restored a backup of thedatabase from five days ago and the query plans are completelydifferent for the same exact query. Five days ago the final estimatedrow count is 1.6M now it is 1.7E+10 OUCH! The amount of data added inthose five days is insignificant (percentage wise). I rebuilt all theindexes using DBCC DBREINDEX with no change in the plan or results. Isthere anyway to save the query plan (from the backup) and run itagainst the new data?TIA,Tracy
I've been using some of the data mining capability in SQL 2005, and have been very impressed. But I've mostly been using decision trees/neural nets, and now have a problem that would seem to require clustering, but I'm not sure how to approach it...
Have list of parts by serial number and part number, and in a data cube have crunched a bunch of data to give me each parts' relative reliability.
So what I can do so far is to ask the cube "for a certain part number, which specific serial numbers are our 'bad actors'", ie have an unacceptably high failure rate compared to the average, by standard deviation.
The next logical question would be... what factors do these particular "bad" parts have in common? Environmental exposure, usage rates, a particular repair facility, etc etc. I have that type of raw data in other tables, no problem.
So what it seems like I would want to do is force a clustering algorithm to choose a certain segment of parts as a cluster and then use DM to determine what characteristics those parts share. Then we could see if there was any corrective action we could take, or whether we should just chuck those parts in the bin (if the DM didn't find any reasonable relationships between the parts). But I don't know enough about the clustering/association type DM to figure it out.
Hi, I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
The problem: IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.
Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?
Im looking at the sql server log, and I see a strange behavior. Im getting at least 5 "sa" login attempts from an sepcific IP address. How can I avoid this, lock that IP or add a delay to 'sa' failed logins?
In some text boxes within the table header, I want to force the text to wrap. This would be comparable to using Alt+Enter in Excel. For example, the text box may read like this...
Is there a way (during the Control Flow) to force a package to complete successfully when a specific condition is met?
Here is what I'm trying to do:
A package will be scheduled to execute once a day The first thing it does is downloads Excel files from an FTP site. If there are no files to download, I don't want the package to fail, I simply want the package to stop (preventing the subsequent Data Flow tasks from executing), returning a successful completion.
The reason, I need a successful completion is because I plan to have MOM monitor the Windows event logs, notifying us of any errors that this package logs.
i have a datatable with Part , DateSold,ItemsSold i want to show the sales for the last 12 months by month. if there were no sales for a given month i want the table to show 0.
here was my first attemp that only gives me data if all twelve months had sales.
SELECT MONTH(DateSold) AS Month, SUM(ItemsSold) AS ThisYear FROM inv_Monthly_Sales WHERE (YEAR(DateSold) = @Yr - 1) AND (Part = @Part) GROUP BY MONTH(DateSold) ORDER BY Month
i tried using isnull with the sum but that didn't work. how can i force the table to always generate a dummy table of 12 months and then left join to it and in that way force it to give 12 moths of sales even when it does'n exist.
I want the following query to return a row even when table 'X' is empty. How would I do this? SELECT TOP 1 @Var1, @Var2, @Var3 from X The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included. When table is empty, it reurn nothing. It only return a row when table is not empty.
Hi all! I've been banging my head on this for way to long and decided it's time to reach out for help. I'll try to be complete and concise with this, but this will end up being lengthy most likely. The crux of the issue is that I apparently do not have any control over what is returned from a stored proc via the SqlCommand.ExecuteNonQuery method. I want the procedure to explicitly return 0 if the proc was successful, otherwise it will return the error code generated. A value of -1 is consistently being returned when SET NOCOUNT ON is in place, and when not, a 1 is returned. This seems to be ignoring the fact that I have RETURN 0 at the end of the proc. Perhaps I'm missing something, but if I RETURN 0, I should get 0...here is the procedure:1 ALTER PROCEDURE [dbo].[epsp_EditCities] 2 @CityId int = null, 3 @City varchar(50), 4 @CreatedBy int, 5 @UpdatedBy int, 6 @ActionType varchar(1) = 'X', 7 @Identity int = 0 OUT, 8 @RowCount int = 0 OUT 9 10 AS 11 DECLARE @ERROR int -- Local @@ERROR 12 13 14 SET NOCOUNT ON 15 BEGIN TRAN 16 17 IF @ActionType = 'I' /* --- INSERT --- */ 18 BEGIN 19 20 INSERT INTO [EnrollmentPrograms].[dbo].[Cities] 21 ([City] 22 ,[CreatedBy] 23 ,[UpdatedBy]) 24 VALUES 25 (@City 26 ,@CreatedBy 27 ,@UpdatedBy) 28 SET @Identity = SCOPE_IDENTITY() 29 30 END 31 32 IF @ActionType = 'U' /* --- UPDATE --- */ 33 BEGIN 34 35 UPDATE [EnrollmentPrograms].[dbo].[Cities] 36 SET [City] = @City 37 ,[UpdatedBy] = @UpdatedBy 38 WHERE CityId = @CityId 39 END 40 41 IF @ActionType = 'D' /* --- DELETE --- */ 42 BEGIN 43 44 DELETE FROM [EnrollmentPrograms].[dbo].[Cities] 45 WHERE CityId = @CityId 46 END 47 48 49 -- Error checking (place this after every statement) -- 50 SET @ERROR = @@ERROR 51 SET @RowCount = @@ROWCOUNT 52 53 IF @ERROR != 0 GOTO HANDLE_ERROR 54 55 COMMIT TRAN -- No Errors, so go ahead 56 57 RETURN 0 58 59 HANDLE_ERROR: 60 ROLLBACK TRAN 61 RETURN @ERRORAnd a fascinating tidbit is the results from this which shows the return value as 0:1 DECLARE@return_value int, 2 @Identity int--, 3 --@RowCount int 4 5 EXEC@return_value = [dbo].[epsp_EditCities] 6 @CityId = 1, 7 @City = N'Agoura Hills', 8 @CreatedBy = 1, 9 @UpdatedBy = 1, 10 @ActionType = N'U', 11 @Identity = @Identity OUTPUT--, 12 --@RowCount = @RowCount OUTPUT 13 14 SELECT@Identity as N'@Identity'--, 15 --@RowCount as N'@RowCount' 16 17 SELECTReturnValue = @return_value
And finally, here is the C# call that always returns -1: retVal = cmd.ExecuteNonQuery(); Now, I've tried almost all variations and unit tests on the procedure that I could dream up. Commenting this out and putting this in etc....I still remain unable to fix the return code to 0 and get that result into my retVal var. Any love? Much thanks in advance for spending time on my issue! Cheers! Wayne
In the Application event log I am seeing entries like the following:
Login failed for user 'sa'. [CLIENT: 60.32.67.85]
Once every second.
I am assuming this is a brute force password attack, an ARIN query of the IP address indicates it's from: Asia Pacific Network Information CentreIs there any way to block this IP from accessing my server?Thanks,Tylerp.s. I tried posting this on MSDN forums, but the site appears to be broken.
