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.
View 3 RepliesI 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?
Any advice would be greatly appreciated.
Many thanks
Smilla
Hello:I am new to T-SQL programing, and relativly new to SQL statements ingeneral, although I have a good understanding of database theory. I'm alittle confused as to the fundamental differences between a view and aquery, or rather, when it is more appropriate to use one vs. the other.It seems to me that most select queries can be implemented as views, and Ican't see the downside to doing so.I have a rather complicated database structure in terms of the relationshipsbetween various tables, and to get the information that I need into the userinterface forms I have to use a number of nested queries (first to combinetables together that have been normalized to place the related info in asingle table, then next to extract only the info that I require. In somecases I would need a 3 level deep nested query to get to what I need. Byimplementing the first step, that of combing the normalized tables back intoa single table as a view, it seems to me that I've completed the first stepat the database level, thus making my queries or stored procedures easier toimplement.I would appreciate any comments which can help me to decide when it is or isnot appropriate to use a view in these circumstances. I should point outthat I don't plan on using the views as part of action queries since theyare made up of multiple tables, I'm primarily interested in using them tooutput data which has been consiolidated from multiple tables.Thanks in advance to any and all who take the time to reply.
View 8 Replies View RelatedHello all,
I'm looking for a best practice.
Let's say you have a report that contains out of 3 queries.
How are you going to create the report and why?
1. Just use "select * from table where p1= value" in your report
2. Save the query in your db as a view and use the view in your report
3. Create a procedure that contains all 3 queries. And use the procedure with some parameters in your report?
Kr
Karel.
I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?
View 6 Replies View RelatedHi,I'm converting an Access 2000 database to Sql Server and must bemissing something obvious.Using the Import utility in Sql Server, the Access queries seem to getexecuted and the resultant data imported as tables. Oops!Using the Upsize lizard in Access 2003, the queries aren't even in theselection list of "tables" to upsize. It looks like the Upsize wizardisn't supposed to do queries.How does one automate the migration of standard conforming queries toSql Server?Of course, I expect to spend some time addressing the stickiernon-compliant queries, but there must be a way to do the easy ones.What am I missing?-Dave
View 4 Replies View RelatedI'm trying to access a System Catalog View (sys.database_permissions, and others) from a table valued function. I need to be able to pass the database name into the function so that I can operate on any database. Typically I'd use dynamic SQL to do something like
INSERT INTO #tempTable
EXEC ('SELECT * FROM ' + @DBName + '.sys.database_permissions')
But of course I can't use dynamic SQL inside of a UDF. I know I could do this using a stored procedure, but I'd need the output to be a recordset that I can query.
Has anyone done anything like this? I think I'm stuck.
Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error?
It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access.
Thanks in advance - I hope.
Hi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!
Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.
Thanks!
Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))
There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?
I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:
CREATE TABLE [Person].[Person](
[BusinessEntityID] int NOT NULL,
[FirstName] varchar(20) NOT NULL,
[MiddleName] varchar(20) NULL,
[code]....
Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:
SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) AS [SUMQuantity]
FROM [Person].[Person]
GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]
The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:
SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'
The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view. I'll then experience more locks / blocks, because now the queries are seperated on two objects.
our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.
I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient.
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.
Any suggestions would be greatly Appreciated.
-Ray
I am trying to get the same results in SQL server with a query as it works in access
There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
[code]...
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 .
Hello~
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.
Any help is appreciated.
Thank you!
Shelli
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.
Any suggestions greatly appreciated.
Thanks
Hi,
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.
I am wondering if it is possible to make 1 query that gives several, different resultsets.
I am working on a database for speedskating times, in particular I am working on seasonlists
The query I use for this i (in simplified form)
SELECT LastName, FirstName, Country, Time
FROM TbExample
WHERE Distance = '500' and Gender = 'Man' My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.
I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.
In the current setup I need to make 6 queries.
Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all possible combinations?
I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:
I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?
If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
View 1 Replies View RelatedHello,
We have allowed one of our clients to create views in our .net /sql application so they can build reports using MS Access. They have been doing so successfully for years. However lately they are experiencing Timeout Expired messages when in the actual web application. I have confirmed that several of the views when opened in Access do cause blocking, the locks are promptly realeased when the the access odbc of the sql view is closed.
When the views are opened in SQL query analyzer, there is no sql blocking.
Would this be related to how the tables are linked?
Any suggestions would be greatly appreciated.
Is it possible to convert an ms access mdb file to an sqlexpress mdf file?Any help appreciated,Henk Feijt
View 7 Replies View Relatedwhat is the proper method of taking a database made in MS Access and converting it so that it runs in SQL Server 7?
View 1 Replies View RelatedI am trying to convert the following Access 97 query into transact SQL for SQL Server 7 but can't seem to get the syntax correct.
"UPDATE datInventory INNER JOIN tmpInventoryReport ON (datInventory.DenomID = tmpInventoryReport.Denomination) AND (datInventory.ReportDate = tmpInventoryReport.ReportDate) AND (datInventory.RegionID = tmpInventoryReport.Region) AND (datInventory.ACCID = tmpInventoryReport.Carrier) AND (datInventory.OwnerID = tmpInventoryReport.[Financial Institution])
SET tmpInventoryReport.[Working Inventory] = [datInventory].[WIBalance], tmpInventoryReport.Surplus = [datInventory].[DSBalance];"
Can anyone help?
I did create the field on table as TinyInt. I created an appending query and appended the records to the SQL table. Now I have 0's or 255's in the field.
Shouldn't they be 0's and 1's instead????
What am I doing wrong?
What's the best way to convert the Yes/No fields into SQL, since I want to keep the access front end.
Thanks for any help.
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies View RelatedI'm converting a View from access to Sql and I'm stuck on this IF statement.
IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);
I know that I could use CASE statement but i keep on getting errors.
My Case Statement:
case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand
when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty]
end as CoreQty
I'm currently using Management Studio 2008 R2 and struggling as I am very raw to SQL full stop eg a complete newbie to it.
WHERE (((dbo_ED_ATTENDANCE.AttendDateTime)>=Date()-IIf(Weekday(Date(),2)<2,4,2) And (dbo_ED_ATTENDANCE.AttendDateTime)<=Date()-1)
The above criteria is cut from an existing Access query which I am trying to replicate in SQL Management Studio 2008 R2.
Hope this makes sense.
I am trying to convert an Access based blog app to SQL Server but I'm having some trouble with some SQL.
The sql is as follows:
SELECT *, (SELECT COUNT(*) FROM tblComment WHERE tblComment.blogID = tblBlog.BlogID AND tblComment.commentInclude <> 0) AS TOTAL_LINKS
FROM joinBlog
WHERE BlogIncluded <> 0
ORDER BY BlogID DESC
The access version returns blog entries & the number of comments posted to each entry.
joinBlog is an Access query:
SELECT tblBlog.BlogID, tblBlog.CatID AS tblBlog_CatID, tblBlog.BlogHeadline, tblBlog.BlogHTML, tblBlog.BlogDate, tblBlog.BlogIncluded, tblCategory.catID AS tblCategory_catID, tblCategory.catName
FROM tblCategory RIGHT JOIN tblBlog ON tblCategory.catID = tblBlog.CatID;
I assume I need to make a view out of the Access query, I have done this & that appears to work.
The problem I have is when I try the 1st sql that is in my page with sql server I get the following error:
The column prefix 'tblBlog' does not match with a table name or alias name used in the query.
I can make the following change which returns data but does not attach the blog comment counts to the proper blog entry, instead it returns the total comments in the query:
SELECT *, (SELECT COUNT(*) FROM tblComment,tblBlog WHERE tblComment.blogID = tblBlog.BlogID AND tblComment.commentInclude <> 0) AS TOTAL_LINKS
FROM joinBlog
WHERE BlogIncluded <> 0
ORDER BY BlogID DESC
Can anyone tell me how to convert this for SQL Server? This is my 1st access to sql server attempt.
Thanks.
I'd like to convert my Access database table to MS SQL Server 2005 Express.
I have a text field and a memo field.
What are the corresponding datafield types for SQL Server?
thanks.
Hey people,I have to convert MS Access 2000 database into mysql database, the wholething being part of this project I'm doing for one of my facultyclasses. My professor somehow presumed I knew db's and gave me long listof things to do with that particular database, first thing being thatparticular conversion. Truth is that I don't know a first thing aboutdb's, let alone using mysql... I downloaded mysql form www.mysql.com andstill searching for MS Access 2000 (it doesn't work with 2003 I have,or I don't know how to make it work).Any kind of help will be welcomed and highly appreciated!!!Thanks,Mario
View 17 Replies View Related