I'm converting an Access 2000 database to Sql Server and must be
missing something obvious.
Using the Import utility in Sql Server, the Access queries seem to get
executed and the resultant data imported as tables. Oops!
Using the Upsize lizard in Access 2003, the queries aren't even in the
selection list of "tables" to upsize. It looks like the Upsize wizard
isn't supposed to do queries.
How does one automate the migration of standard conforming queries to
Sql Server?
Of course, I expect to spend some time addressing the stickier
non-compliant queries, but there must be a way to do the easy ones.
What am I missing?
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 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?
I recently started with a company that uses access for a front end and sql2000 for the back. They have set up an ODBC connection though access2k to reach sql2k. This works fine but fouls up in regards to the record locking. You can set record locking in access but it has no effect. In the office help it says 'data in a form, report, or query from an Open Database Connectivity (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.'
Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.
So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?
I have created an Access2K front end application that connects to aSQLServer2K backend. I use this vba code to create the connection fromthe Access app:Dim strConnect As String'make sure all previous connections are closed:CurrentProject.OpenConnection "Provider="'create new connection string to server:strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSISTSECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATASOURCE=nycvnewsbas01"CurrentProject.OpenConnection strConnectEverything functions.The problem is the users cannot make the connection if they are notpart of the local admins group on the server. As soon as they areremoved from the local admins group their conenctions fail.How do I remedy this?
In an Insert Into statement I have used the Host_Name() function toidentify which user has suppied a record to a table that holdstemporary data.I'm using an Access2K front end.Code:Alter procedure SPName@parameter1 intASSet nocount OnSet xact_abort offDeclare @myHost nvarchar(50)Set @myHost = Host_Name()Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,myMachineName)SELECT tblMyName.UniqueID, AnyField1, AnyField2, @myHostFROM tblMyNameWHERE tblMyName.UniqueID = @parameter1I have two problems.In some cases (and only on one or two of maybe about 250 clientworkstations) Host_Name() returns the name of MY machine. I'm thinkingthis is because I developed the app and distributed it and for somereason it's retaining the info contained in my original connectionsetup set in the MS-Access Connection window???Also, on some occasions, I get blocking messages in my trace logfollowing this operation.Any help on these two issues is appreciated.lq
I currently have replication setup from a MSSQL2k machine to Oracle 8.1.5 and while the replication works, it times out every time there is a hiccup in the network. It acts as if it won't retry like it does from MSSQL2k to MSSQL2k. I'm using Microsoft ODBC for Oracle for my connection. Any ideas?
Q--Is there any feature of sqlserver-2000 by which we can restore the database with minimum time by using the parallel processor? if the answer is yes wht is the statistics with example
For example
Time reqd for restoring 20GB of database 25minutes.
with configuration of Xeon processor,1GB memory and no users are using the server.
Basic purpose of my question is i need to give a solution to the client by using which he will be able to restore 20GB of data in 1 minutes .Is there any 3rd party utility which is available in market or can we achive this in mssqlserver2000, itself by increasing the resources like memory,cpu etc.
Hi All,I'm trying to track down a mysterious problem we're experiencing inwhich updates and inserts to tables in our mssql2k server appear to be'disappearing.'To explain our situation:We have a web page (written in ASP, if that's relevant) on which weaccept enrollment information.When that page is submitted, the form data is passed to a storedprocedure on our mssql2k server, which performs several operations,all of which are wrapped in a transaction.In particular, the stored procedure performs an update operation on arecord in one table (i'll call it TableA) and an insert into anothertable (TableB).If the procedure encounters a problem (ie after each update / insertoperation in the procedure we test for IF @@Error<>0) it performs arollback, performs a select similar to the one immediately below, andthen RETURNs.SELECT '1' as error, 'Unable to update TableA' as errormsgIf the procedure doesn't fail any of the @@Error tests, thetransaction is committed, and a membership number is SELECTed to bereturned.SELECT '0' as error, @memnum as membershipnumberThe @memnum variable is populated within the transaction.Back in the ASP page we test both for the proc returning an emptyrecordset, or for it passing an explicit value in the error field, andpush the page to an error page if either of these conditions are met.If, on the other hand, none of these conditions are met, and themembershipnumber field in the recordset is populated with a validmembership number, we push to a confirmation page.This confirmation page receives the membership number in a sessionvariable, performs a SELECT against TableB (the table that receivedthe insert during the proc) using that membership number in the WHEREclause, and the resultant recordset is used to populate theconfirmation details on that page. That recordset is also then used topopulate the details of a confirmation email, which is automaticallysent by the confirmation page.And now here's our problem: we've become aware of a handfull of peoplewho have gone through the enrollment process, have received theconfirmation email containing the information they supplied asexpected, but the data appears to be entirely missing from our tables.By that I mean that the record in TableA does not appear to have beenupdated (under normal circumstances that record should have hadseveral flags set, and several other fields updated with informationsupplied by the person enrolling), and the record in TableB does notappear to have been inserted.In essence, looking at our tables, it *feels* like the transaction inthe stored procedure for that particular enrollment hit a problem andwas rolled back. However, the evidence that we have in the form of theconfirmation email argues strongly that the data must have existed inour tables (particularly in TableB), if only for an unknown period oftime.We're kind of at our wit's end to work out what is going wrong withthese enrollments. From my understanding of transactions (and I couldwell be wrong) any changes to data (ie updates, inserts etc) containedwithin are essentially 'invisible' to any other operation (ie theSELECT that happens in the confirmation page) until the transaction iscommitted, implying that the effect of the update and insert shouldhave been 'permanently' successful if no error code is received and ifa valid membership number was returned. I ask, because someone in ourteam has suggested that maybe the operations in the transaction'lasted long enough' in the tables to have been visible for the SELECTon the confirmation page to have worked, but were then subsequentlyrolled back, explaining why the confirmation email is appropriatelypopulated and why the data then appears to be missing. However, as Isaid, this doesn't match my understanding of how transactions behave.Sorry for the length of this post, but I felt it was best to explainthis as best as I could.Does anyone have any advice they can give us on this situation? ie,are there any known problems with operations in transactions 'bleedingover' into tables, but then being rolled back at some later point?Does anyone have any thoughts or suggestions on how we can furtherdiagnose this issue?Truly, any help will be immensely appreciated...Thanks in advance,M Wells
Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.
I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.
Our ado class tests the connection in the 'execute' method ie if connect() execute db task
.connect() is
IF this.loConnection.State = adStateOpen *-- The connection is already open. ELSE create connection ENDIF
return (this.loConnection.State = adStateOpen)
Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @ on the sql server while the app is running and try to perform a task that requires a db connection.
problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.
It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.
I have to write a statement that answers the question: "Which pilot is authorized to fly all the aircraft in the fleet?" implying that the individual aircraft could be of any of the three aircraft types.
Below are the tables with notation Tbl_name (important keys [clarification of keys]):
In words my question should be something like: "For all those who are authorized to fly aircraft (the only pers_id:s listed in the authorization table), show the names of those that have their pers_id:s next to all aircraft_type:s."
I've gotten this far:
Code:
SELECT DISTINCT p.name FROM personnel AS p, authorization AS b, aircraft_type AS f WHERE p.pers_id = b.pers_id AND b.type_designation = ... ;
Now what I want to accomplish is to select the pilot which has a record for ALL type_designation entries in the aircraft_type table.
Is there a magic keyword that I don't know of? Is that magic keyword called "EXIST" and how do I use it?
Hi.I have the problem that some records in a ms sqlserver table is unableto update from Access.I get the error message odbc-time out error in linked table......I tried to copy this table to another database, where none but me wasaktive.And then it worked quit ok when I try to save the record.I am thinking about if there is some trigger och restraint that Idon't know about, but I don't know how to se all of this in thedatabase.I have changed the odbc-timeout settings in Access but that doesn'tseed to make any differenceSo what's the problem?Does anyone have an idea?/regards BigOlle
Hey there everyone, hopefully someone can shed some light on this problem.
Here is the stored procedure:
CREATE Procedure sp_getRequestorwCredentials
@LoginID varchar(50)
AS
SELECT Users.Name as name, Users.Email as email, Users.Manager as manager, Users.Mgremail as mgremail, Users.Empid as empid, Users.Phone as phone, Users.Dept as dept, Users.Busunit as busunit, Users.Segment as segment, Users.EmpPosition as position, Users.Region as region,
Requests.RequestDate, Users.EmpPosition, Users.Reason as reason, Users.District as district, Users.NetId, Users.Status as status
FROM Users INNER JOIN
Requests ON Users.Empid = Requests.UserId
WHERE (Users.NetId like RTRIM(@LoginID))
ORDER BY Requests.RequestDate DESC
return
GO
Now this query will work fine when using the like operator however if a user with a similar name to another user already in the table then the user may get the other persons record instead of thier own. Of course the solution here is to use the = operator instead of LIKE. This works fine when querying the DB directly however when executed within the stored proc no records are returned using the = operator even if they exact same query text works fine in a query window with a hardcoded var.
Yes I know there are SQL injection issues but the LoginID is being grabbed from the users domain login DOMAINNAME and is grabbed from the authenciation module. Since Active Directory names have a limited char set and theres not a way to pass an invalid name with text that allows for an injection attack.
Hi All,Further to my previous long-winded question about a situation in whichwe appear to be mysteriously losing data from our mssql2k server.We discovered an update statement, in the stored procedure we believeis at fault, after which no error check was being performed.Under certain conditions, this update is fired against the same recordin the same table as the immediately preceding update statement withinthe transaction. We are now suspecting that under some circumstances,these two updates get into a locking conflict that is eventuallyforcing the transaction to be rolled back.However, I'm still left with three questions.1) Where an update in a transaction gets locked, and an error isn'ttested immediately afterwards (ie no 'IF @@Error<>0' test is made),would the transaction proceed as normal?2) Most critically, would statements in the stored procedure thatappear after the COMMIT TRAN statement also be executed, even if anunresolved lock existed within the transaction?3) Assuming that (2) does happen, would a SELECT made on anotherconnection with a 'WITH(NOLOCK)' locking hint be able to see thechanges made in the locked transaction even if the server is set toREAD COMMITTED, and the SELECT takes place some time after the COMMITTRAN is issued? More to the point, given (2), how long would thelocked transaction survive before being rolled back after the COMMITTRAN has been issued? Is it possible that the COMMIT TRAN takes place,the transaction is flagged for potential rollback while a lockresolution is attempted, the stored procedure exists as thougheverything was fine, a subsequent SELECT (ie performed as one of thenext operations in the same application) using WITH(NOLOCK) 'sees' thechanges made by the transaction, reinforcing the impression that thetransaction succeeded, and then at some point thereafter the lock isdetermined to be unresolvable and the transaction is rolled back,making it seem as though the data disappeared, even though it had beenSELECTable via a different connection to the server?Thanks, by the way, to Simon and Erland for your advice on my previousquestions about this problem.Much warmth,M Wells
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?"
