Help Required Converting From MS Access SQL To TSQL

May 20, 2008

Hello, I am fairly new to SQL Server so I apologise if this is the wrong forum. I have a Sales analysis table in a SQL Server 2000 database. The table is populated from various sources in our ERP system. via a DTS package For our French branch sales unit of measure is eachs (EA) for actuals, but the primary UOM and our forecast data is normally in cartons. I have a product master table which defines primary unit of measure, and a unit of measure conversion table. So if I wanted to convert the data all to the primary measure I would write the below in Access:

UPDATE (tblSalesReport INNER JOIN tblItemMasterERP ON tblSalesReport.fldProductNo = tblItemMasterERP.fldProductNo) INNER JOIN tblUOMConvertERP ON (tblItemMasterERP.fldShortItemNo = tblUOMConvertERP.fldItemNo) AND (tblItemMasterERP.fldPrimaryUOM = tblUOMConvertERP.fldUOM1) SET tblSalesReport.fldUOM = [tblItemMasterERP]![fldPrimaryUOM], tblSalesReport.fldQuantity = [tblSalesReport]![fldQuantity]/[tblUOMConvertERP]![fldConvFactor]
WHERE (((tblSalesReport.fldCompany)="00007") AND ((tblUOMConvertERP.fldUOM2)=[tblSalesReport]![fldUOM]) AND (([tblSalesReport]![fldUOM])<>[tblItemMasterERP]![fldPrimaryUOM]));

I have found that in the DTS I can add an SQL task, but it seems to only allow UPDATE if there are no joined tables. I found the same thing in Stored Procedures, the SQL designer would only allow me to use one table. I guess I am looking in the wrong places. Can anyone point me in the right direction to incorporate the above sql (or equivolent) into our DTS package. Unfortunately the company decided to dispense with the services of the person who designed the package.

View 6 Replies


ADVERTISEMENT

Converting Access DB Code To Tsql--Help!!!!!

May 3, 2001

We are migrating an access97 database to sql server7.0. there are queries in access which need to be made into tsql queries.And what are the steps to convert access97 to sql7.0 and how do i migrate memo fields to sql7. Is there some method to convert or tool that does that...any help is welcome and thanks in advance.

View 1 Replies View Related

Help Required - TSQL - Tricky Qns

Nov 20, 2005

hi all

If any one has a document on the tricky TSql questions, kindly share it with me.
I have an interview next week. My mail id is kpsreenath@gmail.com
Thanks
Sreenath

View 1 Replies View Related

SQL 2000 TSQL Example Required To Set DEADLOCK_PRIORITY To 10

Mar 7, 2008

I have some deadlocks in defragging indexes in a sql 2000 db
the defrag runs late @ night but the conflict is with a 24/7 app (email app)
MY SQL job which calls an sp to defrag dbs get to be the dead locked victim (dooh)
So I thought that I would: set SET DEADLOCK_PRIORITY '10' but i get SET DEADLOCK_PRIORITY option '10' is invalid
This works in sql 2005 but not 2000. I can't set the apps connection to low.
Any rate would any one know a TSQL 2000 example of setting the deadlock priority to 10 ?
Please.

View 1 Replies View Related

Converting Oracle SQL To TSQL

Jul 20, 2005

Hello,I am new to sql*server, but have used Oracle for years. Can anyonerecommend a good online guide for converting all the Oracle SQL I knowto TSQL?tia,Mike

View 1 Replies View Related

Does MS Access Installation Is Required For Running Application That Uses Access Mdb File

Nov 28, 2006

Hi,

I am developing an application that uses Access database (mdb file) to store the user data. The user of this application is not interested in the database file (to view in MS Access Environment). Does the user machine requires MS Access installation to run my application or just some couple of dlls (OleDB driver, Access DB Engine,..) should be enough to run my application?



Thanks,

Rao

View 3 Replies View Related

TSQL From An Access SQL Statement

Feb 21, 2001

Good morning one and all,

I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.

Any and all help appreciated,

Thanx Gurmi

View 1 Replies View Related

What New Features Of .NET 2.0 Required In A Data Access Layer

Jan 10, 2008

Hi Experts ! I want to use maximum feature of SQL
Server 2005 and ASP.Net 2.0  in making Data Access LayerSuggestions will be welcomed .Thank you
RegardsKAMRAN

View 3 Replies View Related

MS Access SQL Migration To MS SQL Server TSQL

Jun 22, 2006

got some MS Access SQL Code that needs converting into TSQL:

SELECT dbo_qryMyServices.FormsServiceID,
dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1))
AS Completed,
Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS
Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount
FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType
GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName
ORDER BY dbo_qryMyServices.ServiceName;


because it's Access and got VBA stuff in it - IIF and ISNULL, I can't figure out how to make it work in proper SQL.

Any help would be most appreciated!

View 2 Replies View Related

How To Access Global Variable In DTS And Use In TSQL

Mar 21, 2002

I have a VBscript below which works fine, which creates a unique file name for a text source and creates it just fine. What I need is to use that file name in an insert statement in TSQL to load a record to a transaction table. How do I utilize this variable to do this?
Thanks

Function Main()
Dim oConn, sFilename

' Filename format - exyymmdd.log
sFilename = "ex" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".log"

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename
' oConn.DataTarget = sFilename
Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function

View 3 Replies View Related

What Kind Permission Required To Access Sys.sysreferences On SQL 2005?

Jul 20, 2006

In SQL 2000, a guest user can access any system tables in a database, but it looks not the same in SQL 2005. What even worse is the SQL security handles it in a confused way. When I sign on as sa and run:

SELECT * FROM sys.sysreferences

Of course I get what I want. While, if I sign on as a user only has guest privilege, I get nothing. Based on my understanding, if one does not have permission to SELECT, he should get rejucted error message. Otherwise, he should get return data. Now, SQL 2005 does not give any.

More interesting thing is when select * from sys.sysobjects, SQL 2005 return data to both sa user and user with guest privilege. Did MS applied different security on different sys.sys.. table? When a select return nothing, how can we know if there is no data at all, or the user does not have sufficient privilege.

Please help me to get out this puzzle.

Thanks.

View 1 Replies View Related

TSQL - Retrieve All Columns In My MS Access Table Less Two Of Them

Apr 6, 2008

Hi guys,
Working on a MS Access database, I have a table named "myTable" which contains several fields.
I just want to retrieve all the fields (columns) in the myTable, without retrieving Col1 and Col2
What should my SQL string be?

SELECT * (not Col1, Col2) FROM myTable

Thanks in advance for any help.
Aldo.

View 5 Replies View Related

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?

Jan 18, 2008

Hello all.

I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.

I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table

Any help on this would be greatly appreciated.

View 3 Replies View Related

Converting Access SQL To T-Sql

Oct 15, 2007

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")))

View 2 Replies View Related

Converting MS ACCESS Db To SQLExpress

Nov 10, 2005

Is it possible to convert an ms access mdb file to an sqlexpress mdf file?Any help appreciated,Henk Feijt

View 7 Replies View Related

Converting Access Databases To SQL

Apr 18, 2001

what 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 Related

Converting Access 97 To SQL7

Jul 23, 2001

I 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?

View 1 Replies View Related

Converting Yes/No, Access Field To SQL

Oct 24, 2000

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.

View 2 Replies View Related

Converting Query From Access

Dec 6, 2007

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 Related

Converting A View From Access

Nov 17, 2014

I'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

View 5 Replies View Related

Converting Access To Server

Apr 13, 2015

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.

View 1 Replies View Related

Converting Access App To Sql Server

Dec 14, 2005

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.

View 3 Replies View Related

Converting Access To MS SQL Server

Feb 23, 2007

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.

View 1 Replies View Related

Converting Access Db To Mysql

Jul 20, 2005

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

Converting ACCESS And EXCEL Data To SQL

May 14, 2005

Hi,
I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.How may this be done?I am able to create a Data Component with the ACCESS mdb file.
Likewise, how may I convert EXCEL data to SQL2005 table?Thanks.
David
 

View 3 Replies View Related

Access 97 DB And SQL Server 7 - Converting Nvarchar To Int

May 9, 2001

I need help converting an nvarchar value to int. I receive a SQL error when running the following query...

"SELECT DISTINCT [Time Cards].TimeCardID, [Time Cards].Status, [Time Cards].Verification, [Time Card Hours].[Date Worked], [Time Card Hours].[Billable Hours], [Time Card Hours].[Billing Rate], [Time Card Hours].[Overtime Hours], [Time Card Hours].[Overtime Rate], Employees.FirstName, Employees.LastName, [Function].[Function], [Time Card Hours].[Invoiced Hours], [Time Card Hours].[Invoiced Overtime], [Time Cards].[30 Day Grace]

FROM [Function] INNER JOIN (Employees INNER JOIN ([Time Cards] INNER JOIN [Time Card Hours] ON [Time Cards].TimeCardID = [Time Card Hours].[Time Card ID]) ON Employees.EmployeeID = [Time Cards].EmployeeID) ON [Function].FunctionID = Employees.FunctionID

WHERE ((([Time Cards].[30 Day Grace])=-1));"

[Time Card Hours - nvarchar, Time Cards - int]

View 2 Replies View Related

Error In Query Converting From Access To SQL

Dec 7, 1999

Hi,

I'm using a database through ODBC in an application designed on Macromedia Drumbeat 2000, and use the following query for one of the segments:

SELECT DISTINCTROW Books.ISBN, Books.Title, Books.Category, Books.Description, Books.Price, Books.Pubdate, Books.Keywords, Books.UnitWeight, Authors.au_lastname, Authors.au_firstname, Authors.au_midname, Publishers.pub_name, Books.Pub_No
FROM Publishers INNER JOIN (Authors INNER JOIN Books ON Authors.au_id = Books.Au_No) ON Publishers.pub_id = Books.Pub_No

It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:

Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.

Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.

Thanks...

View 1 Replies View Related

Help Converting Query From Access To Sql 2000

Aug 10, 2007

This query from access does not work in sql server 2000. How do I write this in sql to run?

SELECT First(tri_ProcMast.ddesc) AS FirstOfddesc, tri_ProcMast.proccd
FROM tri_ProcMast
GROUP BY tri_ProcMast.proccd;

View 2 Replies View Related

Converting IIF In Access Query To SQL Server

Jul 20, 2005

I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com

View 1 Replies View Related

Help With Converting Access VBA To Appropriate SSIS Solution

Mar 20, 2007

Hi

I have created a package which imports a unit of measure table. I now want to populate more rows in the same table from itself. In Access VBA the below code extract does the first part of the job I need to do. Can anyone point me in the right direction to convert this into an efficient SSIS solution.
SQLstr =€?SELECT [UOM conv].[Short Item No], [UOM conv].UOM, Count([UOM conv].[UOM 2]) AS [CountOfUOM 2] €œ & _
€œFROM [UOM conv] €œ & _
€œGROUP BY [UOM conv].[Short Item No], [UOM conv].UOM €œ & _
€œHAVING (((Count([UOM conv].[UOM 2]))>1)); €œ

Set RsMoreThanOne = db.OpenRecordset(SQLstr)
With RsMoreThanOne
While Not .EOF
SQLstr = "SELECT [UOM conv].* FROM [UOM conv] WHERE ((([UOM conv].[Short Item No])=" & ![Short item No] & ") AND (([UOM conv].UOM)='" & !UOM & "'));"

Set RsSql = db.OpenRecordset(SQLstr, dbOpenSnapshot)
RsSql.MoveLast
x = RsSql.RecordCount
Set rs = db.OpenRecordset("UOM Conv")
rs.Index = "PrimaryKey"
RsSql.MoveFirst
Item = RsSql![Short item No]
For y = 1 To x
Um1(y) = RsSql![UOM]
Um2(y) = RsSql![uom 2]
Fct(y) = RsSql!factor
RsSql.MoveNext
Next y
For y = 1 To x - 1
For k = 1+y To x
rs.Seek "=", Item, Um2(y), Um2(k)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(y)
rs![uom 2] = Um2(k)
rs!factor = Fct(k) / Fct(y)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(k) / Fct(y)
rs.Update
End If
End If
rs.Seek "=", Item, Um2(k), Um2(y)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(k)
rs![uom 2] = Um2(y)
rs!factor = Fct(y) / Fct(k)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(y) / Fct(k)
rs.Update
End If
End If
Next k
Next y

RsSql.Close
.MoveNext
rs.Close
Wend
.Close
End With
db.Close

Regards

ADG

View 3 Replies View Related

Converting A MS Access Query To SQL Stored Procedure

Nov 9, 2001

I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure

CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;

here "MessageID" is a run time generated parameter, and is not a field in the database.

thanx

View 1 Replies View Related

SQL 2012 :: Converting Access Queries To Views

May 13, 2014

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 Replies View Related

Recommendation For Tools Converting Access Databases To SQL

Aug 21, 2006

Hi,We have several Access database that we would over time plan to convertto SQL. I am looking for a tool that works better than the upsizewizard in helping with the transition - I am looking for somethingreliable yet reasonably priced.Thanks in advance for all your helpKR

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved