SQL Server 2014 :: How To Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run.But print statement dos not work correctly!

Note: I cannot use "go" in my query!

/* My query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[code]...

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!

Note: I cannot use "go" in my query!

/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[Code] .....

View 1 Replies View Related

SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should

Mar 29, 2015

I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:

- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.

To explain wat I'm doing is this:

1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:

SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:

SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'

Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.

View 9 Replies View Related

SQL Server 2012 :: How To Go To Next Line In Output And Print A Table

Aug 13, 2014

I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:

This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'

How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'

Also how can I create a table in this variable, something like this:

(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME

(Course Name) (Last Completed) (Now due in Month/year)

My T-SQL code:

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)

[Code] ......

View 2 Replies View Related

SQL Server Admin 2014 :: Leave DB In Restoring State After Attaching To Instance?

Apr 7, 2015

Why do I leave my DB in Restoring State after attaching to the instance? Could not this fire a redo activity on Transaction Log?

Or, How does sql server undo all uncommitted transactions on active vlf during recovery?

View 2 Replies View Related

Query Runs On Sql7.0 But Keeps Running On Sql2000 Without Output

Aug 15, 2001

The following query runs fine on sql 7.0 but it kind of hangs/keeps running without any output on sql server 2000
---------------------
Set @cmd = 'Update ABCD Set
'+@day+'_LB = IsNull(LB,0),
'+@day+'_UT = IsNull(UNIT,0)
From tempData as T
Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC'
execute (@cmd)
------------------------------------
But if we hard code the @day parameter and run the query like this it runs very fast on sql 2000....

Update ABCD Set THIRD_LB = IsNull(LB,0),
THIRD_UT = IsNull(UNIT,0)
From tempData as T
Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC
------------------------------

View 2 Replies View Related

SQL Server 2014 :: Need Output In Single Row

Oct 29, 2015

I need out put in single row for below 2 queries using joins

SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info

[code]....

View 4 Replies View Related

SQL Server 2014 :: Similar Queries Produce Different Output?

Sep 22, 2015

create table #t1 (id int)
create table #t2 (id int)

insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)

insert into #t2 values (1)
insert into #t2 values (2)

Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id and b.id is null

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id where b.id is null

View 3 Replies View Related

SQL Server 2014 :: CASE Condition Not Affecting CAST Output

Jun 10, 2014

I've a variable in my SP that I'd like to use to change the format of my output. Try this!

select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Then this:

select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):

select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2

Work fine.

View 8 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

SQL Server 2014 :: Does Running Multiple Queries In Same SP Cause Latency

May 14, 2014

I have the following:

(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.

(b) Three SQL Indexed View queries that take 0 ms when run together.

When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.

What is causing the extra 363 ms of latency.

View 9 Replies View Related

SQL Server Admin 2014 :: CheckDB Running For Two Days

Jun 3, 2015

We carried out an in-place upgrade on our production server on Saturday - going from 2008 R2 to 2014.

We had tested this method out in dev/test and pre-production with only minor post issues to fix.

However, on production we had an issue whereby checkdb was hitting 100% CPU and caused overnight processes to hang. The checkdb statement was terminated and disabled by a colleague at 1 am.

Since then we have restored this database to a dev server and ran checkdb against it with no_infomsgs and all_errormsgs but it still hasn't finished since Monday morning!

The database is just over 800 GB and whilst checkdb was crippling the cpu, logical reads are less than one. However, sp_whoisactive is showing that it has done 56 million reads so far and this number increases periodically so it looks like it keeps going back to re-check the database with a deep dive.

Also, on a different environment, we ran check table statements and one of them took over 9 hours for a single table but came back clean (see attachment).

We need to wait for the output but the database is still in use in production and the mess will just get worse if it is indeed corrupted.

View 5 Replies View Related

SQL Server Admin 2014 :: Multiple Database-collation Running On One Server?

Mar 30, 2015

We are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.

Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS

Our new server (2014) has the standard collation Latin1_General_CI_AS

Both collations have CI and AS

From experience I know different databases can reside next to eachother on the same Instance.

The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....

The application the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).

using different databases using a different collation running on the same instance?

View 5 Replies View Related

SQL Server 2014 :: Insert Stored Procedure Output To Table And Add Datetimestamp

Jun 22, 2015

I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.

insert into Table_Test execute sp_who

View 2 Replies View Related

SQL Server 2014 :: Identify Difference Between Two Table Columns And Output Only The Differences

Sep 4, 2015

IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;
IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;

CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)

[Code] ......

View 6 Replies View Related

SQL Server Admin 2014 :: Running Multiple Instances On 2 Nodes

Apr 12, 2015

I'm looking at installing 2008R2 and 2014 side by side, then using Mirroring to provide HA for the 2008R2 instance and AoHA for the 2014 instance. I'd be using the same two physical servers for both the Mirroring pair and the AoHA pair.

View 2 Replies View Related

SQL Server 2014 :: Calculate Running Difference In Values Between Days?

Oct 13, 2015

I am trying to write a query to calculate the running difference between data on different dates. Below is what my table of data looks like. Basically I want to calculate the difference between the total_completed for each state and date.

DateStatesTotal_Completed
08/27/15CA 19,952
09/11/15CA 26,336
10/02/15CA 35,444
10/08/15CA 38,278
08/27/15CO2797
09/11/15CO3264
10/02/15CO4270
10/08/15CO4297

below is what I am trying to achieve:

DateStatesTotal_CompletedCompleted_Difference
08/27/15CA 19,952 0
09/11/15CA 26,336 6,384
10/02/15CA 35,444 9,108
10/08/15CA 38,278 2,834
08/27/15CO27970
09/11/15CO3264467
10/02/15CO42701,006
10/08/15CO429727

below is my code (I almost have what I need) I just can't figure out how show 0 as the completed_difference for the first Date for each state since there is no prior date to calculate against.

MRR_TOTALS_WEEK_OVER_WEEK AS
(
SELECT
T1.[Date]
,T1.States
,T2.Total_Completed
,ROW_NUMBER() OVER(PARTITION BY T1.States ORDER BY T1.States,T1.[Date]) AS ORDERING
FROM TOTAL_CHARTS T1
LEFT JOIN TOTAL_COMPLETED T2 ON T1.[Date] = T2.[Date] AND T1.States = T2.States
)

[code].....

View 4 Replies View Related

Output File (.txt) Running Package From SQL Server Agent

Mar 19, 2008



Hello,
I have created a package that runs without problem.
I run the package with the command dtexec /F "package_name.dtsx" > package_name.txt.

Then I run the same package from SQL Server Agent, everything is OK

Then, I tried to edit the command line to have the output file, but I got an error.

The command line is:
dtexec /F "package_name.dtsx" MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E > package_name.txt.
(MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E are created by default)

How can I do?

Thank

View 4 Replies View Related

SQL Server Admin 2014 :: Cannot Open Data File When Running Agent Job

Apr 30, 2015

I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.

We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).

Here what I have done so far:

I created the database

Created the file and successfully imported data into it using the Import Data feature.

I saved the SSIS package

Scheduled an Agent Job for this package to run at certain time,daily

At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.

Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.

View 9 Replies View Related

SQL Server Admin 2014 :: Ensuring Upgrade Advisor Is Running Properly?

Oct 30, 2015

I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).

I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?

The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.

If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc. DB is running in Compatibility mode 90.

View 4 Replies View Related

How To Carried Proc Print Output To A .txt File?

Dec 21, 2007

For certain reason, I set nocount off in the proc procABC.
I need to collect all the counters(Ins/Upd/Del) and
print statment on the isql/w screen to a .txt file.

How could I capture all the running info as a daily log file?

thanks
David

View 1 Replies View Related

Incomplete Print Output From ReportViewer Control

Dec 29, 2006

We are experiencing a problem where a server report is rendered in the
ReportViewer control successfully but when the user chooses to print the
content, the print output is incomplete (ex. only 18 of 23 pages are printed,
etc.).

This is happening consistently and has been reproduced for multiple
different reports. It does not happen if the same report is printed via the
Report Manager web interface.

I have also found that if I switch to Print Layout mode and then initiate
the print from the ReportViewer that the problem does not occur. This is our
current workaround, but it would be nice to know what is causing this and
have it fixed.

Has anybody else seen this? Is this a known issue with my current version
(SQLRS 2005 with SP1 applied).

Thanx in advance.

View 3 Replies View Related

SQL Server Admin 2014 :: Running Frequently Transaction Log Backup During Integrity Check DB

Jul 6, 2014

Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...

View 2 Replies View Related

Transact SQL :: Server Is Cutting Off Result-output Running A Powershell Script

May 7, 2015

I want to run a powershell script using xp_cmdshell, put the results into a temp table and do some additional stuff.I'm using:

CREATE TABLE #DrvLetter (
iid int identity(1,1) primary key
,Laufwerk char(500),
)
INSERT INTO #DrvLetter
EXEC xp_cmdshell 'powershell.exe -noprofile -command "gwmi -Class win32_volume | ft capacity, freespace, caption -a"'
select * from #DrvLetter
SQL server is cutting off the output, what I get is (consider the 3 dots at the end of a line):
 
[code]....

View 2 Replies View Related

SQL Server 2014 :: Error Running Stored Procedure From SSIS Runs Fine In SSMS?

Mar 23, 2015

I have simple query which creates tables by passing database name as parameter from a parameter table .

SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .

Note** I am not passing any database of name '20'

Exception handler error :

ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------

Error in SSIS

[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .

View 6 Replies View Related

Unable To Set SQL Express Service Status To Running State

Nov 1, 2007

Hi! Im just starting to learn how to use an SQL express database. I used it over at my campus and it worked fine and now when I try running it on my machine it gave me this error :

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection.(Provider : SQL Network Interfaces, error : 26 - Error locating server/ instance specified.)"

I know that by default SQL express doesnt not support remote connection but when i try to get the service status running, i got another error which is :

"The service did not respond to the start or control request in a timely fashion, you need administrator privileges to be able to start/stop this service. (SQLSAC)"

I am already logged on to the computer as an administrator but i still cant get the service to run! Pls help!

View 1 Replies View Related

Query Analyzer Error Unable To Connect Server Local Msg17, Level 16,state 1/ODBC SQL Server Driver [DBNETLIB]SQL Server Does Not

Oct 20, 2007

I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist

View 6 Replies View Related

Analysis :: SSAS MDX Running Total Of Items In Current State?

May 22, 2015

Let's have a table to show what I mean ...

As you can see I have added only one contract (for the sake of simplicity). At any given date I want to calculate the running total for a contract (all contracts), but the aggregation must take the contract state into consideration.

You can see my expected result for queries 1 - 3. That means, with the actual state ('B') I want to aggregate all values regardless of their state in the past.

For query 2 the state is 'A' ... I hope you can follow.

Now my aim is to aggregate all contract states for any given date with the right value. The past states of each contract are not relevant. And of course future ones (with respect to the query date) shall also be irrelevant.

Maybe the solution is a combination of 'LastNonEmpty' and SUM/PERIODSTODATE ...

View 5 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

SQL Server 2012 :: Dynamic Query To Print Out Resultset From A Table?

Sep 9, 2015

I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation

Is there any way to print all the 3 INSERT stmt.

IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'

[code]....

View 4 Replies View Related

Transact SQL :: Use Print Function To Output Numeric Variable With Fixed Amount Of Leading Zeroes

Apr 23, 2015

I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string.  For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.

Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.

View 4 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related







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