Connect To Microsoft Access Via Stored Procedure
Hi,
I'm new to SQL server, still in the beginning stage of learning SQL Server. I'm here would like know, besides using the Connectivity from the DTS Designer to connect to different databases, is it possible to connect the database, i.e: Access via stored procedure? and how? Pls advise...
I'm have been trying to look for the solution via a lot of SQL Server site, but fail to get what I want.
What I'm trying to do is something like :
First connect to the Database and Query the data, after that insert it into another database....
View Complete Forum Thread with Replies
Related Forum Messages:
Calling An SQL Server 2005 Stored Procedure Within Microsoft Access And Reading Values
Goodday. I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way Code Block Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _ "Initial Catalog='DBName';Integrated Security='SSPI';" cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "sp_DefaultEntityData" cmd.CommandType = adCmdStoredProc Set rst = cmd.Execute rst.MoveFirst Do While Not rst.EOF lstEntity.AddItem (rst.Fields(0)), 0 'lstEntity.AddItem (rst.Fields(1)), 1 rst.MoveNext Loop The Stored Proc is as follow: Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_DefaultEntityData] AS BEGIN SET NOCOUNT ON; SELECT tblEntities.[Name], tblEntities.PrimaryKey FROM tblEntities ORDER BY tblEntities.PrimaryKey; END The table contains 24 entries As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data. I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work. Here are my Q's: 1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment? 2) How do I read the values into the listbox, without displaying the primary key in die box? Thank you in advance! Any help is very much appreciated.
View Replies !
Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
View Replies !
Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same. ddave
View Replies !
Microsoft KB 308049: How To Call A Parameterized Stored Procedure By Using ADO.NET 2.0-VB 2005 Express-pubs Is Processed By ?
Hi all, I tried to use the "How to call a Parameterterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsoft.com/kb/308049 to learn "Use DataReader to Return Rows and Parameter" in my VB 2005 Express. I did the following things: 1) created a stored procedure "pubsTestProc1.sql" in my SQL Server Management Studio Express (SSMSE): USE pubs GO Create Procedure TestProcedure ( @au_idIN varchar (11), @numTitlesOUT Integer OUTPUT ) As select A.au_fname, A.au_lname, T.title from authors as A join titleauthor as TA on A.au_id=TA.au_id join titles as T on T.title_id=TA.title_id where A.au_id=@au_idIN set @numTitlesOUT = @@Rowcount return (5) 2) created a project "pubsTestProc1.vb" in my VB 2005 Express and copied the following code from http://support.microsoft.com/kb/308049 (i.e. Added the code to the Form_Load eventQL_Client) : Imports System.Data Imports System.Data.Client Imports System.Data.SqlType Imports System.Data.Odbc Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim PubsConn As SqlConnection = New SqlConnection & _ ("Data Source=.SQLEXPRESS;integrated security=sspi;" & _ "initial Catalog=pubs;") Dim testCMD As SqlCommand = New SqlCommand & _ ("TestProcedure", PubsConn) testCMD.CommandType = CommandType.StoredProcedure Dim RetValue As SqlParameter = testCMD.Parameters.Add & _ ("RetValue", SqlDbType.Int) RetValue.Direction = ParameterDirection.ReturnValue Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _ ("@au_idIN", SqlDbType.VarChar, 11) auIDIN.Direction = ParameterDirection.Input Dim NumTitles As SqlParameter = testCMD.Parameters.Add & _ ("@numtitlesout", SqlDbType.Int) NumTitles.Direction = ParameterDirection.Output auIDIN.Value = "213-46-8915" PubsConn.Open() Dim myReader As SqlDataReader = testCMD.ExecuteReader() Console.WriteLine("Book Titles for this Author:") Do While myReader.Read Console.WriteLine("{0}", myReader.GetString(2)) Loop myReader.Close() Console.WriteLine("Return Value: " & (RetValue.Value)) Console.WriteLine("Number of Records: " & (NumTitles.Value)) End Sub End Class ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I compiled the above code and I got the following 15 errors: Warning 1 Namespace or type specified in the Imports 'System.Data.Client' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 2 9 pubsTestProc1 Warning 2 Namespace or type specified in the Imports 'System.Data.SqlType' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 3 9 pubsTestProc1 Error 3 Type 'SqlConnection' is not defined. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 10 25 pubsTestProc1 Error 4 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 15 30 pubsTestProc1 Error 5 Name 'testCMD' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 17 9 pubsTestProc1 Error 6 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 20 23 pubsTestProc1 Error 7 Name 'RetValue' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 21 9 pubsTestProc1 Error 8 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 23 23 pubsTestProc1 Error 9 Name 'auIDIN' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 24 9 pubsTestProc1 Error 10 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 26 28 pubsTestProc1 Error 11 Name 'NumTitles' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 27 9 pubsTestProc1 Error 12 Name 'auIDIN' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 29 9 pubsTestProc1 Error 13 Type 'SqlDataReader' is not defined. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 32 25 pubsTestProc1 Error 14 Name 'RetValue' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 39 47 pubsTestProc1 Error 15 Name 'NumTitles' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 40 52 pubsTestProc1 //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// First, I am completely lost here alreay. Second, I should have the following code from http://support.microsoft.com/kb/308049 too: OLE DB Data Provider Dim PubsConn As OleDbConnection = New OleDbConnection & _ ("Provider=sqloledb;Data Source=server;" & _ "integrated security=sspi;initial Catalog=pubs;") Dim testCMD As OleDbCommand = New OleDbCommand & _ ("TestProcedure", PubsConn) testCMD.CommandType = CommandType.StoredProcedure Dim RetValue As OleDbParameter = testCMD.Parameters.Add & _ ("RetValue", OleDbType.Integer) RetValue.Direction = ParameterDirection.ReturnValue Dim auIDIN As OleDbParameter = testCMD.Parameters.Add & _ ("@au_idIN", OleDbType.VarChar, 11) auIDIN.Direction = ParameterDirection.Input Dim NumTitles As OleDbParameter = testCMD.Parameters.Add & _ ("@numtitlesout", OleDbType.Integer) NumTitles.Direction = ParameterDirection.Output auIDIN.Value = "213-46-8915" PubsConn.Open() Dim myReader As OleDbDataReader = testCMD.ExecuteReader() Console.WriteLine("Book Titles for this Author:") Do While myReader.Read Console.WriteLine("{0}", myReader.GetString(2)) Loop myReader.Close() Console.WriteLine("Return Value: " & (RetValue.Value)) Console.WriteLine("Number of Records: " & (NumTitles.Value)) ////////////////////////////////////////////////////////////////////////////////////////////////////// Now, I am completely out of touch with these two sets of the code from the Microsoft KB 308049 and do not know how to proceed to get the following output stated in the Microsoft KB 308049-see the below: 4. Modify the connection string for the Connection object to point to the server that is running SQL Server. 5. Run the code. Notice that the ExecuteScalar method of the Command object returns the parameters. ExecuteScalar also returns the value of column 1, row 1 of the returned rowset. Thus, the value of intCount is the result of the count function from the stored procedure. //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Please help and tell me what I should do to get this project landed on the right track. Thanks in advance, Scott Chang
View Replies !
Microsoft Access Doesn't Support Design Changes To The Version Of Microsoft SQL Server
Dear All,Access adp on sql-server 2000After upgrating to A2003 updating data with 1 perticular combobox causes theprogram to hangs without any error-msg.Traying to change te combobox recordsource i get this error:This version of Microsoft Access doesn't support design changes to theversion of Microsoft SQL Server your project is connected to. See theMicrosoft Office Update Web site for the latest information and downloads(on the Help menu, click Office on the Web). Your design changes will not besaved.The solution in :http://support.microsoft.com/defaul...kb;en-us;313298tolks about SP 'dt_verstamp007' but I have SP 'dt_verstamp006'What should I do.Is the failure of the combobox also caused by the absence of dt_verstamp007???Filip
View Replies !
[Microsoft][ODBC Microsoft Access Driver] System Resource Exceeded
odbc_pconnect() [function.odbc-pconnect]: SQL error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded., SQL state S1001 in SQLConnect we got the error with access 2000 database and PHP as prog. language . we created dsn for the connection. reboot solves the problem. but we need another solution better than this.
View Replies !
Cant Access Sql Express Stored Procedure..help!!
Hi everyone, im having alot of trouble trying to execute a stored proc from sql express. heres my code DbProviderFactory db = DbProviderFactories.GetFactory("System.Data.SqlClient"); using(DbConnection conn = db.CreateConnection()){ ConnectionStringSettings s = ConfigurationManager.ConnectionStrings["constrolservicetest"]; conn.ConnectionString = s.ConnectionString; conn.Open(); DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "StoredProcedure1"; cmd.CommandType = CommandType.StoredProcedure; DbParameter param = db.CreateParameter(); param.ParameterName = "@test"; param.Value = 2; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } ****************************procedure code*************************** Create PROCEDURE dbo.StoredProcedure1 ( @test int ) AS Update temp Set test = @test ****************************************************************** The code just doesn't update. I know my connection string is correct because i got the datareader to work, but i just cant get the stored proc to call. Any help will be greatly appreciated. Thanks, -D
View Replies !
Calling A Stored Procedure From Access
Given this connect string for cnn:Provider=SQLOLEDB.1;Data Source=MYDATABASE;ExtendedProperties="DEFAULTDATABASE=TRACS_Dev;InitialCatalog =TRACS_Dev;UID=sa;PWD=";with cnn.CursorLocation = adUseClientthe command cnn.Execute "usp_IPT" fails with "cannot find storedprocedure usp_IPT"the command cnn.Execute "dbo.usp_IPT" fails with "cannot find storedprocedure dbo.usp_IPT"but the command cnn.Execute "TRACS_Dev.dbo.usp_IPT" worksDoes anyone know why I have to specify TRACS_Dev when it is already inthe connect string?Thanks,Norman B. ScheininF-22 Applications DevelopmentM/S 4E-09(206) 655-7236Join Bytes!
View Replies !
CLR Stored Procedure To Access Ole Datasource, How?
I tried to write a CLR stored procedure using C# in SQL 2005 to access an Access database. When I use the OleDbConnection class in System.Data, the procedure throws SecurityException at runtime. Output as following: System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.OleDb.OleDbConnection.PermissionDemand() at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string, String source_table, String destination_table, Int32 batchSize, Int32 notifyAfter) The action that failed was: Demand The type of the first permission that failed was: System.Data.OleDb.OleDbPermission The Zone of the assembly that failed was: MyComputer
View Replies !
How Do I Access The Results Of A Query In The Stored Procedure Itself ??
Hi I've run into a slight problem with my stored procedure.If I fire a query in a stored procedure, how can I access the results of that query in the stored procedure itself ?? ALTER PROCEDURE GetDetailsOfWebsiteUserForMail @request_id intAS declare @k int select person_id from Website_user w, Request r where r.user_id=w.user_id and r.request_id=@request_id set @k =person_id /* What do I do here??, value returned is unique */ if (@k!=0) /* Exists */ begin /* do something */ end else /* entry on person table does not exist */ begin /* do something */ end
View Replies !
Access Crashes When Updating A Stored Procedure
Hello,I am having a problem when using access xp as a frontend for sql server2000.I have been trying to update a number of stored procedures (Just simpleadding fields etc) which results in access crashing with event ID 1000 and1001.Does anyone have any ideas as to what could be the problem?Thanks in advance..
View Replies !
ODBC For MS SQL Server To Access Stored Procedure
I have a stored procedure written in MS SQL Server2000 which takes argument(OUTPUT) as a cursor, and fills in the cursor with the record from the table. I have to run this stored procedure from my C application program running in SUN OS2.9 with the help of ODBC calls. Can anyone guide me through steps as to how to run the store procedure from my C program and receive records with the help of the cursor. The store procedure is as follows CREATE PROCEDURE testCursor @xyzCursor cursor varying OUT AS DECLARE temp CURSOR LOCAL FOR SELECT * FROM table OPEN temp SET @xyzCursor=temp RETURN(0) GO
View Replies !
Access Not Executing Stored Procedure Correctly
Hi guys I cant seem to get my stored procedure to execute properly through Access Xp. Do you think there is something wrong with my stored procedure?? CREATE PROCEDURE [insert_ConditionalLicense_UpdateFromTerms] (@TM_# [int], @FirstName [nvarchar](50), @LastName [nvarchar](50), @SS# [nvarchar](50), @Birthdate [nvarchar](50), @reasonforconditional [ntext], @Notes [ntext]) AS INSERT INTO [GamingCommissiondb].[dbo].[ConditionalLicense_View] ( [TM #], [FirstName], [LastName], [SS#], [reasonforconditional], [ConditionalStart Date]) SELECT [TM#], [LASTNAME], [FIRSTNAME], [SSN#], [NOTES], [DATEOFCONDITIONAL] FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATION-COND' IF @@Error <> '0' RETURN when I execute it through a command button this is the message I get "paramater" not quite sure why I am getting this message
View Replies !
Using A Stored Procedure Parameter To Access A Column
I trying to create a general stored procedure which updates 1 out of 140 columns depending on the column name provided as a parameter. I'm not having much luck, just wondering if anyone else had tried to do this and whether it is actually possible? Any help would be much appreciated Chris
View Replies !
ACCESS CROSSTAB From SQL SERVER Stored Procedure
The following query sends a query to Access and returns a crosstab query. SELECT * FROM OPENQUERY (ACCESS_OLEDB, 'TRANSFORM First([SUBSET].[Amount]) AS FirstOfAmount SELECT [SUBSET].[ROW_HEADER] FROM SUBSET WHERE test IN ( "PASS" , "PERFECT") GROUP BY [SUBSET].[ROW_HEADER] PIVOT [SUBSET].[COLUMN_HEADER ];') This is not dynamic since I cannot change ROW_HEADER, or COLUMN_HEADER on the fly. I want to be able to pass ROW_HEADER, COLUMN_HEADER, and filtering criteria to a stored procedure. How can I get such dynamic functionality through a stored procedure. The exact query above works fine but when I substitute variables I get various OLEDB and string errors. PLEASE HELP Ziggy
View Replies !
Converting A MS Access Query To SQL Stored Procedure
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure CREATE PROCEDURE FORUM_MESSAGE AS SELECT * FROM FORUM_MESSAGES WHERE ID=MessageID; here "MessageID" is a run time generated parameter, and is not a field in the database. thanx
View Replies !
Passing Value From Access Combo Box To Stored Procedure
I am using SQL 7 with Access 2000. Can someone please tell me how to pass the value from a combo box on an Access form to the following stored procedure: Alter PROCEDURE usp_Find_Order @VendorID varchar(255) AS SELECT * FROM ViewItems WHERE VendorID= @VendorID This procedure is the Row Source for the combo box. This is easy in a Jet backend. All I had to do was type the following in the criteria of an Access query: Forms!frmReceivingMaster!VendorID. Not the same when using SQL 7. :) Thanks in advance for your help. Gary
View Replies !
Convert Access Query To SQL Stored Procedure
I'm trying to convert an Access database application to an ASP.NET application with SQL Server 2005 as the database backend. However, I'm having trouble converting some of the queries to SQL stored procedures. Here's an example Access query that I'm trying to convert into working SQL: SELECT DISTINCTROW EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) AS FullTimeInfo, EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) AS ShortTermInfo, EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) AS GeneralInfo, Accounts.* FROM Accounts INNER JOIN AccountCodes ON Accounts.AccountID = AccountCodes.AccountID WHERE (((Accounts.SummitID)=@SummitID) AND ((AccountCodes.CodeID)=10 Or (AccountCodes.CodeID)=22 Or (AccountCodes.CodeID)=24)) ORDER BY Accounts.LastName, Accounts.FirstName My understanding is that EXISTS can only be used in the WHERE clause in SQL. Any suggestions on how to properly rewrite this? Jon
View Replies !
Pass Access Data To Stored Procedure
I am very familiar with MDBs but just converted an MDB to an ADP. Now I need to declare global parameters to pass Access form data to SQL stored procedures and not sure how to do that. I realize this is an Acess to SQL question but this is critical to completing my project.
View Replies !
Problem In Data Access Though SQL Server Stored Procedure
I have two database SOP and CRM. Both supports windows authentication and SOP suports sql authentication too. Now i have to write a SP in SOP database with identity impersonation (with superadmin authentication) which will do some work with this impersonated id on SOP database but needs to fetch some values from CRM with the current user account - not impersonated account.Lets clear... I want to show the orders generated by all users and in the display grid there is a column like "IsMyCustomer?" which will show the order is to a customer which i have access. In CRM (MS) if i select from FilteredCustomer view i will get the customersid of those under me. And if i left outer join with FilteredOrder view in SOP (with Super Admin windows credentials) which returns orders by current user i will get the desired result.Now the problem is i can't sent two connection credentials to a SP. So what i want is to connect SOP with user's windows credentials (not with impersonation) and from the SP we will Select data with Admin's account. But i don't know is there any way to connect to a linked server with a different credentials. Like when i am selecting from CRM server it will use different credentials.Remember i am using SQL server 2000 and everything should be done through a single SP.I know i can do this easily with two different select from Data access layer. But i am looking for some performance effectinve way. My PM wants this idea to be implemented. I have no chice guys....
View Replies !
Stored Procedure Can't Access Table On External Database
Hi I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net. My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList'). I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures. Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo. Example of stored procedure on external database: /****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/ CREATE PROCEDURE dbo.AddUser ( @Username Varchar(20), @Password Varchar(20) ) AS ( Select User_ID From UserList Where User_Username = @Username ) GO Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops! Any help would be greatly appreciated as I am completely stuck. John
View Replies !
Stored Procedure Error When Run From Visual Basic In Access
I am working in an access data project. I have a stored procedure that runs fine when I open and run it directly in sql. When I use the DoCmd.OpenStoredProcedure method in VB code, the stored procedure also runs fine (and successfully adds records as it should) but then I get an error: #7874 "...can't find the object...'[Name of sp'". This halts the vb code and is a problem. Here's example code from a sp that causes this problem: Insert into Table (Field1, Field2, Field3, Field4) Select Field1, 'Test', Field5, GetDate() from View1 I understand there may be another syntax to run a stored procedure from access visual basic other than DoCmd. I would very much appreciate guidance as to how to do this. Thank you.
View Replies !
Problems Calling SQL Express Stored Procedure From Access (using VBA)
Crossposted from VBA forum: I'm upsizing a database from Access 2003 to a SQL Express backend combined with an Access frontend. Along the way, I'm trying to shift the larger queries into stored procedures, but many of our queries require variables. I'm having a hard time with the VBA to run this stored procedure, and looking for suggestions that make sense to a relative newbie to VBA. Here's my code, which is being triggered by a button click on a form containing input boxes StartDt and EndDt for the date range: Code Block Private Sub btnDateFormQuery_Click() 'On Error GoTo Err_btnDateFormQuery_Click If CreateDSNConnection("server", "database", "user", "password") Then '// All is okay. Else '// Not okay. End If Dim rs_sp As Recordset 'Set in Global Module Dim qdf As QueryDef 'Set in Global Module Dim SP_SQL As String 'Set in Global Module Dim Db As Database Set Db = CurrentDb() Set qdf = Db.CreateQueryDef("qry_Donors") 'Set in Global Module qdf.ReturnsRecords = True 'Set in Global Module qdf.ODBCTimeout = 15 'Set in Global Module SP_SQL = "Execute sp_DonorQRY " & "'" & StartDt & "'" & ", " & "'" & EndDt & "'" qdf.SQL = SP_SQL 'Set in Global Module 'Check Dates for errors Call CheckDates 'Procedure to check dates If CheckDatesErr = True Then Exit Sub ' don't continue End If 'Here is where we need to enter the code that will drive the query. Set rs_sp = qdf.OpenRecordset qdf.Close rs_sp.MoveFirst rs_sp.Close Exit_btnDateFormQuery_Click: Exit Sub Err_btnDateFormQuery_Click: MsgBox Err.Description Resume Exit_btnDateFormQuery_Click End Sub During Debugging, when I reach the line "qdf.SQL = SP_SQL", I receive the following error: Runtime Error '3129'Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. I've been able to copy out the value of SP_SQL and paste it into a pass-through query, and it runs beautifully. Just not having any luck trying to script it. Thanks in advance.
View Replies !
How To Connect ADODB With Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider For Microsoft SQL Server Compact 3.5)
Hi We are checking VB 9 (Orcas). we connected to database created under with sql server 7. with this code Public cn As New ADODB.Connection Public Sub OpenDB() cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.") End Sub this code worked well. we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5). Rgds Pramod
View Replies !
Convert Access Query Dateserial Function Into Stored Procedure
Hi, Below is a access query which is using dateserial function. can you please convert the below query into equivalent TSQL using function and calling that function into the stored procedure. "select DateSerial([cy],IIf([mois_activity_plan]="Janvier",1,Null,1) from Table" datatype for "cy" and "mois_activity_plan" are "text" in the table. Dateserial function has the format:Dateserial(year,month,day) please help. Many thanks, rakesh
View Replies !
ADO - Cannot Access The Return Parameter Of A Stored Procedure On SQL Server 2005
Hello, I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field. To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using. try{ pCmd.CreateInstance((__uuidof(Command))); pCmd->ActiveConnection = m_pConnection; pCmd->CommandType = adCmdStoredProc; pCmd->CommandText = _bstr_t("dbo.GetFlightPlan"); ............................ code here ........................................ pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour)); pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin)); VARIANT returnVal; returnVal.vt = VT_I2; returnVal.intVal = NULL; pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal)); //Get Return value by executing the command //The return value should be the DB unique ID. pCmd->Execute(NULL, NULL, adCmdStoredProc); int uniqueId = returnVal.intVal; //pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc); //GetFieldValue(0,pRst,uniqueId); printf("The DB unique ID is: %i",uniqueId); return uniqueId; } Cheers, Seth
View Replies !
How To Access Data From Different Database And Display Result Set In Managed Stored Procedure
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.OleDb Imports System.Configuration Imports System.Text Imports System.Collections Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _ ByVal strCompany As String, _ ByVal decSerial As Decimal, _ ByVal strSerialCode As String, _ ByVal strSerialScan As String, _ ByVal decMasterSerialNumber As Decimal, _ ByVal strCustomerPart As String, _ ByVal strTakataPart As String, _ ByVal strCustomerRanNo As String, _ ByVal strCustomerAbv As String, _ ByVal strDestinationAbv As String, _ ByVal decQty As Decimal, _ ByVal strCreatDate As String, _ ByVal decVoidSerialNo As Decimal, _ ByVal strProductionLineNo As String, _ ByVal strProcType As String) Dim sp As SqlPipe = SqlContext.Pipe Dim strResult As Integer = 0 Dim strErrorText As String = String.Empty Dim dsData As New DataSet Dim parameter(15) As OleDbParameter If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then ' Populate parameter collection parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName)) parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany)) parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial)) parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode)) parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan)) parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber)) parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart)) parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart)) parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo)) parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv)) parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv)) parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty)) parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate)) parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo)) parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo)) parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType)) RunDB2Sp("FABLE.MAP", parameter, dsData) If dsData.Tables.Count > 0 Then dsData.Tables(0).TableName = "Supreeth" Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString() Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString() ' I am not sure here SqlContext.Pipe.Send(bitresult) SqlContext.Pipe.Send("No errors") End If Else Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid") End If End Sub Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet) '********************************************* ' Declare Variables '********************************************* Dim daAdaptor As OleDbDataAdapter Dim cmdAS400 As OleDbCommand 'Dim dstestMe As New DataSet Try cmdAS400 = CreateCommand(strProcedure, parms) daAdaptor = New OleDbDataAdapter(cmdAS400) ' Fill the Data Set daAdaptor.Fill(dsData) Catch expError As OleDbException daAdaptor = Nothing Finally daAdaptor = Nothing cmdAS400.Dispose() 'Me.Close() End Try End Sub Public Shared Function CreateParameter(ByVal name As String, _ ByVal type As OleDbType, _ ByVal size As Integer, _ ByVal direction As ParameterDirection, _ ByVal paramValue As Object) As OleDbParameter Dim param As OleDbParameter = New OleDbParameter param.ParameterName = name param.OleDbType = type param.Size = size param.Direction = direction param.Value = paramValue Return param End Function Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand Dim CmdSAS400 As OleDbCommand Dim parameter As OleDbParameter Dim connAS400 As OleDbConnection connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;") connAS400.Open() CmdSAS400 = connAS400.CreateCommand() CmdSAS400.CommandText = strProcedure CmdSAS400.CommandType = CommandType.StoredProcedure CmdSAS400.Parameters.Clear() 'CmdAS400.CommandTimeout = intTimeOut If (prams Is Nothing) Then Else For Each parameter In prams CmdSAS400.Parameters.Add(parameter) Next End If Return CmdSAS400 End Function I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset. My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs Thanks
View Replies !
Error Message Given Using Access 2003 Adp File To Change A Stored Procedure
Client/Server machine: Windows Xp Pro (SP2) (latest patches) Office Software: Access 2003 (latest patches) Database S/W: SQL Server 2005 (latest patches) The following error message is displayed when trying to modify a stored procedure. This version of Microsoft Access doesn't support design changes to the version of Microsoft SQL Server your project is connected to. See the Microsoft Office Update Web site for the latest information and downloads (on the Help menu, click Office on the Web). Your design changes will not be saved. However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved. Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ? Have I provided enough detail as this is my first post ? Philip
View Replies !
Unable To Access VM Network Drive In SQL During Backup Generated By Stored Procedure
I get the following error in a log file created in the osql command: Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150 Cannot open backup device 'x:Servername_master_sqlbu_200703101930.bkf'. Device error or device off-line. See the SQL Server error log for more details. Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150 BACKUP DATABASE is terminating abnormally. The device is established in a CMD file right before the osql command is started that starts my stored procedure. The CMD in this file is: for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i :loop if exist x: net use x: /del net use x: \192.168.%SUBNET%.1ackup if not ERRORLEVEL 1 ( goto continue ) else ( echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%LogFiles\%Computername%.log" sleep 60 goto loop ) :continue echo IP ADDRESS OBTAINED Echo delete old log file if it exists if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log Echo Rename log file to old.log rename %3\%computername%_kaman_full_sqldb_backup.log %computername%_kaman_full_sqldb_backup_old.log echo backup SQL Databases on server will start now osql -E -n -d %1 -i %2kaman_full_sqldb_backup.sql -h-1 -o %3\%Computername%_kaman_full_sqldb_backup.log In the stored procedure I try to use the x: drive and that does not work. I have tried obtaining the \192.168.x.x address and that only works on some of my servers. All are running SQL 2000, some are using Win2K and Win2003. It does not seem to matter. One of them that is using Win2003 only fails occationally. I notice when I do a exec master..xp_cmdshell 'x:' The system cannot find the drive specified. Yet, when I go to the server there is an x drive. I am using the sqlserv user to run the job and that use is an administrator on the local machine. This is a virtual machine. We add the X before the osql and drop it after the command finishes. Any help would be appreciated. Thanks, dbmsql
View Replies !
Microsoft Ole Db Provider For Db2 Vs Db2 Connect
Do you get better performance by installing db2 connect on a Windows 2003 server running sql server 2005 and have your application hit a db2 database or do you get better performance setting up a linked server that uses an ole db connection to a db2 database? At first, I thought it would be the OLE DB connection, but I have confused myself now and need some clarification. Thanks
View Replies !
Problem Use Sqldatasource To Access Stored Procedure And Get Data Bind To Label Control
Hi every experts I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table. My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web? Thanks anyone, who can give me any advice. Satoshi
View Replies !
How To Connect Jsp File With Microsoft SQL Server
Can anyone please teach me how to make a connection between jsp file and Microsoft SQL Server database steps by steps? I'm using Windows XP and I found that my computer do not have JDBC (I already have ODBC). Is this mean that I have to install some JDBC or SQL driver in order to make the connection? Which driver should I install and where can I get the driver? If possible, please show me some example so that I can understand better..Thanks a lot. Regards, san san
View Replies !
Feedback On Microsoft Connect Site
I don't know how many folks here log into the Microsoft Connect site occasionally to check suggestions and bugs submitted to Microsoft for SQL Server and SSIS (still called DTS on their list). A small pecentage? Almost everyone? (Possibly in this group.) Anyone can vote for feedback they think is important. Theoretically issues with the most votes will get Microsoft's attention first. Links to a couple new submissions that look interesting: 1. SSMS/QA Style Message Logging for SSIS Execute SQL Tasks 2. ForEach SMO Enumerator Filtering I vetted these issues in the forum first, so hopefully they're legitimate enough to warrant some useful feedback or even a few high fives!
View Replies !
Cannot Connect Using ADO With Excel Microsoft Jet Provider 4.0
Hi: I have a vb 6 app that exports data from sql to excel. The user has the ability to select either local or server. When they select server, the connection string is modified to include the server name instead of the local msde instance to look like this: "provider=sqloledb;data source=MyServer;initial catalog=MyDatabase;user id=UserX;password=PasswordX;database=MyDatabase" I'm using Microsoft Jet Provider 4.0 and OpenDataSource. SELECT * INTO TempXL FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Sheet1$] This code works fine when user is accessing msde on local, but when using above DSN, I receive the following message: "2147217900 - OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEdB.4.0' IDBInitialize:: Initialize returned 0x800040005: The provider did not give any information about the error.] When I trace the connection, these error messages occur: Failed to set proper user name ('NT AUTHORITYSYSTEM') for the connection [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter table 'TblXMyTable' because this table does not exist in database 'master'. (State 42S02) (Code 4902) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TblxMytable'. (State 42S02) (Code 208) [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. (State 42000) (Code 7399) [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. (State 01000) (Code 7300) I am especially puzzled by the second message because I am specifying the database name in my connection string as: "provider=sqloledb;data source=MyServer;initial catalog=MyDatabase;user id=UserX;password=PasswordX;database=MyDatabase" I can read from the server copy and copy to Excel, but I cannot write back to SQL. Can someone please tell me what I'm doing wrong? Thanks in advance for any help.,
View Replies !
SqlDMO Connect Error From Microsoft Operations Manager (MOM)
MOM connects to 19 instances without issue and fails on one (1). The failing instance resides on a server with two(2) other instances that do not fail. Security is setup the same on windows and sqlserver. Receiving error in MOM: "Could not connect using SQLDMO to SQL Instance WEB. However, the instance is currently running." Presents in Event Viewer as: "18452: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." Troubleshooting so far: Created simple SqlDMO VBScript that uses a trusted connection. This works fine from my desktop to all twenty (20) instances yet fails on the one server that MOM is running on. Any ideas?
View Replies !
|
TRACKER
