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.
: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
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.
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?
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.
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!
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.
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!
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
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
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
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 "
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.
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.
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
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!
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!
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: http://www.string-functions.com/hex-string.aspx
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?
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 field...how can i convert in my select statement the field birthdate to a character datatype?
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:
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.
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")))
Is it possible to convert an ms access mdb file to an sqlexpress mdf file?Any help appreciated,Henk Feijt
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) 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
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
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
what is the proper method of taking a database made in MS Access and converting it so that it runs in SQL Server 7?
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?
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?
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
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
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.
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.
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