New answers tagged sql-server
0
Loopback linked servers cannot be used in a distributed transaction . Install two SQL Servers in local to solve your problem. Link one instance to another.
http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx
0
I think you cannot perform backup and restore from production server as it is a crucial data.
Well without the proper rights it really becomes more Complicated.
But if you have db backup n restore right then you can perform it .
Or else, One way that I would recommend though is to drop all of your constraints and indexes and then again add them once the ...
0
I have ended up going with a dual table design. The first table would contain all data that would not have a translation for it. A second table would have the word "Translation" appended to it and it would contain columns to cover all the data needing translations and have a single translation per row.
0
In my specific case, this was a result of cell security. The role did not have access to all the fields returned in the drillthrough action. I missed this because there are two very similarly-named fields and my eye did not notice. It's still odd that a security denial creates a query timeout, but at least this note is here for the next person.
0
There are way to many options to just make a list of them and chekcing one by one. Each scenario has its own approach, depending on many factors. There are some key actions you could take in order to define, more or less, where to start. Building a baseline to later test any change you do is a starting point, once you cross all the network, app, or any other ...
1
There's some lack of detail in the question; for example, it would be helpful to know:
What is the reason to combine the data together into a single database? It makes no sense to me if there isn't a further goal behind doing so. Perhaps there is a better way to make that end goal happen with fewer intermediate step(s).
Is there some kind of boundary ...
4
You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.
The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your ...
0
Your syntax is wrong for adding a backup device.
When you create a backup device in SQL Server you are actually creating the "bak" file as a media set.
Along with the suggestion from Bob that you should be using the full UNC path, your syntax should be changed to something like this:
IF EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = ...
0
Have a look at Importing Data Into SQLServer on Amazon RDS.
Also, from codeplex SQL Database Migration Wizard with a reference video as well.
0
There is an interesting detail to this query that I did not spot at first. Thanks to Fabricio Araujo's answer I now see it: you are accessing two tables. I have never seen this kind of usage of the update statement before and I do not advise using it. I recommend you use the more intuitive join syntax per Fabricio's answer.
The likely cause is that the join ...
0
Linked Server from SQL Server 2012 to SQL 2000 is not supported natively or directly. SQL Server 2012 comes with new version on native client i.e. SQLNCLI11, which only connect back to SQL 2008R2/2008/2005 versions.
Also even if you install previous native client i.e. SQLNCLI10, it won't work.
So, if you still wan to connect use MSDASQL provider instead of ...
0
First of all, change the query to:
UPDATE t1
SET
costPercentage = ISNULL(t2.PaymentIndex, 1.0),
t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
* ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
* ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
dbo.table1 t1
inner join dbo.table2 ...
2
The backup hangs because the SSAS database is corrupt, which was the result of a forced reboot of the server.
Some questions this raises:
If an SSAS database has an estimated size of 0, does that always indicate a corrupted database?
Are there any other ways to detect SSAS db corruption by using SSMS dialogs or AMO?
1
You can do by creating a role for the AD Group user and assigning permissions to it.
Create your Active Directory Group as a login in SQL Server as below :
USE master;
GO
CREATE LOGIN [COMPANYDOMAIN\ADGroup] FROM WINDOWS;
GO
Then create a user from the login in the database
USE DatabaseName;
GO
CREATE USER [COMPANYDOMAIN\ADGroup] FROM LOGIN ...
4
You could implement a query using row_number() to delete everything but the most recent row. This partitions the data by the employee_id and orders it by the autoId column, then you delete everything that is greater than the first row number:
;with cte as
(
select [EMPLOYEE_ID], [ATTENDANCE_DATE], [AUTOID],
row_number() over(partition by ...
7
This query requires you to scan every row in the table because
I guess procodet or ProviderCode are not indexed
Even if they were indexed, you have a LEFT which is a function on a WHERE predicate
And you have COLLATE too which is effectively a function on a WHERE predicate
"a function on a WHERE predicate" means indexes won't be used
If you batch it ...
0
I dont see a need for 1.
You can use below script to move logins from one server to another. It uses SQLCMD and xp_cmdshell.
You can also look for a PowerShell option if you dont want to use xp_cmdshell.
set nocount on
-- Author :: Kin
-- Desc :: Move Logins from one server to another
-- Version :: 1.0 for dba.stackexchange.com
-- Date :: ...
0
It may also be worth checking that the new records are created with an adequate default size.
You do not want new pages allocating 10 bytes per record and then the application populating them with 100bytes worth of stuff.
This would be forcing page splits right/centre/left. It could be avoided by finding the average record size and having default values of ...
1
Specify float when fetching the value from the XML.
select @XML.value('(//Balance)[1]', 'float');
To change the value in a XML column with decimals included you need to first extract the value as a float and write it back as an numeric with the appropriate precision and scale.
Something like this.
update T
set XMLCol.modify('replace value of ...
0
You can use the convert function, like so :
DECLARE @x XML =
'<Root>
<Row>
<Rowid>1</Rowid>
<date>2013-05-06</date>
<Balance>1.0002E7</Balance>
</Row>
</Root>'
SELECT CONVERT(FLOAT,(@x.value('(/Root/Row/Balance)[1]','nvarchar(100)')))
1
Your trouble is likely due to how migration was done.
Stuff shouldn't be attached to your user, unless you are considered the owner.
Schemas are there to help you separate tables by whatsoever makes sense.
Suppose you have one table of resources for the HR departement and want a separate one for the production department, while keeping both on the same ...
4
Some notes:
After rebuilding indexes, do not update all statistics!
REBUILD indexes, don't REORGANISE
An index rebuild will rebuild statistics anyway. A further update actually means you'll have worse statistics because of sampling ratio. You can rebuild column statistics though.
Also, it's worth comparing the server specs (RAM, CPU, Disk) and query ...
0
In my current project, shrinking is a weekly part of our lives. Not into production, of course, but our various testing environments. When your live DB can take a huge amount of space, much of which isn't needed on development and testing servers, it can be a feasible idea to parse the unnecessary information from the production DB. Then shrink it to some ...
1
My guess is they were trying to create a 4-byte GUID situation.
Not sure what the benefit would be though: OK, it would avoid insertion 'hot spots' (at the expense of fragmentation built in the design). But being only 4 bytes the chances of collisions are vastly higher than a GUID. So it would fit a relatively small table with a very high rate of new ...
0
Not sure if this is what you're after but when I migrate databases I generate the CREATE LOGIN statements like this
select 'create login ' + name + ' with password=', password_hash, ' hashed, sid=', sid from sys.sql_logins
Note that this transfers the SQL logins only
0
Could it be that the database is used in a different way in the new location?
This is a common theme - different queries running in the database copy (perhaps reporting-style queries).
In this case you may want to drop the existing indexes and create new ones that support these new queries.
You'd first have to let them use the db on the new location for a ...
0
You may want to check out ETL tools like Talend Studio and Pentaho Kettle.
Also, my sympathies. Horrible messy undocumented data models that change all the time and come in the form of dumps in random irregular forms are no fun at all.
9
This is a classic case of why you should specify the schema name when accessing database objects. When it is unspecified and you're trying to access an object in a non-default schema then you're going to run into the issue that you're seeing right now.
The real fix is to change your application (or whatever querying agent you have right now causing the ...
3
As addition to @AdamWenger answer.
To create scripts for transfering to anothe schema you can use following script
select 'ALTER SCHEMA dbo TRANSFER '+s.name+'.'+t.name
from sys.schemas s
join sys.tables t on t.schema_id=s.schema_id
where s.name='erpadmin'
6
@@TRANCOUNT reports a count of BEGIN TRANSACTION statements, not active transactions. From a different perspective, it is reporting the depth of a nested transaction.
@@TRANCOUNT (Transact-SQL) Returns the number of BEGIN TRANSACTION
statements that have occurred on the current connection. [source]
Misunderstandings of nested transactions and ...
1
This behaviour is described in the Microsoft documentation on ROLLBACK TRANSACTION:
ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
Because a ROLLBACK TRAN can exist in the middle of some complex logic, parts of your code might not even be sure whether one was executed or not, making it difficult to figure out whether you need to ...
10
If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema:
ALTER SCHEMA dbo TRANSFER erpadmin.tablename;
An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of ...
1
As long as you are not using features from Standard that are not supported in Express you should be able to move the database between versions. Just don't do an inplace upgrade. You will not be able to roll the upgrade back.
0
SQL Server editions differ by the features and capabilities that they provide like no.of cpu's , memory, database size, High Availability options, Disaster recovery options, etc. You can refer BOL for more details.
When you say Trial - it can be Enterprise Edition for 180 Days.
Unless you have the same database version, I would recommend your to do a ...
1
Yes you can, but it can be somewhat tedious if the database versions are different. If you are staying on the same database version, you should be able to re-attach the database files or restore the database from backup -- assuming that you haven't used any features of the new database that can't be downgraded (like compression or partitioning in the ...
0
You can also look in the windows event log, under Application. It will often provide additional depth to the SQL Server error log and can give specific drive/folder information on what was denied. If you don't have administrative tools installed here is how to add them Display Administrative tools Windows 7
0
You need to look in SQL Server's error log, which is kind of a catch-22, since you need access to the SQL Server in order to see the error log.
You don't need direct access to SQL Server to read the log; if you have physical access to the machine, you can find the log in something like:
C:\Program Files\Microsoft SQL ...
0
There's actually a really easy way to do this without installing SSRS or any other tools. I used SQL Profiler, ran the reports stated, and got the code which I copied below. You will want this copied in a scheduled job and have it send out the mail from within the job and you're done!
This is the query passed for the 'jobs history' report:
exec ...
0
Well... I don't have access to 2005, but you can probably use this as a base.
Assumptions:
The principal that calls this script has permissions to read tables in msdb (though you can run the script from any context, not necessarily from within msdb).
The SSIS package(s) of interest are:
Stored in the local SQL Server package store
In the root folder of ...
3
A good scenario on where you would need to shrink a database file is to remove Virtual Log Files in your transaction log. VLF's can come about because of improper growth and sizing strategy, or just kind of creep up on you over time. One of the maintenance tasks we have is to monitor VLF's. If you find your database has a large number of VLF's then shrinking ...
1
You cannot and should not blindly implement recommendations by any tool without understanding the overall consequence that it might make on the application as well as server performance.
Always evaluate it before implementing it blindly.
DTA will generate its recommendations based on the workload that was presented to it.
From BOL :
Database Engine ...
3
The tuning advisor (DTA) can yield information about missing indexes, but the results are only as good as the workload you've provided and your ability to interpret the results and fill in the gaps.
Your .trc file may only contain certain events, not all, and may not cover a full business cycle. So if you have a big set of reports that people run at the ...
0
When an index rebuilding operation is performed, it drops the index, recreates the index, and updates its statistic.
If I read the question correctly, your question of dropping indexes, recreating then update the statistics is pretty much the same as simply rebuilding the index.
Please note that index reorganization operation does not update the ...
2
Is the data transactionally consistent across all applications?
yes = you need one database
no = use separate databases
0
I suggest you to try free trial version of Devart's Data and Schema Compare Tools. If you like them in furute you can buy full professional version (it is not expensive).
More about the tools here:
SQL Data Compare - http://www.devart.com/dbforge/sql/datacompare/
SQL Schema Compare - http://www.devart.com/dbforge/sql/schemacompare/
5
I agree with mrdenny to have 1 database per Application. There are many good reasons to do that :
Most importantly Log :
SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online.
Full Backups : Easy to manage per application and can be ...
1
I had a similar problem with a patch on SQL 2008 R2. It just silently died. I ended up calling Microsoft and documented the process we went through to find the solution. While I realize your problem is going to be different from the one I had the process may be of some help.
http://www.sqlservercentral.com/Forums/Topic1262389-391-4.aspx
0
Indexes fragmentation and statistics are not the only possible cause for a performance trouble.
Apart from this rebuild and reorganize are different process.
INDEX REBUILD: it drops the existing index and recreates the index. Can be done online or offline.
INDEX REORGANIZATION: it physically reorganizes the leaf nodes of the index. It is always online.
...
Top 50 recent answers are included
