.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Stored Procedure Metadata failure- Dummy select not working

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server
 

I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -

CREATE PROCEDURE GetSettlementData
AS
BEGIN
SET NOCOUNT ON
SET FMTONLY OFF

IF 1=0
BEGIN
SELECT 
		CAST(NULL AS INT) AS ID
		,CAST(NULL AS INT) AS Marks
END

CREATE TABLE #Tmp_GetData
(
	ID INT,
	MARKS  

View Complete Post


More Related Resource Links

Unable to select any stored procedure while creating TableAdapters in wizard

  

I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?


Executing a job from a stored procedure stopped working

  
I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:   CREATE PROCEDURE xxxxx WITH EXECUTE AS 'domain\username'   AS   EXEC msdb.dbo.sp_start_job B'jobname' ;   RETURN   The domain\username is the in the database sysadmin role and the owner of the job.  To make this work originally, I had to change the msdb database to be trusted.   This worked for the past several months.   Now it doesn't work (perhaps after a reboot but not sure).  The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'   I looked to make sure that the account had grant execute rights and it does.  I tried setting it via GRANT statement and it was granted successfully yet the error still occurs.  I've tried changing accounts and anything else I can think of to no avail.   Any ideas how to troubleshoot this issue.  I've tried all the tricks I can think of.   Thanks - SM

DYNAMIC SELECT QUERY IN STORED PROCEDURE

  
Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Had working Stored Procedure for years and broke all of sudden

  
I had a stored procedure that used a simple cursor to do a query against a linked SQL 2005 database.  It worked for years and failed out of the blue a month ago and the only way to fix it was to modify the cursor declaration to make it INSENSITIVE.  Any ideas why this would be?  No data is modified on the linked DB, just the local DB.  Seems weird this worked for years up until mid-august.  All SPs with cursors that were not local had to be modified to be INSENSITIVE.   Thanks Henry    MVP WanneBe

stored procedure: select and update

  
Is it possible to use a stored procedure to get a value of the database, change it and save it back at ones? Let me show a simple example: In the databse I have an integer value (20). Then I get another number (5), and I would like to subtract it of the  value from database (20 - 5). That means I have to get the value 20 out of the database, of it subtract value 5, and insert (update) new value (15) back to the database. Or is it not even necessary to get the value 20 out of the databse, but only directly subtract value 5 of the value in the database? Is this possible to do with stored procedure?

Its posible to do a select without the table and the column names into a stored procedure?

  

Hello!!

I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

calling stored procedure with in select statement

  

Hi,

        Any one pls tell me how to call the stored procedure in SQL server


Stored procedure returns two select results

  

Hey guys

I am creating a buddy list and I need to select my entire buddy list and then the ones that are online

I have two querries one is your standard

 

SELECT *
FROM buddies

 

the other is a bit more complicated

 

SELECT Buddy as Online
FROM Buddies
WHERE EXISTS (SELECT Buddy
FROM online 
WHERE online.Buddy = Buddies.Buddy)

I can't use the union command since I create another column

What I would like is to append the column to my original result so I can access it from some vb script using the sqlclient.datareader

 


INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

  
I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.
Stephen

MSSQL 2005 JDBC driver multiple select statements in stored procedure

  

Hello,

we have a stored procedure with multiple select in it.

 

Example

Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3

 

Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.

 

 

 


Stored Procedure stopped working in Visual Studio 2010; System.Data.SqlClient disabled?

  

Hi,

In Visual Studio 2010, I have a webform, with a button that attempts to manipulate a SQL table that has a lot of data (several years & archives of 12 people's emails: emailID, bodies, to, cc, bcc, date/time).  Problem has always been that with so many emails (500,000), I had to break up the manipulation jobs a bit, or VisualStudio/ASP.Net development server would freeze up running those SQL stored procedures.

So I determined, for each (.pst) email store, a unique series that was in each emailID - for instance, person A would have 00000195195xxxxx as their emailIDs, and person B would have 00000123456xxxxxx as their email IDs - and used those 30 [or so] identifying flags to recursively call a function that until yesterday was running my SQL stored procedures just fine. 

YEsterday, while that was running, for giggles, I rebuilt the index on my table, and freaked out the development server by accident.  I had to rollback the database using SQL Server Management Studio, or the database was "Suspect".

Now, my SQL Stored Procedure in that recursively called function won't run, despite the fact that the original SQL Query [that gets the identifying flags into a Datatable, and runs the sproc on each "row"] works just fine.

I think it's a setting, to protect me ... but can't

Stored Procedure stopped working in Visual Studio 2010; System.Data.SqlClient disabled?

  

Hi,

In Visual Studio 2010, I have a webform, with a button that attempts to manipulate a SQL table that has a lot of data (several years & archives of 12 people's emails: emailID, bodies, to, cc, bcc, date/time).  Problem has always been that with so many emails (500,000), I had to break up the manipulation jobs a bit, or VisualStudio/ASP.Net development server would freeze up running those SQL stored procedures.

So I determined, for each (.pst) email store, a unique series that was in each emailID - for instance, person A would have 00000195195xxxxx as their emailIDs, and person B would have 00000123456xxxxxx as their email IDs - and used those 30 [or so] identifying flags to recursively call a function that until yesterday was running my SQL stored procedures just fine. 

YEsterday, while that was running, for giggles, I rebuilt the index on my table, and freaked out the development server by accident.  I had to rollback the database using SQL Server Management Studio, or the database was "Suspect".

Now, my SQL Stored Procedure in that recursively called function won't run, despite the fact that the original SQL Query [that gets the identifying flags into a Datatable, and runs the sproc on each "row"] works just fine.

I think it's a setting, to protect me ... but can't

Not working a stored procedure executing in vb net instead of running it via sqlcmd

  

I created a tored procedure which runs perfect using sqlcmd command, it works also using "Step Into stored procedure" from sserver explorer using Visual studio net. But when i execute this procedure using "ExecuteNonQuery" it doesn't work. Does anyone has any idea why is that?

Thanks for help


pp

Select within Stored Procedure

  

Hi...

What the correct syntax for this, im  doing this in a stored procedure.

	select @useridHigh, @userBalHigh = UserID,MAX(Balance)
		from [User]
		where LastName = 'Tom'
		group by UserID

 Thanks

Rebekah


Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions

  

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names

  

Hi

I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.



Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend