Recently Indexed Messages:-

SQL 2012 :: XML SSIS Variables

Sep 17, 2015

I have a process which connects to a SQL server remotely, runs some code, creates a temporary table with an output. I want to be able to select the results of this table into XML (not a problem), put the results into a variable in SSIS, and put the variable results into a SQL table a different SQL Instance. Doing it this way removes the footprint of needing a normal table on the source SQL Server.

View 2 Replies

Mirroring :: How To Reduce LDF Size While Mirror Enabled With Mirror And Witness Server

Jun 14, 2015

I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.

View 4 Replies

Find Records Where There Is Difference Between 2 Columns

Jan 24, 2014

I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg

Debt_amount - billed_amount

where debt >billed amount

I only want to see records where the debt is more than the amount billed

View 1 Replies

Integration Services :: Possible To Get Output Of Execute Task To Excel Destination

Jul 10, 2015

IS that possible to get teh output of a execute sql task to excel destination.I have query which will comapre the  data difference between two databses. It will comapre all tables in both databses and list out the difference in data by each table. I need to run this query using SSIS and need to get the output to a excel sheet...I have used the data flow task to run this query but my query is giving some error when used with data flow task. So i have used excecute sql task and need to write teh out put to a excel sheet.

View 11 Replies

Transact SQL :: CASE Order Of Evaluation

Sep 24, 2015

I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.

WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END

This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?

View 10 Replies

SQL Server 2008 :: How To Find Which Queries / Processes Causing Large Memory Paging Rate

Mar 30, 2015

Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?

View 3 Replies

7 Day Sales Analysis Query

Aug 31, 2014

Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.

now the time is 22:00 & group by shop plus underline +24 hour format

Sun Mon Tue Wed Thur Fri Sat Sun Total
Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx
Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx
Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $30 $30 $10 $4 $20 $0 $no sales $xxx

View 12 Replies

Recovery :: Availability Group Quorum - Majority Of Nodes Down But Cluster Still Up?

Sep 9, 2015

I'm just starting to work with AlwaysOn Availability and WSFC.

I have in my environment (in Azure) a DC, WSFC and to SQL instances, so I have 3 nodes in my Failover Cluster:


If I simulate failure by shutting down one of the SQL boxes my Availability group seamlessly fails over to the other SQL instance - which is great.

However, I'm starting to look into the workings of the Quorum, my envt has the default settings and when I shutdown both of my SQL servers I expected the Cluster itself to go offline as 2 out of the 3 votes will be negative, but the Cluster is still up - Screenshot below when SQL1 and SQL2 are  shutdown:

Going through the Wizard (but not changing anything) it shows following config:

View 6 Replies

SQL Security :: Permissions Disappearing From Tables And Views

Feb 26, 2014

As a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will

Go into SQL Server Management Studio 2008Open the database
Locate either the table or the view
Right click on permissions.
Add in the role or user that's needed
Grant them Select permissions

This allows the report in CRM to access the database and this works.

The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.

I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.

View 6 Replies

SQL Server 2012 :: Duplicate Values In UNPIVOT

Feb 18, 2015

I have a set of data in which i have a product number going through 6 stages and each stage has a date. Since the each stages are in columns, I have created a unpivot query to transpose the columns into rows.

The actual result data needs to be in format of

ProductNumber Event_NameEvent_Date Event_Days
101 Stage 1 2/13/2014 1
101 Stage 2 2/13/2014 0
101 Stage 3 2/18/2014 5
101 Stage 4 2/23/2014 5

However the result data i am getting is like

ProductNumber Event_NameEvent_Date Event_Days
101 Stage 1 2/13/2014 1
101 Stage 1 2/13/2014 0
101 Stage 1 2/18/2014 5
101 Stage 1 2/23/2014 5
101 Stage 2 2/13/2014 1
101 Stage 2 2/13/2014 0
101 Stage 2 2/18/2014 5
101 Stage 2 2/23/2014 5

The unpivot query is working fine however I am getting duplicate values in the result. For each productnumber there must be only 6 results however i am getting 24 rows for each product number due to duplication.

I have attached the code and the source data for reference

SELECT distinct ProductNumber ,
Event_Name ,
Event_Date ,
SELECT ProductNumber ,

[Code] .....

View 2 Replies

Transact SQL :: Finding Last Purchase Price For Each Product?

Sep 29, 2015

I need to find the last purchase price for each product.  If I run the following code, I correctly get 1 result for each productID and the last purchase order number.

SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article

Now I need to add in the price for that product on that orderID.  I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.

SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article

How can I get it to simply add the price to the first query?

View 10 Replies

SQL Server 2012 :: Merging Two Tables Using Effective Start And Effective End Date Fields

Sep 10, 2015

I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.

Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.

Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected to achieve this in T-SQL query.

create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit


View 4 Replies

SQL 2012 :: Role Based Server Security

Sep 20, 2015

There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:

Step One:
We create a Server Role called SQL_APPLICATIONS – for the application accounts
We create a Server Role called SQL_DBA – for the DBA accounts
and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)

Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012

Step two:
I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively

Step three:
I remove the “Connect SQL” Permission from each Login

The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.

View 3 Replies

SQL 2012 :: Remember Definitions Of Clustered And Non Clustered Indexes?

Nov 24, 2014

What is the easiest way to remember the definitions of clustered and non clustered indexes.

View 9 Replies

SQL Server 2012 :: Get Data Centralized

May 30, 2014

I am not sure how to tackle the following. Environment has SQL Server 2005 and above which hosts one database (table1 & table2) per server. I have created a new SQL 2012 server (database called collection (table1 & table2) which will be the central point. I need to do the following:

1. Connect to each server get the data from specific table
2. Add a additional column called "datasourceserver" and add the server name where data came from
3. Able to schedule this task so that new data are sync to SQL 2012 server

View 2 Replies

Copyrights 2005-15, All rights reserved