So where I work is thinking about one day moving to SQL server. Right now they have indexed files that aren't normalized with repeating fields in them and lots of repeat data and blank space (so a customer number in one file may be stored literally in 10 other files that are easily realted). In the intrest of saving time and money I think that they will not normalize, index, or anything to any of these files. From what I hear it will be a straight field by field creation for the most part and preserving the primary keys.
My question: I keep thinking this is going to be massive hit on performance and maintaince. How much would converting in such a manner hurt the performance of their database and how much could it potentially add to maintaince?
Hi folks,I was working on MS sql server 2005 evalution where i have built a number of databases. However, i came to discove that the evalution version has expried before i finished my work. Now i have disinstalled the sql server 2005 and installed the Sql express edition.My concern here is how can i keep my databases so they can work with sql express edition?Thank you very much in advance.
Hi, I am developing a project that using the one of the starter kits which use the MS SQL EXPRESS database.The project is almost ready to be launch. few questions:
I am looking for a good host with good support reasonable paid. What is my options if I would like to convert from the current database, to other databases like MySql, MS SQL Server or any? which tools can help with this convertions? thats all, thanks.
So where I work is thinking about one day moving to SQL server. Right now they have indexed files that aren't normalized with repeating fields in them and lots of repeat data and blank space (so a customer number in one file may be stored literally in 10 other files that are easily realted). In the intrest of saving time and money I think that they will not normalize, index, or anything to any of these files. From what I hear it will be a straight field by field creation for the most part and preserving the primary keys.
My question: I keep thinking this is going to be massive hit on performance and maintaince. How much would converting in such a manner hurt the performance of their database and how much could it potentially add to maintaince?
I'm new to SQL server. I've been working with oracle and now need to convert this sentence to SQL Server but I get this error:
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'A'. Server: Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'where'.m_proceso;
My sentence is: /***************** update data_grup A set new_grup_code =( select cod_grup_2 from data_grup_new B where A.cod_grup = B.cod_grup ) where zone = '001' ; /***************** Can anyone help me to convert this to SQL server, I don't know why I get this error. Thanks.
Columns from database have values like 1,5 etc and I'm getting the error: Syntax error converting the nvarchar value '1,5' to a column of data type int. when trying to convert from nvarchar to int: SELECT CONVERT(int, MyNVarChar_column) FROM MyTable What I'm doing wrong? Thanks a lot
I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.
My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error. I am using the following commnd: exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C: est.xml" -U -P -c -r -t'
Is there any way I can tune my query to fix that error?
Public Class DB Dim myDB As New OdbcConnection Dim myCMD As New OdbcCommand Public Sub New() Dim db_server = AppSettings("db_server") Dim db = AppSettings("db") Dim db_user = AppSettings("db_user") Dim db_pwd = AppSettings("db_pwd") Dim DBConnection As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=" & db_server & ";" & _ "DATABASE=" & db & ";" & _ "UID=" & db_user & ";" & _ "PASSWORD=" & db_pwd & ";" & _ "OPTION=3;" myDB.ConnectionString = DBConnection myCMD.Connection = myDB End Sub
Public Function q(ByVal mySTR As String) As OdbcDataReader myCMD.CommandText = mySTR Try myDB.Open() q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConn ection) Catch ex As Exception Err(ex.ToString) End Try
End Function
Public Sub c(ByVal mySTR As String) Try myCMD.Connection.Open() myCMD.CommandText = mySTR myCMD.ExecuteNonQuery() myCMD.Connection.Close() Catch ex As Exception Err(ex.ToString) End Try
End Sub
Private Sub Err(ByVal strError As String) Current.Response.Write("<h1>An error has occurred.</h1><br>" & vbCrLf) Current.Response.Write("Please report to us the error message<br>" & vbCrLf) Current.Response.Write(strError) Current.Response.Flush() Current.Response.End() End Sub End Class
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?
I am trying to convert the following working SQL Server query to query a DB2 database. I am getting the following error: An ON clause associated with a JOIN operator or in a MERGE statement is not valid. SQLSTATE=42972. Can anyone help me convert it? Thanks for your help!
Hi.. I want to convert .dbf files to sql server 2000 tables.. without using any tools. I need to create a different structure for sql server tables other than contains in the .dbf files. May be the dbf files contain only 3 columns. but i need 5 columns and some calculations to determine the values of some fields to insert into sql server table... i need to code this using c# in asp.net.. can u help me? thanks in advance.. Fraijo
I need to switch from MS Access to SQL Server for my database. To set up a development environment I downloaded the free Microsoft SQL Server Express (February CTP version). I installed the required .NET Framework v2, and then SQLExpress. The install was done using all the defaults, and was done successfully. I also downloaded and installed the SQL Express Manager Tool.
The SQL Server was installed on the same machine as my VS.NET development environment. The SQL Server process is now running, and I can connect to the server using the SQL Express Manager Tool. This allows me to view and query the sample databases, but not much else.
To convert my Access .MDB database to SQL Server, I am trying to use the MS Access Upsizing Wizard. The version of Access I am using is Access 2002 on a Windows XP-Professional system. The problem is that Access cannot get a connection to the SQL Server. I tried using the default server name "(local)" and "Use Trusted Connection", but I receive the following error:
Connection failed: SQLState: '01000' SQL Server Error: 2 [Microsoft][ODBC SQL Server Driver[]Shared Memory]ConnectionOpen (Connect()). Connection failed: SQLState: '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver[]Shared Memory]SQL Server does not exist or access denied.
Curiously, I get this exact same error message even if the SQL service is stopped. So I'm pretty sure the problem is that it is not finding the SQL server, and not a security issue.
In order to connect to the SQL server using the SQL Server Manager Tool, you have to provide the actual instance name for the server "COMPNAMESQLExpress". So I tried using this server name in the Access Upsizing Wizard, but this returns the same error message as above except the first SQL Server Error is 53. I also tried using a Login ID and password (using the Windows administrator ID and password, and also the "sa" ID and password) to no avail.
I am at wits end, and can't figure out why Access can't find the SQL Server. Any ideas would be appreciated.
Hi There, I'm new to the .Net environment, and I am currently trying to convert an xml document and its appropriate schema, to a dataset then with this Dataset I would like to load it into the database. This XML file is generated from the Access 2000 format. Then sent over the internet where it is received by an HttpFileCollection object, then the schema and xml file are loaded into a dataset. I have created a new SQL database in which I imported the table required from Access, so in theory the table that the xml is generated from should be the same as the table that the xml is destined for. I am getting the data loaded into the dataset however, when I try to execute the data adapter update command oda.Update(myDS, "MSGLOBAL") it is throwing an Exception, in this exception the message is telling me that there is a problem near the key word 'on'. Now I have added a watch window to monitor the sql statements, now the oda has an insert statement generated from a query builder, however this same query builder will not create either an update or delete command. I hope this is a good enough explanation to get a few answers, or a possible example, it seems to be a topic that is quite difficult to research. Thank you, and if you require more info please indicate this. Damon Http://www.ezywiz.biz
OK, I have some SQL Server 2000 databases that I want to convert to 2005. My question: how do I do that? I tried importing the data, but it only picked up 3 tables out of 7 user tables in one of my SS2K databases. Plus, it did not pick up any stored procedures or any other kind of objects. So I tried restoring from a backup, but predictably it could not do that because the original backup was on a different server. I also tried the upgrade advisor utility that comes with SS2K5, but it could not seem to connect to my SS2K server -- it said it could not find any SQL Server objects on the server, even though I am connected using Enterprise Manager and have been using this server for a long time. Is it looking for 2005 databases? That wouldn't make a lot of sense. Anyway, I am stumped... can anyone provide help on this? Thanks!
I'm trying to convert a 6.5 compatible db from SQL Server 2000 to SQL Server 2005. The issue I'm encountering is that:
1. I cannot simply convert the 6.5 compatible db to SQL Server 2005. I tried restoring from a backup - no success. 2. I tried creating the structure in SQL Server 2005 and then importing the data from SQL Server 2000. Problem is - after importing the data, I then tried creating the primary key and indexes - get a time-out error (which I never got with SQL Server 2000.) I also tried creating the primary key and indexes in SQL Server 2005 FIRST before importing - got errors when trying to import and import failed.
I know this sounds like a data problem but I have no problems working with this 3 gig database in SQL Server 2000.
Can anyone please help? This is a pretty serious problem I need to overcome with the SQL Server 2005 conversion.
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
I am struggling rewriting my query from MS Access' IIF, Then to SQLServers TSQL language. I am hoping some one can give me someguidance. I believe I have the first portion of the query correct butdo believe this requires a "NESTED" argument. This is where I amlost.My Original MS ACCESS Query reads--SELECT DISTINCTROW REGION_TRAFIC.*,IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTRA_VENDOR1])))),IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTER_VENDOR1]))))) AS CPM,[CPM]*[MOU] AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESC;I have tried to re-write this in SQL SERVER as --SELET DISTINCT REGION TRAFIC.*,CASEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTRA_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTRA_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTRA_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTRA_VENDOR1ELSEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTER_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTER_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTER_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTER_VENDOR1ENDAS CPMCPM*MOU AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESCMy challenge is the Case portion of the query and the nesting! I amnot sure if I have the correct syntax or even chose the correctargument for my purpose.Any guidance is appreciated.
Is there any other way besides manually changing and moving ASC Sequels. I was thinking there was a way that will do the translation without having to manually do it. Also if there is a way will it also Move ASC Tables and Scripts to run Data Files?
I'm in search of a nice tool to convert the database from MySQL to SQL Server 2005, where the schemas of both the DB are different. I just want to move the data from one database to another with column mapping..
Can any pls suggest me a nice tool for this issue?
Hello, Finally making the move to ASP.NET! I've been advised that to begin with it is probably best to start using SQL Server 2005 with ASP.NET. All my sites are currently using MySQL. Can anyone advise a way for me to import all the data from a MySQL database to a SQL Server 2005 database. Apologies if this isn't directly related to ASP.NET but any help would be greatly appreciated. Thanks
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
select data.mynodes.value('@Name', 'varchar(50)') Name from #data cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes) Results: Name 43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query(' for $drop in /order/Product/Drops/Drop for $Name in $drop/Area/@Name where $drop/@Number=1 return data($Name) ') from #data Results: 43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013' "05/01/2014"...etc...
My requirement is to convert this column into Datetime ?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;
[code="CASE WHEN isdate( arc.UserField1 )= 1 then CAST( arc.UserField1 as DateTime ) ELSE CAST( '01/01/1900' as Datetime ) END"] [/code]
The results are ;
Date Result 29/09/08 1900-01-01 00:00:00.000 09/06/15 2015-09-06 00:00:00.000 18/03/13 1900-01-01 00:00:00.000 09/10/14 2014-09-10 00:00:00.000
as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?
I have a large (huge) database that I want to copy onto my new slq 2005 test server. I'm leaning toward detaching the data on my 2000 box, duplicating it, copying it to my new 2005 machine, and attaching it. Is it possible that it could be that simple? If not, how is it done? Thanks a bunch for any help or pointers to the articles I was totally unable to find on the subject.
I have got SQL Server Desktop Engine running with 2 database and I need to install the evaluation version of SQL Server 2005 instead. A straight upgrade does not seem to be possible. Does someone know if backing up the databases, uninstalling the desktop engine, installing SQL Server 2005 and then importing/restoring the databases would be an option to look into?
SELECT ROUND ('6.465',2) --- result 6.46 and SELECT ROUND (6.465,2) --- result 6.47 with
It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...
Alright:
SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this: SELECT ROUND('0.285',2) -- 0.28 SELECT ROUND(0.285,2) -- 0.29 SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.
MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:
* how is a string value exactly implicitly converted?
* how exactly does the estimation work, that in case of doubt rounds a value up or off?
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl] ( [ID] NUMERIC(18,0) NOT NULL, [Name] VARCHAR(500), [PartStructureKey] NUMERIC(18,0) NOT NULL, [PartNumber] VARCHAR(500) NOT NULL, [ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
