Programming SQL

Syntax question in stored procedure
Hi I've the following simple stored procedure to find all members in a table where the status is in a list of values: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Test] ( @Status varchar(30) = null ) AS BEGIN SET NOCOUNT ON DECLARE @SQL varchar(600) SET @SQL = 'SE... 12 Oct 2009 18:37

EXECUTE permission denied on object 'sp_send_dbmail', database
Try adding the DatabaseMailUserRole role to your web user (not the user you're logging into ssms with). "Daniel" wrote: No.. the problem i cant figure out is why the same exact code i posted below i have in 2 other procedures within the same database and the others work fine.. but this one is h... 10 Oct 2009 01:55

NOLOCK being blocked
Okay, this is weird. (SS 2005 SP2 x64) I've got a simple SELECT statement with a NOLOCK spid (139) being blocked by a replication spid (102). The replication spid has an IX HBT (Heap/BTree) lock is all. I thought if you did NOLOCK you couldn't get blocked unless someone was actually doing DDL/Schema changes? ... 8 Oct 2009 19:06

Display of DATE format in Management Studio
Hi all, When I execute a SQL query to display values from a table, the date format is being displayed as "2009-06-07 00:00:00". But I would like it to display as "6/7/2009 12:00:00 AM". Is there any setting in the Management Studio I need to set? My laptop's Language is set to "English (United States)" in the R... 7 Oct 2009 19:49

Converting varbinary to float array
In SQL Server 2005, I have a varbinary(4000) column which contains a 60 element float array. Using SQL (or similar) how can I extract the contents of the varbinary and print out the data as 60 floats? Each float has a precision of 9 decimal places. Many thanks, cathy ... 7 Oct 2009 18:42

SQL Globalization / Localization
Paul Gibson (sibleypark(a)hotmail.com) writes: Fair enough on the dates but I was more referring to the formatting of the SQL. In the case were I was updating text into an NVARCHAR column type I would be faced with the same challenge. Is there a global setting that I can set in SQL to not try to convert str... 1 Oct 2009 18:30

Running Total in a View, SP or Table Valued Function
Is it possible to create a column in a view, sp, or table function, that represents a running total? e.g. I have an order number, order revision number and quantity ordered. I may have a dozen revisions to an order, and would like to have a running total of the quantity at each order revision number. ... 28 Sep 2009 11:26

Fix by Cloning table struction
We had a database on an SQL 2005 box. We lost access to that server so we needed to downgrade it to our SQL 2000. We used the MS Database Publishing Wizard to extract the database and bring it into SQL 2000. It worked well except the Default Values for the fields did not come over. Our problem is most of the... 15 Sep 2009 16:56

Calculate Differences in Closing Balances
Hi We record the closing Balance of Stock On Hand on a daily basis, I need to calculate the differences between the values so that I can see how much stock is being adjusted in a daily basis, the key to the File is Date, Branch, Product, Location. I have the following data Date Branch Prod... 12 Sep 2009 18:29

How to check that a date field is null and replace by blank value
Hello there, I'm trying to query a date field (smalldatetime) with the following query: select recID, date1, ISNULL(date1,'') from table1. It returns: recid date1 ------ ------ ------------------------ 1 NULL 1900-01-01 00:00:00 Instead of a blank value, it returns '1900-01-01 00:00:00' ... 7 Sep 2009 10:00