I can get the following statement to execute from the mysql command prompt but when I try to build a tableadapter with the visual studio 2008 dataset wizard I get an error. set @strsql = Concat('SELECT tblcustomers.CustomerId, tblcustomers.FirstName, tblcustomers.LastName, tblcustomers.Address, tblcustomers.City, tblcustomers.State, tbl...
I'm currently using Microsoft Sync Framework and everything is working fine except the SQL triggers which don't appear to fire sometimes. Here is the trigger code generated by the framework. It works 90% of the time but occasionally the CreationDate is NULL after an insert. Could there be some date issue that I'm overlooking? set ANS...
I want to call a stored procedure in SQL Server 2005 that returns an XML string that can then be passed into another method; I think the below code is right up until the point where I execute the query, but then I get a bit lost... private string GetChartData(string OC_Ttl1, string OC_OL31, string OC_OL32) { string chartDat...
I wish to return a single ScPollOption item using a Stored Procedure via the code below: public ScPollOption FetchPollOptionByID(int optionID) { StoredProcedure sp = SPs.ScPollOptionGetOptionByID(optionID); return sp; } When working with a query I would use: ExecuteSingle<ScPollOption>() but SubSonic only al...
Hi, I have 2 tables one with a lot of records(table 1), and a second(table 2) with similar data but with far fewer records. On a regular basis i need to add a marker to records in the larger table where there is a corresponding record in the smaller table. For example this could be an email address. So if email address exists in the s...
I have an adjacency list on a legacy system that I would like to query recursively (need to get subtotals, etc). Can I make a trigger in MySQL that either store in a separate table, or alternatively store in separate columns in the same table the "Nested Set Equivalent" of a given set? My set is like this: +-------------+-------------...
I should probably be able to find an answer to this but my Google-fu is weak today. When I have the same stored proc called multiple times by a web app, do the calls queue up or do they run independently? ...
I wrote a package to query rows from a table. This select query will call other functions and returns all the rows from table. But when i write a package with all functions and sprocs , my sproc with select statement gives me an error saying i cannot execute without into statement. But if i use into then it will return only one row. How ...
I am attempting to create a stored procedure/ADO.NET mapping mechanism where a stored procedure with parameters becomes object MyStoredProcedure.Execute(out returnValue, param1, param2, ...) The problem comes when trying to generate the actual data retrieval method. I can easily obtain most of the schema information I need from the I...
I want to build a single select stored procedure for SQL 2005 that is universal for any select query on that table. **Columns** LocationServiceID LocationID LocationServiceTypeID ServiceName ServiceCode FlagActive For this table I may need to select by LocationServiceID, or LocationID, or LocationServiceTypeID or ServiceName or a com...
Hello, Today i faced a pretty weird problem, made of a stored procedure, an autocompleteextender and event handling. If anyone can suggest a concise title for the following saga, please do! First i'll try to introduce the players (some parts are a bit abridged) and then the scenarios. Environment is VS2008, C#, SQL Server 2005 and ASP....
Hi folks, I have a SQL Server 2008 database and want to access a table from a C#-WCF via a stored procedure. The proc is a simple SELECT query that gets the row of a given id and fills the result into some outputparameters: PROCEDURE [dbo].[get_stammInfo] -- Add the parameters for the stored procedure here @id int, @strassenSc...
I'm curious about at what extent I should be writing sproc's. Obviously for actions that require transactions, etc. However for a simple validation against one table and values within, is it still recommended to use a sproc rather than doing the SubSonic query in the code-behind? In one respect it does make sense to write the sproc, a...
I am new to sql and How can I automatically set the value of one variable depending other variable's value. I have price in foods table, and in orders table I want to change the total value of the price according to the # of orders for a specific food. ...
Hi, I have two tables, 1 indexing the details of an email campaign. The second table details the recipients of this campaign, and whether they have responded to the email etc. I need to create a stored procedure that can update the status of the 'master record' in TBL1 when all the references(recipients) in TBL2 have a status >1. The ...
Hi, This could easily be done using code, but I wondered if it could be done at the database level using SQL Server (2008). I have a table similar to below: CROP_ID YEAR_ PRODUCTION 1 1 0 1 2 300 1 3 500 2 1 100 2 2 700 I want to be able to run a que...
Hi, I have been trying to get MySQL stored procedures running with the linq templates in Subsonic3. I added some functions to the MySQL.ttinclude file that seems to have generated the stored procedure reference classes. However when I run the code and call the stored procedures I seem to always get NULL results: public DataSet SPTot...
I am new to web service, The database access should be through Web Services using ADO.NET to access stored procedures. any ideas ? ...
Say I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END Of course my actual procedure is a little more complicated, being why a sproc is necessary. Is it possible to select the output from calling this procedure? Something like: SELECT * FROM (EXEC MyProc) AS TEMP The reason...
Does SQL Server allow you to declare stored procedure parameters like this? @username NVARCHAR Or do you always have to specify a size like this? @username NVARCHAR(100) ...