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

Top 5 Contributors of the Month
Sandeep Singh
Post New Web Links

Collation Issue in Stored Procedure

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


My stored procedure uses the temp table and other reporting DB table to combine the output. If I specify (hardcode) the collation to temp table column then it works fine.

But I want to retrieve the Collation name of Reporting DB within the stored procedure and assign it to temp table or temp table column.

Can anyone help me in this since I am not able to find any solution in internet?

Thanks in advance.


View Complete Post

More Related Resource Links

Stored Procedure Subquery Issue

Hi, In the below stored procedure, I often get this error message... "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Why is this? I check for multiple entries and delete any that exist. Yet still gives that error on occassion.  Thanks. ALTER PROCEDURE [dbo].[MainTbl] -- Add the parameters for the stored procedure here @Name nvarchar(5), AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Begin Transaction DECLARE @DetailsColour nvarchar(6); -- Insert statements for procedure here Begin -- Details DECLARE @CountDetails int; DECLARE @FirstEntryDetails int; SET @CountDetails = (SELECT COUNT (Name) FROM [DetailsTbl] WHERE Name = @Name ) if (@CountDetails > 1) Begin SET @FirstEntryDetails = (SELECT TOP(1) NameID FROM [DetailsTbl] WHERE Name = @Name ORDER BY NameID ASC) DELETE FROM [DetailsTbl] WHERE Name = @Name AND (NameID != @FirstEntryDetails) End Set @DetailsColour = (SELECT DetailsColour FROM [DetailsTbl] WHERE Name = @Name) if @DetailsColour is null begin set @DetailsColour = 'RED' end DECLARE @FoundData int; SET @Found

Stored procedure SQL issue


 Hi there.

Within my stored procedure I have a piece of SQL that is supposed to remove from a temporary table, any values that are not set to '1' for a particular field, but this does not work as required.

The SQL in question looks like this:


  • DELETE FROM table1
  • WHERE value_1 NOT IN
  • (
  •     SELECT tab1.value_1
  •     FROM table1 tab1
  •     JOIN table2 tab2 ON tab1.value_1 = tab2.value_1 AND tab1.line_no = tab2.line_no
  •     AND tab1.client = tab2.client
  •     JOIN table3 tab3 ON tab3.client = tab2.client 
  •     AND tab3.THIS_VALUE = 1 AND tab3.value_2 = tab2.value_2
  •     JOIN table4 tab4 ON tab3.client = tab4.client AND tab3.value_3 = tab4.tab4_value
  •     JOIN table5 tab5 ON tab5.client = tab4.client AND tab5.art_id = tab4.art_id
  • <

    Custom DB Installation Issue While Run the Stored Procedure Script


    1. I have created Custom DB Installation by Installar Class
    2. I created Stored Procedure Script From DB
    3. Copyied Script in sql.txt
    4.Created Custom DB Setup and Executed, But I am getting SP script Execution issue, same script is working in QueryAnalyser Execution

    Here is My Execution code



    Sub ExecuteSql(ByVal DatabaseName As String, ByVal S

    Custom DB Installation Issue While Run the Stored Procedure Script


    1. I have created Custom DB Installation by Installar Class
    2. I created Stored Procedure Script From DB
    3. Copyied Script in sql.txt
    4.Created Custom DB Setup and Executed, But I am getting SP script Execution issue, same script is working in QueryAnalyser Execution

    Here is My Execution code



    Sub ExecuteSql(ByVal DatabaseName As String, ByVal S

    deadlock issue on my database. how to identify which stored procedure which has to be modified.

    Hi ,
    I am seeing so many deadlock issues on my database captured by a 3rd party tool. Could you help me identify which stored procedure is actually causing the deadlock looking at the below log information. Let me know if you need more information.
    Thanks, Jeen
    <deadlock-list>  <deadlock victim="process54a7948">  <process-list>   <process id="process54a7948" taskpriority="0" logused="0" waitresource="KEY: 6:72057594044678144 (cc00f361c119)" waittime="2975" ownerId="404930267" transactionname="SELECT" lasttranstarted="2011-05-02T08:19:03.683" XDES="0x8000f940" lockMode="S" schedulerid="2" kpid="9624" status="suspended" spid="65" sbid="1" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-02T08:19:03.653" lastbatchcompleted="2011-05-02T08:19:03.653" clientapp="Microsoft SQL Server" hostname="MyServer" hostpid="2500" loginname="India\testr" isolationlevel="read committed (2)" xactid="404930267" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">   &l

    Send Email from SQL Server Express Using a CLR Stored Procedure

    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.

    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:

    ID (PK)
    PersonID (FK)
    LocationID (FK)

    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



    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.

    Sort by gridview SortExpression parameter via Stored Procedure


    I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

    Here is my gridview:

    <asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                            GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                            SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                                <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                        <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                <asp:BoundField HeaderText="User Name" DataField="UserName" />

    Stored procedure generator?


    Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.

    Create stored procedure from asp.net



    we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

    In sql server we can use "EXEC" function for execute dynamic query.

    Could anyone help me for create the dynamic query in Oracle?

    I just tried with "execute immediate", which would throws error as 

    "insufficient privileges".

    Please help me.



    Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


    Hi ,

    I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

    I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

     Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


    Thanks In Advance.

    Call SQL Stored Procedure Asyncronously



    I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

    Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

    Any help, please.

    Best regards,


    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?

    Problems connecting stored procedure to Crystal Reports


    I am working on updating a reporting system that uses Crystal Reports.  All of the 250+ reports were created in CR 8 and I have been opening up the old files in Visual Studio 2005 and updating the database location.  All I have had to do with all connections to views and even a couple of stored procedures is create a new connection to the database and then update the old report's datasource location.  The reason I need to update the location is so that the .NET application will use the correct driver.  Everything has worked fine up until I got to one stored procedure.  The old version of the report works perfectly with the stored procedure, but when I try to run the report using VS 2005 I keep getting errors.

    First I connected to the database using the Oracle OLE DB provider, then updated the location of the stored procedure in the report.  It then prompts me for the two parameter values for the stored procedure, both of which I keep as NULL so that the report parameters will be passed to the procedure.  Then I get the following error:

    Query Engine Error: 'ADO Error Code: 0x
    Source: OraOLEDB
    Description: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored'

    The proce

    Trying to run a stored procedure from vb code with oracle data provider.



    Here is my SP:

    create or replace
     Open p_getuserssignon_recordset1 for
     SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
    WHERE substr(UserId,1,2) <> vUid
    Order By UserId;

    I would like to run this SP from code and fill a gridview with the result. 

    I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

    I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

    First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 


    Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

    (I am using VS 2005, VB).

    Thank you.



    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