Transact SQL :: Capture Any Call For Specific SP
May 4, 2015Is there a way to capture any firing for a SP such as (datetime , loginname,..) and not using SQL profiler ?
View 3 Replies
ADVERTISEMENT
Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)
May 23, 2015I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
SQL Server 2008 :: Extended Events Capture A Specific Proc Parameter
Feb 19, 2015I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
View 4 Replies View RelatedHow To Call A SSIS Package With A Specific Parameter
Apr 2, 2008I need to be able to call a SSIS package with a specific parameter for one of the variables. Basically the package needs to be triggered from an application (.NET, C#) with a specific value.
The package is stored in the SQL Server (in Stored packages).
I have seen the article on dtexec and the SET flag, however we do not want to turn on xp_cmdshell on the SQL server due to the security implications, and that seems to be the only way to call dtexec
I have seen another article that says you can have a job invoke a package then have a specific user granted permission to the job, but that doesn't address how to set the parameter/variable for the package.
There are actually some other steps that technically need to run using the same parameter, so ideally if I could have a stored procedure call the SSIS package, then the subsequent toher stored procedures using the sent in parameter that would be great. I just can't figure out how to do this securely (as xp_cmdshell is not considered secure).
Thanks for any comments.
T-SQL (SS2K8) :: Call Stored Procedure And Obtain Specific Results
Apr 17, 2014I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:
DECLARE@return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:
1. How can I have the stored procedure return distinct rows?
2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".
when I change the sql above to:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName
That is when I get the error message.
A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.
Transact SQL :: Capture Data Change From Multiple Table Joins
Jun 9, 2015I have 5 tables that are joined respectively,
Each one of the tables listed below has a “CreateDateTime” and “UpdateDateTime” fields, I need to get yesterday changes, I can get any record where either CreateDateTime or UpdateDateTime is greater than midnight yesterday butI need to watch dates on all of the tables so I need to do atleast 10 date checks.
If any table shows an updated or created record, I need to gather ALL of the information for that customer. So, if my name didn’t change (SCUS table), but my email does (SEML table), I have to pull out both the SCUS and SEML tables (and the others, of course). So It may not be simple WHERE clause, How can I achieve this:
SELECT
SCUS.CUSFULLNAME
,
SCUS.CUSMIDDLENM
,
SCUS.CUSLASTNM ,
[Code] ....
Trying To Call The Function A Web Service From Transact-SQL
Jun 2, 2006I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.
I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?
Thanks for any help.
ALTER PROCEDURE [dbo].[pTAPServiceWeb]
@sUrl varchar(200),
@response varchar(8000) out
AS
DECLARE @obj int
DECLARE @hr int
DECLARE @status int
DECLARE @msg varchar(255)
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
IF @hr < 0
BEGIN
RAISERROR('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1)
RETURN
END
EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod Open failed'
GOTO err
END
EXEC @hr = sp_OAMethod @obj, 'send'
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod Send failed'
GOTO err
END
EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod read status failed'
GOTO err
END
-- IF @status <> 200
-- BEGIN
-- SET @msg = 'sp_OAMethod http status ' + str(@status)
-- GOTO err
-- END
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod read response failed'
GOTO err
END
EXEC @hr = sp_OADestroy @obj
RETURN
err:
EXEC @hr = sp_OADestroy @obj
RAISERROR(@msg, 16, 1)
RETURN
GO
Transact SQL :: How To UPDATE More Than 500 Records In One Call
Aug 13, 2015I have following query which is created dynamically as -
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';
Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?
Transact SQL :: DDL Trigger For Specific Tables
Oct 14, 2015How to set ddl triggers for specific tables, instead of on entire database.
View 2 Replies View RelatedTransact SQL :: Select With Value For Specific Month
Nov 13, 2015I have table with below scheme
Year Month Day RELH
------ -------- ------ -------
1973 4 1 50
...... etc
1973 4 30 20
.....etc
1974 5 1 50
....etc
1974 5 30 99
I need to get years where average of RELH of months within year meet
Month 4 Average(RELH) > 33 and
Month 5 Average(RELH) > 60 and
Month 6 Average(RELH) < 33
How can I achieve this?
Transact SQL :: Call Cobol From Stored Procedure?
Jun 17, 2015In DB2 stored procedures are calling external cobol code/program, I want convert DB2 stored procedure to MS SQL stored procedure, on that case, How to call call cobol from MS SQL stored procedure.
View 7 Replies View RelatedTransact SQL :: Call SP And Return 1 Or 0 On Success And Failure
Jun 1, 2015When I execute Parent SP, it should Return 1 when Child SP is executed Successfully and Zero when Child SP fail .below are sample SP
CREATE PROCEDURE EXEC_CHILD_PROC
AS
BEGIN
SELECT 99/0
END
[code]...
I tried several way , but did not get correct syntax to modify Parent SP give 1 or 0 on child SP execution
Transact SQL :: Bug In REVERT During Cross Database Call
Jul 28, 2015I have noticed rather strange behaviour of EXECUTE AS and REVERT sequence during the cross database calls which appear to be a bug. I tested this issue on developer edition of SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
This issue causes problems in SSISDB where similar piece of code appears in [catalog].[start_execution] and some other scripts in the [internal] schema. This was previous discussed in [URL] The following script illustrates the issue:
USE [master]
GO
CREATE DATABASE [Test1]
GO
CREATE DATABASE [Test2]
GO
-- Set Database to Trustworthy to allow cross database connection
ALTER DATABASE [Test1] SET TRUSTWORTHY ON;
GO
ALTER DATABASE [Test2] SET TRUSTWORTHY ON;
GO
USE [Test2]
GO
CREATE PROCEDURE [dbo].[TestContext]
AS
SELECT 'EXECUTION CONTEXT BEFORE EXECUTE AS CALLER', SUSER_NAME(), USER_NAME();
[code]....
Transact SQL :: INSERT Specific Data To Other Table
Aug 10, 2015I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID PARTY AMOUNT VOUCHER
---------------------------------------
1 A 5000 Purchase
2 B 3000 Purchase
3 C 2000 Purchase
4 A 3000 Payment
5 B 1000 Payment
6 C 2000 Payment
7 A 1000 Payment
Now we have a blank table Table 2 - Liabilities
ID PARTY AMOUNT
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID PARTY AMOUNT
-------------------------
1 A 1000
2 B 2000
3 C 0
Transact SQL :: Returning Specific Characters After Space?
Jul 6, 2015I have a column that has data in column user4 such as :
CALBRIG ALBION COMPANY LTD
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
Transact SQL :: Call Stored Procedure In Select Query?
Nov 9, 2015I have a SELECT query that also needs to call a sproc with a column name passed in as a parameter.
Unfortunately I cannot use a TVF, as the sproc code references a TVF on a linked server.
What is the best option?
SELECT
u.UserID,
u.Username,
u.Address,
EXEC dbo.[GetResult] u.UserID AS 'Result'
FROM dbo.UserTests u
Transact SQL :: How To Call SSIS Package From Stored Procedure
May 21, 2015I have ssis package which is credated by VS-2010.
I want execute this SSIS package from the stored procedure (SQL server 2005).
Transact SQL :: How To Execute Insert Statement With A Function Call
May 20, 2015I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
Transact SQL :: Stored Proc Call By Adding Right Login
May 14, 2015I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.
View 20 Replies View RelatedTransact SQL :: Flattening Hierarchical Data In Specific Format
Apr 22, 2015I have the data in this format
ID NAME ParentID
CV1 CV1NAME CV
CVX1 CVX1NAME CV1
CVXX1 CVXX1NAME CVX1
CV2 CV2NAME CV
CVX2 CVX2NAME CV2
CVXX2 CVXX2NAME CVX2
How can i flatten this data into this format
CVID CVNAME CVXID CVXNAME CVXXID CVXXNAME
cv1 cv1name cvx1 cvx1name cvxx1 cvxx1name
cv2 cv2name cvx2 cvx2name cvxx2 cvxx2name
Transact SQL :: Extract ID And Specific Part Of A String From Column
Jun 10, 2015I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString
1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString
1 Extracts^This^String1
2 Extracts^This^String2
3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,
[Code] ....
Transact SQL :: Database Audit Specification On Specific Users
Mar 30, 2015Currently I am using SQL server 2012 and would like to implement database audit specification on specific users in my database. These are the users in my database name Payroll :-
PayrollAndy.Bred - db_owner
PayrollArpit.Shah - db_owner
Payrollwebapp - db_datareader, db_datawriter, EXECUTE
web_payroll - db_datareader, db_datawriter, EXECUTE
In my database audit specification settings, I would like to capture any SELECT,UPDATE,DELETE and EXECUTE command for users PayrollAndy.Bred & PayrollArpit.Shah only since they owned db_owner access. However, I am unable to capture any single command from both users. I do not want to put 'Principal' as public since I just want to capture both users activity.
Is it I miss out anything? Is it because of windows login account?
Transact SQL :: Select Unique From Table If Specific Value Does Not Exists
Jul 31, 2015I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
Transact SQL :: How To Count Number Of Specific Columns Of Table
Jul 11, 2015I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40
week2: 24
week3: 24
week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
Transact SQL :: How To Select Data With Specific Priority Of A Column
Aug 1, 2015i have table that has rows as below
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[code]...
i want to select the data priority wise. the o/p should look like
e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
Transact SQL :: Extracting Specific Data From One Field Using Charindex
Jul 20, 2015I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975 ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656 ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712 ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
Transact SQL :: Update Date With Specific Hour / Minute And Second
May 7, 2015How I can update the following date to get the desired result with specific hour, minute and second
2014-01-01 00:00:00.000
Desired Result
2014-01-01 17:20:20.000
Transact SQL :: Sequence Of Characters - Extract Some Specific Values
May 13, 2015I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
Transact SQL :: Printing Report - Capture Actual Pages In A Duplex Printing Job
Aug 10, 2015So I have been asked by our sustainability person to create report from our printing data that actually shows the number of pieces of paper used. This is easy enough for single-sided printing, but when printing in duplex the software does not take into account that 3 printing pages actually equates to 2 pieces of paper. I know this sounds simple, but say I have a print job record that looks like this:
Submitted printed total_pages duplex
8/10/2015 8/10/2015 42 1
This is a print jobs that if done correctly is actually 21 pages( duplex printing). If the job is say total_pages =5 I cant just divide by 2 because its actually using 3 pieces of paper ( yes they are wanting this data don't ask why). How can I adjust some sql to accurately depict 5 pages, front and back, as 3 pieces of paper?
Transact SQL :: Simple Trigger To Audit Who When Is Changing A Specific Column
Jul 6, 2015I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test](
[Customer] [varchar](12) NULL,
[Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values
)
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query
Aug 18, 2015I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
Transact SQL :: Convert Time In Specific Timezone To UTC (and Vice-versa)
Nov 17, 2015In C#, I can convert UTC time to a specific timezone using:
TimeZoneInfo localTimeZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
DateTime UTCTime = TimeZoneInfo.ConvertTimeToUtc(localTime, localTimeZone);
Is there any equivalent function in T-SQL (SQL Server 2014 / SQL Azure)?
Specifically, I want to be able to convert a time into UTC before inserting it into a table. Right now I am doing:
INSERT INTO Incident (IncidentDescription, dtOccurred)
VALUES (@IncidentDescription, DATEADD(hour, 8, @dtOccurred))
I don't want to hardcode "8" and I want it to adjust for daylight savings time (DST).
Is there any concise solution that does not require me to define SQL functions or have lookup tables? I also don't want to use the difference between GETDATE() and SYSUTCDATETIME(), because the timezone of my server is not my local timezone.
Transact SQL :: Comparing 2 Records / Multiple Instances In The Same Table For A Specific Combination?
Jun 10, 2015I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.