I have a application that runs non-parameterized queries against SQL, that are filling my proc cache. The most common one is like the below:
Code Snippet
DELETE FROM Item_Temp WHERE SessionID = '1232345'
The numeric value of the SessionID changes for every time it runs. How do I force parameterization of this without changing the application code?
I would like to parameterize the server name and database name in a query inside a stored procedure. I would like to avoid using the exec(@SqlCmd) technique if possible. HELP?
View 4 Replies View RelatedDear Members,
Can I parameterize SSIS packages?
As a example, I wanna know that where the perticular SSIS package is located
Please Can anyone help me ?
I belive I am cornered by a BUG with parameterization parameter in the Sql Server 2005.
Steps to Recreate
==============
1) Set the database parameterization to 'Forced'
2) CREATE TABLE TRY
(
ID1 BIGINT,
ID2 BIGINT,
DESCRIPTION NVARCHAR(256),
CONSTRAINT PK PRIMARY KEY (ID1)
)
3) INSERT INTO TRY VALUES(1,1,'ABC')
INSERT INTO TRY VALUES(2,1,N'ЫФСРШТ')
4) SELECT * FROM TRY WHERE DESCRIPTION LIKE N'%Ф%' ;
This will return you the result which is absolutely fine.
SELECT * FROM TRY WHERE DESCRIPTION =N'ЫФСРШТ' AND ID1=2;
This will also return the result which is perfect !!!
SELECT * FROM TRY WHERE DESCRIPTION LIKE N'%Ф%' and ID1=2;
Results are gone !!!!!!! wooosh ....
any combination of like statement with any other condition does NOT work.
5) Change the parameterization back to simple , and everything works fine.
I am using Sql server 2005 standard edition with SP2.
Any help will be appreciated as I am stuck wih my application developement and at the moment I am working with parametrization a simple.
Thanks
Harneet
Hi
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?
Thanks for Help
Greets
Id like to know how I force the transaction log to shrink in v2000?
I have sp_force_shrink_log but this only works in v7.
... Also, how do I set the log to truncate on checkpoint?
Many thanks,
Lauryn
How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc.
Pretty much how to force it being on number I would like to begin.
Hi All
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.
Thanks
I have a script or SP that takes a very long time to performmultiple tasks, and after each one there is a PRINT statementthat shows the time and what was just accomplished, to help memonitor what's happening and estimate how long it will take.In a script, I can put a GO after each PRINT to cause the outputto appear immediately, but that's not possible inside an SP.Instead, it seems the output goes to a buffer, and the bufferis only output when a PRINT causes the buffer to become full.Sometimes there is a long delay before the buffer fills. Isthere a way other than GO to cause immediate printing? (Iguess I could just use longer messages, and fill up thebuffer with every one, but that's not very elegant.)Thanks,Jim GCountrywide Home Loans
View 2 Replies View RelatedI'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!
View 3 Replies View RelatedI 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?
View 1 Replies View RelatedI 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?
Thanks,
OldSam
Hi,
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....
the question is, Its possible to force a commit?
Thanks!
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.
Thanks,
ZDB
Hi,
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?
Hi everybody,
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.
Thanks in advance
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?
Thanks!
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?
Thank You
Jamie Reis
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.
Thanks for your help,
Les
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?
Thanks!
Hi,
is it possible to to modify/add to QA (using options ,macro ..)to execute line bellow on run of each script opened
from *.sql file
select
'script= put you file name'+ char(13)+
'server = ' + cast(@@servername as char(20))+ char(13)+
'dbname= ' + cast(DB_NAME()as char(20)) + char(13) +
'execution date= '+ cast(getdate()as char(24))+ char(13)+
'executed by = '+ system_user
thanks
Alex
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 hope that made sense.
Thank you
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
View 6 Replies View RelatedI'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.
How does the group think I could approach this?
Geof
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?
Thanks guys...
Hi to all,
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?
Thanks so much
Cristian
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...
Customer
Install Date
but I want it to look like this...
Customer
Install
Date
Thanks.
If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0
How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:
SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0
It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.
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 created the report below in SSRS to show the total by month for the calendar year.
As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.
01
02
03
03
4
05
06
07
08
09
10
11
12
Total
AA
17.5
22
17.5
75
30
162
BB
15
15
15
15
15
15
15
15
15
15
15
15
15
195
CC
15
15
15
15
15
337
15
233.3
15
325.5
117
15
15
1147.8
DD
315
290
1231
1231
1231
269
953
384.8
706.8
1798.8
290
602
419
9720.25
Total
345
320
1261
1261
1261
638
983
633.1
758.8
2156.8
497
662
449
11225.1
As I go to project this report for January 2007 I am having trouble.
I want the report to appear as below with the 12 requisite columns.
01
02
03
03
4
05
06
07
08
09
10
11
12
Total
AA
0
BB
15
15
CC
15
15
DD
315
315
Total
345
345
But as only data for the first column is returned from my query I only get one column to appear.
01
Total
AA
0
BB
15
15
CC
15
15
DD
315
315
Total
345
345
How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?
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.
your help is greatly appreciated,
slimshim
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