I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test] ( @numberOfMonthsint ) AS BEGIN SET NOCOUNT ON set transaction isolation level read uncommitted
[Code] ....
The problems I have run into so far are...
SQL command text in OLE DB Source Editor does not like: - TRY/CATCH block - Will not let me use my input parm (@numberOfMonths int) - When I hard code in my input parm (select @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.
The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.
Also, I know not even to try the Build Query... cause it will complain about any variable declarations (i.e., declare @BeginDate datetime).
For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow.
Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?
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.
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
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?
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:
Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.
Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
CREATE PROCEDURE procCreateBasket @ShopperID int, @BasketID int OUTPUT AS INSERT INTO Basket(ShopperID) VALUES (@ShopperID) SELECT @BasketID = @@IDENTITYI don't want to use this as a stored procedure I want to convert it as a string an use in that way in my asp.net application how can I do that?
Hi i have the following method in my page, it works fine and everything, but i am trying to modify it so that it can take a stored procedure; protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);String sql = "SELECT userID, userName FROM users WHERE userName LIKE " + "'" + userName.Text + "%' OR organisation= " + "'" + OrganisationList.SelectedValue + "'"; conn.Open(); SqlCommand comm = new SqlCommand(sql, conn);SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection); DataList1.DataSource = reader; DataList1.DataBind(); conn.Close(); }
My question is what should my stored procedure be, and also how will i now structure the above method.
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any one tell me the solution of that problem (at ur earliest)
Code: USE [byrndb] GO /****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code: USE [byrndb] GO DECLARE@return_value int EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber] @number = N'23913' SELECT'Return Value' = @return_value GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
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
Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!
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
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.