I have used the Upsizing Wizard in Access 2003 to convert a database to SQL. When I open it using SQL 2000 Desktop edition, I am finding a problem with the views and stored procedures.
When I open a query in design view, the tables are displayed in the upper pane without the details of the individual fields. All I get is a tick box next to *(All Columns)
If I create a new table, the same thing happens. If I create a new database, then make a table within it, any queries behave normally, with all the fields displayed.
I would be grateful for any ideas
Am very new to MS SQL adminstration Can anybody help me out how to work on Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A just for the practice.
The activity which am going to workout on MSDE is below.
How to install SQL(on XP) How the layout will be(like if i insall MSDE what are all Application will be and how they depends on each other) How to create/delete tables if so, how can we do it either by GUI or CUI
Is it possible to install SQL 2000 desktop without installing a databaseserver? I need to have the interface to access my database servers, butdon't want the overhead of a server and the service on my laptop.Possible?ThanksDoug
The sample application provided with Access Database Synchronizer fires a "A request to send data to the computer running IIS has failed. For more information, see HRESULT." exception on my WinCE 4.2 device.
I debugged it with VS2005 to see what was going on and found that the problem resides in the line:
m_connDesktop.Pull("MSysObjects","SELECT Name FROM MSysObjects WHERE Type=1 and flags=0",accessSysConnectString,RDATrackOption.TrackingOff,"Err_MSysObjects");
It seems that connection m_connDesktop is ok but calling Pull method fails.
I've verified that SSEvAccessSync service is started when ActiveSync detects device connection and that firewall rules are correctly updated.
Also, i verified my System.mdw and i discovered that there is no "MSysObjects" table (only MSysGroupList, MSysGroupMembers, MSysUserList and MSysUserMemberships queries are there).
Do you think the problem is there?
How can i solve it without damaging System.mdw?
I cannot figure out what's the purpose of this line of code, can you help me?
I want to know if there is Desktop edition for SQL 2000 Server and if so does it comes bundled on the same CD like it did for MSS 7.0.Also is there a limit to the size of DB's that can be build with it. Thanks a Bunch
How do I upgrade my local sql server 7 to sql 2000 ? Do I just put in the sql server 2000 CD and run it or is there an upgrade CD ? I have lots of stuff on my sql 7 and don't want to lose it.
Here is what I have, but I'm not confident it is correct: CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
hI I M VERY MUCH NEWTO ASP.NET +SQL SERVER U CAN SAY I JUST INSTALL BOTH WHEN I I FIRST INSTALL QUICKSTART TUTORIAL OF MS.NET2003 IT ASK ME TO COPY MY DATABASE file to sqlserverosql -E -S (local)NetSDK -i InstPubs.sqlosql -E -S (local)NetSDK -i InstNwnd.sqlosql -E -S (local)NetSDK -i Portal.sqlosql -E -S (local)NetSDK -i Grocer.sqlwhen i tries to do that it give me the following errors [DBMSLPCN]SQL Server does not exist or access denied.[DBMSLPCN]ConnectionOpen (Connect()). well my sql server name is www same as my computer name (www)services is sql servermy computer is running xp sp2and i m running .net1.1also please tell me how to Granting Access to the ASPNET usermy asp.net user name is ASP.NETif i have to reinstall sql server 2000 tell me what step i should follow to configure it correctlyi m waiting for ur QURU's answers hopefully soonhave a nice dayHUssain
I have a few questions about the SQL Server Desktop Engine (MSDE), sorry there are so many ..
1. Which software products does this come with ? 2. If I create a SQL Server Database and want to distribute it to clients, how do I actually do it with MDSE ? 3. If I need to "upgrade" my database at some point in the future (that may contain data at a client site) how would I go about getting all the data out - would I need to write an external app ? 4. What are it's pros and cons when compared to installing full SQL Server on a client site ? 5. I've heard that the preformance of an MSDE Database degrades severely after more than 5 concurrent connections, is this just due to the performance of the product or is it something to do with the licencing model ? 6. Is it possible to write applications in, say VB, to alter the database once it has been installed on client site e.g. modify/create tables, indexes, stored procedures ?
Has anyone had trouble accessing SQL 2000 desktop install over the LAN on SBS 2003? I cannot access my installation from the same domain, yet when I install it on a machine running something other than SBS 2003 there are no problems. Everything seems shared correctly, and I have tried loggin on to the remote machine as Administrator with no success. Any suggestions would be appreciated.
Can someone advise me if this is basically SQL server 2000 and will work effectively to simulate sql server on a stand alone development machine ? Is this what its meant for?
My question is, is it possible to connect to an SQL database using the MSDE 2000 SP3a edition over the internet? And if so, how do I set it up? Thank you in advance.
Trying to install Microsoft SQL Server 2000 Desktop Engine on my laptop. I downloaded the software and opened it to a folder on the C drive. Then from the command prompt I entered this:
The program starts to install and seems to get near to the end of instal and then the progress bar starts going backwards and nothing gets installed....any ideas of what I'm doing wrong....I really don't know anything about this at all.
I have SQL Server 2000 installed on my laptop and am trying to install an instance of MSDE. I keep getting the error message: "The installation of SQL Server 2000 Desktop Engine has failed. Please see the Getting Started manual for further instructions."
I have done this installation before on other machines. Something must be corrupt on my machine (?). The error message is not very enlightening. Any ideas?
Hi there,I sincerely hope that someone out there can help. I have twoinstances of the SQL 2000 Desktop Engine running. One is on my localmachine for development and the other is on another machine on ournetwork which is the production environment. I have built an Access2003 front end application which connects to this database. Thisworks fine locally, as you would expect. I successfully installed thedatabase on the production machine and am able to connect to it viaAccess 2003 (using the Data Link Properties window) and from thirdparty database manager software (similar to Enterprise Manager). I amnot able to to connect to the database via my application.I am using the "sa" account with a strong password. This is myconnection string:strConnection = "Provider=sqloledb;DataSource=server02;UserId=sa;Password=strong;Initial Catalog=Test"The error I'm getting is:"Connection cannot be used to perform this operation. It is eitherclosed or invalid in this context."The connection string is the only thing that changes in my code when Iswitch from my local to my production database. Is there some reasonthat I can't use the "sa" account in this fashion that I'm not awareof? I'd rather not use integrated security for simplicity's sake asthis is a small, internal application. Also, I would have thoughtthat if that was the issue, I couldn't use "sa" at all, even locally.I'm going to post to the Access group as well but thought someone heremight have some advice to offer as well.Thanks,Barb
There are special instructions for installing service packs on desktop edition installations -- command line options etc. How do you install hotfix roll-ups? Does build 2187 apply to desktop edition?
How do I add login user ASPNET to my SQL 2000 Desktop Engine Server?. I can not find the "Server Manager" in the Start - Programs. Is there any other way to get around adding ASPNET login to the SQL server so I can test my VB program. I got the error: Login failed for user 'com_nameASPNET'.
Dear All, i have a question abt winCE 4.2 and SQL server CE. i am using VB.net of Visual Studio 2005 My platform is using a PDA with winCE 4.2 and SQL server CE. The Host program is using dbf files on desktop side.
I got a problem of how to sync / read the sql CE data from a windows application.
so, i wanna ask,
1. any method to access the data from winCE data by windows application? or can i convert the sdf file to windows readable files? or any others?
2. Can i use a MDB to sync with SQL server CE? can i synchronize the mobile device which has a SQL Server CE database with the Access database on the desktop?
last question, 3. is that windows CE .net 4.2 not support pocket access (cdb) anymore?
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied. SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then MessageBox.Show("Open") End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
Hi Everyone,Apparently, I was being asked on a question, "Why don't we procure adesktop PC to run MS SQL Server 2000 rather than a buying a server?".From a Management point-of-view, buying a desktop PC is much cheaperthan a server. However, I just wanted to understand that is it aviable solution given the database size is something around 200 GB?Equipping with more memory, more storage and a more powerful CPU on adesktop PC could really taking up the role to support the DBMS?Besides this "sensitive" costing concerns, what will be othersdifference in running the SQL Server 2000 on the two differenthardware architecture? For example, IO rate, reliability, RAID-1support, performance, etc.(Note: The operating system is Microsoft Windows 2000 EnterpriseEdition)Regards,Ambrose
The following is my code for Access... can someone help me convert it to sql: My Connectionstring is "server=(local);database=Database;trusted_connection=true"
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> Sub btnLogin_OnClick(Src As Object, E As EventArgs) Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim intUserCount As Integer Dim strSQL As String strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _ & "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _ & "AND password='" & Replace(txtPassword.Text, "'", "''") & "';" myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & Server.MapPath("login.mdb") & ";") myCommand = New OleDbCommand(strSQL, myConnection) myConnection.Open() intUserCount = myCommand.ExecuteScalar() myConnection.Close() If intUserCount > 0 Then lblInvalid.Text = "" FormsAuthentication.SetAuthCookie(txtUsername.Text, True) Response.Redirect("login_db-protected.aspx") Else lblInvalid.Text = "Sorry... try again..." End If End Sub </script>
Dear All, I would like to convert from Access To SQL DB undervisual Studio.Net 2005... How can I do it easily, or if there any software to do this automaticlly, please your help.. Awaiting your valuable reply. Many thanks in advance for your cooperation and continuous support....
I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.
The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.
Here is the basic code:
The code that causes everything to error is commented out.
Private Sub CopySQLRecordSet(ByVal stTableName As String, _ Optional ByVal blIdentity As Boolean = True, _ Optional ByVal stSearch As String = "") Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Set cat = New ADOX.Catalog Set tbl = New ADOX.Table Dim adoCommand As New ADODB.Command, stCommand As String Dim adoSQLCommand As New ADODB.Command Dim errorString As String
On Error GoTo ErrorCopying PrgPart.Value = PrgPart.Value + 1 If blCancelPressed Then End '???fix later cat.ActiveConnection = db1 'This doesn't seem to work with our normal settings for spectrumDbase tbl.ParentCatalog = cat tbl.Name = "dbo_" & stTableName tbl.Properties("Temporary Table") = False 'possibly, this line will work for Oracle as well tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN= ;DATABASE=database;" tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName tbl.Properties("Jet OLEDB:Create Link") = True tbl.Properties("Jet OLEDB:Table Hidden In Access") = False tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False cat.Tables.Append tbl Suspend 1 adoCommand.CommandType = adCmdText adoCommand.ActiveConnection = database adoSQLCommand.CommandType = adCmdText adoSQLCommand.ActiveConnection = rsDbase
'spectrumDBase.BeginTrans 'stCommand = "BEGIN TRANSACTION " & vbNewLine _ ' & "go" & vbNewLine 'If there is an identity field in the table, it must be temporarily disabled 'to insert from an foreign DB. 'If blIdentity Then ' stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine 'adoCommand.CommandText = stCommand 'adoCommand.Execute 'End If
'insert the records from the source table stCommand = "" stCommand = stCommand & "INSERT INTO dbo_" & stTableName _ & " SELECT * FROM " & stTableName & vbNewLine _ & "GO" & vbNewLine
Set adoCommand = Nothing Set adoSQLCommand = Nothing Exit Sub ErrorCopying: 'need to save this to a string so it doesn't reset when Resume occurs errorString = Err.Description ' MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _ ' & "Error: " & errorstring Resume errorCatch errorCatch: On Error Resume Next 'set a log Dim fso As FileSystemObject, fStream As TextStream Set fso = New FileSystemObject Set fStream = fso.OpenTextFile(App.Path & "DBTransfer.log", ForAppending, True) fStream.WriteLine "***Error copying the [" & stTableName & "] table." fStream.WriteLine " Error: " & errorString fStream.WriteLine " " fStream.Close Set fStream = Nothing Set fso = Nothing 'clean up adoCommand.CommandText = "DROP TABLE dbo_" & stTableName adoCommand.Execute If blIdentity Then 'reinsert identity property adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF" adoSQLCommand.Execute End If Set adoCommand = Nothing Set adoSQLCommand = Nothing End Sub
Who convert access dba to ms sql 2000 standard. I try with access conversion, but some buttons commands and rules donīt work. The dba contains tabs, forms, relīs, macros, modules and relations with tabs and examination images. Itīs possible convert everything without damage or transform this itens? The dba contains critical information about identification and personal clinic story about patients, but the access capacity itīs out (2GB) and i need to expand this dba.
I have some urgency to this problem, itīs depends to buy a windows 2008 server and mssql server 2008.
Hello does anyone know how to convert this access code to sql. Also is there a program out there that will do it for you.
IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[HealthBenefits]![medplan.Bi-WeeklyURBio-Rad]*26,[HealthBenefits]![medplan.Bi-WeeklyBio-Rad]*26) AS MedicalCostER, IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[medplan]![Bi-WeeklyUREE]*26,[medplan]![Bi-WeeklyEE]*26) AS MEdicalCostEE
I am being asked to convert this access query into sql server 2000. Access query SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum FROM tri_offnotes;
I'm new to SQL and am not familiar with which function replaces the InStr access function.
