How can I get a default value of 0 if a sum does not return any rows? Edit: I have edited this post to add a more thorough example (which the previous one didn't encounter) E.g. DECLARE @Item TABLE ( Id int, Price decimal, PricePer decimal ) DECLARE @OrderItem TABLE ( Id int, ItemId int, ChargedPrice nvarchar...
I have a client-server application where a .NET client accesses all the data and stored procedures in a SQL Server 2008 database. Is there any way to protect all this data so that only the users I create and authorize can access this specific database? Especially the user 'sa' comes to mind. I don't like him to access all my data. ...
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization stuff and at this point don't know how t...
Help getting crushed by this i did DBCC CHECKDB and one of the DB gave Msg 823, Level 24, State 2, Line 2 I/O error 21(The device is not ready.) detected during read at offset 0x0000000001c000 in file 'E:\MSSQL2000\Data\FTSWE_Data.MDF'. I was working on a remote server, from my, local host accessing a remote DB. Also i was on VPN. ...
Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a ...
select left(emailaddress, len(emailaddress) - charindex('@', emailaddress)) I am getting the result below: foo@ma Please correct the select statement below. I need to do this with tones of email addresses and extract the username ONLY. ...
I need to perform a database restore from my application. Before doing this, I want to kill all processes as follows: private void KillAllProcessesOnSMARTDatabases(Server targetServer) { targetServer.KillAllProcesses(SMART_DB); targetServer.KillAllProcesses(SMART_HISTORY_DB); targetServer.KillAllProcesses...
I know how to get in SQL (SQL Server) the current date, but with the beginning of the day: select dateadd(DAY, datediff(day, 0, getdate()),0) (result:2009-09-17 00:00:00.000) I need to get (in SQL) the current date with the beginning of this hour. For example: 2009-09-17 17:00:00 (I don't care about the exact format) and I need to g...
And How is it different from just making an SQL Server accessible from the internet? tx & regards Lothar ...
Sorry for the long question but this contains all the SQL I've used to test the scenario to hopefully make it clear as to what I'm doing. I'm build up some dynamic SQL to produce a PIVOT table in SQL Server 2005. Below is code to do this. With various selects showing the raw data the values using GROUP BY and the values in a PIVOT as I...
How can I find out if an instance of SQL Server 2005 allows case sensitive databases or not? By case sensitive, I mean case sensitivity of the objects in the database, i.e. the following two statements are not equivalent: SELECT * FROM TABLE SELECT * FROM table I've looked in the property pages of the server (in Management Studio) bu...
I am creating a simple blog app and so far I have 3 tables, Posts, Comments, and Authors. I will just list the primary key for each table as well as a foreign key. Posts will contain postid as a primary key. Comments will contain commentid as a primary key and postid as a foreign key. Posts has a 0 to many relationship with comments....
I have a SQL database that I am currently converting from an Access database. One of the features of the Access database is to 'Copy DB' and is used when working in a 'dev' site - it copies all of the production data by physically copying the production file into the dev site. In this way, all production data and structure and queries ...
Lately, I have been doing some reading on indexes of all types and the main advice is to put the clustered index on the primary key of the table, but what if the primary key actually is not used in a query (via a select or join) and is just put for purely relational purposes, so in this case it is not queried against. Example, say I hav...
I have a query that joins 3 tables in SQL Server 2005, but has no Where clause, so I am indexing the fields found in the join statement. If my index is set to Col1,col2,col3 And my join is Tbl1 inner join tbl2 On Tbl1.col3=tbl2.col3 Tbl1.col2=Tbl2.col2 Tbl1.col1=Tbl2.col1 Does the order of the join statement make a difference as com...
I'm having some performance issues since a DB was moved from SQL2000 to SQL2008. We need to retrieve from a SQL View for a mail merge and for an application called FORMIC. It was fine under SQL 2000, but is now very, very slow. One thing I've noticed is that SQL Profiler shows that Excel does a simple "SELECT * FROM VIEW" where Word se...
Greetings. I have a SSRS 2005 report that shows prices of things. For some customers, I hide a column from the table (with an expression on the Visibility - hidden property). When I do this, my table shrinks. I've searched long and hard for a way to dynamically resize this table (or to do something at design time to make it stay the ...
I have a VB6 app that access's a database thru a ODBC Connection. It will run fine for a few hours then I get the following Error. Any Ideas? [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite(WrapperWrite()) ...
Is it money, float, real, decimal, _____ ? ...
I need to update the GroupID field to a 0. I have figured out how to retrieve the value, I am now running into problems updating it. Any help would ge great! <ProblemProfile> <GroupID>-1</GroupID> <LayoutID>1</LayoutID> <MyQueries>false</MyQueries> </ProblemProfile> Declare @Result xml set @Result = convert(xml,(select Profil...