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 RepliesHello, 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.
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
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 RelatedHi everybody
Could anyone tell me how i can Trace or debug my TSQL Script
like High level Programming Languages line by line? is there any way to do that at all?
Kind Regards.
Hi there,
we are porting a huge PL/SQL(on Oracle 10g) application to TSQL (SQL2005) right now and we will offer a test drive for download. In Oracle we could "obfuscate" the PL/SQL code with a hash, so nobody can see the PL/SQL statements after the encryption. Is there something like that for SQL Server 2005 as well which can not be overidden by a local admin (like with changing a password for example)? We have spent 5 years in writing SQL code (it is huge) and don't really want to spread the source all over the internet. Any help is appreciated.
Niko
Hi,
I'm new to this SQL thing, and I need some help with s stored procedure/function.
First, the scenario:
We are a social service agency, and like all such organizations, we have requirements for perodic reports about our clients. There are lots of them, but if I figure how to do one, I think I can apply the theory to the others.
First, this is some sample VBA code I've tested and which proved satisfactory for the basic task, just for a form. It is passed a date (dteDOP), then adds 3 months, (quarterly report)and loops until it exceeds the current date, thus generating the a due date for the the next report.
'initialize the due date
dteDueDate = dteDOP
'Add quarterly intervals, starting with (DOP + 3 months), until
'you exceed today's date
Do Until dteDueDate > Date
dteDueDate = DateAdd("m", 3, dteDueDate)
Loop
'set a text box to the next due date after today
Me.txtNextQuarDue = dteDueDate
Obviously, this isn't really a function at the moment, but it worked as a test of the logic, with instantly visible results.
This works, but I'd like to do it on the server end, so I can send out notifications. How would this be accomplished as an SQL stored procedure/function? Obviously, for that I'll need to again do a comparison of the current date with the due date for timing concerns, but that should be relatively simple. Also, I know that the '@' symbol is somehow part of variables in SQL Procedures/functions, could you give me a very basic explanation of this, especially the difference between @xxx and @@xxx?
Thanks in advance,
Stephen
-----------------------------------------------
-----------------------------------------------
Gary Getsum: What happened to my mule?
DM: It's dead; It got stung by a giant wasp.
Gary: Can't I heal it?
DM: I'm sorry, I know you were fond of the mule, but it just got attacked by a wasp the size of a Volkswagen!
Gary: So?
DM: Dude, look- you're going to have to carry all your own treasure now... Your mule gives new meaning to the term "Puff Daddy".
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
Hi:
View 7 Replies View Relatedgot 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!
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
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.
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
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.
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")))
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 RelatedHi,
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
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]
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...
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;
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 RelatedHi
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
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
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