SQL Server 2012 :: Stored Procedure - Find Invalid Combination
Sep 30, 2015
I have a scenario where I need to develop a stored proc to identify invalid input provided.
Following is a sample scenario
Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10));
insert into Product
select 'Prod1',10,'Black' union ALL
select 'Prod1',10,'BLue' union ALL
select 'Prod2',20,'Green' union ALL
select 'Prod2',10,'Black' ;
[Code] ....
In following TSql Code , Color and Size are optional. Both are provided as comma separated input. I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.
I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList where Table_Name in ( Select Name From sys.procedures Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%' Order by name )
A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100)) insert into @t2 values ('V') insert into @t2 values ('VL') insert into @t2 values ('1099') insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100)) insert into @t1 values ('I') insert into @t1 values ('U') from the above I need following output (attached output),
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN SET NOCOUNT ON; WITH Darin_Import_With_Key AS ( SELECT [pra_id] ,[pra_ClientPracID]
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
DOC_NO // REV_NO // FILE_NAME ABC123 // A // abc123.pdf ABC123 // B // abc123_2.docx ABC124 // A // abc124.xlsx ABC124 // A // - ABC125 // A // abc125.docx ABC125 // C // abc125.jpg ABC125 // C // abc125.docx ABC125 // C // - ABC126 // 0 // - ABC127 // A1 // abc127.xlsx ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE] WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN (SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
Hi everybody, I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot find the stored procedure '<proc_name>'", this is funny, when I execute the commandin a sql query window "exec sp_GetAllArticles", it also returns cannot find stored procedure, but when I execute the "Use <DBName>" and thenexecute the statement, it works. The sql connection used in codes is ok. So, what I have missed to set or where I made mistake?Code:Inside web.config, <add name="Connectstring" connectionString="Data Source=DENSIASQLExpress;Initial Catalog=DbName;Integrated Security=True" providerName="System.Data.SqlClient" /> inside Load event, DAL.ConnectionString = ConfigurationManager.ConnectionStrings["Connectstring"].ConnectionString; private void DisplayLatestResources() { DAL.CreateCommand("sp_GetLatestFiveTrainingResources"); DAL.ExecuteReader(); SqlDataReader dr = DAL.DataReader; ..... } Try supplying with user id and password, same result...also try using aspnet_regsql.exe, same result also... Thanks in advanced. den2005
This is my first forray into writing stored procedures and like the manual said created one in SQL Server 2005 parsed or tested it, executed it, tested it again succesfully,need to modify it but can't find it under stored procedures file where I initially right clicked and selected New Stored Procedure. Any idea where it went?
I have this data as below. I need to find out the combination from the data and take a count of them
CREATE TABLE A ( nRef INT, nOrd INT, Token INT, nML INT, nNode INT, sSymbol VARCHAR(50), nMessageCode INT ) INSERT INTO A ( nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode ) VALUES (1, 101, 1001,0,2,'SILVER',13073),
[code]....
if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to create proc usp_Proc1 @myTable myTable read only begin return 1 end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name? 2). How do you search *.dtsx files?
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.
Can you please correct my stored procedure, all i am doing is trying to create a temporary table #tempactions. When i am doing the following on query analyzer: execute createtempactions I am getting the error : Invalid object #tempactions
CREATE PROCEDURE dbo.Createtempactions AS DECLARE @SQL nvarchar(2000)
if exists (select * from #tempactions) Begin Set @SQL = 'drop table #tempactions' Exec SP_ExecuteSql @SQL End
Set @SQL = 'Create Table #tempactions(Actionno INT NOT NULL,'+ 'actioncode VARCHAR(200),'+ 'assignedto VARCHAR(200),'+ 'company VARCHAR(200),'+ 'duedate datetime,'+ 'completedate datetime,'+ 'actiondescription VARCHAR(200),'+ 'status VARCHAR(200),'+ 'comment VARCHAR(200))' Exec SP_ExecuteSql @SQL GO
I am having a bit of trouble with a stored procedure on the SQL Server that my web host is running. The stored procedure I have created for testing is a simple SELECT statement: SELECT * FROM table This code works fine with the query tool in Sqlwebadmin. But using that same code from my ASP.NET page doesn't work, it reports the error "Invalid object name 'table'". So I read a bit more about stored procedures (newbie to this) and came to the conslusion that I need to write database.dbo.table instead. But after changing the code to SELECT * FROM database.dbo.table, I get the "Invalid object name"-error in Sqlwebadmin too. The name of the database contains a "-", so I write the statements as SELECT * FROM [database].[dbo].[table]. Any suggestions what is wrong with the code? I have tried it locally with WebMatrix and MSDE before I uploaded it to the web host and it works fine locally, without specifying database.dbo.
I have a stored procedure that I am using to convert tables to a new format for a project. The project requires new tables, new fields in existing tables, dropping fields in existing tables and dropping an existing table. The SP takes care of doing all this and copying the data from the tables that are going to be dropped to the correct places. Everything is working fine except for one table and I can't figure out why.
For this particular table, it already exists in the database and has new fields added to it. Then I try and update those fields with values from another table. This is where I am getting the Invalid column name error (line is highlighted in red). If I comment out the code where the error is occurring and run the update alone everything works fine so I know the Update statement works.
Here is the specific error message I am getting in SQL Server 2005:
Msg 207, Level 16, State 1, Line 85 Invalid column name 'AssignedAgent'. Msg 207, Level 16, State 1, Line 85 Invalid column name 'DateTimeAssigned'.
Here is the SP: -
IF OBJECT_ID('ConvertProofTables','P') IS NOT NULL DROP PROCEDURE ConvertProofTables; GO CREATE PROCEDURE ConvertProofTables AS SET ANSI_NULLS ON
I am trying to create a new stored procedure in Sql server managment studio express in a database. I am getting an error message saying
Invalid object name 'Consumer_delete'.
Can you please tell why I am getting this error message?? Also , I need to make sure that the created procedure appears in the list of database objects after execution. Thanks for your help
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE Consumer_delete
@ConsumerID int,
@BusinessId int
AS
BEGIN
DECLARE @intError int
DECLARE @ConsBusinessID int
SET NOCOUNT ON
SELECT @ConsBusinessID = CONSUMERBUSINESS.[ID]
FROM CONSUMERBUSINESS
WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId
DELETE FROM CONSUMERBUSINESS
WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId
We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..
When I create/alter a stored procedure I am expecting to get an error because one of the columns was renamed.
However, the stored procedure compiles without error. Is this correct behavior?
Example: Create a Test table. Create stored procedure that reference table along with temp table. The columns are not verified. The stored procedure still compiles, even with the join containing bad column names. Why is an error not thrown?
Example: create table Test (col1 int) GO
create procedure TestSP as create table #Temp (col1 int) select 1 from Test a join Temp b on a.bad = b.bad -- Bad column names GO
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
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference' BEGIN TRY select data from mytables END TRY
However I'm not sure this is the most efficient way to handle this.
I have a stored procedure that calls several views that rely on each other. In the past these views used to go parallel and use up all 100% of the CPU (12 cores), and now when the same stored procedure runs it only uses 8% of the CPU (1 core). This extends the time spent on the query from roughly 10-15 sec to 2-3min. I'm not quite sure why this is happening.
Are there some obvious things to look at when optimizing views to utilize all cores/threads? Also, it doesn't matter if I set Cost Threshold for Parallelism to 1 or 50 or 5, it is always the same, and I have Max Degree of Parallelism set to 0 as well, which should mean to use all cores when available.
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?