0
votes
1answer
29 views

Find Without Primary key Table in SQl Server 2008R2? [duplicate]

This is my Query to find out Primary key,Foreign key in SQL Server 2008R2 select * from information_schema.table_constraints where constraint_type ='PRIMARY KEY' Know i need to Select With out ...
1
vote
0answers
30 views

What will do After Press F5? [on hold]

select * from Employee; Name id Col1 Col2 Row1 1 11 1 Row2 2 12 3 Row3 3 13 30 Row4 4 14 44 Above is my table After PRESS F5 What process is running in Background ...
0
votes
1answer
24 views

SSMS 2008 / 2014 showing different values for TempDB file sizes

Here's a fun one. Last week I reconfigured the initial file sizes in our tempdb database to 1GB each. We had an event a few weeks back that locked up our server due to a table with no indexes ...
0
votes
0answers
28 views

Logging and Error MS SQL Server 2008 R2 [on hold]

Currently there are multiple SSIS packages, batch jobs etc. I'm looking for best practices that could be followed for normal Logging and Error Logging for SSIS packages . I understand it is a ...
0
votes
1answer
43 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
1
vote
2answers
43 views

Merge Table Row Values to Header of Another Table

Table 1 id | Field | Values ---------------------------------------- 1 | 'Field01' |Name 2 | 'Field02' |Age 3 | 'Field03' |Gender 4 ...
0
votes
2answers
42 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
-1
votes
1answer
33 views

Issues with procedures, setting multiple values [closed]

I am trying to set multiple values but I am only able to set one value. This code fails: alter procedure modifyvalue @ID int, @flight varchar(6), @destination char(10) as begin update ...
2
votes
1answer
353 views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
1
vote
0answers
37 views

Low batch Batchrequests/sec

I am running this query to do massive deletion in my database. I am looking at Batchrequests/sec and it is quite low (like 15% of what my server can handle) Is there any way to rewrite this query to ...
0
votes
0answers
45 views

table check constraint, greater than > not accepting

CONSTRAINT CK_BABY_YOUTH_CHILD CHECK (case when Passenger_Type='Baby' then Reference_ID >=0) This is my table check constraint, i have no idea why sql server is not accepting ">". Please ...
1
vote
1answer
52 views

Restoring a database, changing recovery model

I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing. The production database uses full recovery ...
0
votes
1answer
124 views

Why straight SQL query run faster than stored procedure?

I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will ...
1
vote
1answer
46 views

Maintenance plan backup and reload the same database

I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here: Restore database - maintenance plan ...
1
vote
1answer
85 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...
2
votes
2answers
70 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
0
votes
0answers
32 views

I am trying to implement Scott Chamberlain's code in SQL Server 2008 R2 . Getting Assembly not present errors

I am trying to use Scott Chamberlain's code, here: http://dba.stackexchange.com/a/17466/45380 I was able to compile it and create the DLL, but now when I try to run SQL CREATE ASSEMBLY I get: Msg ...
3
votes
2answers
200 views

Can I update every datetime in bulk?

The current database assumes all times are local to the user. Now we're changing this so the database stores everything in UTC and our application converts the UTC time in the database to the correct ...
0
votes
1answer
53 views

User defined data types causing implicit conversions

user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into ...
0
votes
0answers
30 views

Sql Server Agent Job

In my Local SQL Server 2008 I created a scheduled job (like daily email will send to particular user from the database based on condition). Now I decided to host my schedules in hosting server so can ...
0
votes
2answers
134 views

SQL Server 2008 R2 memory issue

I have read many posts regarding the memory but still its not clear. I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on ...
0
votes
2answers
114 views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
4
votes
2answers
783 views

Is it possible for SQL Server to miss rows on a select * where PK = “value” query

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment. I have a very strange error in our application and I was ...
0
votes
1answer
60 views

Sql Server sys.Traces log

Working with traces on production server I found some mysterious entries to me in sys.Traces, When I run below query it shows me status of all traces as ‘Paused’ along with entries in ...
2
votes
1answer
83 views

Database backup taking longer than usual to a networkshare [closed]

The SQL server does backups with this command to a network share \BackupServer\weekeklybackups" BACKUP DATABASE DBNAME TO DISK = '\\BackupServer\weekeklybackups\DBNAME.BAK' There is a 1 gig network ...
-3
votes
1answer
98 views

Select Top N rows for Employees in a SQL Server table

I would like to select the TOP 2 activities carried out by all employees from an Employees table "Emp" which has a timestamp column. The regular "Select Top 2 * from Emp...." will only fetch the top ...
4
votes
3answers
154 views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
0
votes
0answers
50 views

msdb 'backupset' log history

One of our production server‘s backup job is getting failed for one DB because of Low disk space when I looked into ‘physical_device_name’ column in ‘msdb.dbo.backupset’ to get the path of backups I ...
2
votes
1answer
23 views

remove a paritition table

I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2. The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
3
votes
0answers
51 views

Using Import Wizard with Access - No null values

I am trying to import a table from Access to SSMS using the Import Wizard but when I do the resulting table has a lot of empty strings instead of the NULL values that I am expecting. How do I make ...
1
vote
1answer
57 views

Simple query not returning blank records

Very simple example here to a very frustrating treasure hunt for the mystery character. I have a table with a varchar(1) NOT NULL column - everything default - ANSI_PADDING & NULLS are ON for the ...
0
votes
2answers
153 views

Get Date Limitation

So what I need is a limitation for the user of the funded contracts. So this is what I am trying to implement. I am trying to let the user enter dates within the year 2014, which will bring up how ...
0
votes
2answers
108 views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( ...
4
votes
3answers
444 views

Calculating the maximum seen so far for each point in time

What I'm trying to calculate is a bit tricky to describe, so please bear with me... I have a table with product numbers, and the price of each product each week over a period of 4 years. Price can ...
0
votes
1answer
85 views

getting error incorrect syntax near @certificate

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local ...
1
vote
1answer
101 views

Database Mirroring and Replication in SQL Server 2008 R2

I have configured the mirroring and replication between 4 servers (A,B,C,D).i.e, Mirroring between A to B and C to D, Replication between A and C. The configuration was a success and I am able test ...
0
votes
2answers
33 views

SQL Server Management Studio is not available

When I installed SQLManagementStudio_x64_ENU.exe I am not able to connect to my server. In the image (1) session, I am not getting server name. Also in (2) session also I am not getting it. Is there ...
0
votes
1answer
604 views

How to export data from sql server 2008 to excel and email as attachement

I'm new. Can anyone advise how I can export data from database sql 2008 to excel and email as attachment?
1
vote
1answer
335 views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
0
votes
0answers
133 views

Suppressing the removal of trailing zeros in numeric data type in sql server

I initially declared the data type of the columns for some of the tables i am working on as "NUMERIC". cause of that, trailing zeros are being removed. Is there anyway to suppress this behavior? i ...
0
votes
1answer
102 views

Is it possible to speed up this PARTITION BY query?

I've got a timeserie table. Each datapoint has also a statut field which indicates its providing source. I'd like to request last available datapoint as of date T but there could be several results, ...
0
votes
1answer
80 views

Table Valued Parameter not pulling the correct data for my sp?

http://sqlfiddle.com/#!3/b2643/5/1 http://sqlfiddle.com/#!3/b2643/5/0 This link above has the sample database tables I created on SQLfiddle. I have 3 main tables in my stored procedure: ...
2
votes
1answer
75 views

Does Index Intersection work with Included Columns?

I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report. The index would be on a single column, CreatedDate, but would have ...
-3
votes
1answer
1k views

What is the Difference between SQL Server 2008R2 and SQL Server 2014?

I want to take my project from sql server 2008 R2 to Sql Server 2014. But I want to learn what is T-Sql the difference between 2008 R2 and 2014 for Database Engine? I mean is there any end of life ...
1
vote
1answer
88 views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
2
votes
2answers
1k views

A transport-level error has occurred when sending the request to the server

In our Vb.net application(userd within a local network connection inside a office), which is used from long time now some of the users get the errors only sometimes a day A transport-level error ...
5
votes
1answer
352 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
0
votes
1answer
1k views

Declaring a scalar variable that its a table valued parameter?

I get error 137 that I must declare a scalar variable (@facilities), although I'm declaring it in the Create stored procedure as a readonly variable, so I wasn't sure what I'm missing here. any advice ...
2
votes
0answers
74 views

Which parameters was a stored procedure or command executed with?

We are using nHibernate which generates queries from .NET code. Occasionally, some queries get stuck in the sp_whoisactive list and we cannot find out why. I have a feeling that it has to do with a ...
0
votes
0answers
43 views

SQL Server 2008 R2 installation device error

I am trying to install SQL Server 2008 R2 to use along with Visual Studio for a website I am creating. However when I am installing SQL Server I am constantly getting the following error: The ...