I am working on a project with a couple of hundred tables and views. There is one primary table around which all other tables revolve with the primary table having somewhere around 42 to 45 million records. Some of the related tables can have many times this number of records. The data is static in that it only changes at specific intervals (once a month or so) when more records are added and some “old� records are removed.
My problem is that users need to query these tables without any major restrictions. I mean, the front end will allow users to create almost any selection criteria form the many columns in the DB. The selection of values will be controlled via drop-downs and lists from where the user will select the criteria. The other consideration is that the grouping can be for any value and up to ten levels deep. Our users are not sophisticated enough to create their own SQL-like queries so we have to guide them through the process of selecting the values…
I am trying to create stored procedures that will allow this behavior but I continue to run into the problem of how to properly join the tables when the combinations are almost endless. My conclusion is that although it seems this seems to be a case where D-SQL is a good choice, there are some draw-backs I am not sure we can afford, specifically the security issues. Furthermore, the creation of a D-SQL “code-generator� seems like almost impossible task and time is of the essence.
Has anyone here had any experience with a similar situation that can shed some light?
I am trying to setup querying Active directory from sql for the first time.
We are running on windows server 2012 and using sql 11.0.2100.60. Have tried the following
sql is on sever dev AD is on sever DO
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' GO
[Code] ....
I get the following error when I try and query
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query "SELECT name FROM 'LDAP:// xxxx.internal' WHERE objectCategory='Person' AND objectClass = 'contact'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
Hi i given my query here. in that joings betweens tables is correct, but i am getting the following error ora-00933 sql command not properly ended can we have exists function inside if condition??
select distinct loc.authorization from account acct, location loc, iom_order iom where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140
if(loc.authorization is null and exists( select distinct line.INTRALATA_PIC_FREEZE ,line.INTERLATA_PIC_FREEZE from line line, iom_order iom, activity act where ((line.INTRALATA_PIC_FREEZE='F' or line.INTERLATA_PIC_FREEZE='F')and line.line_id = act.component_id and act.component = 23 and act.iom_order_id = 140) union all select distinct trunk.INTRALATA_PIC_FREEZE ,trunk.INTERLATA_PIC_FREEZE from trunk trunk, activity act where ( (trunk.INTRALATA_PIC_FREEZE='F' or trunk.INTERLATA_PIC_FREEZE='F')andtrunk.trunk_id = act.component_id and act.component = 25 and act.iom_order_id = 140) select distinct rcf.INTRALATA_PIC_FREEZE ,rcf.INTERLATA_PIC_FREEZE from remote_call_fwd rcf, activity act where((rcf.INTRALATA_PIC_FREEZE='F' or rcf.INTERLATA_PIC_FREEZE='F')and rcf.remote_call_fwd_id = act.component_id and act.component = 24 and act.iom_order_id = 140) select distinct tsg.INTRALATA_PIC_FREEZE ,tsg.INTERLATA_PIC_FREEZE from nodal_tsg tsg, activity act where ((tsg..INTRALATA_PIC_FREEZE='F' or tsg.INTERLATA_PIC_FREEZE='F')and tsg.nodal_tsg_id = act.component_id and act.component = 5 and act.iom_order_id = 140))) Then select distinct loc.authorization from order2misc o2m, location loc, iom_order iom where loc.location_id = o2m.serv_loc_id and iom.iom_order_id =140 else select distinct loc.authorization from account acct, location loc, iom_order iom where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140 end if;
During some non working days i got rid of old merge replication that I had and started new one. Today 5 of my 6 servers are working normally, but on the 6th one I am getting following error:
The transaction ended in trigger. The batch has been aborted. Table [dbo.A...] into which you are trying to insert, update, or delete data is currently being upgraded or initialized for merge replication. On the publisher data modofocations are disallowed until the upgrade completes and snapshot has succesfully run. On subscriber data modifications are disallowed until the upgrade completes or the initial snapshot has been successfully applied and it has synchronized with publisher.
Although this message is descriptive ebough, I must say that my publisher says that whole process of synchronyzing and initializing data with subscriber is done. In fact it allready did more than 100 cycles of replication.
There's a trigger defined on a table that doesn't allow inserting or updating a field if the value inserted already exists in the table. Here's the trigger:
Code Snippet set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER [tUI_ID_ISIN] ON [dbo].[T_table] FOR INSERT, UPDATE AS --IF ((SELECT TRIGGER_NESTLEVEL()) = 1) BEGIN declare @ISIN varchar(12) set @isin = (select id_isin from inserted ) if not @isin is null begin if exists (select 1 from t_titoli, inserted where t_table.cd_isin = @isin and t_table.cd_titolo <> inserted.cd_titolo)
begin Raiserror ('ID ISIN alredy exists!', 16,1) rollback tran end end END
it all works fine when the user inserts/updates the record from the front end.
When I run the SSIS that inserts records into the table, I get the following error: The transaction ended in the trigger. The batch has been aborted.
There are no records that should fail the control. In other words, all ISINs are unique.
WE were using SQL Server 2005 Evaluation Edition having one user database created for application testing and also using reporting services for dashboard purposes.
Now our Evaluation period ended However we have already purchased SQL Server 2005 standard license for actual implimentation.
Can anyone guide me step by step how to seamlessly upgrade from evaluation version to the license version of SQL Server 2005 standard.
its somewhat urgent so i request quick help on this
I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id, ps.last_execution_time , ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds, ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds, ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
I get the below error only when my IDE open. It connects well when it is found closed. [SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.] I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab But I wish someone could give me the proper solution. I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool. Environment: XP pro, VWD and SQL Express
I got an approach like that: 1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc. Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that? The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2). Thanks for the input!
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
I'm a newbie to SQL and wanted to see if someone could help me write a query. I store historical options data in a SQL data base and I'm trying to create a query that will retrieve all ATM (at the money options).
For each unique stock symbol (STOCK_SYMBOL) in my database find the strike price (STRIKE) which is closest to the stock price (UND_LAST). This will be refined by the EXPIRATION and OPT_TYPE
The following query returns all options not just ATM options.
SELECT STOCK_SYMBOL, UND_LAST, STRIKE, EXPIRATION FROM DATA
WHERE EXPIRATION > '2007-02-01 00:00:00' AND EXPIRATION < '2007-02-28 00:00:00' AND OPT_TYPE = 'CALL'
I am using MS SQL Server 2005 Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. )
as a interface between SAP and MS SQL 2005.
Once the connection is created i can use basic query to fetch the data from SAP tables ie., SELECT * FROM AFPO
But say I want to EXTRACT data just for month of July from AFPO.
AFPO does not have date field so you have to join AFPO to AUFK and AUFK to COEP to fetch the date as a criteria to restrict data for the month of July .
This can be done in ABAP by view or Interim Table, if you have Developer Key.
Can anyone please tell me if aforesaid is possible with MS SQL Server 2005 Import and Export Wizard query ?
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
Hello, I want to find all the entries where the date field is a specific date; so I may have 5 entries with the date 1/1/2006, and I want to find all of those. However, datetime fields in SQL Server also have the time, so how do you handle that? Can you just say where requestdate = getdate() to get all of the entries that have records for today? How does time affect this?
Hi,I am new to sql and was wondering if someone could help with this select statement. please don't laugh! SELECT * FROM product_detailsWHERE MemberId = @MemberIdINNER JOIN Member_Ratings ON product_details.MemberId = member_ratings.MemberIdSELECT COUNT(*) FROM member_ratings AS FinalHighRating WHERE member_ratings.MemberId = product_details.MemberId AND member_ratings.Rating = 5 Any questions? Thanks!
Hey, What is the best way to query two datetime values to see if they are alike? I need to determine if an [AccountEndDate] is today (both values are datetime). Using "Like" does not work. Thanks!
If you create a view such as CREATE VIEW TestView AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443 Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not? Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
I have a usage table Aaccessdate (datetime)useridpageidI need to write a sql that would do a report on a monthly basis. ( how many pagehits per month)All is fine - and my SQL looks likes thisselect count(pageid)from A.The twist here is , I do not want to count those records if the same user visits the page on the same day.For Eg02/03/2005 10:09:09.000 user1 page102/03/2005 15:09:09.000 user1 page102/03/2005 11:09:09.000 user2 page202/03/2005 16:09:09.000 user3 page3I want my count to give me back 3 instead of 4.Can this be done?ThanksRajini
I have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
im struggling to see what im doing wrong with the following SQL, wondering if it has something to do with the subquery being a view...cant think why though
select value from table where type = 'Extra Mailbox' and active = 1 and value not in (select login from view) order value
The view returns a list of logins and the type field is also a list of logins. I want to filter the list of logins that do not appear in the subquery....
I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?
I have a table (tbl_a) with 2 columns: itemNames | Date . ItemNames can have duplicates. I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx, state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
I have 2 tables Staff and Class which are as follows
Staff ID (Primary Key) FirstName MiddleName Surname Etc. . .
Class ID (Primary key) LessonTemplateID TeacherID TermID AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Hi All, I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates (
@SupplierGUID uniqueidentifier ) AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS (SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
I have a SQL SERVER 2005 running on my local machine. My local machine is also configure for web applications through IIS.
There is a remote oracle db, which is maintained by another department. They gave us read only access but they discourage web access over their oracle database.
Even if you try to access it through web ... they come to know that certain logins are trying to access it through web ... How ... i dont know.
Now what i did is linked that oracle database with my SQL server 2005 and developed an ASP.NET application which is indirectly accessing oracle database though my SQL Server 2005.
Whenever a user visits my web application on the backend the authentication done on my sql server but the query runs on linked oracle server... as I open connection to my sql server 2005
do you guys think that Oracle database administrator can ever know that his oracle db is access through a web application. whereas they allow us to copy required data on our machines through SQL Server 2005?
Hello all,I'm trying to run a query to make a report. My database is a incidentreporting database. I'm tryng to make a monthy report for incidents.The field I need to query in the date field which is a nvarchar in theform of 01/01/04 and 01/01/2004. I ran a query that looks like this:SELECT incident, doccur, IDFROM dbo.IncidentWHERE (doccur between '01/01/2004' and '01/31/2004')I get some results that look like this:Unsecured doors01/19/0492INTOXICATION 01/17/0477Bill Door entry door 01/28/03130Hit & Run01/21/04105Customer complaint01/02/0370Customer complaint01/02/0491PRINTER MALFUNCTION01/22/04111Customer complaint01/30/042322Trash Smoldering01/15/0451LOST01/02/0380BROKEN GLASS PANEL01/13/0442B.I.A. Assist01/04/03189GAS LEAK01/06/048UNCHANGED CASH BOX01/11/0440Intoxication01/17/0469Intoxication01/02/0471Intoxication01/17/0472Employee accident01/17/0473GREASE FIRE01/18/0474Verbal Dispute01/17/0475PANHANDLING01/17/0476Near Miss/Water backup01/18/0478Unsecured Arcade Door01/19/0493Intoxication01/18/0479Intoxication01/02/0481SUSPECT/WANTED01/18/0482Intoxication01/18/0483Property Damage01/20/0384Unsecured Bingo Snack Bar01/18/0485PANHANDLING01/18/0486Employee accident01/19/0487Unauthorize of proper exit01/19/0488Safety Hazard01/19/0489Key control violation01/02/0390Cracked keno ball01/23/04116Employee accident01/19/0494delay in drop01/27/2003128test01/01/20053763As you can see, the querey will give me the month and day I ask for,butnot the right year. Some to the data has 2 digit years and some have 4digits. How do I design the query to give me the year I ask for.Any assistance will be greatly appreciated
Hi,I'm having two databases in the name of n1,n2 having same designI want to query both the databases by giving the database name in queryDatabasesN1N2Table(both database contains same table)DetailTable detailsNo -intName -varchar(10)Now i want to check the detail table in both n1 and n2 using the selectqueryFor that I have written query it is not workingselect * from N1.DetailSelect * from N2.DetailThe above query is not working in query analyser,I'm getting error as"Invalid object name"In query analyser the Northwind database is currently selected, anywayI have given the database name it has to query from N1 database. How touse a select query with database name?Mani
I realize that this may not be the correct ng for this question, andif so, I'd appreciate a pointer to the correct group.I am not including DDL/DML for this question, because I do not believeit is relevant.What I have is a SQL query, which when executed in Query Analyzertakes about 10-12 seconds. From the time I issue the query, I see aninitial lag of about 1 seconds after which the grid starts gettingpopulated, and then runs its course.The UI responsiveness is impressive. Almost immediately, well, within1 second at most, I see data appear from the server into my queryanalyzer grid.I'd like to get the same responsiveness if possible on my clientapplication written in C# using ADO.NET.I use the SqlDataReader class off the SqlCommand class to make thesame query (identical), and yet it takes close to 9 or 10 secondsbefore even the first row is retrieved by the application. I am notsaying that it takes 9 or 10 second for my DataGrid to start beingpopulated; it is simply that the first Read operation of theSqlDataReader after I connect (which is instantaneous), that takes 9to 10 seconds. Once the data retrieval starts, the grid populates inshort order.Are there any pointer by which I can get just a few rows fast enoughto give the user the impression that the query is executing. In otherwords, what techniques can I use to get the same sort ofresponsiveness as in the SQL query analyzer?In case it is relevant, there are some 250,000 rows returned by thequery.Thanks for all helpful responses in advance.
I have 2 tables that are linked and i am querying sencond table from 1st table here is how my query looks. It works fine when i enter the parameter value from 1st table , it gives me the assoaciated values in 2nd table, But i also want it to display without filtering the second table when the parameter value is NULL ie when there are no associated values in 1st table. I tried putting "IS NULL" but it doest work. Can any one please help me out with this
Thx
SELECT DISTINCT dbo.group_primary.site_group_primary FROM dbo.group_primary INNER JOIN dbo.group_conversion ON dbo.group_primary.id = dbo.group_conversion.primary_group_id INNER JOIN dbo.group_secondary ON dbo.group_conversion.secondary_group_id = dbo.group_secondary.id WHERE (dbo.group_secondary.site_group_secondary = @secondary) OR (dbo.group_secondary.site_group_secondary IS NULL)
