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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

A USE database statement is not allowed in a procedure, function or trigger.

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



I’m automating a large Archiving\Purging script on 1TB multi-databases.


I run all Sp’s from Archive database (Destination database).


The log file gets full fast in both source and destination databases and the whole process stopped, I have to clear all log files, as well I have to shrink file after clearing it.


View Complete Post

More Related Resource Links

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Difference between [Stored Procedure / Trigger & Function]






can any give me what are the major difference between [Stored Procedure / Trigger  & Function] with example


kinds regards


execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

Database trigger & permission

I created a DDL trigger to keep checking on who alter, create views/tables in a database. Almost most of the update are doing by poweruser. When I try to grant execute right to them.  SQL: GRANT EXECUTE ON [trgLogDDLEvent] to #ABC_Admin It prompts error: GCannot find the object 'trgLogDDLEvent', because it does not exist or you do not have permission. however, when I find the trigger by SQL. It does exist. What is the problem? select * from sys.triggers  

Update Database Record using GridView and Stored Procedure

Hi, I am trying to update record via stored procedure, but i got error at very start point. Problem is when i click on Edit link button within the Gridview it produce error. I can populate values from database fine but its produce error when i click on edit link button. Please see the code below. *********** ASP.net GridView Code I am using *****************<asp:GridView ID="GridView1" runat="server" DataKeyNames="RollID, EmpID" AutoGenerateColumns="False" Width="700px"> <Columns> <asp:TemplateField HeaderText="ApplicationID" Visible="false"> <ItemTemplate> <asp:Label ID="AppsID" runat="server" Visible="false"><%# Eval("RollID") %>'</asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ReleaseID" Visible="false"> <ItemTemplate> <asp:Label ID="rlsID" runat="server" Visible="false"><%# Eval("EmpID") %></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Roll Name"> <ItemTemplate> <asp:Label ID="rlsVersion" runat="server"><%# Eval("Roll_Name")%></asp:Label> </ItemTemplate> <EditI

Update Statement help from Multiple database

Hi I am having two separate datbase on same sever one datasbase name private from where i am executing my below query and other name is ODS Table name in Private=[A].[DimCapabilitySpecialty] Table name in ODS = [A].[CapabilitySpecialty]   both of them having column SpecialtyCd   i want to update the flag value of ODS table where there is particular specialitycd missing in source means table in private database   so for that i wrote below query   update A.DimCapabilitySpecialty set ActiveInd ='I' where SpecialtyCd !=[ODS].[A].[CapabilitySpecialty].[SpecialtyCd]   But it is giving me this error   Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "ODS.CapabilitySpecialty.SpecialtyCd" could not be bound.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

Managing TypedDataSets when there is a change in backend Table / Stored Procedure / Database schema

Hi All, I am just curious to know if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema.  I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain  The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again. Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.Hope above question makes sense. Any ideas.

Entity Model- Add Function Import - selected stored procedure returns no columns

I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

Stored procedure and table value function


I have a function called F_TBL_VALS_FROM_STRING this function returns a table and uses '|' as a delimiter to distinguish between values.
this function is used in a procedure (bellow), while I've tested this function and it returns the requested values, using it in this stored procedure does not work.

ALTER PROCEDURE dbo.mytestproc
    @doorList     varchar(4000),
    --From Date
    @FromDateYear   int,
    @FromDateMonth  int,
    @FromDateDay   int,
    --To Date
    @ToDateYear    int,
    @ToDateMonth   int,
    @ToDateDay    int,
    --From Time
    @FromTimeHour   int,
    @FromTimeMinute  int,
    @FromTimeSecond  int,
    --To Time
    @ToTimeHour     int,
    @ToTimeMinute   int,

Difference between procedure and function ?

What is the difference between procedure and function

Using a User Defined Function in a case statement


I created a function and am trying to access it through a case statement, but I am getting the error:

Cannot find either column "core" or the user-defined function or aggregate "core.f_FiscalYear_Open_Days", or the name is ambiguous.

The case statement is below:

CASE WHEN c.program_id = 5 and CAST(minutes) As float

Scope statement using Tail function has count error


My time hierarchy (TimeHier) has a QUADWEEK level with 28 members. When I select the last 13 members with the Tail function, I get the appropriate members:

Tail([Time].[TimeHier].[QUADWEEK].Members, 13))

However, if I use that same function inside a Scope statement, it only applies to the last 12 members. I have to increase the count by 1, to 14 to make it work inside the scope. I'm baffled.

Here's the full code:




Procedure or function ... has too many arguments specified


When i execute this SP in DB, it works fine. But when i execute from asp.net app it is throwing error:

Procedure or function ... has too many arguments specified


ALTER PROC [dbo].[sp_Customer]

@brchID char(40),
@brchName VARCHAR(50),
@userID VARCHAR(50)



DECLARE @req_pk  int
DECLARE @customer_org_pk int

IF EXISTS(SELECT * FROM [customer] WHERE [org_id] like @brchID)

    SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID


    EXEC sp_get_next_control_no 'organization.org_pk',@customer_org_pk out;
    INSERT INTO [customer] (org_pk,[org_id],[org_name])
    SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID

--Address Book Entry

IF NOT EXISTS(SELECT * FROM [address_book] WHERE [prsn_id] like @userID)

    exec sp_get_next_control_no 'person.prsn_pk',@req_pk out;

IF (@@ERROR <> 0)


calling stored procedure with in select statement



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

DDL Database Trigger


I have a DDL Trigger 'FOR DDL_DATABASE_LEVEL_EVENTS'  that works great for all users that are "sysadmin", but does not fire for new users that have limited security.  I thought that a DDL trigger fired on an event and did not look at the users.  I have provided the DDL Trigger below.....

Thank you in advance


/* This will install a dll trigger on every database except tempdb */

       ,@v_MAX INT
       ,@v_db  SYSNAME
       ,@v_SQL NVARCHAR(MAX)

  col1   INT IDENTITY (1,1),
  dbname SYSNAME

INSERT @t_db
  FROM master.dbo.sysdatabases (NOLOCK)
 WHERE name <> 'tempdb'
 ORDER BY name

SELECT @v_MIN = MIN(col1)
      ,@v_MAX = MAX(col1)
  FROM @t_db

WHILE @v_MIN <= @v_Max

  SELECT @v_db = dbname
    FROM @t_db
   WHERE col1 = @v_MIN
  'EXEC ['

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