Hi,
I'm running SQL Profiler on an SQL Server 2000 database. I see that one
stored procedure gets repeatedly executed having a handle of '1'. This
query takes a long time to complete.
How do I find what the text of the stored procedure is? I cant see any
handle being created (using sp_prepare) with an id of '1' in the
profiler. Is there any way to force the server to re-prepare all
statements so that I can see the statement text and its preparation in
SQL Profiler?
If I'm running a DTS with 2 connections(flat file source and SQL table destination) and a Data Driven Query Task, and then I get an error message saying "Stataments sould not be prepared. Line 2: Invalid syntax near "."
Which code should I look for the error? The message says Line 2 but i don't know where to look.
Hello,I have a table which contains some nullable columns. I want to write asingle query, which can be prepared (ie. prepared statement), that canhandle null or non-null values for the where clause. Is this possiblein a standard-conforming manner?The simple for of the query is this:SELECT * FROM <table> WHERE <column> = <value>But when the value to be matched on is NULL, the syntax of the querymust change to be:SELECT * FROM <table> WHERE <column> IS <value>In the second case <value> is NULL.I know one option might be to change the ANSI NULL handling option,but I am loathe to do this (I have five RDBMS's to support, not justMSSSQL).I thought I might have been able to cheat using an IN clause to makethe SQL consistent, but no luck.ThanksKevin
I have an application which uses ODBC to insert, update, delete records in an MSSQL DB. The application uses prepared statements and keeps the odbc statement handles to execute the same statement multiple times. This application works fine until triggers are introduced. The triggers are pretty simple and take the inserted, updated record and insert this information into another table. If we change the application to not re-use the prepared statement handle, by closing the statement handle after each execute, it also works fine. (However, this results in bad performance.)
The error message is: "[ODBC SQL Server Driver]Connection is busy with results for another hstmt". If anyone has any information on this, I would greatly appreciate the help!!!
I have a problem with a prepared statement against SS2005 SP2 that I do not fully understand. I have a query that is prepared and executed from a java application that looks similar to this:
SELECT TOP 1 str.ID FROM bigtable1 big1 LEFT OUTER JOIN bigtable2 big2 ON (big1.ID = big2.ID) LEFT OUTER JOIN htable h ON (big2.HID = h.HID AND h.DIRECTSW=1) WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT LID FROM LAS WHERE LASID = ? )) AND big2.COMPLID <> 2 AND big2.COMPLID <> 3 AND ISNULL(big2.date1, big2.date2) BETWEEN ? AND ?
When run as a prepared statement this runs in about 27 seconds. When run under the query component of Management Studio it runs in about 1 second. Before each run I am clearing the buffers and the prepared statement cache. When running these I have looked at the execution plan from profiler and they both look the same. If I change the
WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT LID FROM LAS WHERE LASID = ? ))
to INNER JOIN it runs ~1 second in both environments.
Any pointers would be welcome to explain the disrepency.
I recieve an error when I use the Lookup component in SSIS that reads:
Statement(s) could not be prepared.
I'm using a SQL 2005 DB as the source which runs into a lookup table and is use to compare records with an SQL 2000 Database. I've created connection managers successfully to both these databases. When trying to use the results of an SQL Query for the lookup to the SQL 2000 database (which is a linked server) and I try to map the columns, the error pops up and exits out of the lookup properties Window
The details to the error read:
Program Location: at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.PrepareSQLStatement(String sql, Boolean bypassPrepare) at Microsoft.DataTransformationServices.Design.DtsConnectionCommonControl.CheckSqlQuery()
I'm looking to use the results of this comparison to output in some form of a report. Ideas would be greatly appreciated!
FileName is coming as blank in db, while in debug mode i can see that it is being set with a proper filename. The other 2 params are inserting fine.
The FileName field is set as Text field with width length 255, and the incoming data is always shorter than 255 chars. The insertion happens without any errors/exceptions. I have also tried replacing the data for filename with a string like "Test", still it is inserting blank string.
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me... private void Submit1_ServerClick(object sender, System.EventArgs e) {
I am trying to get my mobilink aplication to synchronize with MS SQL Server 2000.
After getting rid of the global variable references I was using from ASA that the synchonization was failing on, it is now failing on the 8180 error.
I am not sure what it is not liking. I set the begin connection script to be blank as before it had "CREATE VARIABLE @UserID VARCHAR ( 128 )", as I found no easy way to delete the script sets with ISQL.
Microsoft says it has a hotfix for this particular error code, but I am not interested in calling their tech support line and paying to get it.
Any ideas?
The log from the Mobilink server says:
I. 03/05 11:24:07. <thread 1.4>: Working on a request I. 03/05 11:24:07. <thread 1.4>: Synchronization request from: JM (version: 50data) I. 03/05 11:24:07. <1.4> [JM]: Warning: [10050] ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'busler'. (ODBC State = 01000, Native error code = 5701) I. 03/05 11:24:07. <1.4> [JM]: Warning: [10050] ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (ODBC State = 01000, Native error code = 5703) I. 03/05 11:24:07. <1.4> [JM]: ODBC isolation set to: Read Committed I. 03/05 11:24:08. <1.4> [JM]: COMMIT Transaction: begin_connection E. 03/05 11:24:08. <1.4> [JM]: Error: ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (ODBC State = 42000, Native error code = 8180) I. 03/05 11:24:08. <1.4> [JM]: Error Context: User Name: JM Modified User Name: JM Transaction: begin_synchronization Table Name: NULL I. 03/05 11:24:08. <1.4> [JM]: Script Version: 50data Script: End of Error Context I. 03/05 11:24:08. <1.4> [JM]: Warning: [10010] No error-handling script is defined. The default action code (3000) will decide error behaviour. I. 03/05 11:24:08. <1.4> [JM]: ROLLBACK Transaction: begin_synchronization I. 03/05 11:24:08. <1.4> [JM]: COMMIT Transaction: end_connection I. 03/05 11:24:08. <1.4> [JM]: Disconnected from consolidated database I. 03/05 11:24:08. <1.4> [JM]: Synchronization failed
I am using PowerDesigner to reverse engineer a SQL 2000 database. I am getting the following error. I can't find out what this error means. Does anyone know?
Unable to list the users. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. SQLSTATE =37000
Here's the deal. I have a table that contains data for contracts each time they are brought to our attention for a review. The first column in the table contains the contract number, the second column contains which review number the data is associated with (the first time it was reviewed, the second time, third, etc.). I need to make a subtable that contains the records from the last review of each contract. The problem is that, of course, each contract can and will have different numbers of reviews than most other contracts. I know how to use the max funtion to return the largest review number of the table, but this doesn't help because not all contracts have that many reviews. Is there any way I can accomplish this using a single SQL statement?
I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:
WHERE Email like "%Char(13)%" WHERE charindex(Char(13), @Email) > 0 WHERE patindex(@NewLine, @Email) > 0
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date,
CASE WHEN msdb..backupset.type = 'D' THEN 'Database'
I want to export a database from localhost anh import it to hosting server. Which version of SQL database server I need to install to have the export feture? Also I would like to know which version of SQL database server can be installed on Windows XP and Vista? I have nightmare to restore my database to the godaddy hosting envirenment because the host server does not allow to restore the database that was backup from localhost. Please help me to solve this problem. Thank you,
I think I have a problem with the execution plan caching in context of prepared statements. Please comment and advise.
When caching a new execution plan SQL Server apparently takes into account the actual query parameters and the current situation of the SQL Server (open transactions, transaction locks, current workload and so on). That can cause the same prepared statement to be executed verry badly with other parameters.
I am having trouble with a production system where some queries more or less suddenly start running extremly bad. The reason is an execution plan which might be optimal for some cases but is in general verry bad. Forcing a recompile of execution plans either by updating statistics or running sp_recompile solves the problem for some time. But after an unpredictable time the bad execution plan is comming back. Probably the good execution plan might also be reinstalled after som time but I cannot wait for this to happen.
The factor between good and bad execution plan is about 160 and increasing (30ms vs. 5000ms).
I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed to be modified to be called fully qualified (e.g. exec dbo.sp_###instead of exec sp_###), but I don't think that those will increase theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1
I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?
(id_text int ,text_name varchar(10) ,id_author int)
insert into test (id_text, text_name, id_author) values (205, 'name1', 123) insert into test (id_text, text_name, id_author) values (205, 'name2', 124) insert into test (id_text, text_name, id_author) values (205, 'name2', 125) insert into test (id_text, text_name, id_author) values (206, 'name2', 124) insert into test (id_text, text_name, id_author) values (207, 'name2', 122) insert into test (id_text, text_name, id_author) values (207, 'name2', 128)
select id_text, id_author from test
and i need to get out only id_text with the first author. any solutions for sql server 2000?
I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.
I tried the following expression: =iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")
but this only works if the first day is the maximum value.
I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not Row1 Date1,falseRow2 Date2,trueRow3 Date3,falseRow4 Date4,false so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow
I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details : SELECT * FROM Members where surname = (textbox1) Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way. I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text. Is this possible? Thanks in advance Mike
I have a table TEST. I want to find the triggers which use this table test. For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST, I want to find this particular trigger. Any ideas? I am using SQL server 2000 thank you
hai I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table structure. Thanks in advance satya
I have a customer database with the following structure:
Dept ID (int) Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.
Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.
How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)
Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer.. I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.
EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.
I have a column of data of type varchar. it contains a mix of text id's and numeric id's. all i want is the highest purely numeric value The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.
I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.
The script I have written fails with overflow error. I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...
Code:
Dim str_SQL_Asset_1 daysBack = 1 tblFound = FALSE Dim xbl_Holdings_date Dim xMonth Dim xDay xbl_Holdings_date = date() If Len(Month(xbl_Holdings_date))=1 Then xMonth = "0" & Month(xbl_Holdings_date) Else xMonth = Month(xbl_Holdings_date) End If If Len(Day(xbl_Holdings_date))=1 Then xDay = "0" & Day(xbl_Holdings_date) Else xDay = Day(xbl_Holdings_date) End If
IF MM_checkCmd.EOF Then xbl_Holdings_date = (xbl_Holdings_date - daysBack) daysBack = daysBack + 1 tblFound = FALSE ELSE tblFound = TRUE MM_checkCmd.CLOSE() SET MM_checkCmd = Nothing END IF loop
The goal of this snippet is simply to identify and set the value for the existing table name. An example table name would be "xbl_Holdings_2006_12_10".
I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.
I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)
thank you
SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups FROM TERMINATION GROUP BY [TM#] HAVING Count([TM#])>1; GO
