I have a table which is referenced in multiple stored procedures .Among them only one procedure loads data (INSERT statement) to the table. In all other procedures table is referenced in FROM clause.
My question is how do i find the stored procedure that is actually loading data into that table.
Hi,
I need to a find table which is used by list of stored procedures.
Can you please send me the query which is used?
Thanks and Regards
Abdul M.G
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
View 3 Replies View RelatedI am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
I've looked through many suggestions and partial examples all overthis newsgroup and still am not coming up with anything that doesspecifically what I'm wanting to accomplish.I'm writing a VB 6.0 application which uses SQL Server as theback-end.Here's an example of what I'm wanting to do...A user accessing the VB GUI attempts to open a certain form. Coderuns behind the scenes in VB that checks the user's "Execute"permissions on the stored procedure that retrives the data into theform. If the user is determined to have "Execute" permissions to thatstored procedure, the user is allowed to proceed with opening theform. If they don't have permissions, they are informed of this andaren't allowed to open the form and view the data.Let's say that we initially determine through the VB code that theuser has the ability to "Execute" the stored procedure that allows forviewing of the data. We go ahead and let them open the form. Oncethe form is being opened though, we run VB code to check for theirpermissions on other stored procedure that can be used in UPDATING /INSERTING / DELETING the records being displayed in the form. If theydon't have "Execute" permissions on these particularUpdate/Insert/Delete stored procedures, then I'll have a text box onthe form show as visible to advise them while they're looking at thisdata that they can't do anything to this data other than view it...they can't insert new records, they can't delete any records, and theycan't update any records.I have yet to find specific code that tells me how to checkpermissions on a storedc procedure for the user that's logged-in, whatthe resulting codes (i.e. 12291, etc.) mean as far as identificationof their permissions, etc.If anyone can help me out here, I'd greatly appreciate it. I'd hateto just have to resort to trying to allow the user to run the storedprocedures and just trap the error codes that may arise once eachstored procedure is executed and inform the user on the tail-end ofthe process that they can't run the stored procedure. I'd like toidentify their permissions to the stored procedures on the front-endof the process either before each form is opened or just as it'sopening to advise them early as to what they can/can't do with thedata being displayed in the form.Thanks in advance for any examples/information!Sincerely,Brad H. McCollumJoin Bytes!
View 1 Replies View RelatedJust wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies View RelatedI have a stored procedure (sproc) running in Firebird Database. Now I try to use this sproc to build a report in the SSRS 2012 (SSDT) Report Designer.
After I connect to the dataset, and when I choose the Query Type as 'Stored Procedure', and type the name of my sproc, and then go to the Query Designer, and click ! (F5) , I get the following error:
TITLE: Microsoft SQL Server Report Designer
------------------------------
An error occurred while executing the query.
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 1 [sproc name]
ADDITIONAL INFORMATION:
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 1 [sproc name] (OdbcFb)
------------------------------
BUTTONS:
OK
------------------------------
how to resolve this? If its a T-SQL sproc, then it will be no issue; in this case, Firebird database connects to the SSRS via a 32-bit System ODBC connection.
When I proceed and click ok for everything, I get this error (after I exit the Query Designer), in the Dataset Properties tab:
"Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."
The ODBC connection between SSRS and Firebird works fine. In fact I am able to run many queries as ad-hoc ones, but when I use the queries as sprocs, then this issue pops up.
how to handle Firebird stored procedures in SSRS.
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
Hi,
I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies View RelatedI am trying to create a stored procedure that Deletes Table if there is Data.
Also stored procedure will Insert new data into table.
I have already created table. This is part of my current stored procedure.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'srd.[PNMACjmerlos].FHLMC_Trials') AND type in (N'U'))
DELETE srd.[PNMACjmerlos].FHLMC_Trials
I have a table which has data like shown in below image , I want to get count of case numbers which has responses 123 AND 125 ( case should have both responses), like this I have requirement for other responses, how can I do it in Stored procedure .
View 8 Replies View RelatedI've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
View 7 Replies View RelatedHi there,
Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so.
Please help.
Thanx
Hi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,
View 8 Replies View Related
I want to create a CLR Stored Procedure with a Table User Defined Type like this short example in SQL:
CREATE TYPE [dbo].[TypeTable] AS TABLE
(
[Id] [integer] NOT NULL,
[Value] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
[Code] ....
I found some example in this link : CLR UDT
But I can't figure how to do the same with a User Defined Type Table.
WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:
Begin Transaction
Lock Table XTable
Drop XTable
Alter Table XTable_temp rename to XTable
Release Lock XTable
End Transaction
Create XTable_temp
I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.
Any ideas on a process that might help?
TIA,
A
I want to read file from c:abc.txt through sql programming and Insert this file data into table.
suppose c:abc.txt has following data
aman 10 50,000
sumesh 20 40,000
suppose I have created table abc( a varchar(30), b int, c int)
How I will do it
Thanks
Ajay
Hi,
I created a package to load a fact table
which loads more than 7 million records.
When loading the table it took nearly 15 minutes.
Then indexes were created for the table at the DB level after which the time to load same number of records has increased.
How to resolve this time delay?
Hello, I want to load data from text file to MS SQL DB table.
In MySQL, it is the "LOAD DATA INFILE..." query statement.
What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?
I developed a ASP.net web application with a MSDE database backend on my laptop(vs.net 2003 XP Pro), then I transferred the website onto a server(Windows Server 2003) and generated a SQL Server 2000 database from the scripts I exported from MSDE(web administrator). The problem I am having is that it can't find any stored procedures. I keep getting errors when logging on, 'Could not find stored procedure "_myProc" '.
Any one with any clues what might be the problem?
Yes I have changed the connection strings.
Thanks in advance
P
I use the following code in ASP.NET 2.0 to update the database:
Dim myConnection As New SqlClient.SqlConnection("server=local);uid=sa;pwd=xxx;database=Northwind")Dim myCommand As New SqlClient.SqlCommand("dbo.spTralen_customer_save 'CACTU'", myConnection)myCommand.CommandType = CommandType.StoredProceduremyConnection.Open()myCommand.ExecuteReader(CommandBehavior.CloseConnection)
I get the following error message: "Could not find stored procedure..."
The sp is in the database and dbo is the owner of the sp and I'm logged in as sa as you can see above. It doesn't matter if I remove the "dbo." from the sql command, it still doesn't work. If I remove the parameter value 'CACTU' above I get an error message saying that the sp expects the parameter so the sp is obviously in the database.
Can someone please help me as soon as possible!// Gato Gris
Not sure if this question belongs here or in a .NET forum.
But Im going to give it a shoot. The problem is that Im getting the following error: "Could not find stored procedure 'xxx'".
Ive never used stored procedures before, so what I am wondering is there anything basic that Im forgetting?
I have this simple stored Procedure:
ALTER PROCEDURE Person_info
@FirstN varchar(128),
@LastN varchar(128)
AS
SELECT FName, LName
FROM Person
WHERE (FName = @FirstN) AND (LName = @LastN)
and each time I call this procedure I get the prior stated error.
returnValue = sqlcmd.ExecuteReader(); //crashes when this line executes.
Ive found some people talking about this and it might be caused due to the "initial catalog=<database name>" in the connection string is missing. I tried that but didnt work.
Hello,Our SQL machine is getting bogged down by some sort of stored procedureand I am trying to find which one. My SQLdiagnostic software (by Idera)that monitors our SQL server, says that these commands are executingand taking upwards of 30 minutes to run. This is new and unexpected.The commands are:exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =25, @Pm2 = 2, @Pm3 = 1exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =105, @Pm2 = 2, @Pm3 = 1exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =57, @Pm2 = 2, @Pm3 = 1I am getting pages of these and yesterday the are taking upto 30minutes to run (currently they are taking 1-2 minutes to complete w/opeople on the machine).We are not getting much help from our software vendor (of ouradmissions software, not Idera) on this matter. I have sa access to theSQL machine and I can see the pages and pages of stored procedures, butI don't know what the above is running. I want to find the storedprocedure that keeps getting executed. Is the @Pm0 = an encryptedentry?Any advice I would appreciate.ThanksRob Camarda
View 2 Replies View RelatedI have an Access 2000 database connected to a SQL Server and am tryingto execute my first stored procedure. I created the stored procedureand verified that it works, but when I try to execute it from Access:cnn.Execute("sp_IPT")it says: 'Could not find stored procedure 'sp_IPT'Any ideas?Norman B. ScheininF-22 Applications DevelopmentM/S 4E-09(206) 655-7236Join Bytes!
View 1 Replies View RelatedI have this error (Merge Replication):
The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.
How can I solve this problem?
Thanks.
Hi Every one,
How can I load or copy say millions of rows to a table in the database faster?
Thanks,
Mejo George
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies View RelatedI have a Custom .net assembly which retrieves some data, basically just a set of data containing, ID, value,
I need my stored procedure to return this data as a table.
My question what/how is fastest way to do this, my assembly can return anything you want, a datatable, a hashtable, list of(class) etc..
I am not sure were to start on how to fix this. I am not having any problems connecting to the mssql 2000 server. My problem is Could not find stored procedure 'CMRC_ShoppingCartAddItem'. The user has exec permissions on that procedure. This is a custom VB.net 2005 web application.. Does any one have any ideas on how to check whats wrong?
The sub that calls the procedure:
Public Sub AddItem(ByVal cartID As String, ByVal productID As String, ByVal Company As String, ByVal quantity As Integer) ' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection("Data Source=MANDB02;Initial Catalog=db_name;UId=nobigaccess;Password=$$$$$$$") Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingCartAddItem", myConnection) ' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterProductID As SqlParameter = New SqlParameter("@ProductID", SqlDbType.NVarChar, 15) parameterProductID.Value = productID myCommand.Parameters.Add(parameterProductID) Dim parameterCompany As SqlParameter = New SqlParameter("@Company", SqlDbType.NVarChar, 8) parameterCompany.Value = Company myCommand.Parameters.Add(parameterCompany) Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50) parameterCartID.Value = cartID myCommand.Parameters.Add(parameterCartID) Dim parameterQuantity As SqlParameter = New SqlParameter("@Quantity", SqlDbType.Int, 4) parameterQuantity.Value = quantity myCommand.Parameters.Add(parameterQuantity) ' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub This is what the procedure looks like in sql: CREATE Procedure CMRC_ShoppingCartAddItem( @CartID nvarchar(50), @ProductID nvarchar(23), @Company nvarchar(8), @Quantity int
)
AsDECLARE @CountItems intSELECT
@CountItems = Count(ProductID)FROM
CMRC_ShoppingCart
WHERE
ProductID = @ProductID AND Company = @Company AND
CartID = @CartID
IF @CountItems > 0 /* There are items - update the current quantity */
UPDATE
CMRC_ShoppingCart
SET
Quantity = (@Quantity + CMRC_ShoppingCart.Quantity)
WHERE
ProductID = @ProductID AND Company = @Company AND
CartID = @CartID
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO CMRC_ShoppingCart ( CartID, Quantity, ProductID, Company ) VALUES
(
@CartID,
@Quantity,
@ProductID,
@Company
)
GO
can anyone please help me with my poll application? whenever i run it, i will comes out with this error "Could not find stored procedure 'GetActivePoll'". i've got stored procedure in my database with the name GetActivePoll', how come it cannot find the stored procedured? below are some images and codes i've attached with. 1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="poll.aspx.vb" Inherits="Polls_poll" title="Fanzine if Liverpool FC" %>
2 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
3 <div style="text-align: left">
4 <span style="font-size: 30px; color: #3333ff; font-family: Verdana"><strong><span
5 style="color: #000000">Please take a vote...</span><br />
6 </strong></span>
7 </div>
8 <div style="text-align: left">
9 <br />
10 <table width="100%" align="center">
11 <tr>
12 <td style="width: 100px; border-top: thin solid; height: 20px;">
13 <asp:Label ID="lblPollQuestion" runat="server" Font-Bold="True" Font-Names="Verdana"
14 Font-Size="10pt" Text="Poll Question" Width="500px"></asp:Label></td>
15 </tr>
16 <tr>
17 <td style="width: 100px">
18 <asp:RadioButtonList ID="rdoPollOptionList" runat="server" Font-Names="Verdana" Font-Size="9pt" Width="500px" DataSourceID="SqlDataSource1" DataTextField="PK_PollId" DataValueField="PK_PollId">
19 </asp:RadioButtonList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Poll.mdf;Integrated Security=True;User Instance=True"
20 ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Polls]"></asp:SqlDataSource>
21 </td>
22 </tr>
23 <tr>
24 <td style="width: 100px; text-align: left">
25 <asp:Button ID="btnVote" runat="server" Text="Vote" Width="71px" BackColor="Silver" BorderColor="Silver" BorderStyle="Solid" Font-Bold="True" ForeColor="White" /><br />
26 <br />
27 <asp:Label ID="lblError" runat="server" Font-Names="Verdana" Font-Size="Smaller"
28 ForeColor="Red" Text="You cannot vote more than once..." Visible="False" Width="500px"></asp:Label></td>
29 </tr>
30 </table>
31 </div>
32 </asp:Content>
33
1 Imports System.Data
2 Imports System.Data.SqlClient
3 Partial Class Polls_poll
4 Inherits System.Web.UI.Page
5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
6 If Not IsPostBack Then
7 DisplayPoll()
8 End If
9 End Sub
10 Private Sub DisplayPoll()
11 Try
12 Dim ds As DataSet = getActivePoll()
13
14 lblPollQuestion.Text = ds.Tables(0).Rows(0)("Question")
15
16 Dim i As Integer = 0
17 For Each dr As DataRow In ds.Tables(1).Rows
18 rdoPollOptionList.Items.Add(dr("Answer"))
19 rdoPollOptionList.Items(i).Value = dr("PK_OptionId")
20 rdoPollOptionList.SelectedIndex = 0
21
22 i = i + 1
23 Next
24 Catch ex As Exception
25 Throw ex
26 End Try
27 End Sub
28 Private Function getActivePoll() As DataSet
29 Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
30 Dim sqlConn As New SqlConnection(strConnString)
31
32 sqlConn.Open()
33 Dim sqlCmd As New SqlCommand()
34
35 sqlCmd.CommandText = "GetActivePoll"
36 sqlCmd.CommandType = Data.CommandType.StoredProcedure
37 sqlCmd.Connection = sqlConn
38
39 Dim ds As New DataSet
40 Dim da As New SqlDataAdapter(sqlCmd)
41
42 da.Fill(ds)
43
44 sqlConn.Close()
45
46 Return ds
47 End Function
48 Protected Sub btnVote_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVote.Click
49 If Response.Cookies("Voted") Is Nothing Then
50 Response.Cookies("Voted").Value = "Voted"
51 Response.Cookies("Voted").Expires = DateTime.Now.AddDays(1)
52
53 lblError.Visible = False
54
55 RecordVote()
56 Else
57 lblError.Visible = True
58 End If
59 End Sub
60 Private Sub RecordVote()
61 Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
62 Dim sqlConn As New SqlConnection(strConnString)
63
64 sqlConn.Open()
65 Dim sqlCmd As New SqlCommand()
66
67 sqlCmd.CommandText = "IncrementVote"
68 sqlCmd.CommandType = Data.CommandType.StoredProcedure
69 sqlCmd.Connection = sqlConn
70
71 Dim sqlParamQuestion As New SqlParameter("@i_OptionId", Data.SqlDbType.Int)
72
73 sqlParamQuestion.Value = rdoPollOptionList.SelectedValue
74
75 sqlCmd.Parameters.Add(sqlParamQuestion)
76
77 sqlCmd.ExecuteNonQuery()
78
79 sqlConn.Close()
80 End Sub
81 End Class
I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?