Where MasterKey in (select * from ZT_AuthDetail d, ZT_AuthMaster m where (ReplyTime Between '2004/11/22' and '2004/12/31') And (m.CustId = '33196700') And (m.Prikey = '1840'))
when I run this query , the error message shows somethins wrong with "Sub Query from Exists....."
if I run (select * from ZT_AuthDetail d, ZT_AuthMaster m where (ReplyTime Between '2004/11/22' and '2004/12/31') And (m.CustId = '33196700') And (m.Prikey = '1840')) alone
the result is fine and Ok.. but with select ZT_AuthDetail where MasteKey in... I got error message
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
When I am trying to read rows from any table from any database in the offsite production server with Pc Anywhere ( all other servers can be acess fine with pc anywhere
I get this error
An unexpected error happned during this operation {query} query designer error encountered a query error unexpected error
I checked the version of Enterprise manager Help About microsoft managment console 1.1version 4.0 bulid 1381 service pack 4
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
I have a web application developed in VS.NET 2005 [using C# as code behind]; and it uses SQL Server 2000 Enterprise edition as backend. The application runs fine, though it gives an error on IRREGULAR intervals on SQL data requests. Error Details: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
My problem is this: 1) Why does it show an error of SQL 2005, while I use SQL 2000. SQL 2005 is not even installed on the server, though VS.NET 2005 is installed. 2) The error comes only at irregular intervals. Users are able to login properly otherwise. 3) Application starts working again if we do either of the following: (a) Restart IIS (b) Restart application pool (c) Restart server. 4) Named pipes and TCP/IP are added to the "Network Configuration" of the SQL server 2000. 5) Error does not come on any specific page; or any specific code; or at any specific time. 6) We do not have any background service or any other activity happening on the server; and the server hosts only this application, within a single virtual root.
Any thoughts on why is this happening, and how to resolve this?
I am applying hourly differential backup to the backup server from production with the following command. This command makes the database on standby server into read only mode.
RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup' WITH MOVE 'arsystem' TO 'd:ardataarsystem.mdf' , MOVE 'arsystem_log' TO 'D:ARLOGARsystem' , STANDBY = 'E:SQL backup from productionSQL daily diff back up'
Now I want to run a command which will put the database in write mode. I have created a job which would make the datbase Write mode. This job runs successfully sometimes and fails sometimes. I need to ensure that the job always succeeds. When it fails, how do I troubleshoot and what is the possible fix?
Thanks in advance.
The error message is
Cannot apply the backup on device 'E:SQL backup from productionSQL daily diff back up' to database 'ARSYSTEM'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The steps for the job are as follows with the failing step highlighted in bold.
copy /y "\172.31.9.12Remedy BackupackupSQL backupsql_full_backup" "E:SQL backup from productionsql_full_backup"
copy /y "\172.31.9.12Remedy BackupackupSQL backupSQL daily diff back up" "E:SQL backup from productionSQL daily diff back up"
xp_cmdshell 'net stop "bmc remedy action request system server"'
exec rp_kill_db_processes 'ARSYSTEM'
RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
NORECOVERY
Failing step
RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionSQL daily diff back up'
WITH
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
RECOVERY
xp_cmdshell 'del /f "E:SQL backup from productionsql_full_backup"'
xp_cmdshell 'del /f "E:SQL backup from productionsql daily diff back up"'
xp_cmdshell 'net start "bmc remedy action request system server"'
I have scheduled the following hourly diffential restore job too which never fails.
RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup' WITH MOVE 'arsystem' TO 'd:ardataarsystem.mdf' , MOVE 'arsystem_log' TO 'D:ARLOGARsystem' , STANDBY = 'E:SQL backup from productionSQL daily diff back up' EXEC MASTER..XP_CMDSHELL 'del /f "E:SQL backup from productionSQL daily diff back up"'
Here is what I have, but I'm not confident it is correct: CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
Our SQL Server 200 box is getting perflib errors when we get a decentamount of people using an application that I wrote, call queue system,web based. To accomplish a queue type system on a button push I wrotea query like this...BEGIN TRANSACTION;Select top 1...fields here...FROM table with (xlock,readpast)(2 joins)WHERE numerous where clausesORDER BY 2 order bys.Now our sql server starts timing out..then in the app log this showsup....Error: 1203, Severity: 20, State: 1Process ID 62 attempting to unlock unowned resource PAG: 6:1:126407.For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/events.asp.The reason I am doing xlock is to make a record not viewable to 2people if they click the button on the web form that runs the abovequery within the same minute, they would get different records....So to avoid this error which I assume is due to my xlock should Irethink my query?
We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.
UPDATE PERSONS
SET FLOORNR = dbo.FloorNR(OFFICELOCATION)
WHERE OFFICELOCATION IS NOT NULL
IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait
END
However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.
How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.
I'm trying to finish a DTS Package by controlling the error flow with @@ERROR. Yet, there's one error which I can't seem to capture 'Syntax error converting the varchar value to a column of data type int'
If you try the code below, I can't seem to pull an @@ERROR ID from it.
IF OBJECT_ID('tempdb..##LogTemp') IS NOT NULL BEGIN DROP TABLE ##LogTemp END
CREATE TABLE ##LogTemp ( Error_ID INT ) INSERT INTO ##LogTemp ( Error_ID )
VALUES ( 'Test' )
PRINT @@ERROR
Query Analyzer prints this out:
Server: Msg 241, Level 16, State 1, Line 83 Syntax error converting datetime from character string.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in ** =================================== Sequence contains no elements ------------------------------ Program Location: Â Â at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke() Â Â at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error. We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go into production in a few days.
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1 @Franchise ObjectId , @dtmStart DATETIME , @dtmEnd DATETIME AS BEGIN
SET NOCOUNT ON
SELECT p.Product , c.Currency , c.Minor , a.ACDef , e.Event , t.Dec , count(1) "Count" , sum(Amount) "Total" FROM tb_Event t JOIN tb_Prod p ON ( t.ProdId = p.ProdId ) JOIN tb_ACDef a ON ( t.ACDefId = a.ACDefId ) JOIN tb_Curr c ON ( t.CurrId = c.CurrId ) JOIN tb_Event e ON ( t.EventId = e.EventId ) JOIN tb_Setl s ON ( s.BUId = t.BUId and s.SetlD = t.SetlD ) WHERE Fran = @Franchise AND t.CDate >= @dtmStart AND t.CDate <= @dtmEnd AND s.Status = 1 GROUP BY p.Product , c.Currency , c.Minor , a.ACDef , e.Event , t.Dec
I have a SProc that runs across many clients without any problems. Every now and then, though, I get the following error:
Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630).
All I am doing is populating Temp tables with some data and then joining them together to create a Global Temp table that is being BCP'd to a network share.
Has anyone come across this error in SQL Server 2005? I cannot find anything on Google or Microcsoft.
Hi, I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties Result Set : None
ConnectionType : OLEDB Connection : Connected to a Local Database (DB1) SQLSourceType : Direct Input SQL Statement : exec(?) IsQueryStorePro : False BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Has anyone come across error 8630 "Internal Query Processor Error: The query processor encountered an unexpected error during execution." with SQL Server 7 with SP4 installed?
There are microsoft articles on the error, but the errors are suposidly fixed in SQL Server 7 SP2 and SP3.
We are selecting from one view left outer joined with another, but no unions are involved.
This is not a problem in SQL Server 2000, but unfortunatlely Microsoft are supporting SQL Server 7 until 2005 and so we have to do the same...
I am new to SQl tranasactions and I'm currently trying to write an SP that will check the database sizes and print the error to the windows eventlogs so that MOM can monitor them. I do not wish to retain the information so I am using a temp table. which once this is written to the logs will be dropped, I also want it to loop though all the databases except for exemption list. SQL 2000 query (hope I'm not completely going in the wrong direction)
SET NOCOUNT ON
DECLARE @size int DECLARE @name AS sysname DECLARE @string varchar(1000) --DECLARE @query varchar(2000)
SET @Name = (SELECT TOP 1 name FROM master..sysdatabases WHERE -- exemption list NAME NOT IN ('master', 'tempdb', 'model', 'msdb') and NAME > @Name )
--SET @name = 'DBA'
SET @string = 'INSERT INTO ##temp (sum([size]) as [size])' + @name + '.dbo.sysfiles' --SET @string = 'select sum([size]) as [size] into ##temp from ' + @name + '.dbo.sysfiles' --(I REALISE THAT THIS ---------WAS TRYING TO CREATE THE TEMP TABLE FOR EACH DATABASE) EXEC (@string) SELECT (@string)
SELECT @size = DBsize from ##temp
-- once done all dbs then get out IF @Name IS NULL BREAK
END
DROP TABLE ##temp -- --Raise error inputs to Eventlog RAISERROR ('Database size %s is %i', 10, 2, @name, @size ) with log SELECT @size
I would welcome any help from someone on amending this query or indeed showing me a best practice way of doing this.Thank you inadvance to everyone that reply€™s :-)
Hi guys I have a program which imports data into sql server from csv files. This program uses the something like this
CREATE PROCEDURE MyImport ( @FileName AS VARCHAR(200) ) AS SET NOCOUNT ON DECLARE @SQL VARCHAR(4000) SET @SQL = ' SELECT * FROM OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:;'', ''SELECT * FROM ' + QUOTENAME(@FileName) + '' + ''') as t ' EXEC (@SQL)
when i excute this i get a permission error saying the account i specified in the web config doesn't have rights to run these type of scripts. I tried using the sa account which works. But its not recommended to setup applications to access the database using the sa account. i did some research and i found out that yon can actually enable that by change a registry key. But i cant find the MSDASQL datasource under ms sql server providers in the registry. Any other ways i can enable this?
I'm looking for an in depth book, article, faq, whatever, regarding the query optimizer...
I've read the books online pretty thoroughly and have been sql coding for a number of years. The system I work on relies heavily on real time access to data and the number crunching procedures we use are a critical part of the design. For the most part, sometimes through trial and error, I have been able to find ways to achieve the performance we need, but I'm often surprised by the methods that prove most effective.
For example, I have cases where I can only get the performance I'm looking for using table functions, and other cases where indexed temporary tables are the only way. I have statements that run fast as a select statement, but when converted to an update statement limp along, forcing me to resort to cursors, temp tables, or table hints with varying degrees of success.
I'm wondering if anyone has come across material that takes an in depth look at the various technologies available and how to tweek queries. I want to get away from hours of testing and hacking.
I have a procedure (used to create a report) and was used in sql 7.0 service pack 3.
Problem is that we are upgrading to SQL 2000 and this procedure now takes 1 minute and 30 seconds to execute vs. 10 seconds previously.
Everything is same between the sql 7 and sql 2000 server. i.e. database size, indexes, hardware etc.
I looked at the query execution plan and it seems to do a sort which is taking majority of the resources on sql 2000 even though there is no sort stmt issued in the procedure itself.
Any help would be appreciated?? I am more curious to find out why this is the case when all the variables are same between the two servers yet sql 2000 performance is much worse than sql 7.0. It should be the other way around!!
If I use and Execute SQL Task like this ... ------------------------------------ DECLARE @VAR SET @VAR = ?
EXEC sp_Test @VAR ------------------------------------ and then click parse query I get an error "Syntax error or Access violation"
However; if I use it this way ------------------------------------ EXEC sp_Test ? ------------------------------------ it works.
Now here is the odd thing. If I put in a DECLARE statement and a global parameter "?" I get the syntax error.
But .... If I use a disconnected edit and use DECLARE and "?" then task will run properly. So the problem seems to be with the query parser.
The problem is I don't have the time to edit everything in a disconnected manner. Is there a way or update that will allow me to use DECLARE and "?". I have tried many hotfixes but none have worked.
I am having an issue with a SQL query I wrote in SQL 2000. I am almost there but am lacking in one area, hoping a better programmer than me and a fresh set of eyes can pick off my mistake.
The goal is very straight forward, order and rank the following fields: R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference
The issue lies in my sub query in the AND / OR joins... This query will work if I sort and rank on two fields, but if I try three or more it does not work properly.
( SELECT COUNT(*) FROM tblWeeklyStandings_All R2 WHERE R2.AutoPoolID = R1.AutoPoolID AND R2.Week = R1.Week AND ( R2.PSWins > R1.PSWins OR R2.PSWins = R1.PSWins AND R2.PSPoints_Total > R1.PSPoints_Total OR R2.PSPoints_Total = R1.PSPoints_Total AND R2.PSTBDifference < R1.PSTBDifference ) ) + 1 AS Rank
FROM dbo.tblWeeklyStandings_All R1 INNER JOIN dbo.qryUsers_SDR ON R1.AutoPoolID = dbo.qryUsers_SDR.AutoPoolID AND R1.PoolID = dbo.qryUsers_SDR.PoolID WHERE (R1.PoolType = '2007' OR R1.PoolType = '2008') AND (dbo.qryUsers_SDR.OrderBy1 = '6') AND (dbo.qryUsers_SDR.PointSpread = 1)
ORDER BY R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference
At this point I am ready to buy SQL 2005 because I think this gets easier, hoping a 2000 wiz can help me correct my error, or missing "(", or something!
Here are the results: Rank Username Wins Points TB Difference 1. test 0618084 16 50 0 4. test 0618083 16 50 66 3. test 0618081 15 50 55 4. test 0618082 14 50 55 5. admin 2 0 0
As you can see the ranking is not correct. It should be the following: Rank Username Wins Points TB Difference 1. test 0618084 16 50 0 2. test 0618083 16 50 66 3. test 0618081 15 50 55 4. test 0618082 14 50 55 5. admin 2 0 0
Sometimes it works sometimes it does not, frustrating!
In different scenerios, I am ranking the following way when ties occur. My code sometimes does this correctly but as you can see above it fails in certain areas, like it forgets R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference....
Rank 1 Tied 1 Tied 3 4 5 Tied 5 Tied 5 Tied 8
etc...
The most frustrating thing is the sort order is correct, it is the rank sub query that fails under certain scenerious...
ISTR QA running in 7.0 would return print statements and messages as thequery ran (well, after a "GO" statement anyway). In 2000 it doesn't returnany messages until after the entire query batch has completed. In wanting tokeep an eye on progress on long batches, how can I make 2000 work more like7.0 in this respect?
I upgraded a SQL 6.5 NT 4.0 server to SQL 2000 sp1. I successfully upgraded the databases and everything appeared to be working properly. When I attempted to create a backup device the Enterprise Manager MMC seemed to freeze and would not respond. I closed EM and tried to reopen. I get an error box that states "Snap-in failed to initialize. Name: Microsoft SQL Enterprise Manager CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}". I have tried loading a generic MMC console and adding the EM snap-in, same error. I have reloaded the NT SP5. I have rebuilt the registry and tried reinstalling the SQL 2000 sp1. Nothing seems to work. Any ideas? Thanks in advance Janet
Hi all my problem is as follows : when i try to configure an ODBC DataAdapter using the configuration wizard
i use the query builder to select some fields from multiple tables , the displayed info depends in a user input , when i define the input parameter by writing(= ?) in the criteria column of the input field and continues , it displays an error
Error H4000 Error while executing the query , Error near ')' near character (214 for example).
so please if any one knows the solution to this error reply soon
Hi, I am using SQL SERVER 2000. I need a procedure/ method where i need to display the output value of a particular query. As I wrote the following procedure CREATE proc proc1as beginDeclare @str nvarchar(100)set @str = 'select top 5 * from CT_TM_EmployeeMaster'print @strend the output is giving "select top 5 * from CT_TM_EmployeeMaster" I need the output of the above query. What should i do?
Dear all,I have a database with 3 tables, one is named Enrollment, and the other Attendance. What I am trying to accomplish here is to get all the Student ID from the Enrollment table which matches a given class ID and insert all of them into the Attendance table for a given day.I did this with the SQL query below :- INSERT INTO Attendance (ClassID, Date, StudentID, Status) VALUES (1, '10-4-2005', (Select t.StudentID FROM Student t WHERE t.ClassID=1), 1)Trouble here is I get the error : Subqueries are not allowed in this context. Only scalar expressions are allowed.I understand waht is going on here, that it returns more than 1 result. So my question is, are there any other ways to accomplish what I am trying to do? Hope you all can help me with this.Thanks all in advance,-Victor-
I was wondering if someone could help me out. What would be the best way for me to go about determining the sale number from the following table:
applicantID calltime result 1 1/5/05 no sale 1 1/15/05 no sale 1 1/25/05 sale 1 1/26/05 no sale 12 1/5/05 no sale 12 1/15/05 no sale 12 1/16/05 no sale 12 1/25/05 sale 12 1/26/05 no sale
the resulting query would provide: applicantID saleattempt 1 3 12 4
etc. Ultimately, I am trying to figure out how many attempts needed to make a sale.
