sql-server

What should be the best way to store a percent value in SQL-Server?

Hello! I want to store a value that represents a percent in SQL server, what data type should be the prefered one? ...

Inexpensive tools for SQL Server ERDs and forward-engineering?

In the past, I've used Visio (2003 Professional, if it matters) to doodle database diagrams while we're in the conceptual design phase. This is pretty painful, though, because there's no good way to get the data out of Visio (though this answer gives me hope of a temporary analgesic). Since the designs are generally pretty small, that pa...

How to detemine content type of binary data in the image field of SQL Server 2008?

I need to determine file type (i.e., MimeType) of stored data in the SQL Server 2008. Is there anyway, if possible using SQL query, to identify the content type or MimeType of the binary data stored in the image column? ...

Trapping Exception using TSQLQuery & params

I am getting a "SQL Server Error: arithmetic exception, numeric overflow, or string truncation" error here is the code below AQuery:= TSQLQuery.Create(nil); with AQuery do begin SQLConnection:- AConnection; SQL.Text:= 'Insert into.....'; ParamByName('...').asString:= 'PCT'; . . . try ExecSQL; finally AQuery.Free; end; end;...

How to "Open" XML data in Oracle

Hello all, Here is an example of some TSQL that I would like to rewrite in PL/SQL. DECLARE @xml XML SET @xml = '<theRange> <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow> <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow> <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRo...

SQL Server XML Declare Namespace

Hi All, I would like to have a few namespaces available by default when running an XQuery in SQL Server 2005, is this possible? For example, I would like to do the following: select * from TableWithXML where FieldWithXML.exist( declare namespace nsp="http://www.example.com/exampleNamespace"; nsp:root/nsp:childnode) = 1 Without having...

SQL Comma Separated List from Multiple Columns

Hi, I have data that looks like this: CUSTOMER_ID OPERDAYSJUL OPERDAYSAUG OPERDAYSSEP ... OPERDAYSJUN 1 30 15 2 2 5 1 0 3 6 0 12 4 12 5 23 For each customer_id, I want a comma-delimited list indicating w...

Linked server setup between SQL Server Express and SQL Server

Can you please explain how to setup a linked server between a SQL Server (A) and a SQL Server Express (B) scenario. Server A is SQL Server 2000, whereas Server B is SQL Server 2005 Express. I have set these up in the past, but none where connected to a SQL Server Express version. Thanks, Billy ...

Error Accessing SQL Server from an ASMX Web Service

I am accessing my server through remote desktop connection and have configured a webservice in IIS. I am able to see the methods but when I click on the button to "Invoke" I get the following error: System.Data.SqlClient.SqlException: Login failed for user 'SOLDev\Server02$'. at ShareWare.Web.Service.WebAPI.Reservation.GetInfo() Why...

SQL Server : How to treat nulls as zeros

Here is an example of what I'm trying to do: Select S1.Name as S1Name, S1.Itemcontent as S1ItemContent, ... S2.Name as S2Name, S2.Name as S2ItemContent, **(S1.OrderValue * 1000*1000 + S2.OrderValue*1000 + S3.OrderValue) as OrderValue** From (joining s1, s2 and s3 on a particular value) Order by OrderValue When S1.OrderValue, S2.Order...

SQL-Server Performance: What is faster, a stored procedure or a view?

Please excuse me if this has been asked before (I did not find the question if it has) But what is faster in SQL Server 2005/2008, a Stored Procedure or a View ? EDIT: As many of you pointed out, I am being too vague. Let me attempt to be a little more specific. I wanted to know the performance difference for a particular query in a V...

tsql- update a table using join

I want to update a column in a table making a join on other table e.g.: begin tran update table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO = '110' But it is complaining : Msg 170, Level 15, State 1, Line 2 Line 2: ...

SQL Server out of memory

I got this error running a query that goes against 2 tables with combined 50k rows. An error occurred while executing batch. Error message is: Insufficient memory to continue the execution of the program. How do I get around this? Edit I get this when I run DBCC MEMORYSTATUS Msg 2571, Level 14, State 1, Line 1 User 'XXXX\YYYY' doe...

Exclude weekends and custom days (i.e. Holidays) from date calculations

Currently, I am calculating a finish date based on the start date (DateTime) and duration (# of days), but my calculations do not take into account weekends or holidays. So, my solution is not correct. This was just a starting point. I read some articles out there and one approach is to create a gigantic calendar table that has all the...

SQL LIKE Performance with only the wildcard (%) as a value

I am wondering what the performance of a query would be like using the LIKE keyword and the wildcard as the value compared to having no where clause at all. Consider a where clause such as "WHERE a LIKE '%'". This will match all possible values of the column 'a'. How does this compare to not having the where clause at all. The reason I...

how to use group by with union in t-sql

hi all, how can i using group by with union in t-sql? i want to group by the first column of a result of union, i wrote the following sql but it doesn't work. i just don't know how to reference the specified column (in this case is 1) of the union result. great thanks. SELECT * FROM ( SELECT a.id , a.time ...

Dealing with XML datatype(SQL SERVER 2005)

Hi, I have a table having 2 columns EmployeeId (int) and EmployeeDetails(XMl type) EmployeeId EmployeeDetails 1 <Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee> 2 <Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age>...

HD Crash SQL server -> DBCC - consistency errors in table 'sysindexes'

Hello A client of mine has had an HD crash an a SQL DB got corrupt : They did not make backups so they have a big problem. When I tried (an ultimate measure) to DBCC-repair I got the following message. Can anybody help me with this ? Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:872) with latch type SH. sy...

Aggregate function in comparison of 2 rows in the same table (SQL)

Given the table definition: create table mytable ( id integer, mydate datetime, myvalue integer ) I want to get the following answer by a single SQL query: id date_actual value_actual date_previous value_previous where: date_previous is the maximum of all the dates preceeding date_actual for each id and values corresp...

Sql Server 2005 Data Types

Hi, What is the diff between real, float, decimal and money. And most important, when would I use them. Like I understand - real and float are approx. types, meaning they dont store the exact value. Why would you ever want this? Thanks ...