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

Post New Web Links

Custom DB Installation Issue While Run the Stored Procedure Script

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

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

View Complete Post

More Related Resource Links

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

run a bunch of sql script files using a stored procedure (without using xp_cmdshell)

Is this possible? Let me elaborate. I need to automate the execution of a bunch of sql script files (*.sql) placed by my users in a certain folder. One way I can do this is by using some kind of a script to loop thru the files in that folder and for each *.sql file found, launch a 'sqlcmd' command to execute the current script file using the -i option, ie, sqlcmd -S <server> -i <the current sql file in the loop> I tried doing this, but ran into a liitle bit of difficulty with the particular scripting language (JAVA). I was going to research this a bit more, but I also wanted to consider something that doesn't involve another script. So I was thinking about doing this using a stored procedure, but then I was wondering, how would I run a sql script file from sql server? The only way I can think of is still using sqlcmd, but then to use that within a stored procedure, I would need to turn on xp_cmdshell. Although I can do that, that invloves getting other people involved so I was wondering if there is way to do this without turning on xp_cmdshell.

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

Collation Issue in Stored Procedure



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.


custom stored procedure error


We have been presenting an issue randomly since we changed our replication to use custom stored procedures for replication.

We have several offices which replicate to our central datacenter, each of this offices have different data corresponding only to their site and we centralize the data from all databases in only one database.

We use custom stored procedures for some tables only for the insert method, but randomly the update method of that table drops and I have to create again. We have been set the replication by using the 'replication support only' method so it only creates the replications sp. All our articles have the create update, insert and delete stored procedures for replication.

We have done traces for this and the replications is generating the drop but it doesn't recreate the procedure. It only occurs for the articles with custom procs for insert.  

The snapshot jobs for each office are disabled.

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
  • <

    ssis deployment issue - Unable to load custom dll - due to change in Sql server installation


    Hello all

    I had a strange issue in testing my package, after it was deployed to a Test server.

    The details of the server

    Sql 2005 is installed, in the D:\ drive

    I have used a package, where i use a custom DLL to read values. Hence, i added that DLL to the GAC. I was in the assumption that this should complete my proceess of deploying this package.


    However once i executed the package, i got an error - Could not load the Helper object.

    Unable to CAST "system.object"  to namaspace.class.Helper.


    I reviewed the setting on my macihine, since as part of development inorder to reference the dll i added this to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies location so that i can reference this from BIDS. I checked that location in my test server, i was not present. This location was present in the D: drive ( i assume this was present since i installed my complete SQL Server in the D:\ ). I copied the dLL in that location and added it to GAC from that location. My package still failed with the same error.


    To resolve this issue, i created the same path in the C:\ folder as C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

    assemblies was newly created in C: and i added the dll to GAC from there. Now my package worked.


    Custom paging sorting stored procedure problem



    I want to make my gridview enable for custom paging and sorting. I started to follow a tutorial and write the Stored procedure.

    But my stored procedure seems not to be working. here it is:

    ALTER PROCEDURE [dbo].[USP_getStudentsEmail]
        @sortExpression nvarchar(100),
        @startRowIndex int,
        @maximumRows int,
        @receiver nvarchar(38)
    IF LEN(@sortExpression) = 0
        SET @sortExpression = 'SendDate'
    DECLARE @sql nvarchar(4000)
    SET @sql = 'Select *
    	Select m.Body,m.Subject,m.SendDate,m.SenderProfile,m.Receiver AS Receiver,m.ID,s.FirstName,ROW_NUMBER() OVER (ORDER BY '+ @sortExpression + ') AS RowRank 
    	from Message m INNER JOIN StudentProfile s ON m.Sender=s.UserId
    ) AS OrderedEmails
                WHERE     m.Receiver= '+ CONVERT(nvarchar(38), @receiver) + 'AND RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                    ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                    + CONVERT(nvarchar(10), @maximumRows) +')'
    EXEC sp_executesql @sql

    I tried to run this by the following line

    exec USP_getStudentsEmail 'SendDate',1,1,'5ed19f91-eeb7-4c6a-b564-98c8dd83e18c'

    but it says  

    Incorrect syntax near 'd19f91'-

    can anybody help

    How to execute a parameterized stored procedure using ssis dataflow script component transformation


    Hi All,

    Can any body send code for how to execute a parameterized stored procedure  using ssis dataflow script component transformation using .net

    I know how to execute storedproc using control flow script task, but I need using ssis dataflow script component transformation.




    Need to order stored procedure output based on custom order


    *Read the whole question before you decide to send this to the chart forum

    So here is what I am trying to do:

    I have a chart in VB Express (vb.net) that obtains its data from a stored procedure in my SQL Express database. The chart is a stacked column chart and shows events on the X axis. What I need is these events to be displayed in a very specific order in my X Axis.

    The problem:

    I have been told that I cannot custom order this in the application, but rather I need to change my stored procedure output to get the events in the order I want. It was suggested to me that I should make a new table in my database that has the names of the events in 1 column and the order I want them in, in the other. Then use that to order my output in my stored procedure.

    I do not care how I accomplish what I need to accomplish so long as I do indeed accomplish it. Here is some database structure to help you understand my database:

    Table: Buyer

    BuyerID (PK), ProjectID (FK), Status, Type

    There are 12 different statuses, though my stored procedure doesn't know that. These 12 statuses need to be shown in a very specific order. My current stored procedure is as follows:

    select Status as Status, Type as Type, count(BuyerID) as Value from Buyer WHERE projectID = @ProjectID AND Status IS NOT NULL AND Type IS NOT NULL GROUP BY Status,Ty

    stored procedure based custom conflict resolver truncates data


    I created a stored procedure based custom conflict resolver in SQL 2005, I return the winning result set and also save that result set to a test table to compare the values.  The values saved to the test table are correct but some of the values saved as the conflict winter are truncated.


    Example a char(3) filed is updated at the subscriber as '111' and updated at the publisher as '222', in my custom conflict resolver if I use the value from the subscriber the conflict resolver updates the field as  '11 ', if I use the publisher value the conflict resolver updates the field as '22 '.  Now the same records is saved to the test table correctly as either '111' or '222' depending on the logic I used.  So the result set has the correct values, its after the custom conflict resolver is called where the values is somehow truncated.  Has anybody run into this before and what steps can I take to avoid this.


    Thank you,

    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.

    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