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 had tried out join but it is not serving needs.

---===========================================
-- Sql
--============================================

DECLARE
@ProdId varchar(10),
@color varchar(max) = Null,
@size varchar(max) = Null
BEGIN
set @ProdId='Prod1';

[Code] .....

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Find All Tables Used In Any Stored Procedure

Feb 14, 2015

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
)

View 7 Replies View Related

SQL 2012 :: Use SSIS Or Stored Proc Or Combination To Handle Stopping A Process On Failure

Apr 7, 2015

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.

View 2 Replies View Related

SQL Server 2012 :: Query To Produce Permutation Combination

Jun 4, 2015

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),

View 3 Replies View Related

SQL Server 2014 :: Execute Stored Procedure To Update A Table / Invalid Object Name

Jan 21, 2015

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]

[code]....

View 2 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

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

View 3 Replies View Related

SQL Server 2012 :: Select Records Where Combination Of Two Values Are In Subquery Result?

Dec 12, 2014

I have some data in the following format;

MYTABLE

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?

View 9 Replies View Related

SQL Server Express And C# 2.0: Cannot Find Stored Procedure...

Sep 5, 2006

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

View 1 Replies View Related

Can't Find The Stored Procedure I Wrote In SQL Server

Jan 9, 2008

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?

View 3 Replies View Related

In MS SQL Server 2000 How To Find Last Used Stored Procedure ?

Jul 30, 2007

Hi

This is seshu.

Is there any way to find out the last used stored procedure.

Let me know about this one.

View 1 Replies View Related

T-SQL (SS2K8) :: Find Same Combination Of Rows?

May 10, 2015

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),

[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.

Hence the desired output will be

Symbol Count
Castorseed-Silver 8

How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 1 Replies View Related

Transact SQL :: Find Combination Of Rows?

May 10, 2015

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.

View 6 Replies View Related

SQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?

Jan 21, 2014

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.

View 5 Replies View Related

Transact SQL :: How To Find Combination Of Column - Row Unique

Mar 8, 2012

I have table with some columns with no primary key..

I have to find the combination of columns which may the row unique...

How to do that like microsoft...

View 14 Replies View Related

SQL Server Admin 2014 :: Stored Procedure - Find The Cause For Blocking

Sep 23, 2015

I was trying to create stored proc

[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,

[Code] ....

But the query was continuously blocking the query below

updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0

I was not sure, why the create procedure statement is blocking the update statement.

View 1 Replies View Related

SQL Server 2008 :: Find All Items That Call A Stored Procedure

Nov 2, 2015

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?

View 9 Replies View Related

SQL Server 2008 :: Find List Of Stored Procedure Used By Crystal Reports

Jul 1, 2015

We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?

View 4 Replies View Related

SQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?

Jul 22, 2015

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 Related

SQL Server 2014 :: Find Out Last Executed Date For Any Stored Procedure In Database

Oct 6, 2015

Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.

View 2 Replies View Related

Please Help Stored Procedure, Invalid Object #tempactions

Sep 24, 2004

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


Thank you very much.

View 3 Replies View Related

Trouble With Stored Procedure (Invalid Object Name)

Mar 31, 2005

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.

View 2 Replies View Related

Invalid Column Name Error In Stored Procedure

Sep 19, 2012

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

[Code] ....

View 7 Replies View Related

Stored Procedure Creation,Invalid Object Name

May 25, 2008



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



DELETE FROM CUSTOMERREMINDER

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM NOTE

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM VISIT

WHERE ConsumerBusinessID = @ConsBusinessID



--ERROR HANDLING--------

SET @intError = @@ERROR

IF @intError <> 0

GOTO ExitError

RETURN 0

ExitError:

RETURN @intError

END

View 3 Replies View Related

SQL Server 2012 :: Generate PDF From Stored Procedure

Mar 3, 2015

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

View 3 Replies View Related

SQL Server 2012 :: Passing Sum To Stored Procedure

Sep 21, 2015

Is it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error

exec mysp
@param = (@var1 + @var2)

View 1 Replies View Related

Transact SQL :: Invalid Column Not Detected In Stored Procedure

Aug 17, 2015

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

View 2 Replies View Related

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

Feb 4, 2008

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 Related

SQL Server 2012 :: Run ASPX File From Stored Procedure?

Dec 9, 2013

After my Stored Procedure has run, I need to call [URL] ..... file. Can this be done from my Stored Procedure?

View 2 Replies View Related

SQL Server 2012 :: Stored Procedure With One Or More Input Parameters?

Dec 17, 2013

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.

View 2 Replies View Related

SQL Server 2012 :: How To Create Password On Stored Procedure

Jun 27, 2014

is it possible to create PW on Stored Procedure? No one can execute or Alter any Store Procedure with Password?

View 1 Replies View Related

SQL Server 2012 :: Restore Database By Stored Procedure

Jul 26, 2014

I need to create a procedure with Restore Database command in many applications in differente platforms. But I can't "use master". How do I do it?

View 1 Replies View Related

SQL Server 2012 :: Optimizing Stored Procedure To Go Parallel

Jan 22, 2015

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.

View 9 Replies View Related

SQL Server 2012 :: Run Stored Procedure Without Writing To Transaction Log?

Feb 26, 2015

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?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved