I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables.
I've been asked to put in an 'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building.
So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days looking at tutorials on the net and learning about queries and this is what I've come up with:
Select Time_In_Table.Visitor_Number
Not Union
Select Time_Out_Table.Visitor_Number
If I'm right (and I'm probably not), this will give me the people who are in the time in table, but not in the time out table.
Can someone please help me by letting me know what I'm doing wrong in the statement above, and also, advise me as to where it goes since none of the tutorials I've found cover where to put the statement in my programming? At a guess, I'd say it goes in the print button click function.
Hello all, Does anyone know of a utility to migrate Access 97 Queries to SQL Server 7? I've over 400 of them and I am trying not to have to do them by hand.
Also I tried to use to migration wizard from MS instead of the DTS and all I got was Error after error. 'Invalid Date/Time', 'Error with YES/NO field' and 'Timed out' were the most common. In the end only 4 tables out of 89 were migrated.
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.
I need to know how to submit queries, and print reports.
I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)
SELECT tblCalls.* FROM tblCalls WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));
SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime FROM qryFlyTippingStatsSummary1 GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);
I am using SQL2005 (Installed on VISTA). It was running smoothly until today....for some reason I cannot access any tables in queries that I try to run in Management Studio. I can open the tables and see the information, but all queries I try to run result in the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.BEYLEVEL16'.
I tried to run simple select queries on other tables but I get the same error message. Do not understand, eveything was working fine yesterday.
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription, tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%])))) AS [%], 0+round2([Amount]*[%]) AS Allocated, 0+round2([Allocated]*[Salvage%]) AS Salvage FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON ([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND ([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND ([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
Hi All, I hope I am at the right place to post this question: How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself. Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT * INTO dupContactKeys FROM Contacts WHERE ContactKey IN ( SELECT ContactKey FROM Contacts GROUP BY ContactKey HAVING COUNT(*) > 1)
SELECT * FROM dupContactKeys WHERE ContactKey IN ( SELECT AddressLine1, Zip FROM Contacts GROUP BY AddressLine1, Zip HAVING COUNT(*) > 1) ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have an application where users can enter data into any (or all) of 6 search fields, to produce a filtered query.
This works fine using my Access version(see code below), but as SQLS2k cannot use "IIF", I tried to replace these bits with "CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
FROM (((tAudit LEFT JOIN tCategories ON tAudit.CategoryID = tCategories.CategoryID) LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID) LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID) LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType])) AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID])) AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])) AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID])) AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID])) AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
I have upsized some Access 2002 tables to Sql Server 2005 express. I put together a query and when I try to execute it, it tells me the MS Access is an earlier version prior to SQL server 2005 and it will not save the queries. When I try to run them anyway, I get the repeated message and the query never executes. I'm taking a T-SQL class and this is how the instructor. Is they a fix for SQL Server or Access to get around this or do I have to revert back to MDSE 2000?? Can MDSE 2000 and SQL Server be loaded onto the same machine (laptop)? Help!!!!! I am very new at this...
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton (IT Support) Durham Aged Mineworkers Homes Association
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
How due I set up a database in SQL 7 to be accessed via the web and to have queries performed on it. What else do I need besides NT 4.0 SRV Pack 6. I need detailed instructions, really appreciate your help. Thank You!
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
i know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:
Dear friends, ours is a product company,we are delivering our product presently in sql server.in future we r planning to deliver the product in all the big databases.
now the problem is conversion of the module to other back end.there are so many functions and procedures as well.some will execute only at runtime.through some where string.
is there any tools those can automatically convert queries, functions and procedures to the other back ends?
or is there any open source code to convert automatically?
Hi, newbie here:I have created a small (5 fields) Access table which I will beconverting to MS-SQL Server format when the ASP code has beencompleted. It is basically a collection of links to news releases frommining companies. The group of people who will be browsing thisdatabase want to know if the news release pertains to their area.Sometimes the news release pertains to multiple areas if the miningproperties are scattered. Given the possibility of a one-to-manyrelationship, ie one news release, many areas, I created an additionaltable for the areas. I created the ASP code to pull down the newsrelease information, then loop through the area records such as:set RSNewsRelease = Server.CreateObject("ADODB.Recordset")NewsRelSQL = "Select date, company, title, newsreleaseID fromnewsreleases;"do while not RSNewsRelease.EOF'display news release date, company and titleresponse.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &RSNewsRelease(2).Value'loop through areasset RSAreas = Server.CreateObject("ADODB.Recordset")'run querydo while not RSAreas.EOF'display areasLoopset RSAreas = nothingLoopIn other words, the only way I could get the results I wanted was toset the Recordset to nothing, then reset it with each iteration of theouter loop.Is there a better way to do this?Jules
Hi, I have the following error logged in SQL Server's error log whenever I run a query on a particular database :
2002-01-04 22:54:02.46 spid11 Error: 823, Severity: 24, State: 1 2002-01-04 22:54:02.46 spid11 I/O error 1117(The request could not be performed because of an I/O device error.) detected during read of BUF pointer = 0x14eac480, page ptr = 0x73c94000, pageid = (0x1:0x158c89), dbid = 8, status = 0x801, file = d:mssql7datamydb.mdf..
2002-01-05 05:54:22.01 kernel SQL Server terminating because of system shutdown. 2002-01-05 05:54:22.14 kernel LogEvent: Failed to report the current event. Operating system error = 31(A device attached to the system is not functioning.).
and get this error in the Results pane of Query analyzer : Server: Msg 823, Level 24, State 1, Line 1 I/O error 1117(The request could not be performed because of an I/O device error.) detected during read of BUF pointer = 0x14ace4c0, page ptr = 0x35df6000, pageid = (0x1:0x44b73), dbid = 14, status = 0x801, file = d:MSSQL7DATAmydb.mdf.
Connection Broken
select @@version returns, Microsoft SQL Server 7.00 - 7.00.961 (Intel X86) Oct 24 2000 18:39:12 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Books Online Help on Error 823 indicates that it is a disk problem and suggests that DBCC checkdb needs to be run on the database. But, hardware diagnostics on the server do not report any disk problems and dbcc checkdb also returns no errors.
Help on the cause of error 823 would be greatly appreciated. Thank you, Praveena
I have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Say for example DatabaseA has failed over to the mirror. Then you call a stored procedure in that database that queries a table on DatabaseB that is on the principle (has not failed). What happens? Is mirroring smart enough to know that? If not, are there workarounds? Perphaps manually failing over the other databases?
I am using SQL 2008 r2. I have two SQL Queries from 2 different database but they share one server. I need to linked these two SQL Queries as they share the same Primary key which CustomerID see example below
Query 1
Database::Student Select StudentID , FName, LName From Student
Query 2
Database ::Finance Select StudentID,Tution FROM Payment
I need to be able combine two query which come from two database but they share one server.I would like to use two temp tables so that I can perform a left / right join to retrieve the data by linking two queries using primary id which they both share ( StudentID)
Summary : I have two DB's on the same server. I have two simple select queries for each DB which work correctly.
As a part of DBA, I used to execute various SQL files. Most of the time, it is like a manual effort to execute the files individually.
I am looking to automate the process, like a single click to execute all the .SQL files.
The main hurdle I have is, some files needs to be executed in A1 database, some in B1 database and some other SQL files need to be executed in C1 database. In this scenario, I need to pass the DBName information to the powershell query dynamically.
My design for this requirement is, say each .SQL file need to contain a template like
@DBName = 'your Database name' @Executeon = 'When to execute'
In this case, the powershell first need to read the SQL file and finds the value for @DBName and replace it in the powershell query and execute the SQL files automatically.
Is it feasible ? Or any other alternate easier way to proceed.
I'm using connection strings to SQL Server Express that have the form
"DataSource= .SQLEXPRESS;AttachDbFilename="(path to my development directory)CurrentDevelopmentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
I'm assuming this works because the DB is already there, created "manually".
How might I build a connection string for a SQLCommand for a "Create DataBase" query?
I'm confused about what the AttachDbFileName would be, or if there would be another variable in its place, since there's no file yet.
Hi! we have no Business Intelligence solution in my job and the general manager asked for a query and reports system where he (and other section managers and non IT staff) could query the corporative database (we work in healthcare, so it's mostly about patients, clinic histories, treatments) so I will propose them SQL SERVER 2005. I'll tell you my general idea and maybe you could tell me if I'm right: Through Integration Services we will load our data from the corporative database (it's Borland's InterBase) and generate a datawarehouse (now we don't have a datawarehouse) then through Reporting Services we can query this datawarehouse to generate different reports (this reports can be generated for non-technical staff, but staff who knows quite a lot about for example the different treatments that different groups of patients follow, so this staff may want to query about which patients followed which treatment in a period of time and generate by himself a report about that). And through Analysis Services we could (in a future) generate some OLAP solutions, Data Mining etc
But for the query and report system it could be enough to start with Integration Services and Reporting Services? it's very important for this system to work efficiently in terms of time: the non-technical staff generates the query and in seconds receives the report result... some of this non IT staff who will use the system knows exactly which information we house in each InterBase table, so through this system they would like to generate their own queries and reports (this is a different level of non-IT staff who is in between a Medical doctor and a Software engineer, this staff does have some technical background specially in SQL.
So my idea is to have an SQL SERVER 2005 to which this staff gets connected via LAN (all this system runs locally in one corporative place, will not run through Internet) feeded by the InterBase corporative database and delivering efficiently queries and reports... is this possible? could I test this using the trial free 6 months version? and after that if everything's working fine how much will I have to pay for 15 persons to access the SQL SERVER 2005 for keeping using the system?
