All Questions
Tagged with sql-server-2008-r2 sql-server-2008
337 questions
2
votes
1
answer
908
views
How do I fix the Failed to retrieve data for attaching mdf file is which not primary database file
I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user
I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach ...
- 33
1
vote
0
answers
116
views
MSSQL Server Database Trigger Permissions - issue after migrating from 2008 to 2017 Server
I have some triggers set up on a database (Dynamics NAV) across various tables for Insert, Update, Delete. The triggers write to a separate database to log the DB, Table, RowId and date/time. The ...
- 11
0
votes
1
answer
1k
views
Cannot Insert it into in my table for Special characters when ANSI_WARNINGS is Off even with nvarchar
I want to insert a data row into my table as name: DSKWOR00 , but I get this error :
String or binary data would be truncated.
so I turned ANSI_WARNINGS OFF and it inserted the row but the result ...
2
votes
1
answer
1k
views
Why SERIALIZABLE level does not lock the table for other session in SQL Server
I want to Prevent concurrency in my table for Delete, Insert, Update, and Select
so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
0
votes
1
answer
175
views
SQL Server Access Database in Filesystem
We have a Windows Server 2008 which is offline. We no longer have access to the server OS, or any GUI tools, but we do have a backup of the entire C:/ of this server.
This server used to host an SQL ...
- 177
0
votes
3
answers
8k
views
How to remove database from explorer after deleting .MDF and .ldf files
I wanted to know if I can delete or remove the database shown in the object explorer after someone has manually deleted the MDF and ldf files from the database folders.
2
votes
2
answers
484
views
How to remove dbcreator server role?
I was able to add the dbcreator server role to a user of a SQL Server DB, thanks to this nice question/answer: create-database-permission
How can I REMOVE that permission? Is there a stored procedure ...
1
vote
0
answers
383
views
Missing tables in AdevntureWorksDB
Recently I found some old questions that involve querying data from AdevntureWorksDB across several operations(DML,DDL).
One such question was :
Create and execute a query that computes the total ...
- 131
0
votes
1
answer
285
views
SQL server row count
I had an activity for restoring a full database in SQL server 2008.
I had to restore the database will norecovery since I had to restore the differential backups as well which will be shared everyday.
...
-1
votes
3
answers
77
views
how to count second col if first col value is null and avoid duplicate count
I'm making sales statistic report to calculate scanned documents station wise using below Query:
SELECT
RIGHT(CONVERT(varchar(11),DATEADD(MONTH,DATEDIFF(MONTH,0,InvoiceDate),0),13),LEN
(CONVERT(...
- 109
0
votes
1
answer
62
views
odd OS memory consumption by two nearly-identical SQL 2008 R2 instances
Instances are legacy, something we have to carry for the next 3 years, 2008 R2 64-bit, both SQL Server and Windows Server OS. Two copies on two nodes.
Both servers are set to consume "Maximum server ...
- 123
0
votes
1
answer
1k
views
calculate running balance of debit credit using union all
i'm making account statement for which i need that account balance before today and with each transaction row of debit or credit plus and minus from initial opening balance on the basis debit/credit ...
- 109
-1
votes
2
answers
381
views
How to get First in and Last out in multiple records with night shift:
We have Date, Time, EmpId, InOut, Shift_Start_Time and Shift_End_Time of an employee.
Need your help to write a sql for these conditions:
1.Consider the records which Time is falling between ...
- 1
1
vote
3
answers
3k
views
How to Add A User To A Database In Standby / Read-Only Mode In Sql Server
I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?
0
votes
0
answers
89
views
Unfinished A Killed/Rollback Transaction [duplicate]
I have killed a transaction 2 days ago but it hasn't finished yet. You can see the situation in the pic. What can i do?
- 15
-1
votes
1
answer
2k
views
How to Find All Sql Server Users Privileges Rights
I want to list all user's privileges which is in Active Directory accout or using Sql Server account to connect to my database server. I want to see that which users have ddl right or which users can ...
- 15
0
votes
2
answers
2k
views
Database Log File Size Growth So Huge Unexpectedly [duplicate]
My database has two log files. Their sizes are growing very fast. My backup sizes are becoming massive. I know that when I take full backup of my database, the log files are emptied but it is not. I ...
- 15
1
vote
1
answer
896
views
How to Get Backups & Restores Start and Finish Times?
I backup my databases from prod environment to file server. After taking my backups, i restore them to my report server. And i want to know that if i can get information about when backup and restore ...
- 15
0
votes
1
answer
1k
views
Moving Partitioning Files To Another Disk Drive In SQL Server
There is a production table which was partitioned by someone in our company. Some partitioning files was wrongly located to different disk drive.
I have to move those files to the disk where they ...
- 25
0
votes
2
answers
226
views
How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It
We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want ...
- 47
0
votes
1
answer
1k
views
Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value
I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each day is divided into 24hrs/2 = 48. ...
- 181
0
votes
1
answer
367
views
Conversion of Row into Column
I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios.
Input looks something link this.
Where Each patient has Primary Dx code, Admit Dx ...
1
vote
0
answers
198
views
SSMS - Data load Option
I have recently installed the newer version of SSMS on my system. But the option to load the excel file is grey out. I'm not able to load BULK Load
I tried to run DTSWizard.exe, even it doesn't give ...
0
votes
1
answer
3k
views
SQL PRINT vs SQL EXEC
When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error:
Is there a way of ...
- 294
0
votes
1
answer
345
views
Using IF clause or while clause in OUTER APPLY
I want to select dates between @startdate and @enddate but I get an error when a month doesn't have 31 days.
DECLARE @StartDate date, @EndDate date
set @StartDate = '2019-04-01'
set @EndDate = '2019-...
- 11
1
vote
2
answers
4k
views
Subtracting sum of previous rows
I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause.
I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total ...
- 11
1
vote
1
answer
751
views
Convert SQL Server results to a text file that PowerShell can access and use
The query below is used to create a text file with a list of server names.
The problem I have is that the text file gets created like
Server, Server, Server etc.
but I need it as follows
Server
...
- 294
0
votes
2
answers
785
views
What is the best way to remove large amount of data from large tables?
I have two large SQL 2008 tables:
table_header with 7 columns and ~ 2 million records
table_detail with 22 columns and ~ 78 million records
The common field between the two is called item_name ...
- 11
2
votes
1
answer
762
views
SQL Server 2008 Version of rows between... preceding and current row
Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008
As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + ...
- 119
2
votes
1
answer
1k
views
How to execute a query based on the condition if a column exists?
I want to run the following query:
CREATE TABLE dummy AS
SELECT x, y, z
FROM table1
WHERE y!=0
then I want to do the following:
if z column has a non-null entry do:
1. copy the content of z into A
...
- 307
-3
votes
1
answer
400
views
calculate group by running total in sql server 2008 [closed]
I HAVE SOME SAME SAMPLE DATA IN MY TABLE
ID | HOUSENO | AREAR | CURRENT | TOTAL_DEMAND | TOTAL_RECIVED
----------------------------------------------------------------
1 | 1/10/1 | 0 | 165 ...
0
votes
1
answer
2k
views
Specifying the IP address to be used when connecting to an instance
I have an application that was compiled with a hard-coded connection string (using the server's IP address '192.168.40.201' and not the server name) to an SQL Server.
A backup of the database has ...
- 3
1
vote
2
answers
1k
views
Windows Authentication In SQL DTS packages
We have a SQL server 2008 R2 and have legacy DTS packages running on the server. As of now, the subtasks inside DTS packages are using dynamic properties where SQL Login ID details have been given, ...
- 13
2
votes
1
answer
467
views
How to restore .bak only if it's good
I'm using Tsql script to restore several databases.
RESTORE DATABASE XXX_M
FROM DISK = 'C:\XXX.bak'
WITH FILE = 1, replace
GO
Sometimes the .bak file comes corrupted (not data corrupted ).
I ...
- 211
2
votes
1
answer
223
views
SQL 2008/R2 Extended Events - Row Count [closed]
I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.
In sql trace, this is possible. But I can'...
- 1,174
1
vote
1
answer
760
views
Find the destination owner (schema) for replication on SQL Server
We have a table replicating to another server with a different Schema at the destination. When I set the replication up it shows as destination owner in the GUI. I am trying to create some reports ...
- 165
2
votes
2
answers
421
views
Changing the column width in Transactional Replication
Changing the column width in SQL server Transaction Replication.
Can we do direct width change ? or we need to takeout the article and modify.
This change replicate to subscriber? or we need to do ...
Anilkumar Jantikar
3
votes
1
answer
555
views
SSIS Excel export changes # to
I am exporting an excel file through SSIS using excel connection manager. I am using SSIS 2008 version and the excel is .xlsx
CREATE TABLE `Sheet1` (
`Date` DATETIME,
`NMMO` NVARCHAR(30),
...
- 85
4
votes
1
answer
8k
views
Setting up Mirroring in SQL Express Edition
I know doing something like mirroring in SQL Server express edition is not possible, but can we do real-time db backup and restore and setup some automated task which acts like mirroring and keep them ...
1
vote
1
answer
1k
views
Incrementing an integer every 150 rows
I'm trying to write a SELECT query that calculates a BatchNumber column that increments by 1 every 150 rows, starting at 1001.
The expected BatchNumber should be 1001 for the first 150 rows, then ...
- 85
1
vote
1
answer
7k
views
Need to filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour) [closed]
I want filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour)
I want to fetch data from "ProDataTable" by in between Start Date and End Date with Interval of (1 Sec, 1Min, 10 Min,...
-1
votes
1
answer
6k
views
Turn Rows Into Dynamic Number Of Columns
I have a table in SQL Server that is horrid design, but am in need of a way to display ONE row per username with a breakdown of each item and qantity of that item the person sold. This is sample DDL
...
2
votes
1
answer
9k
views
Update Top 1 Row In SQL Server
I know that typically you would have an autonumber field - but in this instance there is not, and it honestly does not matter which row get's updated as long as it is only 1.
I have sample DDL like ...
0
votes
1
answer
1k
views
Regular Data Space Specification vs Text/Image Filegroup
I want to change the FileGroup of a table for FG01 to FG03 . but in properties of table there is the Regular Data Space Specification and Text/Image Filegroup which have the list of FileGroups, what ...
- 157
0
votes
2
answers
222
views
Create Table Every X Rows
I have a SQL Table that we are selling off some of the data, so I need a quick way of splitting the main table into sub tables every 50 rows. Table nomenclature does not matter, I am just stuck on a ...
1
vote
0
answers
409
views
SQL Server Configuration Manager: Error 0x800706be
Running a computer with SQL Server 2012 Express installed.
Just installed SQL Server 2008 Express and updated to SP3. Also have installed SQL Server 2008 R2 Express and updated to its SP3.
Opened ...
1
vote
2
answers
633
views
Customized date range in sql
We have got a customized quarter list from client which we need to take reference to get data in SQL. Could you please help.
30-Oct 29-Jan AS Quarter1
30-Jan 30-Apr AS Quarter2
01-May 30-Jul AS ...
- 85
0
votes
3
answers
1k
views
Comparing the values of two columns with different datatypes
I have two columns in a table which need to be compared against the data.The problem that I am facing is that each of the columns have different datatypes: float and datetime2.
EventID EventStartDate
...
- 908
-2
votes
2
answers
8k
views
Return First Non Zero Value
My desired result set is
Red Purple
1 5
all on one row.
My query below returns 4 rows.
What would be the syntax to only return 1 row?
DECLARE @BlueBonnet TABLE (
randarfield FLOAT
,...
- 261
0
votes
1
answer
588
views
Return only One Row From Dataset [closed]
I have three tables and all tables hold a primary key of empnum this is an image of my desired result set
However my query is returning 9 rows instead of 3! See screenshot
This is my DDL -->
...
- 261