Tag Info

New answers tagged

0

1.How they talk each other(the two Servers)? From the database engine perspective, both SSRS and SSAS are clients like any other application. These query data from SQL Server using SQL queries. Microsoft just chose to include these components with SQL Server licensing and media. 2.Where the cube for SSAS stored? On the server with SSAS installed ...


1

Your syntax is slightly off. In SQL Server, here is how I would perform this update: UPDATE s SET s.city = c.city FROM dbo.Suppliers AS s INNER JOIN dbo.Customers AS c ON s.supplier_name = c.customer_name; From my most popular answer on Stack Overflow. Remember to always use the schema prefix and also it is often easier to just call those columns name ...


1

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use... and Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use? This is partially true. Index fragmentation wont cause HIGH CPU. Internal ...


0

The following actually kills all connections. Quite useful in cases where setting single user mode fails declare @execSql varchar(1000), @databaseName varchar(100) -- Set the database name for which to kill the connections set @databaseName = 'databasename' set @execSql = '' select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ' from ...


0

I was getting the same error, i was using a proxy account with my own database security credentials, and the password had changed. Once i changed it in the security ->credentials, the package started working..


0

I'll venture a guess at what you're looking for. I've made the assumption that the table is called test, the column which contains the entries for FIREARMS, MONEY, etc. is called category, and of course you have your other columns. So, with a simplified table and some data defined as CREATE TABLE test ( id integer, person varchar(30), category ...


0

Try: UPDATE Suppliers AS s JOIN customers as c ON (c.customers_name = s.supplers_name) SET s.City = c.city;


0

This is a SQL Server answer, hopefully similar in mySQL: DECLARE @orderLines TABLE ( orderLineID INT, orderID INT, itemCode VARCHAR(30), netValue DECIMAL(10,2), taxValue DECIMAL(10,2), storeID VARCHAR(2) ); DECLARE @orders TABLE ( orderID INT, grossValue DECIMAL(10,2), paymentValue DECIMAL(10,2), typeID INT, orderDate DATETIME ); ...


1

There are several ways to do it. However, this may work for you: select myTable.* from myTable order by (case myCol when 'FIREARMS' then '' else myCol end);


0

Assuming that you want to SUM the netValues of each storeID but a paymenValue distinct: Query: SELECT storeID,SUM(Total) AS Total,SUM(paymentTotal) AS paymentTotal FROM( SELECT ol.storeID,SUM(ol.netValue) AS 'Total',0.00 AS 'paymentTotal' FROM orders AS o LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID) WHERE o.orderDate BETWEEN ...


4

You should shred on /WEBSITES/WEBSITE in your second query as well and use a predicate against the NAME node in the values clause and then get the value from VALUE node. select S.X.value('(WEBSITE_ID/text())[1]', 'nvarchar(100)') as WEBSITE_ID, S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text())[1] eq "COST"]/VALUE/text())[1]', ...


0

In addition to possible drive failures, check the state of your RAID subsystem. We saw something similar and it turned out the battery on the RAID controller failed so there was no write cache available - all writes had to go directly to disk. One side note - we could feel the system pause while RDC'ing into it.


2

It can't use two separate indexes to satisfy that WHERE clause - it will have to pick one or the other. If you want to enable seeks you will need to make a single index with both columns, and order them from most selective to least selective. Think about designing indexes in such a way that SQL Server can eliminate as many rows as possible, as quickly as ...


2

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were ...


2

PRINT @String; will yield that it says PhoneNumber = 123-4567 (which treats it like an expression, 123 subtract 4567, which equals -4444), not PhoneNumber = '123-4567' like in the example you hard-coded. So you should be escaping the values you're appending with two single-quotes: SELECT @String = ' UPDATE ' + @Location + ' SET PhoneNumber ...


0

Also, is the Services section in SQL Server Configuration Manager just a "shell" of the full Services screen, which is limited to SQL Server related services? Yes, SQL SCM is the SQLServerManager10.msc file that is opened as a snap-in Windows Management Console (mmc.exe). Is there any reason not to stop and start SQL Server services from the full ...


1

atThis is pretty much when bluefeet gave you as an example in the comment. DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status]) from [EVAULTTEST].[dbo].EvidenceItems FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set ...


1

Does .bak file also contain backup of ldf file? No it does not contains backup of ldf file. It might contain some information not the complete backup. A full or differential backup contains enough log to be able to recover the database to a transactionally-consistent view of the database at the time the data-reading portion of the backup finished (or ...


0

Figured out the problem. Even though the user I was adding to the Linked Server security section had permissions into the target database, the user I was logged into the SQL Server instance at the moment didn't. Since the error message is incredibly unhelpful (it doesn't say which user failed to log in), I discovered this entirely by accident.


3

Well just while your Tuning Advisor displays a missing index, this won't mean, that your current running query would benefit from this index. The missing index will just be displayed if the current plan would take a benefit from an index. You can also take a look at one of these DMV's, they will display missing indices for the current db. SELECT * FROM ...


3

In my opinion putting the tempdb in the SSD drive without looking at the performance stats is the wrong way to go. SSD drives have high cost/GB and are a premium resource that should be used in the best possible way. You will probably get better performance moving the most used database files to the SSD, given that you already have broken your database into ...


1

I will try to enumerate some options here with advantages and disadvantages as I percieve them: SQL Server Replication - this is the best and most optimized native SQL Server tool for this task. But there are several problems: a. for all your clients, regardless on they are SQL Express databases or not, you will need SQL Server CAL license. ...


3

A full backup will contain all used pages in both files (you can convince yourself of this by restoring a full backup: the new files end up the same sizes with the same amount of content, which means the backup must have contained the information required to achieve that). Both files, especially the transaction log, will contain block that are allocated from ...


3

It's getting upset about your other columns. So try: SELECT [NEW] ,[IN] ,[OUT] ,READY = [READY FOR DISPOSAL] ,TRANSIT = [IN ROUTE TO DISPOSAL] ,[DISPOSED] ,[TYPE] = [ItemType] FROM (SELECT ItemType, Status FROM [EVAULTTEST].[dbo].[Items]) AS ei PIVOT( COUNT([Status]) FOR [Status] IN ([NEW], [IN], [DISPOSED] , [IN ROUTE TO DISPOSAL], [OUT], [READY FOR ...


0

This issue happens because the SQL Server lost the logon id credentials or used an account without rights to start services. For starting your services automatically, SQL Server needs an account with rights to start services during operating system start up. To solve this issue you need to go Administrative Tools > Services > SQL Server(MSSQLSERVER or the ...


2

Yes there is a big difference between both indices. The following index will use the courseID as a seek predicate. This will help SQL Server to find all pages with a defined courseID. In addition, the column userID is included in the pages of the index. This will allow SQL Server to serve those values directly from the index. CREATE NONCLUSTERED INDEX ...


0

This is not a full answer as I haven't ever used SRSS for mapping, however reading the comments on Peter's answer it would appear that you need to create some spatial data to work with. Here's a simple example of how you could put build spatial data for a seating plan without having to use GIS software. Once that is done it should just be a matter of ...


0

Your trigger should look like this to handle UPDATE with multiple affected rows: CREATE TRIGGER AccountChange ON dbo.Customer AFTER UPDATE AS BEGIN UPDATE dbo.Customer SET AccountTypeID = 8 FROM Inserted i INNER JOIN Deleted d ON i.AccountId = d.AccountId -- use the primary key here WHERE AccountNumber = i.AccountNumber AND ...


3

Why do you think clustering will help performance? There is no such thing as active/active anyway - a failover cluster exists to provide a fallback so that the database can continue running if the hardware fails (the database is only ever active on one of the cluster nodes at any one time). Clustering <> load balancing. If you mean splitting up the data ...


2

If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to NVARCHAR(4000) on the way out as that is the most that can be stored by a SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since ...


1

How to rename sa to new_sa in SQL Server 2000? How to do and what is the impact I'm not sure how to rename sa in SQL 2000 or even if you can that far back. SA rename impact in SQL Server 2005, 2008, 2008R2, 2012, 2014, Express Edition I have heard of this as a best practice from some people. To the best of my knowledge there is no effect other than trying ...


0

ANSI_NULL_DEFAULT { ON | OFF } : When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type. When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL. Connection-level settings that are ...


2

The paths in the generated script are the locations where the data and log files should be on your laptop. If you copy paste the script to amazon AWS that will be the location where the files are created when you create the database. If that path doesn't exist on the Amazon instance you will get errors. You could comment out the lines and then the files ...


4

To determine the setting you need to read from the registry. Replace your version/instancename in the query below: EXEC [master].[dbo].[xp_regread] @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<version>.<instancename>\MSSQLServer\SuperSocketNetLib', ...


4

since INSERTED can contain more than one row the update could be performed using a join and not fillin single valued variables: UPDATE C SET AccountTypeID = 8 FROM Customer as C on INSERTED as I on I.AccountNumber = C.AccountNumber WHERE I.TotalSales between 0 and 5000


4

The following is basically your algorithm but implemented as a single statement: WITH ranked_and_counted AS ( SELECT ID, r = ROW_NUMBER() OVER (ORDER BY ID ASC), c = COUNT(*) OVER () FROM dbo.tblLookups ), ranked_and_counted_and_r0 AS ( SELECT *, r0 = MAX(CASE ID WHEN @ID THEN r END) OVER () FROM ranked_and_counted ) ...


4

Quite convoluted but should work and return the 2 rows with values below the @repid, the row with @repid and the 2 rows with values above the @repid (assuming that id is a unique key). If there are less than 2 values below or above, the query will get more from the other side. In total a maximum of 5 rows will be returned. Note: the ORDER BY is needed. ...


0

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script: WITH perms_cte as ( select USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) ...


0

Short version is yes its possible to do what you are describing. Your report will have a spatial query to display the shapes you want. Whether that's a map or a floor plan. You can then add analytical data in a second query to colour the map. The two queries needs to be joined by a key. Im not familiar with qgis but ideally you'd want both the spatial ...


0

I have a hunch you changed system date to workaround with evaluation period expired message and then you forgot to change it back. Then you did edition upgarde, this some how changed registries to incorrect value. As per logs the system has been upgraded with NO FAILURE but problem lies with SSMS. SQl Server support team has written article to overcome ...


0

Turns out Cross Database Chaining was the answer (for us at least). Turning this on allowed me to give the user limited (dataread/datawriter) access to the database that only shows their data, and NOT the underlying database that actually stores everyone's data. I appreciate the suggestions - thanks for weighing in. As an aside - the reason I don't like ...


0

A SID is a mechanism that assigns privileges to the service itself, rather than to the account under which the service runs. For Clustered installations: It enables you to use a domain account with minimal privileges on the box, which also improves your security because people end up knowing the domain service account credentials. For non-clustered ...


1

Finally found the a solution online which lead me to the actual solution of the problem. Apparently it is an issue with SQL Server 2008 and installing the Service Pack is the answer to this. How can we install a service pack while installing the application: See this article Slipstream Service Packs and Cumulative Updates on a SQL Server 2008 ...


3

In SQL Server, a stored procedure's argument can be either a string literal or a variable. It certainly cannot be a string expression, like in your attempt. To resolve your issue, you could declare a variable, assign your expression to it and then pass the variable to the sp_send_dbmail stored procedure: DECLARE @myfilename nvarchar(255); SET @myfilename = ...


3

Declare a variable first - but use format 112 for this. Like this: DECLARE @query_attachment_filename VARCHAR(100) = 'pca-test-' + CONVERT(VARCHAR(10), GETDATE(), 112) + '.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'support', ... @attach_query_result_as_file = 1, @query_attachment_filename = @query_attachment_filename, @query_result_header =0, ...


1

There are a couple of issues here. First, the partition/filegroup mapping shown in your question does not match the DDL. A RANGE RIGHT function boundary is inclusive of the partition on the right so the results should be as below to match the DDL. Consider downloading the partition details custom report from codeplex that can run from an SSMS object ...


0

You have just downloaded the Management Studio, which is nothing else as an IDE. It seems to me, that you haven't downloaded/installed the SQL Server itself. You can download it right here. On the website, just hit the download button and check the SQL Server, which is called something like SQLEXPR_x64... or if you running a 32 bit system SQLEXPR_x86.... ...


0

Well you can try to run a trace (if it's possible for you). But you can also try to just write it to your SQL Server Agent Job History instead. I tried to write a little bit of code which can be added as a first step in front of all other steps. It will simply write a line "Executed from [hostname]". I couldn't test it, due to the fact that I have just one ...


5

Well I might not get it, but I try to answer it. You said you need a high performance solution which runs often (minimum all 2 minutes) and you need a good approach which should be fast without locking. But you don't want a blackbox system. Instead of a blackbox system, which is used on millions of installations with good results, you try to invent the ...



Top 50 recent answers are included