SQL Server 2012 :: Call SP On The Basis Of Input?

Feb 15, 2015

I have create Store procedure for all the Source and destination table for loading new and updated record.

For example I have below Store procedure

1-SP_Archive_using_merge_Fdoor
2-SP_Archive_using_merge_Fdoop

now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .

below is the 2 sp .

--First SP
ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int

[code].....

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Get Data On Quarterly Basis

May 12, 2014

I need to get results on quarterly basis, matching 2 quarters AUTOMATICALLY.

- As the new quarter starts, it needs to match the last quarter results.

SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
QUARTER QuarterNo

[Code] ....

Here is my Query, I don't know whether I'm getting it right?

--Quarter 1
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: Query On Grouping Data On Weekly Basis

Oct 6, 2015

I have query on grouping data on weekly basis..

1. Week should start from Monday to Sunday

2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).

3. I want output like below

Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12

View 1 Replies View Related

SQL Server 2012 :: How To Calculate Attendance For Single Student For Yearly Basis

Jul 2, 2015

I have to calculate the Total number of days present and absent for a singel student.

AS of now i have 3 tables.

1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]

So now i have loaded all the data into the table.

I can fetch the counts for total present and absent

Query i have tried is

Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,

[Code] ....

AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.

SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.

View 7 Replies View Related

SQL 2012 :: Write Query Which Runs In Background On Cyclic Basis

Jul 9, 2014

I want to write sql query which runs in a background on cyclic basis. Basically i want to count the row entries of 1 table and store the data and the count in two distinct columns.

View 3 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 :: Input Parameter For Store Procedure Like In Statement Value 1 / Value 2

Jun 20, 2014

How can I input parameter for Store Procedure like In ('Value1','Value2')

Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)

View 6 Replies View Related

SQL Server 2012 :: Call A Split Parameter Function

Sep 15, 2014

In t-sql 2012, the followinng sql works fine when I declare @reportID.

IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION

[code]...

However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:

FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)

GO

how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?

View 2 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

SQL Server 2012 :: Table Returning Function With Input Table Name As Parameter

Nov 19, 2014

I'm using SS 2012.

I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.

In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.

The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.

This means switching to the multi-line function, which I will if I have to, but those are more tedious.

Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?

A simple example of what I'm trying to do is below:

Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)

Error I get is:

Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".

If I use "table" as the parameter type, it gives me:

Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".

The input table can have several thousand rows.

View 9 Replies View Related

On What Basis Sql Server Sorts The Rows

May 16, 2007

Hi,



When I execute the following statement:



Select * from table1;



On what basis, SQL server decides the sequence of these rows?



I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.



Thanks veyr much.



Regards,

Tim

View 5 Replies View Related

SQL Server 2014 :: Count Order On Weekly Basis

Mar 10, 2014

I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....

View 5 Replies View Related

SQL 2012 :: Calculating Fiscal Week Based On Input Dates

Aug 19, 2014

I need a Query for calculating the fiscal_week based on the input dates (start_date and end_date), though I got a query from this forum, it is not giving me exact result.

the sample is in the excel file with the attachment.

In the excel:

First tab tells you the raw_data what I am using to find the Fiscal_week
Second tab tell you the data where i found the mistake, and how I am expecting the output.

I also have attached the query I have got from this forum, query I have modified for fiscal week.

View 4 Replies View Related

SQL 2012 :: Insert ID Column Value Into Table Depending Upon Input Filename

May 19, 2015

I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.

Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport

Location one has three different set files starting with

First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,

This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101

AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3

If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301

after processing all these files in first location, the SSIS looks for files in second location and does the same?

--How to achieve CustID depending upon file name ?

View 0 Replies View Related

SQL Server Admin 2014 :: Identify Table Made On Temporary Basis Are Not Used?

Sep 9, 2015

In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?

View 4 Replies View Related

DB Engine :: Remote Procedure Call Failed After 2012 Installation

May 8, 2015

We have server with Sql2008 R2 RTM 64 bit standard edition.We installed 2012 sp1 64 bit standard edition  in win2012sp1 64 bit as side by side setup.After installation, i found the below error when i opened SSCM:

Remote Procedure Call Failed:

I fixed this error by renaming the MSC file.

At event log i am observing continuously below error:

Source:        Application Error    Event ID:      1000

Faulting application name: wmiprvse.exe, version: 6.3.9600.16384, time stamp: 0x5532e9c9

Faulting module name: svrenumapi100.dll, version: 2009.100.1600.1, time stamp: 0x4bb681be

Exception code: 0xc0000005

Fault offset: 0x00000000000514e6

Faulting process id: 0x17e8

Faulting application start time: 0x01d0873d9d37c382

Faulting application path: C:Windowssystem32wbemwmiprvse.exe

Faulting module path: C:Program FilesMicrosoft SQL Server100Sharedsvrenumapi100.dll

Report Id: afe88c1-330-11e4-80c4-008786656bda

Faulting package full name: 

Faulting package-relative application

is there any other way to get rid of this error without installing a SP1 or later on sql 2008 R2 RTM version.

View 7 Replies View Related

Data Access :: How To Reduce Record On The Basis Of Time If Minimum 2 Minutes Duration In Server

Sep 8, 2015

I have a table with following data

(Id, date ,time)

11 2015/8/1
12:20:00

11
2015/8/1 12:21:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
11:21:00

12 2015/8/1
18:10:20

I need the table with following record only

(Id, date ,time)

11 2015/8/1
12:20:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
18:10:20

View 7 Replies View Related

SQL 2012 :: Produce Report To Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

I have to produce a report to calculate no of days based on user input start date and end date.

say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:

PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :

[code]...

HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.

View 0 Replies View Related

SQL 2012 :: Data Migration - Pull Data From All DBs To Input Into DW Table

Jul 15, 2014

I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).

I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)

An example of my branch script :

select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500

Where interid in ('comp1', 'comp2', 'comp4', 'comp5')

what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?

I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?

View 0 Replies View Related

I Just Want One Entry For Each Call, With SLA Status 'Breach' If Any Of The Stages For The Call Were Out Of SLA.

Mar 19, 2008

Hi,

I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.

The results table should look like the one below:





CallRef.

Description

Severity



ProblemRef

Logged
Date

Call
Status

SLA Status



C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

Breach


C0002

PO€™s not published

2



DGE0014

06-01-06 10:21

Resolved

OK


C0003

Approval for PO€™s not received from Siebel.

2



n/a

05-01-06 14:48

Investigating

OK



















Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:

if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';

The Select statement in my query is looking like this...

Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;

The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.






CallRef.

Description

Severity



ProblemRef

Logged
Date

Call
Status

SLA Status



C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

Breach


C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

OK


C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

Breach



















Any help will be much much appreciated, this issue has been bothering me for some time now!!!




View 7 Replies View Related

Basis SQL Question

Mar 21, 2008

How do I format a date value in SQL 2000 sp?  Tired the following none are returning the quivilent rates.  What is the syntax please?
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')

View 3 Replies View Related

DTS - Dataimport On A Daily Basis

Apr 3, 2000

I have to import data into 2 tables on a daily basis.
The data is provided as a flatfile.
In order to fullfill this task the tables have to be truncated first.
Are there any possibilities to do this job automatically with dts, or do I
have to write an Interface
in VB or something like that?

Thanx

Michael F.

info@sunguard-explorers.de

View 2 Replies View Related

Update On Basis Of Another Table

Apr 12, 2007

hello guys !
i need a little help in an update statement.

i have two tables as

table1(id1,name)
table2(id2,date,val2)

what i want is to set val2=45 in table2 where date=4/4/2007 (say) and id2, now id is to be obtained from table1 based on the name;

like
table1
---------
(1,"akmal")
(2,"peter")
(3,"kamal")

I know the name as peter, but i want to used its id1 (2) in the update statement. You might have got the idea that id2 is a foreign key referring to id1.

How to do that ?

View 2 Replies View Related

Getting Count On Weekly Basis

Apr 11, 2008

Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)


it gives me Result as,

Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2

i want count on weekly basis
Could any one help me?

View 8 Replies View Related

Job Agent To Schedule For Second Basis ????

Dec 4, 2006

Hello friends!

I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????


T.I.A

View 2 Replies View Related

Synchronization Performed On Day Basis

Nov 5, 2007

Currently database1 and database2 are planned to be synchronized with the interval of 5 seconds!! ( dont know exact, but i select run continoulsly on subscriber screen )
This could cause serious performance issues as both applications will be live on difference servers.

how can i Synchronization should be performed on day basis or it should be modification basis to avoid any performance issue on live application

i am using merge replication
Local distributor
and subscriber on another server .


plz help me
its urgent

View 3 Replies View Related

How Xml Could Help Us With Our ETL Processes On Daily-basis?

May 26, 2006

Hi all of you,

I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.

I mean, are there big differences between store data as XML format and nvarchar?

That sort of stuff.

Thanks in advance for any input,

View 5 Replies View Related

Breaking Query On Basis Of Conditions

Mar 20, 2008

 On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far.   SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3],  [column4], [column5] FROM [table] WHERE [column4] = 'condition4'  AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate  ", Conn) ;          cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text));         cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text));           cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text));  I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help. 

View 2 Replies View Related

Select On The Basis On Month And Year

May 18, 2004

hi guys.

I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..

any suggestions..??

Thanks in advance..

:)

View 1 Replies View Related

SQL 2005 Sps Need To Be Recompiled On A Daily Basis Why?

Aug 16, 2006

Hi All,

I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.

We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.

Has any one else had a similar issue, any ideas what could be causing this to occur.

Thanks.

View 1 Replies View Related

Capturing A Warning - Ongoing Basis

May 17, 2004

Hi all,

I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.

The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"

I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).

The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROMPortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @CreateDate) AND
PP.PrevDate IS NULL

SELECT @RowCount = LTRIM(STR(@@ROWCOUNT)) /* capture rowcount so @m_error select doesn't clobber it*/

SELECT @m_error = @@Error IF @m_error <> 0 GOTO ErrorHandler
SET @TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @TraceMsg

NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.

So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?

Thanks!
Paul

View 2 Replies View Related

Scheduling DB Restore On Daily Basis

Sep 27, 2013

I need to be able to restore a test db from a production db on a daily basis..

View 3 Replies View Related

Merging 50 Databases Into One On A Nightly Basis

Sep 13, 2007

How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?

We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.

We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.

Some cleaning of the data needs to happen before updating head office.

Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.

We want something robust, performant and easy to troubleshoot.


Kev

View 1 Replies View Related







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