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


Post New Web Links

SQL Server 2008 - Stored Procedure

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server
 

I work for a software manufacturing company and I am doing a project for a very large potential customer.  This customer has a SQL Server 2008 DB and claims that his emplpyee's can only access the DB (read or write) using a stored procedure.  Now the catch is we cant re-write the software to accomidate this customer, and our requests to see the stored procedure to see how hes using it have failed.  But I need to be able to access his DB at no additional cost to the customer.  Is there any way to call a SP using a Data Source Driver?  Or any other ideas, I need an answer for this cutomer today :(




View Complete Post


More Related Resource Links

SQL Server 2008 Stored Procedure output parameter

  
I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

debugging stored procedure created in sql server 2008

  
Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

debugging stored procedure created in sql server 2008

  
Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

CLR Stored Procedure that invokes the CRM Web service SQL Server 2008 R2

  

Hi

I have created a CLR stored procedure that invokes the CRM Web service to delete records, the code complies fine but when I try to deploy it it just times out. I feel that I am missing something, has anyone any suggestions.

There are a number of blogs about the subject of invoking Web services from a CLR stored procedure but they are about doing it in 2005, is the procedure the same?

Regards

Andy


Stored Procedure on SQL SERVER 2008 R2

  

I have 2 tables:

(1) tblSpecies with Columns ID int, Code nvarchar(50), Description nvarchar(50)

(2) tblSpeciesCrossReferenceCodes with Columns ID int, SpeciesCode nvarchar(50), etc. etc.

I want to invoke the following  procedurewith 3 input parameters  in Paged GridView.

When I test this procedure under ServerControl with the value of @SpeciesCode ="APQ"

I get the error:

" Invalid Column Name APQ."

 

This points to the statement:

        WHERE (X.SpeciesCode LIKE ' + @SpeciesCode + '))

I would be grateful if an Expert could tell why I should I

get this message and where am I going wrong?

==============================================================

 

ALTER PROCEDURE [dbo].[StoredProcedureFilterXCodes]
 (
 @startRowIndex int,
 @maximumRows as int,
 @SpeciesCode as nvarchar(50)
 )
AS

IF @SpeciesCode IS NULL
      EXEC dbo.GetSpeciesCrossReferenceCodes @startRowIndex, @maximumRows
ELSE

BEGIN

    SET @startRowIndex=@startRowIndex+1
 DECLARE @sql nvarchar(4000)

 SET @sql = 'SELECT ID, PresentationCode, StateCode, FreshnessCode, SizeCode, Sp

Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

  
Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:

1)http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html
2)http://stackoverflow.com/questions/329051/execute-db2-iseries-stored-procedure-from-a-sql-2005-linked-server

I installed linked server using all this 3 providers

sp_addlinkedserver
    @server=N'DB2',
    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
    @catalog='S65CF29B'
go
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.


declare
  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a

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.

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

  
Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Folders or folder like equivalent in SQL Server 2008 ? for packages & stored procedures ?

  
Hi,      Currently running SQL-Server 2000.  We have hundreds of stored procedures and DTS packages. We have so many it's becoming a serious challenge for names and such.  We were wondering if SQL Server 2008 has a way to 'nest' both the equivalent of DTS packages (SSIS) and stored procedures in something akin to 'folders'.  We know we have to upgrade one of these days but a version that would handle these would be much more attractive. Just getting this functionality would be worth whatever effort would be required to upgrade.   Thanks in advance for any help on this.

CLR stored procedures asssemblies working in sql server 2005 but giving issues over sql server 2008

  
Hi I developed CLR stored procedures assemblies using VS2005 and deployed in sql server 2005 are working fine. Currently we are migrating to sql server 2008. The same CLR assemblies are not working in 2008 version and raising following error. Here i created CLR assemblies in sql server 2008 using 'sa' user account which is having db_owner permission over 'mydatabase'. I am getting an error message like "A .NET Framework error occurred during execution of user-defined routine or aggregate". In sql logs showing messages like as -- Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework\v2.0.50727\. -- AppDomain 2 (mydatabase.dbo[runtime].1) created.   Please let me know what may be the cause for this type of issues. Is there any specific backward compatibility settings has to be done over sql server 2008 or sql server 2008 R2 while using 2005 CLR routines?   Thanks in advance.  

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

  
I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......

ALTER PROCEDURE [QMS].[GetQueries]

@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS



SELECT [QueryID]

,[UserID]

,[QueryName]

,[Comments]

,[CreateDate]

,[UpdateDate]

,[ExpirationDate]

,[LastAccessedDate]

FROM [ReportQueries]

WHERE

[UserID] = @UserID

Accessing Stored Procedure Of SQL Server From Popup window

  

Hello everybody.

I have a parent window parent.aspx and a popup window popup.aspx. parent.aspx has a confirm button which on click open popup.aspx. This popup.aspx holds a filled application form like leave application form of employees and a email sending button. That button on click send emails to the specified email addresses with attachment. The

how to create stored procedure in sql server ???

  

here i have 3 attributes i want to write a sp it should take 1input value by tht i get remaining 2out put values

 table name: Employee

attributes: id,name,sal;

help me in this....


if this exected i should get succecss msg also and boolen is true...

else


flase and not exectuted msg...


also


how to log all inputs for each stored procedure? plss help as I am new to sql server

  

Hi ,

I have upto 16 calculators(I mean 16 stored procedures) in one database.How do I log all inputs for each calculater(each stored proc)?.Most of the variables are not populated in the output table so it became hard to reproduce.The variables are not consistant across all the 16 procs . Do I need to set up new table for these inputs?

your help would be appreciated ..pls help how do I create a table or write a common proc ? Your help with the idea and code will be greatly appreciated!

 

Thanks


executing stored procedure from different server

  

'Hi All,

If have two servers A and B.I have a stored procedure in server A.But i need to execute the Stored procedure from server B.

i have a job in server B, in the step of that job,it should be executing the stored procedure from Server A. how can we do this.there is no linked server.

Exec sp_prcLoadData (this is in server A,but i need to execute it from Server B.)

 

Thanks in Advance


executing stored procedure from different server

  

'Hi All,

If have two servers A and B.I have a stored procedure in server A.But i need to execute the Stored procedure from server B.

i have a job in server B, in the step of that job,it should be executing the stored procedure from Server A. how can we do this.there is no linked server.

Exec sp_prcLoadData (this is in server A,but i need to execute it from Server B.)

 

Thanks in Advance


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