Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
3,778
questions
-1
votes
1
answer
93
views
DMV sys.dm_exec_procedure_stats sometimes returns weird (incorrect?) values in successive object snapshots
I'm working on a small project to monitor activity and execution metrics of user stored procedures. Periodically (every 10 minutes or so), I'm taking snapshots of the sys.dm_exec_procedure_stats DMV, ...
- 3
0
votes
0
answers
30
views
Prevent Sql Server sending Failover partner to connecting clients
I have 2 Sql Server 2008R2, enterprise edition servers setup in an asynchronous (High Peformance) mirror setup. There is no witness.
Intermittently I find that a client will attempt to connect to the ...
- 141
0
votes
1
answer
148
views
MS SQL Login failed for user 'sa'.[CLIENT: <local machine>]
I changed the password of sa using the command [ALTER LOGIN WITH PASSWORD]. Then , it keeps showing the below message in fail log every minute.
Login failed for user 'sa'. Reason: Password did not ...
- 1
0
votes
1
answer
24
views
Table structure where name changes to be retained
Example Situation - order table with client list and products. In below example table , when client name/company changes (company name may change) when updating records need to retain old company name ...
0
votes
1
answer
81
views
SSAS Backup to Remote Location
I followed the steps in the best answer on this question - How can I dynamically back up all SSAS databases on a given instance? - and can successfully create backups of our BI Cubes to a local drive ...
- 101
1
vote
1
answer
62
views
Delete records in different tables
I need to delete the records where UserIndex = 1 and ItemNumber = 5202 in all three tables, all in a single query. I am using SQL 2008 R2.
Table UserInfo1
UserIndex
Itemnumber
ItemCount
1
5202
99
1
...
0
votes
0
answers
124
views
Dealing with Corruption
I started getting Error 823 alerts from my SQL Server. When I checked SQL Server error log, I found lots of this:
A read of the file 'path\to\my\file.ndf' at offset 0x00000d5f850000
succeeded after ...
- 319
0
votes
0
answers
46
views
Using SQL DMA with SQL Server 2008R2 to capture running SQL
I have a requirement to upgrade a SQL Server from SQL 2008R2 to 2019. Microsoft provides the data migration assistant (DMA) to help with analysing SQL code for compatibility issues. Running a static ...
- 13
2
votes
1
answer
101
views
What is trace flag 1605?
What is trace flag 1605 in SQL Server 2008 R2? I haven't been able to find any documentation on it so far.
- 9,630
1
vote
0
answers
30
views
SA disabled and no other user has access to master db [duplicate]
I have inherited an old SQL Server 2008 R2 where SA login is disabled. No other logins gives access to master db, so I cannot really gain access to administrative stuff (create new logins, change ...
- 111
0
votes
0
answers
72
views
How do I edit DataRows of DBF file via SQL Server Management with Query?
I have an old DBF file that I want to manipulate its data directly through SQL Server Management itself, for example, perform update, insert, delete operations.
I tried using the following code from ...
- 33
0
votes
0
answers
203
views
Inplace upgrade SQL Server 2008R2 to 2019 while retaining IP and computername using Start-DbaMigration?
I am running existing legacy applications in production using an older version of SQL Server 2008 R2 Standard Edition, my goal here is to perform an inline/in-place upgrade to SQL Server 2019 Standard ...
0
votes
1
answer
479
views
Is it possible to grant EXECUTE permissions on a custom function without compromising read-only access?
We have an ancient database instance - Microsoft SQL Server 2008 R2 SP1 - that is running an (also obsolete) business-critical application. I know this is horrible, but for now it's not my call.
Long ...
- 103
0
votes
0
answers
409
views
SQL Server - Exclusive access could not be obtained because the database is in use
Took backup of database from SQL Server 2008 R2 Enterprise and trying to restore it on a database on SQL Server 2019 Enterprise but getting error:
Exclusive access could not be obtained because the ...
- 441
0
votes
1
answer
607
views
Intall SQL Server 2008 On Windows Server 2016
We update our Server from Windows Server 2008 to Windows Server 2016. And we use SQL Server 2008 R2 on the old server. I read from the website said that SQL Server 2008 is compatible with Windows 7, ...
0
votes
1
answer
243
views
Sql Dump growing so fast
permission to ask. I have a SQL dump with very fast file growth. what should I do to stop it?
The solution I have done so far is to delete the file.
2
votes
0
answers
477
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
0
votes
0
answers
73
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 ...
0
votes
2
answers
60
views
How to substruct some table rows from another related table rows and get the SUM of the totale result
I have two table (Orders, Payments)
they are 1 to many relationships (1:N)
One order can have many payments
Table 1: Orders
id
total_price
1
1000
2
2000
3
3000
Table 2: Payments
order_id(FK of ...
1
vote
0
answers
94
views
High parallel tempdb disk usage
We recently added some secondary tempdb files to reduce allocation contention, this seems to have worked; however we are now getting lots of slowdowns related to I/O (WRITELOG and SQLTRACE_FILE_BUFFER ...
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 ...
-3
votes
2
answers
164
views
cant attach mdf file because of suspect mode
Please I need Help
i cant attach Mdf Database , As last time it was suspect , i made detach and i cant now attache it again .
and i recieve this message
An exception occurred while executing a ...
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 ...
-2
votes
1
answer
5k
views
How to Get all Dates Between Two Dates in SQL
I want to display all the dates between two dates for a particular record
Table.
JWDID
FromDate
ToDate
Quantity
100
2022-06-01
2022-06-05
1000
101
2022-06-03
2022-06-03
1500
102
2022-06-29
2022-07-...
- 5
0
votes
2
answers
645
views
Changing table name from upper to lower case
Can I alter a table name in upper case to lower case? Would it be reflected in its references also?
My project is already done.
- 1
3
votes
1
answer
1k
views
Users time out (or it's very slow) authenticating to SQL Server, but RDP authenticates immediately
The authentication timeouts and slowness happen for both local SQL logins and Windows authentication using AD. Rebooting "masks" the problem for a while, but after 6-8 hours, we see them ...
- 606
1
vote
1
answer
46
views
Safely delete on-disk backups and change behavior
I have inherited a server running SQL Server 2008 R2 and it keeps running out of space. After doing some digging with WinDirStat, I found that it is almost entirely due to the SQL Server backups ...
- 11
0
votes
2
answers
54
views
Recovering space on a restored and pruned SQL Server database for testing purposes
I have a requirement to regularly take a backup of a set of tenant databases (all same schema), and restore them in our UAT environment, then clear down most of the client data, leaving only a few ...
- 299
1
vote
0
answers
63
views
SQL Server 2008R2 - Cannot View DB Properties in SSMS or Query Database Files
I am trying to view the properties of a database but it fails in SSMS with below error in screenshot.
I searched online for solutions but none have worked. These include change db owner to sa. The ...
- 1,394
2
votes
0
answers
432
views
SSIS: How to export more than 4000 characters to Excel
I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors
Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
- 194
0
votes
1
answer
53
views
How do I fix this SQL server query 2008r2
I'm trying to update type from table Titem column type and set it to new id, while having it to check if user id owns those 2 items(oldid which we are updating, 2nd id which is just for checking if ...
- 79
-4
votes
1
answer
214
views
Select INTs from start of string separated by a character (pipe). Update other columns using these INTs
In the above screen shot, the Description column has many special characters. We want before | number update in T1 column and | after number in T2 column. Please suggest the proper syntax.
If there ...
7
votes
3
answers
2k
views
ALTER TABLE ADD COLUMN in batches but with NEWSEQUENTIALID()
I have a large table, but not huge (less than 2 million rows on aging hardware), and when adding a non-nullable column to an existing table, I usually follow the structure set out here to avoid ...
- 299
0
votes
1
answer
299
views
Missing rows in table after restore database from MDF and LDF with SQL Server 2008
I have got two files, test.mdf and test.ldf, with sizes around 200 Mb and 200 MB.
I restore the database using "Attach Databases" options, all is ok, but I found that there are a lot of ...
- 113
0
votes
1
answer
165
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
0
answers
661
views
If I rename a table with dependencies, and then create an synonym of the old name pointing to the new table name will the dependencies still work?
In an attempt to remove some downtime to my users, I want to redirect them to a back up table while I'm repopulating the production table. The tables in question have been in place for years so my ...
- 9
-1
votes
2
answers
783
views
1
vote
2
answers
459
views
Replication alternative
We are using SQL express that does not support DB replication to other servers.
1- is there any way/free tool that enable us to perform any type of replication?
2- do you have any idea to share the ...
- 11
0
votes
0
answers
141
views
SQL 2008r2 : The database is not accessible
Is there something that I am missing?
Newly created database with 2 users. Both have the same permissions. SQL01 gets error "The database DB01 is not accessible" on browsing through SSMS.
...
2
votes
1
answer
552
views
Watch and log query from a user on SQL server?
I need to find a way to log all scripts and queries (SELECT, INSERT, UPDATE, DELETE and EXEC) from a user.
Context: I want to check what kind of queries my support are doing on the database. At this ...
- 163
0
votes
1
answer
3k
views
How do I produce the relationship diagram in Microsoft Sql Server?
How do I produce the relationship diagram in Microsoft Sql Server?
Here is a visual of what I am talking about here:
- 131
0
votes
1
answer
386
views
Log backup Job disable and enable during FULL backup -Sql Server 2008R2
I am planning to test the Sql server migration of Production databases but i am thinking to stop TLog Backup job during Backup and Restore as I don't need TLog backup and my Backup and Restore process ...
- 11
0
votes
1
answer
357
views
SQL server: Any user can access database instance without any grants [closed]
User created from Active directory can access all instances of databases in SQL Server without any grants.
Was shocked to see this behaviour on our environment.
Example.
We have a database named MyDB
...
0
votes
0
answers
631
views
SQL Server Performance of a user defined table type
I have a stored procedure that is being passed a User Defined Table Type to retrieve 1 to many records back from a table that is filtered down by a join instead of building out dynamic sql to generate ...
- 1,771
3
votes
2
answers
509
views
Question Regarding ISDATE Function and DATENAME Functions
We have had an issue this morning with a report failing due to the following error:
"Conversion failed when converting date and/or time from character string"
The issue is that in the ...
- 33
0
votes
0
answers
60
views
Cannot modify a job in SQL 2008 R2. The Modify window simply doesn't come up
I created a maintenance plan to perform daily backups with transaction log backups to be scheduled on four hour increments, however, I did not enable the job immediately, nor did I enter add the ...
- 21
2
votes
2
answers
135
views
In a mirror, how can I keep the SQL Server Agent offline, but have it start automatically in the event of a failover on the Principal?
In this scenario there are three MS SQL servers. ServerA has a series of jobs that write data to databases on ServerB. I want to set up ServerC as a mirror of ServerA, with all the same jobs available....
- 21
0
votes
1
answer
77
views
Is possible to restore BD SQL Server without key encryption from 2008 R2?
I have a database that has an encryption. The encryption key was lost.
This database is on a SQL Server 2008 R2 server, and my mandate is to migrate this database on a more recent server.
Is there a ...
berodev
2
votes
1
answer
83
views
Identifying elements in SSIS package built in MS Visual Studio 2008
I recognize that these are variables, but I am not sure how to add these elements to the package (or how they are added to begin with), what effect that have or even how to manipulate them. No right ...
- 75
0
votes
3
answers
5k
views
What is Fastest way to shrink a datafile?
I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the ...
- 31