Urgent Urgent Please.(Access SQL Pass Through Queries)
Jul 6, 2000
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
How can transfer data from SQL Server Database to Oracle Database. I have one table in SQL SERVER called as Employee ColumnName DataType --------------------------- Emp_ID Int Emp_Name Varchar(25) Salary Int
Primary Key : Emp_ID
Simillarly i have same structure table called as Employee_details in Oracle Database
In oracle database there is allready some data.
I need to transfer data from sql server where if Emp_id exists then i need to update the Salary column in oracle taking value from Sql server database.
If Emp_id doesn't exists in oracle database then i need to Insert the row from sql server database.
Simillary i need to check whether Emp_iD exists in sqlserver or not, if doesn't then i need to delete the record from Oracle database.
How can i do this DTS package, if some one can help me i would be greatfull. I had been traying DataDriven Query in DTS but i am not getting clue how to do it.
Can anyone help me how to extract data from two different SQL servers? Both the servers are alredy registered on my local machine.I need to select data from one database on Server1 and insert the same in a table in another database of Server2.Names of the servers are abc-efg-hij and 143.89.55.67. Thanks for any help!
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++ A connection could not be estabished to server--Timeout expired Please verfy SQL Server is running and check your SQL Server registration properties and try again. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
When I am giving like http://localhost/ReportServer the following message is coming in explorer. What I have to do please help
Reporting Services Error
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) Get Online Help
hi, I have settup up sql mail and did the following: 1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook 2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services 3. I used the profile that I created in outlook to test the sql mail but got an error: Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account I do have a valid domain account I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed. I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment. The error is: 19015: The encrypton is required but no available certificat has been found.
Hi, I am having 10 tables in my database. Some times users are complianing that they are not getting permission to one particular table. I checked the table and the owner is dbo only. Why it is behaving like this. the users having all permissions on the database. Pls suggest me.
I think I may be going a little bit mad. Last week I created a database on SQL Server. I then created an access database on a different windows server, and set up an ODBC connection on that windows server to my database on SQL Server. I then set up linked tables within Access to point to my tables on the SQL Server. I'm fairly sure that I opened those links at the time and managed to display and update the data from Access. However I have come to this today and when I go into the Access database and attempt to open one of the links I get a connection failed message. When I check the ODBC connection on the windows server it is fine. Does anyone have any ideas on this? I really need this to be working by tomorrow to do a demo, and I'm so convinced it worked last week I am a little bemused!
Many many thanks in advance of your wonderful and prompt replies!!! :)
We have a view looking at all the columns of a table on a SQL server 7.0 db. When we link this view to MSAccess, we are seeing the old data which was there previously in the original table! not the latest data. We recompiled the table and views and also re-linked the view to MSAccess with no help,we still see the old data only.This is happening only for one of the views not for all.Any help??? sa.
We have several people accessing the SQL server 7.0 data thru' MS Access Server has been slow on queries etc.Everything looks good,other than this one finding of MS Access being used for update/select. Is MS Access a potential problem? How to solve the problem of slow response time?
Note: MS Access is running on the client desktops retrieving data from SQL Server and joining data from MS Access to SQL Server.
What kind of permissions do you need to be able to run a job created by another user or sa if you are not the job owner and don't have any sys admin priveldges??
I send you this message to told hwo can i do to convert one database which is in Access97 to SQL Server6.5. I must create a interface in the web which is able to interrogate the database that the reason why i want to do that. Thanks for any help in advance.
I developed a web application with MS Access there is a destop application that works with MS SQL SERVER 2000. The web application is just meant to allow user to view data and very little modification. I need to transfer from MS SQL Server to another MS Access database on another machine for security reasons and then to the internet server. Please can someone help me on this or any better method to go about this
We have a production Access database and SQL Server database. When I created a DTS package to import data from Access and run manually, it works fine. But when I schedule, it doesn't work, saying some one already opened the file or no permission error.
WE CAN'T get permissions for the Access file to the account that is assigned to SQL Server agent. It's internal policy of the company,because that account is used on all the sql servers to run the agent. Is there a work-around to import data from access. We do have a service account which has access to the Acess db.
Hello everybody I need to a solution to my problem asap. My problem is that i have a sql server 2005 script which is working fine and returning me the correct query. However the same script in access does not return the required query. The Sql server script is as follows:
UPDATE Transactions SET Transactions.EndProductDescription = TB.EndDescription FROM Transactions INNER JOIN (SELECT Distinct EndDescription, ProdBatchNo FROM ((SELECT EndProductCode FROM EndProduct WHERE (Product1 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB1 INNER JOIN (SELECT EndProductCode FROM EndProduct WHERE (Product2 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB2 ON TB1.EndProductCode = TB2.EndProductCode) INNER JOIN (SELECT EndProductCode, EndDescription, ProdBatchNo FROM EndProduct, Transactions WHERE (ProdBatchNo = '06122007/000002') AND (Product3 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB3 ON TB2.EndProductCode = TB3.EndProductCode) AS TB ON Transactions.ProdBatchNo = TB.ProdBatchNo WHERE Transactions.EndProductDescription = ''
So when i put this query in access same as it is i do not obtain the required results. Can someone please help me urgently on converting the above sql script into access query?
Hi I am developing a Windows Service product and use SQLCE 3.1 as data store, I found problem, that, when I inserted a record to sqlce data store from within service, I got an hidden window, the name is ms_sqlce_se_notify_wndproc, Could anyone has idea of how to disable this hidden window? Because my product is running as Windows Service, so this hidden window would be a serious security hole.
Aaaaaarrgghh ! (that's better)I am trying to convert a field within my Oracle 9i Database that is oftype BLOB (but this BLOB may contain a combination of clobs/varchars orimages such as gif images, jpg images) to Microsoft SQL Server 2000using Microsoft DTS.On trying to perform this simple conversion I recieved the error "Needto run the object to perform the operation - Exception AccessViolation" from Microsoft DTS and my table that contains this BLOBfield is not converted across.After further investigation I implemented the fixes suggested by theMicrosoft Knowledge Base and "sqldts.com" but still no joy the errorkept occuring.I discovered my modifying the step in the DTS package that handled thistable conversion that contained the BLOB column that when I changed thedata type on my SQL Server target table to VARBINARY and modified thequery so that only the BLOBs that contained clobs/varchars were broughtacross that the error went away.I then proceeded to create another DTS package step that had a querythat only brought across the BLOB column that contained images such asgif images/jpeg images etc. and the error went away and the target typefor the SQL Server target table was set to IMAGE.As the data for this BLOB contains a combination of VARCHARS/CLOBS(concatanted) and also GIF IMAGES/JPEGS in the same source column withOracle 9i I require the same in my target table within SQL server asone column (and I should be able to do that with type IMAGE especiallyas it can store larger objects than VARBINARY but any source BLOBS thatcontain VARCHAR/CLOBs don't seem to want to be loaded as IMAGE theywill only load in to VARBINARY).However judging by my experiences above this doesn't seem to bepossible ?Can anyone help me out with this ?I am on Microsoft SQL Server 2000 Service Pack 4 with latest MDAC(2.8.1).Cheers,Gary
I am getting error in sysindexes when i run dbcc checkdb on a production db. the error is Server: Msg 8928, Level 16, State 1, Line 1 please help me to remove this all the options of dbcc checkdb as well as table are not helping me
Does any1 know if when A SQL pass through query is executed in access (to a SQL srv dbase) wether or not access will wait until that call is completed before running other steps (in an macro say). I don't think it does but would appreciate a more definitive answer.
Is there a way that I can prevent people from running pass through queries on my SQL Server - without removing linked server access? I simply want to make sure that the processing occurs on the clients machine, not on my server....
Hi, I want to pass below given query into a variable
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ <POS_MONTH>]]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[ <POS_DATE>] GO
SELECT * INTO [dbo].[<POS_DATE>] ] FROM SG_POS_Template WHERE 1 = 0; GO" Where [<POS_DATE>] is a parameter by which value will be assigned dymanically.....anybody please help me out....!!
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK. Browse to the Access database file and connection set up---all good!!!
Dataflow task Add an OLE DB Source component Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list. I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
I pull data from Sql Server through the query, I want to pass the region parameter to the power pivot connection query. So that I can automatically pull the required region data. The parameter should pick the value from the excel range. And also how to control this through VBA
Hi Guys i write a thread before asking for help with reading an uploaded csv file, i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db. Heres the code<script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If (IsPostBack) Then Grid1.Visible = True Else Grid1.Visible = False End If End Sub Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Save the uploaded file to an "Uploads" directory ' that already exists in the file system of the ' currently executing ASP.NET application. ' Creating an "Uploads" directory isolates uploaded ' files in a separate directory. This helps prevent ' users from overwriting existing application files by ' uploading files with names like "Web.config". Dim saveDir As String = "Data"
' Get the physical file system path for the currently ' executing application. Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify ' that the FileUpload control contains a file. If (FileUpload1.HasFile) Then Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the ' uploaded file to the specified path. ' This example does not perform all ' the necessary error checking. ' If a file with the same name ' already exists in the specified path, ' the uploaded file overwrites it. FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully. UploadStatusLabel.Text = "Your file was uploaded successfully."
Else ' Notify the user that a file was not uploaded. UploadStatusLabel.Text = "You did not specify a file to upload." End If
End Sub
Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited""" Dim objConn As New OleDbConnection(sConnectionString) objConn.Open() Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn) Dim objAdapter1 As New OleDbDataAdapter() objAdapter1.SelectCommand = objCmdSelect Dim objDataset1 As New DataSet() objAdapter1.Fill(objDataset1, "csv.txt") Grid1.DataSource = objDataset1.Tables(0).DefaultView Grid1.DataBind() objConn.Close() End Sub
</script> My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app. Thanks for your help
