Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Converting Yes/No, Access Field To SQL

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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Access Time Too Slow After Converting A Field Fron SmallInt To Int
:confused: Hi everybody, I'm in a middle of something weird to me, I hope not to You :). I have a Table in a Database that use to hold around a millon of registers. and It had been working very well. My problem actually began when I changed a field Data type, from SmallInt to Int. After that, accessing that table is too slow, I've already reindexed the table, and even already return the field Data type to SmallInt, and reindexed again. The problem continues.

I saw by the Query Analizer that the problem is mainly when I try to update a register in the Table where idDetail = @idDetail.

It last 3 seconds... when, before the issue it was inmediatly. In task manager the SQLSRV. exe only uses 25% of processor, but memoy use is really increased. (This update has to be done several times per minute)

I tested with a new empty table. I inserted few records... and obviously it worked as fast as it used to do. But with the table with real data It doesn't

Any suggestion it'll help me.

Thnx in Advance


View Replies !   View Related
Question About Converting Bigint Field To Int Field
We made a poor decision a long time ago when designing our databasestructure. We used bigint data types as the identity keys for many ofour base tables. For many reasons I would like to change these fieldsto int at the largest. The largest data in these fields is around200,000. I know that int can easily store this.What should I be worried about when changing these fields from bigintto int? If anything. Your help is appreciated. I did severalsearches without much luck.

View Replies !   View Related
Converting An Integer Field Into An Identity Field
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?







View Replies !   View Related
Converting From Soul MS-access To MS-Access/MS SQL System

i'm trying to convert a soul ms-access database to a sql enviroment.
I'm testing on a windows 2000 server with SQL2000 and Acces-XP.
I used the 'upsize wizard' to transfer the tables and convert everything else.

I used a lot of code to get things the way we want.

The thing i use much is the listbox , in a dynamically way, when a record is selected another listbox is filled. I use the following code :

Me.list0.RowSource = "SELECT Orders.Ordernummer, Orders.Omschrijving, Orders.Kosten, " _
& "Orders.Opbrengst , Orders.Categorie " _
& "FROM Orders " _
& "WHERE (((Orders.Categorie)=[Forms]![menu_maint]![Keuzelijst0])) " _
& "ORDER BY Orders.Ordernummer;"

As you see i use the selected item from the first listbox as a filter for the second listbox.

Now the problem ... the converted database does not return any data.

Could someone explain to me what i need to change ?
It is working in a soul Access enviroment

Thanks in advance.

View Replies !   View Related
Converting An INT Field To CHAR
I have been asked to investigate the feasibility of converting an Invoice Number field from 6 to 7 digits, and allowing alpha characters. This means CHAR. The Invoice Number is not used outside of the application, but changes would have to be made to the database, and VB code that runs the app. Any ideas on how to proceed?

Any responses would be greatly appreciated!

View Replies !   View Related
Converting To Text Field
Is there a way to convert from varchar to text? I need to concatenate an varchar value into an existing text field. Any help is greatly appreciated.
Thanks in advance.

View Replies !   View Related
Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.
I'm using DTS to import data from an Access memo field into a SQL Server ntext field.  DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View Replies !   View Related
Converting A Date Field To The Month
How can I create a field in a view that takes a date field andconverts it to a month?For instance, if I have a field called "CreatedOn" with a date of'2/22/2007', how can I create another field called "Month" that willsay "February"? Is there a built-in SQL function that does this (likeusing CONVERT) or do I need to write my own?Thanks!Lisa

View Replies !   View Related
Converting A Text Field To Number
I have a table with over a million rows and one of the fields containsamounts of money in text format.What is the most efficient way of converting this field to a numberformat that I can sum on?Regards,Ciarán

View Replies !   View Related
Converting 6 Character To Date Field
I have a 6 char field which has to be converted to a datetime. I thought I had it solved when I did this


Problem is with a date value of 081649
I get 08/16/2049 instead of 1949, where did I goof

View Replies !   View Related
Converting A String Into Datetime Field
Stuart writes "Hi being new to this game
I have have an error when trying to inseet string into a table with datetime field.

the date is not that important its the time that I use in later steps

I am creating a global temp table and then inserting values into it

below is the code
-- create the temp table
Execute ( 'create table ##progsch0
([Time] [DateTime] , '
+ '[' + @day7 + '] [varchar](100) ,'
+ '[' + @day1 + '] [varchar](100) ,'
+ '[' + @day2 + '] [varchar](100) ,'
+ '[' + @day3 + '] [varchar](100) ,'
+ '[' + @day4 + '] [varchar](100) ,'
+ '[' + @day5 + '] [varchar](100) ,'
+ '[' + @day6 + '] [varchar](100) )')

set @Starttime = 'JUL 21,2006 5:30am'

I am doing the insert in this manor becuase the @Starttime
in code actually changes time and a new record in inserted into the temp table.

Set @SQL = 'Insert into ##progsch0 (Time)
Values(convert(varchar,Convert(datetime,'+ @Starttime +'),100))'
execute sp_executesql @SQL

I may to doing this in the completely wrong manor.

Any help would be greatful "

View Replies !   View Related
Access Memo Field To SQL Server Text Field

I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).

I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.

Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.

View Replies !   View Related
Export Access Memo Field To SQL Text Field

Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.


View Replies !   View Related
MS Access Memo Field To SQL Server Text Field
Hi all,

i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.

I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format)  using the SQL Server Management Studio, for

Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.

I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.

Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!



View Replies !   View Related
Converting Date Field From Informix To SQL Nvarchar
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)

Any help is greatly appreciated!


The goodness we share is rewarded twice!

View Replies !   View Related
Converting Binary Field To ASCII File
I'm having trouble converting the data in a binary column (varbinary(max)) into an ASCII text file.

Basically, we store all incoming logs in raw binary format in a table.  Now, I want to grab one of these logs, put it into an ASCII file for use with a test program which will simulate the log transfer using the ASCII file.

Here's what I've tried so far:

select CAST(RawLog as VARCHAR(MAX)) as ASCII from RawLogs where RawLogID=18
select CONVERT(VARCHAR(MAX), RawLog) as ASCII from RawLogs where RawLogID=18

The value displayed is a very short ASCII string, which is definitely not all the converted data.  If I check the length, however, it appears correct:

select LEN(CAST(RawLog as VARCHAR(MAX))) from RawLogs where RawLogID=18  == 
select LEN(RawLog) from RawLogs where RawLogID=18

So, it seems like the display of the non-standard ASCII characters is not handled properly, but the conversion happened correctly.

Next, I tried to output it directly to text file, hoping that would work:

EXEC master..xp_cmdshell 'osql.exe -S localhost -d MyDB -E -Q "select CAST(RawLog as NVARCHAR(max)) as ASCII from RawLogs where RawLogID=18" -o "C:output.txt"'

This produces a very strange file, that is formatted, has some header and footer text, and appears to have a subset of the actual ASCII values.

The only way I've been able to get it to work is as follows:
Copy of the hex values from the binary display:

select RawLog from RawLogs where RawLogID=18

Then, select the column, copy it, and paste it into the following online tool:

This tool produces ASCII which I can copy and paste into a text file.  This works fine, except that it truncates the results, so it's no good.

Can anyone shed some light?

View Replies !   View Related
Oledbcommand Converting Date Field To Character Datatype
im usoin sqlbuklcopy class to migrate dbf files into sql server tables, but i got a proble and its because the format of a field named birthdate  its mm/dd/yyyy and i got this
Cadena = "select SITE_ID ,PATIENT_ID , LOCAL_ID, " & _                    " BIRTHDATE , GENDER  ,  LAST_NAME  , FIRST_NAME from patient "
then i open mi oledbcommand
        Dim cnSource As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Origen & ";Extended Properties=DBASE IV")        Dim cmdSource As New OleDbCommand(Campos, cnSource)
and throw me an exception referring the format of birthdate can i convert in my select statement the field birthdate to a character datatype?

View Replies !   View Related
Converting A Carriage Return To Comma Inside A Field - How?
My problem is I have an address field where the address has been inputed with a Carriage Return (CR) at the end of each line. I need to update this field and change every instance of a CR to a comma as I am to then separate each line of address to separate fields.

Can anybody point me in the right direction to get SQL to recognise for eg the ASCII(013) = CR.

Sample things I have tried are:

Declare @var1
Declare @var2

Set @var1 = ASCII(043)
Set @var2 = ASCII(033)
SELECT REPLACE ('This string!+','@var1','@var2')


SELECT <field> from <table> where <field> like '%ASCII(044)%'

In the above SQL analyser is picking up the characters inside the quote marks literally and not as ASCII code which is what I really want.

Any help at all would be greatly appreciated.

View Replies !   View Related
Converting Access SQL To T-Sql
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.


Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))

View Replies !   View Related
Converting MS ACCESS Db To SQLExpress
Is it possible to convert an ms access mdb file to an sqlexpress mdf file?Any help appreciated,Henk Feijt

View Replies !   View Related
Converting JET SQL (Access) To SSRS
Hello... I'm having trouble  converting this query I created in MS Access to SSRS. I'm still fairly new to SSRS, so...I still dont know much about this program but here is what i got so far. Any kind of help will be much appreciated.  JET SQL: SELECT dbo_HD_Call.Status, Year(dbo_HD_Call!Close_Date) AS
CloseYear, Month(dbo_HD_Call!Close_Date) AS CloseMonth,
Year(dbo_HD_Call.Call_Date) AS CallYear, Month(dbo_HD_Call.Call_Date) AS
CallMonth, dbo_HD_Call.Catg_Code, dbo_HD_Call.Group_Code, dbo_HD_Call.Prob_Code,
dbo_HD_Call.Priority, dbo_HD_Call.Total_Adj, dbo_HD_Call.Call_Num, IIf((dbo_HD_Call!Entered_By=dbo_HD_Call!Close_Staff),0,1)
AS Escl, IIf((dbo_HD_Call!First_Time>dbo_HD_Call!SL_Respond),0,1) AS SLR,
IIf((dbo_HD_Call!Total_Adj>dbo_HD_Call!SL_C_Min),0,1) AS SLC,
IIf([SLR]+[SLC]=2,1,0) AS SLAFROM dbo_HD_CallWHERE
(((dbo_HD_Call.Status)="CLOSED") AND ((Year([dbo_HD_Call]![Close_Date]))="2006")) OR
(((Year([dbo_HD_Call]![Close_Date]))="2007"))ORDER BY
Year(dbo_HD_Call!Close_Date) DESC , Month(dbo_HD_Call!Close_Date) DESC ,
dbo_HD_Call.Catg_Code, dbo_HD_Call.Prob_Code;SSRS: Everything works except the part thats underlined. SELECT     Status, Call_Num, DATEPART(yy, Call_Date) AS "Call Year", DATEPART(mm, Call_Date) AS "Call Month", DATEPART(yy, Close_Date) AS "Closed Year",                       DATEPART(mm, Close_Date) AS "Closed Month", Catg_Code, Prob_Code, Group_Code, Priority, Total_Adj,                       CASE WHEN Entered_By = Close_Staff THEN 0 ELSE 1 END AS Escl, CASE WHEN First_Time > SL_Respond THEN 0 ELSE 1 END AS SLR,                       CASE WHEN Total_Adj > SL_C_Min THEN 0 ELSE 1 END AS SLC, Iif((SLR + SLC = 2), 1, 0) AS "SLA"FROM         dbo.HD_CallWHERE     (Status = 'CLOSED') AND (Call_Date > '12/31/2005')ORDER BY "Closed Year" DESC, "Closed Month" DESC, Catg_Code, Prob_Code

View Replies !   View Related
Converting Access Db To Mysql
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 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 Replies !   View Related
Converting Query From Access
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 Replies !   View Related
Converting Access Databases To SQL
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 Replies !   View Related
Converting Access 97 To SQL7
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 Replies !   View Related
Converting Access To MS SQL Server
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?


View Replies !   View Related
Converting Access App To Sql Server
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

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

Can anyone tell me how to convert this for SQL Server? This is my 1st access to sql server attempt.

View Replies !   View Related
Converting ACCESS And EXCEL Data To SQL
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.

View Replies !   View Related
Converting IIF In Access Query To SQL Server
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

View Replies !   View Related
Converting Access Crosstab Queries To SQL
I am converting an Access database to SQL 7.0 and have a number of crosstab queries. What is the best way to handle these in SQL? Any help is greatly appreciated as I am kinda new to SQL. Thanks!

View Replies !   View Related
Converting Access Query To MS Sql Server Help!!!
I am having problems converting this code to sQl server.

SELECT AOA_ODs_2001_10_08.*, (Not IsNull([PC1].[SpecialtyCode])) Or (Not IsNull([PC2].[SpecialtyCode])) AS IsPrimaryCare,
(Nz([Prac_TYP],"")="90") Or
(Nz([MAJ_PRAC],"")="RET") Or
(Nz([MIN_PRAC],"")="RET") Or
(Nz([PROF_EMP],"")="90") Or
(Nz([DUESCAT],0)=9) AS IsRetired
FROM (AOA_ODs_2001_10_08 LEFT JOIN PrimaryCareSpecialtyList AS PC1 ON AOA_ODs_2001_10_08.MAJ_PRAC = PC1.SpecialtyCode) LEFT JOIN PrimaryCareSpecialtyList AS PC2 ON AOA_ODs_2001_10_08.MIN_PRAC = PC2.SpecialtyCode;

View Replies !   View Related
Converting An ASP/MS Access Site To ASP/SQLserver
To all,

I am looking for resources of information on this topic, any direction would be useful.
I am currently in the final stages of an extensive intranet site and website that share the same database resource
in MS Access 97. I told upper management that the design should be done in something more robust than MS Access 97.
There response was basically, "We'll cross that bridge when we get to it." Well, the bridge is here and it is in pretty bad shape.
I have heard horror stories of MS Access 97 .MDB files that get corrupted because they were not intended for mass amounts
of data and access. I would like to move to an SQLserver based data resource, but I have no idea what this will take and how much
of the ASP code I will have to convert. Thanks for any feedback you might offer.

View Replies !   View Related
Error In Query Converting From Access To SQL

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.


View Replies !   View Related
Converting Access DB Code To Tsql--Help!!!!!
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 Replies !   View Related
Access 97 DB And SQL Server 7 - Converting Nvarchar To Int
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 Replies !   View Related
Help Converting Query From Access To Sql 2000
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 Replies !   View Related
Help Required Converting From MS Access SQL To TSQL
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 Replies !   View Related
Help With Converting Access VBA To Appropriate SSIS Solution

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)
        x = RsSql.RecordCount
        Set rs = db.OpenRecordset("UOM Conv")
        rs.Index = "PrimaryKey"
            Item = RsSql![Short item No]
            For y = 1 To x
                Um1(y) = RsSql![UOM]
                Um2(y) = RsSql![uom 2]
                Fct(y) = RsSql!factor
            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![Short item No] = Item
                            rs!UOM = Um2(y)
                            rs![uom 2] = Um2(k)
                            rs!factor = Fct(k) / Fct(y)
                            rs!calculated = True
                            If rs!calculated = True Then
                                rs!factor = Fct(k) / Fct(y)
                            End If
                    End If
                    rs.Seek "=", Item, Um2(k), Um2(y)
                    If rs.NoMatch Then
                            rs![Short item No] = Item
                            rs!UOM = Um2(k)
                            rs![uom 2] = Um2(y)
                            rs!factor = Fct(y) / Fct(k)
                            rs!calculated = True
                            If rs!calculated = True Then
                                rs!factor = Fct(y) / Fct(k)
                            End If
                    End If
                Next k
            Next y
End With



View Replies !   View Related
Converting From MS Access To Reporting Services
Is there an easy way to convert the queries I have written in Microsoft Access to the Reporting Services query builder, without having to re-write them from scratch?


I've tried re-writing the query from scratch in the Reporting Services query builder (in Visual Studio) and I set up a datasource to connect to the data-tables.  The data-tables are stored remotely, and I can connect to them fine in Access, but when I try to execute the query in Reporting Services, I received this error message:



TITLE: Microsoft Report Designer

Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.







Does anyone have any ideas about what I could be doing wrong?

View Replies !   View Related
Recommendation For Tools Converting Access Databases To SQL
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 Replies !   View Related
Converting Access Qrys With Logical Expressions
I'm upsizing an Access database. Got the data converted, working onthe front end, converting queries to views, but having troubleconverting queries that use logical expressions like the following:SELECT OrderId,Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItemsFROM OrderDetailsINNER JOIN ItemsON (OrderDetails.ClientId = Items.ClientId)AND (OrderDetails.ItemId = Items.ItemId)WHERE (NOT (SitesCustomerTypeId = 2AND ExpressBackorder =TrueAND OrderUrgency = 1 ))GROUP BY OrderId;Can someone suggest a strategy to achieve the same result, ieOrderId,ReadBackOrderItems that I can use in further joins?Thanks in anticipationTerry Bell

View Replies !   View Related
Converting A MS Access Query To SQL Stored Procedure
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure


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


View Replies !   View Related
Problem Converting Access SQL Statment To MSSQL
I am not sure how to convert the following from an Access query to a MS SQL query statement



Apparently, the update statement uses two tables, and depending on the value of the B1PEND.DOC field, will determine whether to update the B1PEND.AWP_CODE to 'A'.

I am not sure how to convert this. Any help would be appreciated.


Mike V

View Replies !   View Related
Problems Converting Data From MS Access To MSSQL
Hi All,

I am converting an old MS Access database to MSSQL. While I do get some data into MSSQL, some weird things are happening that are beyond my capabilities.

The database I am trying to convert exists of several tables. The first few are converted perfectly, but one of the last is giving difficulties. What I do is:
            SET @SQLQuery = 'INSERT INTO TableName ( ' +
                                                   '[field1], ' +
                                                   '[field2], ' +
                                                   '[field3], ' +
                                                   '[field4], ' +
                                                   '[Date], ' +
                                                   '[field6]) ' +
                            'SELECT  ' +
                                                   '[field1], ' +
                                                   '[field2], ' +
                                                   '[field3], ' +
                                                   '[field4], ' +
                                                   '[Date], ' +
                                                   '[field6]) ' +
                                    ' FROM '+ RTRIM(@LinkedServerName) +'...TableName'

            EXEC (@SQLQuery)

I do the same for each table, all with their own TableNames and field names. 80% of the tables have a date field, which is of type DBTYPE_DBTIMESTAMP in Access and datetime in MSSQL.

As mentioned: the first tables are converted perfectly, however one fails with error message:
Msg 8114, Level 16, State 8, Server XXXXXX, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

If I leave the date field out of the query for that table, it all works (obviously, with an empty date column). The weird thing is that most of the other tables have a date column as well, they are the same data types as in this column and the conversion query is very similar as well.

I have Googled on the full error message and on DBTYPE_DBTIMESTAMP alone, but all results I get seem different to what I have. I have tried using convert to put the date in the correct format (format in the date tables is DD-MM-YYYY), but this doesn't help. Would have been funny if it had helped, as all other tables use the same date format, and according to the specs, DBTYPE_DBTIMESTAMP should be automatically converted to datetime anyway (which in all other tables works)...

Basically, what I am saying is: I have 8 extremely similar tables, which I all try to convert using the exact same method, 7 tables succeed and one fails...

Thanks in advance for your help!

View Replies !   View Related

Copyright © 2005-08, All rights reserved