Hello,
My problem is this. I did a dts of my old access database to a new sql 7
db. I used Access 97 as the frontened and connected the two databases using linked tables. I made sure all the indexes and such were correct in the SQL database before linking + made sure all the security was corret. When I open a table in the access frontend that is linked to the sql backend, I cannot update or change any data even though I am part of the NT administration group which is given full permissions to the database. Any reason why?
Hello, My problem is this. I did a dts of my old access database to a new sql 7 db. I used Access 97 as the frontened and connected the two databases using linked tables. I made sure all the indexes and such were correct in the SQL database before linking + made sure all the security was corret. When I open a table in the access frontend that is linked to the sql backend, I cannot update or change any data even though I am part of the NT administration group which is given full permissions to the database. Any reason why?
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Collections;
namespace TimeTracking.DB { public class sql { OleDbConnection conn;
// //the constructor for this class, set the connectionstring // public sql() { DBConnectionstring ConnectToDB = new DBConnectionstring(); conn = ConnectToDB.MyConnection(); }
// // // public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment) { int m_ID = ID; int m_Week = (Convert.ToInt32(Week)); int m_Year = (Convert.ToInt32(Year)); string m_Date = Date; string m_Project = Project; int m_ProjectID = new int(); string m_Action = Action; int m_ActionID = new int(); Single m_Time = (Convert.ToSingle(Time)); string m_Comment = Comment;
// //get the project ID from the database and store it in m_ProjectID // OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject" + " WHERE (((tblProject.Project) LIKE @Project))", conn);
// //get the action ID from the database and store it in m_ActionID // OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction" + " WHERE (((tblAction.Action) LIKE @Action))", conn);
finally { //close the connection if (conn != null) { conn.Close(); } } } } }
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
If I have a lot of dropdowns of similar type but only differ in one field, is there a way to set parameters so that the dropdownlist can specify what the parameter values are? For example dropdowns: expenseaccount, incomeaccount, assetaccount SqlDataSource: SelectCommand="select accountid,accountname from accounts, accounttypest where accounts.accounttypeid=acounttypes.accounttypeid and accounttypes.accounttypename=? order by accountname" <SelectParameters> <asp:Parameter Name="accountTypeName" Type="string" /> </SelectParameters> ------- I would like expenseaccount to be able to specify "EXPENSE", incomeaccount to specify "INCOME", etc... Or is sqldatasource reusage bad practice? (damn vs2k5 colors)
Hi all. We have a mix of informix and mssql server and I want to knowif something we do in informix has an analogous feature in MSSQL. Wecan define a "row type" in informix, like so:create row type name_1(fname char(20),lname char(20));The when we create any table that includes a first and last name, wedo so using this row type like so:create table sometable(name name_1, some column,...etc)This allows us to set a standard for certain common fields and avoidshaving different developers build the same type of field in more thanone way, different lengths, etc.Is there a similar function in MSSQL server?
Alot of people complain, legitamately, that they wish to remove columns from the SSIS pipeline that they know are not going to be used again. This would help to avoid the "clutter" that can exist when there are alot of columns in the pipeline.
If you are one of those people then click-through below, vote and (most importantly) add a comment. The more people that do that - the more likely we are to get this functionality in a future version.
SSIS: Hide columns in the pipeline https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=252462
Can some participants in this forum provide ideas on suggested training for somebody with a basic knowledge of SQL (started working in this field 6 months ago and still feel like a newbie beginner).
I assume I should focus on getting T-SQL nailed down before delving into the SSIS piece of SQL Server 2005?
Maybe there are a few companies that seem to give good practical knowledge and are located in the greater Los Angeles metro area?
Is it better to get certified through a 1 week or 2 week course via companies like Trainingcamp - does that really give you the practical skills to jump up several levels at one does at work?
Hope I can get some suggestions or direction on where I could best post this question for answers.
Hi all. We have a mix of informix and mssql server and I want to know if something we do in informix has an analogous feature in MSSQL. We can define a "row type" in informix, like so:
create row type name_1(fname char(20),lname char(20));
The when we create any table that includes a first and last name, we do so using this row type like so:
create table sometable(name name_1, some column,...etc)
This allows us to set a standard for certain common fields and avoids having different developers build the same type of field in more than one way, different lengths, etc.
I have a user that says that after she installed the ActiveX control for printing, that she can no longer export the reports. Has this happened to anyone else? What settings do I need to check on her machine to fix this problem?
I am using a FTP Connection Manager and the configuration setting for it is being set in the package configuration xml file. So the xml file contains the Ftpserver, FTp server username and password. The package is picking up the values from the xml file and is executing successfully. I have to do this because I was not able to provide an expression to the Connection Manager Server Password property.
Now, I want to pick up the ftp details from a database table and set it in the xml file during runtime. Is this possible? OR something like using the
Kindly look at the items in bold. Is this possible? Then I can set the value of the variable in the package before the FTP connection manager task is executed.
After a period of use (typically 1-2 hours), an application using SQL Server Express stops writing to the database, whilst still continuing to read from it. As far as I can tell, no error message is generated by any operation. The application is written in VB Express and is executed on several machines, with SQL Server Express on a central machine. Re-starting the application on the affected machine restores normal operation.
The problem on one machine does not cause a similar problem elsewhere at the same time (hence it appears to be an issue with the relationship between the application and SQL Server)
This does not appear to relate to the response to Q asked by sgcook on 9 may as we are not using user instances, but only a bare minimum connection string.
I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime. 2. I cannot use backup and restore option either.
OS: Windows XP Pro, SP2 SQL: SQL Server 2005 (Server 9.0.2047) SP1 VS: Visual Studio 2005 RTM.050727-4200 --------
After upgrading our SQL Servers to SP1 we have lost the ability to debug .net CLR stored procedures. The debugging process worked fine until we installed the Service Pack. All other features we use test out fine after the upgrade. The only and very important feature that does not work is debugging CLR procs. In fact this problem is happening in every environment we have. Be it SQL2005 running on Windows XP SP2, Windows Server 2003 SP1, Window Server 2000 SP4. SQL Server remote or local on the machine doing the debugging. All 5 developer workstations with Visual Studio in virtual machines or installed on in base OS are behaving exactly the same. Every developer on our team has the exact same problem regardless of their configuration after the upgrade to the service pack 1. All developers and environments were working correctly before the upgrade.
Even building a new SQL box from scratch and installing all tools and creating a new clean database we fail with same error.
We have verified CLR Debugging is enabled for the database connection. We have dropped the connection and recreated it.
Here is an example of the output from the output window:
This is the output up to the CLR call
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0). The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0). Auto-attach to process '[1072] [SQL] sql_test2' on machine 'sql_test2' succeeded.
Output AFTER the CLR Call
Auto-attach to process '[1072] sqlservr.exe' on machine 'sql_test2' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'CIS_Release6a', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILMicrosoft.VisualBasic8.0.0.0__b03f5f7f11d50a3aMicrosoft.VisualBasic.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.
Program Locks up with blank screen and hangs. If we stop the Debugging then the correct results will appear in the output window.
<CIS><Header><MessageType>FosterParent</MessageType><Status>-1</Status><ErrorMsg><Code>-100</Code><Description>Requested Record Not Available</Description></ErrorMsg></Header></CIS>
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String Dim ss As Integer = seconds Mod 60 Dim mm As Integer = (seconds - ss) / 60 Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss) End Function
Can anyone help me out? I am not that familiar with VB.
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
Please consider enhancing SQL 2005 Reporting Services to provide the ability to disable Clickthrough Reports (Live Links) at the Report and Export Function level. Today you can only enable or disable Clickthrough Reports at the View/Model Level for all Report Builder reports.
Is there a way to link an Access 97 database to a SQL database which would update weekly as changes in the Access 97 database were made? In fact, the SQL database will be just a copy of an Access database. If there were tables added in the Access database these tables should also be added to the SQL database as well.
Hello,I'm in need of a little assitance using MSAccess with an SQL Server 2000 database backend.I currently have an application populates an Access database,but I would like to be able to send this data directly to the SQLServer also. Potential solution include the use of ADO/ADOX to make aconnection, a Pass-through/Update query to send the datato an identical database schema on the SQL Server, andstored procedures to make everything efficient. I don't have muchexperience with any of these technologies, but would greatlyappreciate any help and /or code snippets to get things moving.Thanks,Daryl
I need to be able to update a database in SQL Server Express from an Access database that a user will be uploading. Can anybody give me some suggestions about how to accomplish this besides reading in each record and updating the SQL server one record at a time. David
I am trying to convert code I have working for access to work with SQL. fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting. System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'. And here is the stack trace (which I don’t know how to read except for the line the error is on) [SqlException: Line 1: Incorrect syntax near '?'.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:Inetpubwwwrootwebrootgoodellwebadmadm_contacts.aspx.vb:306 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1315 here is my code.
Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?" Dim SqlConn As New SqlConnection(ConnStr) Dim Cmd As New SqlCommand(editSQL, SqlConn) Cmd.Parameters.Add(New SqlParameter("@fldName", nameEdit.Text)) Cmd.Parameters.Add(New SqlParameter("@fldEmail", emailEdit.Text)) Cmd.Parameters.Add(New SqlParameter("@recNum", recNum))
SqlConn.Open() Try Cmd.ExecuteNonQuery() Finally SqlConn.Close() End Try Response.Write("recNum " & recNum & " <br>") Thanks Michael
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
Is there a way to link an Access 97 database to a SQL database which would get updated as changes in the Access 97 database take place? In fact, the SQL database will be just a copy of an Access database. If there were changes made to the fields in the Access database these changes should be made to the entries in the SQL database as well.
I finally added WinXP SP2 to my system yesterday (along with a lot of other security updates/patches through Automatic Updates) and now my backups and data exports are failing. Further, when I open my "Enterprise Manager" and try to export data ("All Tasks-Export-...") using the Import/Export Wizard, it won't let me select the "Export From" database.
Can someome please advise what the equivalent query would be inMicrosoft SQL Server ... I've tried a number of combinations with nosuccess ... Thanks, Ralph Noble (ralph_noble@hotmail.com)================UPDATE INVENTORYINNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)SET INVENTORY.DATE_PURCHASED = "20050127"WHERE (((SALES.SOLD)="20050127"));
hi guys, my prblem is that I cannot update sql query against MS ACCESS using C#. Everytime wen i do so, i got an exception (at runtime) as 'UPDATE syntax not correct'. I don find any error in my 'update' syntax.
I can successfully run other dbase operations like insertion, deletion & all.; except Updation.
But, i can successfully run the same update query in the 'sql query tab' of MS ACCESS, and is executed successfully.
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert. Can some one Help me how can i do it.. THX Sorry for my bad englisht
I am no longer able to manage SQL Server at the server explorer in Visual studio. i.e. create, modify and delete for Diagrams, Tables, Views, Stored Procs & Functions. Further more, it missing Diagram and function folder. I can only view or read data.
I just updated MDAC on one of my machines and I'm now having trouble accessing my SQL server. It's a local connection using SQLOLEDB in a VB dll. It was working fine before the update and now I get:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/cms25/CMS.asp, line 33
Most of the information I can find on this refers to TCP/IP connections but the SQL server is local. My connect function looks like this:
I have a system that has the tables on an SQL Server with the front-end on a Microsoft access database. They are connected via odbc and the tables are linked to the access front-end. The odbc connects to the sql server via a user created for the purpose that only has rights to this database. This user has rights set so that they can access/alter any data in the database.
When my users try to alter data on some of the linked tables they get "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." This does not happen to every table only on some of them.
This happens even when there is only one user accessing the system. If I log into the enterprise manager I can alter those tables/ records without any problem (as some articles I’ve read say if the problem is connected to a corrupt record its not possible to then alter that record and sql server should give me an error message instead).
I have tired. - Compact and repairing the access front-end. - Deleting the links in the access front-end then re-linking them. - Copying the objects from the access to a new blank access db. - I have tried altering the account the odbc uses to login to the SA account. - I have asked the server admin to do the 'compact and repair' on the s.q.l server, shrink I believe it is, as some articles suggest this could solve the problem. - The admin has also tried this on the transaction files (so he has told me).
None of this has worked.
I'm stuck. I don't have any training on SQL server (nor will I be able to have any as I gather the training budget may be needed to fill some finance holes). If anyone has any idea of the answer to my problem or could point me in a direction that I could try investigating I would be very grateful. I have asked the admin if it’s anything to do with transactions, he has told me he does not know how to see those but he will try to find out if we can. Am I shooting in the dark or does this sound like a transaction/process locking problem? Is there something I should be looking for?
I am trying to update some SQL-linked tables in my Access database by repoiting the existing linked tables to a new datasource. The problem is, when I go to select the machine data source where the table sits, I get an error message saying the MS Jet Database can't find the object. This is because when Access creates the linked table, it replaces the period in the <schema>.<table_name> with an underscore. So when I go to update the links, it is essentially looking for the new table with the wrong file name.
I have about 80 linked tables to update and I haven't been able to figure a work-around. HELP PLEASE!
