sql-server

SQl Server - Hierarchical Data

I use SQL Server 2000. Suppose I have two tables like the following: Area ---------------------------------- ID| Name | HierarchyLevel ---------------------------------- 1 | World | 1 2 | America| 2 3 | Europe | 2 4 | Africa | 2 5 | USA | 3 and AreaHierarchy ------------------------ ID | ParentID | ChildID...

How can I convert my float field into a Decimal in sql server?

I want to convert my float field into a decimal field; I want a precision of 11,2 in my decimal field, but when I tried to change the type of my field(example: Amount) I get an error: "Arithmetic overflow error converting float to data type numeric. The statement has been terminated." My field is decimal(11,2) at the table, and my max a...

Disabling SQL Server 2008 Express

I need to write a script that disables SQL Server Express 2008 from running. This script will be ran on about 500 machines. In my script, I'm setting the start up type for all SQL Windows Services to disabled, and then restart the machine. In my mind, I have now 100% disabled SQL Server Express from running. I don't care about the orpha...

What does "start of the transaction" mean?

According to BOL, "SNAPSHOT Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction" However, that is not exactly what I am observing. Consider this: In the first tab, run this: CREATE TABLE dbo.TestTable(i INT); GO ...

Grouping values for INSERTion into table

I'd like to be able to insert some values into a table using a single INSERT statement, but I'm having trouble determining the best way. The data I'm inserting is bootstrap (or hard-coded) data, ie. not available in another table. Here's what I've tried so far. create table #t (id int) insert into #t values (1) --- (a) insert into #t ...

Is it possible to use Excel 2010 like sparklines in SSRS

I want to know if it possible to repoduce the idea of Excel 2010 sparklines in a SSRS 2005 Report. I want to show a report that has an indication of the price fluctuations over a 3 month period for a range of products. I could just give the figures over the 3 month period but it is very hard to quickly distinguish what is happening to ...

Cannot save integer if it is empty.

Hi , i'm developing a form to insert data in the Database (SQlServer 2005) i use Linq to Sql in WPF; I have this database : Column Name Data Type Allow Null CODE ID Int(autoIncr) No BoatName Nvarchar(100) No BoatType Nvarchar(50) yes Numbe...

Why does Entity Framework not return a trimmed string for a nvarchar(MAX) field?

I'm currently working on a Silverlight application connected to a SQL Server 2008 Express database that is largely based on a demo on Brad Abrams' blog. (NOTE: The demo file at the specified link is actually a different solution that the one he shows on the live running application. The demo in the blog post uses Entity Framework, whil...

Why are parameters slower than literal values in a where clause?

Situation: c#, sql 2000 I have a table, lets call it 'mytable' with 30 million rows. The primary key is made up of fields A and B: A char(16) B smallint(2) When i do a search like this, it runs really slowly (eg it does a full tablescan) string a="a"; int b=1; string sql = "select * from table(nolock) where a=@a and b=@b"; using (Sq...

case statement in where clause.

I have a stored procedure with the following code. alter PROCEDURE [dbo].[usp_Product_GetProductByCategoryID] @CategoryID uniqueidentifier, @IsCategory bit=0 AS BEGIN SELECT ProductId, ProductCode, ProductName, MRP, MaxDiscount, ShortDescription, ThumbNailImagePath,ThumbNailImagePath1, ...

Subscriptions etc with SSRS and SQL Server 2005 Work Group edition

Does anyone have any suggestions about how to schedule the creation of reports and how to allow users to view them in a SQL Server 2005 Work Group environment where scheduling and subscriptions do not appear to be supported. SQL Server is installed on a separate machine to that running IIS to support multiple production websites. ...

SQL Comparing 2 tables with a link table

Hi there, I'm going to get some real-world data the best I can for this query I'm having a hard time with. Table HierarchySet: HierarchySetId ClientId 22 1866 23 1866 Table User: UserId UserName ClientId 76 TestUser 1866 Table LinkTable: LinkId UserId OrganisationId Hierar...

how to set 9000 characters in nvarchar(max)

Hi All, I need to set 9000 characters in nvarchar variable declare @inXMLRequest xml declare @insertsql nvarchar(max) set @insertsql='--------9000 characters--------' EXEC sp_executesql @insertsql, N'@inXMLRequest XML OUTPUT', @inXMLRequest OUTPUT print @insertsql But NVARCHAR is taking 5...

Application Role in Program connecting the SQL Server

Hi All I have a program which would use the Application Role to write data to a SQL Server 2005. using (SqlConnection sqlCon = new SqlConnection(connectionString)) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlCon; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = ...

SQL Server Message Broker - External Activation

I have a Sql Server inside a restricted network. I need to somehow get data from the outside in. I would like to harness the use of Message Broker. My thinking is the external db places a message on a queue then I require a service that sits inside of the restricted LAN to listen (poll?) for these messages and then act upon them. I can...

Can this SQL Statement be made to perform better?

I have a SQL statement for merging edits from one table to another. I.e. UPDATE f SET f.AUDAPLCDE = m.AUDAPLCDE, f.AUDSTF_NO = m.AUDSTF_NO, f.AUDUPD_ID = m.AUDUPD_ID, f.AUDUPDDTE = m.AUDUPDDTE, f.UNTTYP = m.UNTTYP, f.UNTSTM_NO = m.UNTSTM_NO, f.UNTIND = f.UNTIND, f.UNQ = m.UNQ, f.TRNCTL_NO = m.TRNCTL_NO, f.TRN_NO = m.TRN_NO, f...

Conversion of INTEGER to DATETIME differs to VB6

I'm looking at some legacy VB6 code (years+years old, before my time) which runs a query against an SQL 2005 db. It supplies a date restriction in the WHERE clause - where the date is given as an integer value as a result of a CLng() on the Date in VB6. e.g. ... WHERE SomeDateField >= 40064 40064 is what VB6 converts today's date to ...

How to run multiple queries in one instance in SQL Server 2005

I want to run a SELECT statement and I want execute a DELETE statement for the same row and the read result be respond by the SQL Server. ...

How can I access a view command through T-SQL?

I'd like to be able to retrieve programmatically the command strings that generate the views on our SQL Server. I though the ADOX collection, used together with an ADODB connection, will allow us to access this through the catalog/view/command property. Unfortunately, the 'views' collection is not available when connecting from an MS-A...

SQL massive performance difference using SELECT TOP x even when x is much higher than selected rows

Hi All, I'm selecting some rows from a table valued function but have found an inexplicable massive performance difference by putting SELECT TOP in the query. SELECT col1, col2, col3 etc FROM dbo.some_table_function WHERE col1 = @parameter is taking upwards of 5 or 6 mins to complete. However SELECT TOP 6000 col1, col2, col3...