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 Replies


ADVERTISEMENT

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

MiguelVV

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?

TIA,

 

Barkingdog

 

 

 

View Replies View Related

Converting From Soul MS-access To MS-Access/MS SQL System

Hi,

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!
Thanks

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

convert(datetime,(left(dob,2)+'-'+substring(dob,3,2)+'-'+right(dob,2)))

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))'
PRINT @SQL
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

Hi,

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

Hi,

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.

Thanks.

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
example.



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!



cheers

Dominic

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!

JT


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:

i.e.
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:

i.e.
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?

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 field...how 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')

OR

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.
Thanks!

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.

Thanks!

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

thanks.

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

Converting ACCESS And EXCEL Data To SQL

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 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 Hammondwww.enhanceddatasystems.com

View Replies View Related







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