i am trying to execute a query by joining two different tables..
one table is a local table and another one is a remote table..
The intresting thing is when i run the query from QUERY ANALYZER it works..
but if i execute the same from STORED PROCEDURE it gives me the following error.
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Hi All, Can someone please tell me what does this means. and How to solw this problem. This is my procedure that I created.
CREATE PROCEDURE stp_Test @TableName varchar(50), @Id int out AS DECLARE @String varchar(500) SELECT @String = 'SELECT * INTO #TempTable FROM ServerName.Database.dbo.' + @TableName EXEC sp_sqlexec @String SELECT @id = max(ID) FROM #TempTable GO
WHEN I run this Procedure.
EXEC stp_Test 'TableName', @Id out
I get this out put.
------------------------------------------------------- SELECT * INTO #TempTable FROM ServerName.Database.dbo.TableName (1 rows affected)
Server: Msg 7405, Level 16, State 1, Procedure stp_MaxNoReturn, Line 2 Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
What is the solution to this problem. Thanks in advance Aziz
Hi Guys,I have one stored procedure on SQL 6.5 and retrieving data from SQL2000. I have defined a linked server on SQL 6.5. ButI am getting following error:-"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics.Enable these options and then reissue your query."I dropped and re-create the stored procedure after adding the SETANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still gettingthis error. I have created this sp on ISQL/W not in Enterprise Manager.Can anyone please let me know, how to fix this problem.ThanksAdnan
DECLARE @a varchar(100) SET @a = @FailedRegionServerName + '.Ithalat.dbo.Product'
DECLARE @s varchar(100) SET @s = ' SELECT * FROM ' + @a EXEC ( @s )
When I execute it I get the error:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Then I put SET ANSI_WARNINGS ON SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work
Then I tried:
DECLARE @s varchar(300) SET @s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @a EXEC ( @s )
I still got the error.
WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...
I have a Stored Procedure I am trying to run that joins to a remote database. I am able to see everything in the QA just fine with this (courtesy of Anatha):
SELECT DISTINCT a.* FROM LOCATION a, LinkServer.MC_Card.webuser.LOCATION b WHERE a.location_number = b.location_number
But I am trying to run this query in Stored Procedure(notice the 4-part name callout to the LinkedServer tables) which returns the error message:
Error 7405: Heterogeneous queries require ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Here is the Stored Procedure: /****** Object: Stored Procedure dbo.spELRMCcardXtionByDate Script Date: 4/24/2001 11:51:27 AM ******/
CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime AS -- declare @dcid nvarchar(255) -- set @dcid = '1032' SELECT STORE.[Str#], STORE.[Dcid#], E.card_number, E.program_number , E.start_date, E.end_date, E.card_number, E.event_number , E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount , L.merchant_name FROM (STORE INNER JOIN LinkServer.MC_Card.webuser.EVENT E ON STORE.[DemoID#] = E.event_number) LEFT JOIN (LinkServer.MC_Card.webuser.LOCATION L RIGHT JOIN LinkServer.MC_Card.webuser.POS_TX P ON L.location_number = P.location_number) ON E.event_number = P.event_number WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate ORDER BY STORE.[Str#] -- and E.card_number IS NOT NULL GO
I am trying to create a stored procedure that updates a table on another server. It give the me the error about requiring ANSI_NULLS and WARNINGS being set. How can I set these if they are not already set? I tried setting them within the stored procedure, but does not appear to be working. Unless I am doing something wrong. I am trying to add SET ANSI_NULLS ON and doing the same thing for WARNINGS. Any thougts or suggestion on what to do? Thanks for the help
I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.
So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.
But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.
I have a SQL200 stored proc that gives me the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query." when I try to execute it through the query analyzer.
I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF SET NOCOUNT OFF SET ANSI_NULLS OFF or else my vb6 recordset crashes.
When I created the sproc, I did what every one was telling me to do in the forums by putting in the
SET ANSI_WARNINGS ON Go SET NOCOUNT ON GO SET ANSI_NULLS ON GO
CREATE Procedure usp_SprocName
AS SET ANSI_WARNINGS OFF SET NOCOUNT OFF SET ANSI_NULLS OFF
I am using SQL server 7.0 and i have created oracle8i linked server(Using MSDAORA as provider) in it. When i run distributed queries between SQl server and Oracle server it works fine.But when i try to run distributed transaction between two servers ( BEGIN DISTRIBUTED TRANSACTION..)it reports an error saying Distributed transactions are not supported by MSDAORA.
My question is; is it possible to run distributed transaction between SQL server and oracle server (where oracle server is a linked server in my SQL server)?
Actually i want to run this transaction in DTS package which updates and transfers data amongst various servers.
My company is working on a bond derivative portfolio analysis tool andwe're facing a problem that I did not see adequately addressed anywhere in literature. I really did RTFM. I'm very experienced inrelational modelling (10+ years) so this is not a case of notunderstanding the principles. Here is the problem stripped ofirrelevant context. The problem below is simplified for the sake of theexample so don't sweat the details.THE PROBLEM1. There are many types of bonds, each type has a different set ofattributes, different attribute names, different attribute datatypes.For example, bond A has two variables: a yearly interest rate anddate of issue, B has five variables: an interest rate and 4 specificdates on which various portions of principal need to be paid, bond Chas a set of 4 variables: interest rate in period 1, interest rate inperiod 2, the date on which the bond can be put back to the issuer,and two dates on which the bond can be called by the issue. And so on.So, on the first attempt I could represent each bond type as its owntable. For example,create table bond_type_a (rate INTEGER, issue_date DATE)create table bond_type_b (rate INTEGER, principle_date1 DATE,principle_date2 DATE, principle_date3 DATE, principle_date4 DATE)create table bond_type_c (rate1 INTEGER, rate2 INTEGER, put_date DATE,call_date DATE)This is the nice relational approach but it does not work because:2. There are many thousands of bond types thus we would have to havemany thousands of tables which is bad.3. The client needs to be able construct the bond types on the flythrough the UI and add it to the system. Obviously, it would be bad ifeach new type of bond created in the UI resulted in a new table.4. When a user loads the bond portfolio it needs to be very fast. Inthe table per type approach if a user has a 100 different types if bondin the portfolio you would have to do 100 joins. This is a heavilymulti user environment so it's a non-starter. It's impossibly slow.THE SOLUTIONSSo now that we ditched the table per bond type approach we can considerthe followiing solutions (unpleasant from the relational point ofview):1. Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, value VARCHAR(255))Comment: The client does not like this approach because they want torun various kinds of reports and thus they doe not want the values tobe stored as VARCHAR. They want the DB to enforce the datatype.2. Typed Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, int_val INTEGER, string_val VARCHAR(255), date_val DATE_Comment: The client does not like this because the table is sparse.Every row has two empty fields.3. Link table with table per data type.create table bonds (bond_id INTEGER)create table bond_int_data (bond_id INTEGER REFERENCES bonds(bond_id),value INTEGER)create table bond_string_data (bond_id INTEGER REFERENCESbonds(bond_id), value VARCHAR(255))create table bond_date_data (bond_id INTEGER REFERENCES bonds(bond_id),value DATE)Comment: This meets most of the requirements but it just looks ugly.4. Dynamic Mappingcreate table (bond_id INTEGER, int_val1 INTEGER, int_val2 INTEGER,date_val1 DATE, date_val2 DATE, string_val1 VARCHAR(255), string_val2VARCHAR(255))Then you have to add some dynamic mapping in your code which willprovide bond specific mapping (say, stored in an XML file). Forexample,For bond_A: yearly_rate maps to int_val1, issue_date maps to date_val1For bond_C: rate1 maps to int_val1, rate2 maps to int_val2, put_datemaps to date_val1, call_date maps to date_val2)Comment: This is very good for performance because when I load aportfolio of different bond types I can pull them all in in one SELECTstatement. However this approach has a problem that the table issparse. The number of fields of each type has to be as high as toaccmodate the most complex bond while simple bonds will only be usingtwo or three.THE QUESTIONS:Are the four approaches I described above exhaustive? Are there anyother that I overlooked?
Hi, I'm setting up a heterogeneous transactional push replication with Sybase ASE 12.5.3 as subscriber. With management studio I try to create an procedure article with following properties
Copy extended properties : false Destination object name : pGS_RefuseRequest Destination object ownere : dbo Action if name is in use : keep existing object unchanged Replicate : Execution of the stored procedure Create schemas at Subscriber : false
When I save the article and then the publication I got following error message:
Can not add artice 'pGS_RefuseRequest'. Object was not found on server. Check if this object exists on the server. (Microsoft.SqlServer.Rmo)
That's realy strange because the wizard offered the procedure pGS_RefuseRequest in the list of possible articles.
Fortunatly I can create the article with following TSQL statement :
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
Hi, I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control. i want to write my own query without help of sqlDataSource control from start something like sqldatasource con=new sqldatasource; con.connection String="" then what all things will come........ ??? and please give me some poitners to some articles which help one to do the requested.
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk, eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate, l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows. But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question). Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (". But, I hate using two queries and just wonder if there's a better way around this.
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution: SELECT d.Name, d.Surname FROM Donators d, Filles f WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age) FROM Donators d, Files f WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id FROM Donators d, Files f, City c WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity FROM City c WHERE NOT IN (SELECT c.idcity FROM City c, Donators d, Files f WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int dateon DATETIME dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate())) FROM dbo.tblonenet_sdt_proj_hold WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as: rides (and shops or all) available in a certain area. rides in a certain area above a certain height rescrictions. etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
how do i make a querie that finds relations to a "part number" for models, and assemblies, but when it finds a relation of part number with a Product, it gives additional attributes for information? this code that i have so far doesn't do waht i want,, showing
Label(LabelID,Label) labelid=pk ------------------------------- Shelf(ShelfID,Shelf) shelfid=pk ------------------------------- Country(CountryID,Country) countryid=pk --------------------------------------- Track(TrackID,Track) trackid=pk ------------------------------- Artist(ArtistID,Artist,ArtistPhoto,CountryID) artistid=pk,countryid=fk country(countryid) ----------------------------------------------------------------------------------------- CD(CDID,CDTitle,CDDate,CDCover,LabelID,ShelfID) cdid=pk, labelid=fk label(labelid),shelfid=fk shelf(shelfid) ------------------------------------------------------------------------------------------------------------ CDTrack(TrackID,CDID) trackid,cdid=pk, trackid=fk track(trackid), cdid=fk CD(cdid) ---------------------------------------------------------------------------------- ArtistTrack(ArtistID,TrackID) artistid,trackid=pk, artistid=fk artist(artistid),trackid=fk track(trackid) --------------------------------------------------------------------------------------------------------- I've already created these tables but i need some aid writing these queries coz i am still a beginner in sql
Search Queries ------------------------------------------------------------ Input Artist OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 1 Group ByCDSection ------------------------------------------------------------ InputCDTitle OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 2 Group ByCDSection ------------------------------------------------------------ InputTrack OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 3 Group ByCDSection ------------------------------------------------------------ InputLabel OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 4 Group ByCDSection ------------------------------------------------------------
Ashish Amodia writes "how to create tables in ms-sql? how different opration can be perform in database table, like insert, update, delete with tables. how to create store procedure?"
Iam developing a randomizing program .which selects random rows frm the database.iam using sql as backend and asp.net as front end. i allow user to give a number say 1000, so 1000 random rows will be slected frm the database.iam using NEWID() for random selection. But how do i put the user value into query. so as to select that many random rows from the databse. please help me out on this
i hav this problem .i hav one column in my table which contains records which can be integers or string. i allow user in my .aspx page to enter integers in three text boxes. and depending on those integers say 1,2,3 ... i hav to select those rows from my table. where the problem is that i hav to selct the row which is say row no.1 which can contain string or integer and iam not getting the slected result instead some integers . the query iam using is SELECT " & range1 & " UserID FROM TAble
where range1 is the integer that user puts into textbox .i hav only one cloumn(UserID) in my table which can contain integer or string so i hav to select the row which is what the user has entered but contains string or integer(which is not what user has entered).
Can we say that if there are 4 tables that are joined , the query will definately take a longer time to execute, can we still reduce the execution time
I mean how can u still further optimize the performance , or say that the performace can no further be done on the particular job
