Seeking Help With A Query

Seeking For The Fast Query
Hello, everyone:

There is a big table with several million records. I am developing a query that retrieve the first rowset that meets WHERE condition. Any suggestions for the fast query? Thanks a lot.

ZYT

View Replies !   View Related

---

Seeking An SQL Funtion ~ Do You Know IT
Hi All,

Please can you help.


i a looking for a funtion that will be able to view all the child (many) records in a one to many relation ship and make a list of the parents names....is there such a function???

Example

In the one to many contacts db below, if this mystery function ran for "bill" it would say his "parents records" are Gary and Mac


Parent Table~ Child Table

Gary Bill
Tom
Harry

Mac Bill
Jef
Tod

Charles Les
Helen



any help here would be much apprecaited


kind regards

Tony UK

View Replies !   View Related

---

Seeking Answer
1)Which statement shows the maximum salary paid in each job category of each department?_______
A. select dept_id, job_cat,max(salary) from employees where salary > max(salary);
B. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat;
C. select dept_id, job_cat,max(salary) from employees;
D. select dept_id, job_cat,max(salary) from employees group by dept_id;
E. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat,salary;

2)description of the students table:
sid_id number
start_date date
end_date date
which two function are valid on the start_date column?_________¡£
A. sum(start_date)
B. avg(start_date)
C. count(start_date)
D. avg(start_date,end_date)
E. min(start_date)
F. maximum(start_date)

View Replies !   View Related

---

DTS Packages- Seeking Confirmation
Hello all,
I have 2 primary key fields the ssn and refnum... if the data in the file is duplicated it will not import to my table rights even though i am using DTS to do my import, correct? or do I need to add an extra validator in there?

View Replies !   View Related

---

Seeking BEST Method For SQL To MSAccess
I would like to pull some data from a SQLServer database, and save it into an Access MDB file (which can be empty to start). I would then zip up the MDB and download it to the user.

I am seeking advice on the most "elegant" or "efficient" way to do this. Here are some ideas I have been considering:

1) Should I start with an empty template MDB and file-copy it before I populate it? Or is there a neat way in ASP.NET to allocate a brand new MDB outright?

2) I could read the SQLServer data into a Dataset object. I could then open a connection to the MDB, create a table object, defining all the columns, etc., and then I could write the data to the new table object. BUT ... I have a hunch there is a nifty ADO.NET way to save the data already in the Dataset object right into the MDB (creating the table and columns as a matter of course) ... all with an instruction or two (or three). Any ideas?

Thanks in advance!

View Replies !   View Related

---

Attach Db ( Desperate Seeking For Help )
A database with 1 mdf and 2 ldf has been detached from SQL Server 7.0 . Then removed the log files ( they are gone , unable to recover ) and there's no backup at all . Now I want to attach the database with the same mdf , but got error msg - 'Device activation error'. It seems like it's looking for one of the log files.

Is there any way to recover the db ?

I guess NOT , isn't it ?

I don't understand why it doesn't work with sp_attach_single_file_db and sp_attach_db . I actually tested it with a dummy database with 1 log file , and it worked - a new log file was recreated. Thus I performed in production server. Don't understand why it doesn't work.


thanks in advance.

View Replies !   View Related

---

Novice Seeking Advice With SQL 2000
Hello,
 I am asking a question i have seen many threads on, but I am looking for an idiot's guide on how to convert my SQL 2005 database to SQL 2000 so i can get it to run on my web hosting server. I'm very new to asp.net , but have ahd years of experience in normal HTML and a year or two in the old ASP.
 I was advised to learn ASP.Net 2.0 and have found it nothing but brilliant. The intergration with SQL 2005 made it a lot quicker to link up a database than using Access. Unfortunatly my hosting company is a little behind and still using SQL 2000. There isn't mch databse intergration (a few aplication forms) so I dont mind re-writing the whole database but I dont know how to set Visual web developer up with a SQL 2000 Database. I have also read from various other forums that you can convert a databse to 2000 by doing something with the scripts, but the explaination is too complicated for me to follow.
 Is there anyone out there who wouldn't mind going over some old ground and explain this all in simple terms? Im using 'SQL Server Managment Studio Express' (although i dont know how to use it) and 'Microsoft visual web developer 2005 express edition' .
 Thanks for reading this
 
Simon

View Replies !   View Related

---

Seeking Walkthru -- Set Up Secure User Rather Than Use Sa
Could someone point me to a walkthru or FAQ that lists the steps involved in creating a user (other than SA) on SQL Server. SA is used in most coding examples but I know I don't want to use it in production.

- seeking info for both a develpment setup and a production setup

I'd like to start out with a user configuration that will move easily to production.

View Replies !   View Related

---

Seeking SQL Server And IIS/E-Commerce Engineers
SQL Server RAPID RESPONSE ENGINEER (NYC area - relocation package available) - Seeking SQL and IIS/E-Commerce engineers. Primary responsibilities include: Providing technical and creative solutions for service delivery by visiting customer problems sites, while ensuring customer satisfaction with Microsoft Networking, BackOffice and Desktop software. The successful candidate will be capable of performing on-site support of on-going support activities including but not limited to Premier Support, supportability content development, and escalation team support. The Rapid Response Engineer acts as the customer-visible point of escalation and resolution for Premier Support and provides advanced technical support to corporate customers and end users for complex products, including highly escalated issues. Provides support in-person at the customer's place of business, as well as via telephone, written correspondence, and electronic media. Exceptional customer service, communication, problem solving, and technical writing skills are required. Must have sufficient technical depth to communicate with development and other internal organizations at a peer level. Must have the ability to work with minimal supervision. Must have the ability to work independently and as part of a team. Recommended qualifications include: Superior problem solving and troubleshooting skills at a Systems Engineer level; Demonstrated aptitude for providing exceptional customer service; Ability to grow the technical expertise of peers via training development and delivery, mentoring, and content development; Ability to apply technology to improve existing products and systems; Ability to actively participate in team problem solving by proposing and implementing solutions; Ability to adapt quickly to changing circumstances, direction, and strategy; Demonstrate strategic thinking with value-add contributions. Technical Requirements: Solid understanding of standard computer and networking concepts, and Internet fundamentals, is required. A superior knowledge and demonstrated technical proficiency in at LEAST one of: Windows 95/98 Windows NT/Windows 2000 Exchange SQL IIS/ASP/Proxy Desirable Technical expertise: Strong technical depth in at least two of the above. Technical expertise in other relevant MS applications such as SMS, SNA, other MS Internet Technologies, etc. Debugging skills in Windows 95/98/NT, Exchange, and SQL Programming skills in one or more of the following: SQL, VB, VBA, C++, VJ++, VBS, Jscript, HTML/DHTML Education: College degree, preferably in Computer Science, is required. MCSE. MCDBA, or MCSD strongly preferred. Will consider related field (or equivalent) experience. Please email resume to: a-tomj@microsoft.com

View Replies !   View Related

---

Seeking SQL2000 Instance Info
I am looking for information (whitepaper, developer notes, heck even good ole hearsay) about the assumed performance hit that a box takes when running multiple instances of SQL Server 2000. In particular, what is the additional overhead and what is the performance difference between a one instance machine and a two instance machine or a two instance machine and two one instance machines? Certainly, the two instance machine will share memory, etc., but I'm looking for some practical "meat" to help us make some design decisions.

My digging has turned up no information, and the box where I have SQL 2000 installed is a turtle that is not production ready.

Any ideas?

View Replies !   View Related

---

Seeking: Alternatives For Initiating SSIS
Hello all,  I'm new to SSIS and this forum, and this is my first post.
 
We're migrating a 2000 DTS ETL process to 2005 SSIS.  We really like the enhanced functionality of SSIS thus far.
 
One problem we have with our 2000 process is that runs at 1:00am each morning. The scheduling is done via a distributed scheduling tool called Maestro. Our process pulls data from a mainframe-based DB2 OLTP and reformats it into SQL Server reporting tables.  We have nightly mainframe batch processing that updates the DB2 tables, and we need those updates on a nightly basis.
 
The mainframe batch process starts at 8:00pm each evening.  It finishes normally by 1:00am 90% of the time, but it is 20+ years old, and has its share of problems, especially during month-end.  The problems can't be resolved until the next business day in some cases.
 
We'd like to elegantly connect the two processes somehow so the SSIS ETL process kicks off when the mainframe batch process finishes.  I intentionally didn't use the word 'trigger' up until this point.
 
It would not be a problem to modify the mainframe batch process to insert or update a DB2 table that SSIS has access to, but I don't think we can get the mainframe batch process to update SQL Server 2005 tables...?
 
Any advice would be greatly appreciated--TIA!
 
John

View Replies !   View Related

---

Replication Scenario - Seeking Suggestion
I have two sites. Site A and Site B

Each site has two databases

Site A

     Db1

     Db2

Site B

    Db1

    Db2

Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.

 

I started Site A as pubisher and distributor and Site A and Site B both as subscriber.

 

Site B is in a different geographical area (state).

 

----------

Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.

 

-------

Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.

 

-------

P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.

 

View Replies !   View Related

---

Upgrading To SQL 2005 From 2000 - Seeking Advice
Hello everyone, I am upgrading from SQL Server 2000 to SQL Server 2005. Any caveats? Can I just detach the db's and attach them into 2005. Or is there any conversion I should run or import first?

Thanks,

View Replies !   View Related

---

Seeking Testing Volunteers W2K MTS/DTC To VMS DECdtm Distributed 2PC Transactions
Hi,I am seeking the help of volunteers to test some software that I'vedeveloped which facilitates distributed two-phase commit transactions,encompassing any resource manager (e.g. SQL/Server or Oracle) controlled byMicrosoft's Distributed Transaction Coordinator in a Windows2000environment, with any resource manager under the control of DECdtm (e.g. Rdb(or Oracle via the XA Veneer)) in a VMS environment.[Yes, at some stage, I hope to sell this software and make money out of it,so unless you have a large philanthropic streak or are simply a techie wholikes to stay on top of Windows<->VMS connectivity issues, then you may wishto look away now. But if you do choose to participate, then rest assuredthat I have no interest in your personal or company details. (Just yourwork-rate :-)]What differentiates my Transaction Manager software from existingTransaction Monitor packages that are already in the marketplace (and whyyou should be interested) is that it is based on the Transaction InternetProtocol TIP standard. (RFC 2372) For those of you who don't know, thebeauty of TIP's "Two-Pipe" strategy is it's application-pipe (or middleware)neutrality. Whereas most XA implementations mandate homogenous TransactionMonitor deployments (such as Tuxedo everywhere, Encina everywhere, MQSerieseverywhere, ACMSxp everywhere and so on . . .), hotTIP from TIER3 Softwaregives you complete freedom to choose the middleware product(s) that bestsuite your particular application and heterogeneous network needs.Would you like to talk to VMS with TIER3 Sockets, COM or DCE/RPC? BEAMessageQ, IBM MQSeries or HTML? The choice is yours and yours alone. Butonce you realize that you need to encase your critical transactions withinthe ACID properties of a true Heterogeneous Two-Phase Commit then you willcome to the conclusion that you need a Transaction Manager that looks a lotlike this.Another drawback of traditional "One-Pipe" strategies is that they precludethe run-time determination of transaction participants. (Functionalitywhich may be advantageous in a wide-area or Internet based application.)Anyway, this is what I have: -On the Windows side, you need absolutely *NO* additional software! I'llreply to this note with a brief description of the COM+ and DTC functionsthat you would need to invoke in order to successfully push a MTS/DTCtransaction to VMS. NB: These are standard Windows APIs that are fullydocumented on MSDN.On the VMS side, I have a VMSINSTAL saveset that (all zipped up) is some150KB that I'm happy to e-mail to you along similar lines to the VMShobbyists (non-commercial use) license. I'll reply to this note with anInternet Daemon (INETd) example of code that uses my software to cedetransactional control, over an SQL insert into a Rdb database, to MTS/DTC.It's under 500 lines long and contains all of the DCL, 3GL, SQL required toproduce a working example of a TIP-2PC capable TCP/IP auxiliary server. Thisexample will insert a row into the MF_PERSONNEL.Employees table on the VMSside in co-operation with Windows2000 MTS/DTC client that is inserting a rowinto the NORTHWIND.Employee table. Commit them all or roll them all back.So, in summary, If you'd like to volunteer to put hotTIP through it's pacesthen simply reply to this mail.Regards Richard MaherPS. The following are a few functionality restrictions with the currentversion of my software that may effect your decision to participate: -1) Transaction has to be started/mastered/coordinated by W2K MTS/DTC2) Transactions cannot be PULLed from VMS and must be PUSHed from W2K3) No cluster-wide recovery.(If a txn falls over after being prepared then you have to wait for thatspecific node to become contactable again even though that lovely RDMrecovery job is sitting on another node protecting the database until myhotTIP TM tells it to commit or abort.)4) There is currently no Alpha or Itanium version available. The Alpha portis currently in progress but, for the time being, you'll either need a VAXor a VAX emulator on your PC.

View Replies !   View Related

---

Seeking MS SQL Server Users To Beta Test New Db Acceleration S/w
Lightning Data Technologies, a Virginia based technology company is looking for SQL Server users in the mid-atlantic area to be beta testers for its "RangeLightning" and "MultiLightning" products.
These products are based on patent pending technology that dramatically improves the performance of SQL Server (and other) databases for both ad hoc and scheduled queries and batch processing applications. Our internal benchmarks have shown orders of magnitude performance improvements in applicable implementations.
Lightning's technology resides between the SQL Server database and user applications. We are looking for beta testers with databases of at least 1 TB and who are willing to provide us with regular feedback for a term of at least 30 days. Lightning Data Technologies will provide a free evaluation license and free on site and telephone support for qualified testers. This is not a business solicitation
To be considered, please contact us with the subject line "beta" at info@lightningdatatech.com or call 703-582-3552.

For more information on our technology please download our white paper and technical overview PowerPoint presentation at http://www.lightningdatatech.com

View Replies !   View Related

---

Seeking Info On 2-node Active Passive Sql Setup
In desperate need of implementing a solution where the customer has purchased a CMS to replace their corporate site and wants to use MSSQL as the DB server type. I have 3 servers allocated to me to complete this and I could use some advice on the best setup.
THey're running windows 2003 standard server along with sql 2000 standard server. The intended plan is for the 2 servers to become web servers with the last server becoming the sql server. The CMS will reside soley on the sql server and the content for the web site on the seb server(s).
What I need to know is if it's possible to set up an active/passive node to accommadate this using the items mentioned above? From what I've been reading sql 2000 enterprise does clustering but I'm hoping this version of sql can be used for something.
Any responses are appreciated.

View Replies !   View Related

---

Seeking SQLServer 7.0 Courses/training Classes In Atlanta
Hi friends,
Just looking out to find out good training centers typically 5-day SQLServer courses offered in Atlanta, GA .

If you know any good courses that you come acroos, or tell me how to find out the information, I would appreciate a lot. My company is after me to get more expert training ( Been using 7.0 for 5 months).

Thanks for your help.
Shabu

View Replies !   View Related

---

Seeking Advice On Converting Crystal Reports To SSRS
Hi. We've decided to convert our Crystal Reports to SSRS 2005. We know (thanks to this forum) there are companies that will convert the reports at a cost; however, we'd like to undertake this ourselves. Are there resources you can point us to that might be specific for Crystal Reports users coming over to SSRS, especially for newbies? Thank you.

View Replies !   View Related

---

Horizontal Variety Seeking Association Rule Problem
I read somewhere that market basket analysis finds rules with substitutes as likely as rules with complements due to a consumer behavior called "horizontal variety seeking". This is when customers buy more than one product in the same category even though they are subsitutes. For example, when people go to the grocery store and buy soda, they buy coke and sprite at the same time even though they are substitutes of each other. I was wondering if anyone has experience with this anomaly and how they solved it. I found a time series model called the vector autoregressive model which is used to find the elasticity of prices over a time period. Does anyone have experience working with the VAR model? I am having trouble figuring out what some of the variables in the model are.

Below is the paper

http://www.feb.ugent.be/fac/research/WP/Papers/wp_04_262.pdf#search='VAR%20model%20market%20basket%20analysis'

 

View Replies !   View Related

---

Insert A Jpeg Into A Column Sql Server 2000 - Desperately Seeking Help
I'm a complete newbie. Need to insert a Company logo into a databasecolumn to use later on in a check printing application. Read how toinsert the pointer instead of the object into the column. Below iswhat I did:SET QUOTED_IDENTIFIER OFFGOINSERT INTO BankInfo(CoLogo) VALUES(0xFFFFFFFF)***Then I did this****DECLARE @Pointer_Value varbinary(16)Select @Pointer_Value = TEXTPTR(CoLogo)FROM BankInfoWHERE CMCo = '91'WRITETEXT BankInfo.CoLogo @Pointer_Value"\192.31.82.77DataCheckImagesWyattLogo.jpg"****This was straight out of a book and it seemed to work it gave me amessage that it was successful and when I view the data in the columnI can see the pointer0x453A5C436865636B496D616765735C57796174744C6F676F 2E6A7067*****But when I try to use the column in either Crytal Report or an AccessReport the Bank Logo does not show up. I also placed the logo on my Cdrive and tried pointing to it there with "C:WyattLogo.jpg" with nosuccess.It can't be this difficult to get a Company logo into a column. Idesperately need assistance. Remember I am the ultimate newbie. Ilooked at my first sql database last week. Thanks in advance for anyhelp, it is appreciated.

View Replies !   View Related

---

Seeking Correct Syntax To Pass A NULL Value For A Datetime Field From An Asp Page To SQL
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!

View Replies !   View Related

---

SEEKING BASIC INFO ON SQLAgent - TSQL Jobstep Type Program_name(s)
 

I'm trying to ascertain how I can find out more about a particular job.
 
The information I have from a script I have to identify deadlock root causes gave me back this information:
spid 86 is blocking spid 51... spid 86 info:  SQLAgent - TSQL JobStep (Job 0xBAD836E3D331B44BA4CCAC400D244B17 : Step 1)
 
Well, that's good to know, but I would like to be able to identify the particular job that 'owns' TSQL JobStep (Job 0xBAD836E3D331B44BA4CCAC400D244B17 : Step 1).
 
I've read the BOL on the sysjob type tables; and, while they tell me about the columns in the tables and what they are, they tell me absolutely nothing about how one goes about figureing out what I want to know.
 
I suspect one problem I have is that '0xBAD836E3D331B44BA4CCAC400D244B17' needs converting to something else and I have no idea how to go about doing this.  I was never that good at converting hex (I assume that is what this is) when I was doing it rather often, which is years ago, so I really have no idea how to start.
 
Can anyone throw me a lifeline here?
Tia
randyvol

View Replies !   View Related

---

Seeking Both An Age From Birthdate And Nearest Birthdate Qry
I'm having a problem finding the nearest age. If the last bday is closer then the current age, if the next bday is closer then current age + 1 year. I'm familar with dateadd, datediff and datepart. Here's some psudo code...

declare @bday datetime ,@age int
set @bday = '01/26/1956'
set @thisdate = getdate()
set @age = case
when next @bday is closer than the past birthdate then age(@bday) + 1yr
when next @bday is farther than past @bday then age(@bday)
end

I know there is not an age() function, but that's part of what I'm looking for. I'm also stumped on getting the nearest age. What I had only worked for part of the year, prior to around June 15th, (183 days into the year)

TIA

JeffP...

View Replies !   View Related

---

Seeking Advice: SQL Server On Win 2008 Virtual Server Or Just Win 2008?
 

Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
 
Any pointers to documentation or any advice?
 
Thanks,
 
A Brown

View Replies !   View Related

---

Report Wizard Doesn't Take A Simple Query In The Query String - Query Works In Query Builder When I Supply The Parameters
The following query in the query string:

 

execute p_rpt_cli_v_index_reg_adj_exp_by_bkt2 @as_of_date='06/06/2007', @client_type=3, @index_as_of_date='05/31/2007'

 

produces following error:

 

There is an error in the query, invalid object name '#CLI_1', Invalid object name '#index'.

 

When I open up the query Builder, and provide the same query and run, it asks for those 3 parameters values and after I provide those parameters, the query runs, but, clicking on Ok, produces same error as above in the 'Microsoft Report Designer' information window.

 

The above query works in the Query Analyzer fine. What's so different in the Reporting Services env?

View Replies !   View Related

---

Query Runs Fine In Query Analyser But Not The Query Debugger
I'm running a query, actually its an insert that works when using the TSQL below.

However when I try to use the debugger to step through and using the exact same values as those below I get the following error:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Its Killing me because everything else works, but this. Can somebody help.


DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,

@BeenRead NVARCHAR(10),-- = NULL

@FK_UserIDList NVARCHAR(4000)-- = NULL


--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'

SET @FK_UserIDList = '1,2,3'


--AS

--SET NOCOUNT ON


SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

SET @Date = GETDATE()

-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)

SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,

@Job_Date,
@Start,
@Finish

-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY


-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList

-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT

COMMIT TRANSACTION

--------------------------------------------------
GO

View Replies !   View Related

---

Query Diff Results From Ent Manager Query And Query Analizer
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
 
SELECT     DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM         testsupplierSCNCR 
I am getting the result of 01/01/2007
 
but in query analizer I get the result of
 
12/31/2006
 
Why the different dates
 

View Replies !   View Related

---

[Query] - Query Designer Encountered A Query Error:Unspecified Error
Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View Replies !   View Related

---

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.
SQL Server 2005 9.0.3161 on Win 2k3 R2

 

I receive the following error:

 

"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)

VALUES (@journal_id, @acct_id, @acct_recv_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).

View Replies !   View Related

---

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View Replies !   View Related

---

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager
I'm trying to find the command to open up an odbc conection inside sql2005 express.  I only have ues of an odbc connector, we're conection to remedy.  We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching.  I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok.  Also I need a really good advanced book on sql2005.  The type of book that would have my odbc answer.  I've spent all morning trying to find this information and was unable to.

 

Thanks in advance

 

Daniel Buchanan.

 

If this was the wrong forum to post this on, please move this question to the correct one.  I need this answer soon.

View Replies !   View Related

---

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable?  It looks like coding WHERE calcd name in (@variable) violates SQL syntax.  My select looked like
 
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)  

View Replies !   View Related

---

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?
 

Hello everybody,

 

I'm developing a report using the following structure :

 

declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)

 

set @sql = 'select ....'

 

If <conditional1>

begin

        set @where = 'some where'

end

 

If <conditional2>

begin

        set @where = 'some where'

end

 

set @sql = @sql + @where

 

exec(@sql)

 

I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.

 

I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?

 

Thanks,

 

Maurício

View Replies !   View Related

---

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
 

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following:  EXEC dbo.SalesByCategory @Seafood.  I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood".  I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above).  Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
 
Thanks in advance,
Scott Chang 
    

View Replies !   View Related

---

Easy SQL Question. How To Display Query Results In Query Analyzer
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Sniier */SELECT TOP 1       @SniierName      = Sniiers.SniierName,        @SniierAlias    = Sniiers.SniierAlias,        @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View Replies !   View Related

---

SP To Perform Query Based On Multiple Rows From Another Query's Result Set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View Replies !   View Related

---

A Query Runs Fast In Query Analuser But Slow In APplication
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay

View Replies !   View Related

---

Stored Procedure Query Optimization - Query TimeOut Error
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

RETURN 1
END



GO

View Replies !   View Related

---

Msg 225 -Cannot Run Query--referenced Object (name NAME NOT RECOVERABLE) Dropped During Query Optimi
Does anyone have any info on the following error message:

Cannot run query--referenced object (name NAME NOT RECOVERABLE) dropped during query optimization

We get the error while running some scripts on the server console. We are running SQL 6.5sp5a. We checked technet and found only references to sp_cursoropen and problems while altering db during a load - neither apply to us.

We'd be grateful for any info anyone might have. Many thanks in advance...

View Replies !   View Related

---

Query Designer Encountered A Query Error Via Enterprise Manager
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.

View Replies !   View Related

---

SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan
Hi Everyone:

*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error

"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I would have searched the forums myself, but at this moment in time, search is broken :(

If anyone has run into this error before, what conditions would exist that this could happen?  That is, if I can sniff this out with suggestions from the community, I would be happy to do so. 

It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine.  It's not as if subqueries are oddities... I've used them when appropriate.

fwiw - Not a newbie t-sql guy.  ISV working almost daily with t-sql since MS SQL 2000.  I have never seen this message before...at least I don't recall ever seeing it.

Thanks in advance for other suggested examination paths.

 

View Replies !   View Related

---

The Merge Process Failed To Execute A Query Because The Query Timed Out
Hi there. I have occasional sincronization error.-XSUBSYSTEM Merge
-XSERVER MYSERVER
-XCMDLINE 0
-XCancelEventHandle 000006E0
2006-12-22 14:55:00.833 Connecting to Subscriber 'Subscriber01'
2006-12-22 14:55:00.895 Connecting to Distributor 'Publisher01'
2006-12-22 14:55:02.974 Initializing
2006-12-22 14:55:03.083 Connecting to Publisher 'Publisher01'
2006-12-22 14:55:06.005 Retrieving publication information
2006-12-22 14:55:06.130 Retrieving subscription information.
2006-12-22 15:00:07.222 The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-12-22 15:00:07.456 Error converting data type nvarchar to numeric.
2006-12-22 15:00:07.800 Category:SQLSERVER
Source: Subscriber01
Number: 8114
Message: Error converting data type nvarchar to numeric.

After manual syncronization it goes under control.

Could anybody explain the relationship between converting error and timed out query?

If this is poor connection's problem how can I increase the query timeout for the process?

View Replies !   View Related

---

How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?
I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string.  But, I don't know how to get a value returned from one sql statement into a second.  Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4  ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID  (Select OfficeID from tMatter where Mid=4)   ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6   (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement? 
 

View Replies !   View Related

---

Prevent Query-Analizer From Caching The Query Execution?
Hi all I'm using Sql server 2000 and sometimes i need to run my Queries in Query analizer before using them in my application just to test them...BUT most of the time when i run a query in query-analizer for second time ,query analizer populates the result (records) more quicker then the first time. Apparently it caches the query !!! i don't know but for some reasons i dont't want this , so how can i prevent Query-analizer from doing so?
Thanks in advance. Regards.

View Replies !   View Related

---

Update SQL 2000 Query (converting An Old Access 2k Query To SQL)
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

View Replies !   View Related

---

Re-display Result Set Without Re-running Query In Query Analyzer?
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View Replies !   View Related

---

Query Call By App Runs Long, Same Query In QA Doesn't.
Hi Guys,

I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.

When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.

I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.

Any idea what might be going on here?

View Replies !   View Related

---

Query Analyzer Runs The Query Instantly But ADO Can Take Forever???
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.

Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"

Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)

View Replies !   View Related

---

How To Create A Make-Table Query With A Union Query
I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View Replies !   View Related

---