sql-server

SQL Server XSD SimpleType xs:list as table?

SQL server has support for XML, but I cannot figure out how to get it to work with the list type <?xml version="1.0" encoding="utf-16"?> <xsd:schema id="XMLSchema1" targetNamespace="http://tempuri.org/XMLSchema1.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd" xmlns:mstns="http://tempuri.org/XMLS...

Error while connecting to Microsoft SQL Server

I am trying to connect to MS SQL Server 2005 from java, I am getting the following error, can anyone help me ? private static final String DRIVER_CLASS = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; Class.forName(DRIVER_CLASS); DriverManager.getConnection("jdbc:sqlserver://localhost:1433","sa","root"); I added the following 5 jar...

SQL code inside SQL Scalar-value function - want to generalize - optimize

I got this code, I would like to optimize. I basically can add new columns to "Disp" table later on, and I don't want to come back modify this function. I cannot use dynamic SQL. Right? Is there anything else that would work in my case? This is the function: ALTER FUNCTION [GetDate] (@hdrnumber INT, @DateColName VARCHAR(50)) RETURNS D...

query plan caching (sql server)

Is there a measure of time frame as to which the execution plan of inline queries will cached? ...

T-Sql syntax error in Update from Select

Hi, I have a stored procedure in T-Sql with this code at the end of it: UPDATE @Results SET Percentage = CASE B.Total WHEN 0 THEN 0 ELSE (CAST(Number AS FLOAT)/B.Total) END FROM @TotalAnswersPerQuestion AS B WHERE @Results.QuestionNumber=B.QuestionNumber The temp table @Results is defined and correctly used in this store procedure jus...

SQL Server high CPU and I/O activity database tuning

Our application tends to be running very slow recently. On debugging and tracing found out that the process is showing high cpu cycles and SQL Server shows high I/O activity. Can you please guide as to how it can be optimised? The application is now about an year old and the database file sizes are not very big or anything. The database...

When designing databases, what is the preferred way to store multiple true / false values?

As stated in the title, when designing databases, what is the preferred way to handle tables that have multiple columns that are just storing true / false values as just a single either or value (e.g. "Y/N: or "0/1")? Likewise, are there some issues that might arise between different databases (e.g. Oracle and SQL Server) that might affe...

Returning NULLs in SQL if joined table is missing records for that Date

Good Morning All. I've been struggling with this issue for a while now, and I can't seem to wrap my head around it. So I have two tables in my Database tblDateTrans CREATE TABLE [dbo].[tblDateTrans]( [Date] [smalldatetime] NOT NULL, ) This table is an external calendar table that contains all the dates from 1/1/2007 - 1/1/201...

Connect to an existing transaction on SQL Server using C#

Hello, Maybe this question is a little far-fetched but i'll give it a shot.. Can i connect to an ongoing transaction in some way? The problem is that i have [C++ Program A] that does various operations on a database and then calls [C# Program B] that does more operation on the same database. I want all those operations from both programs...

SQL Server Linked Server to Microsoft Access

Hello all, I have tried to make use of linked servers in SQL Server 2008 by doing the following to access a Microsoft Access 2003 Table. EXEC sp_addlinkedserver access1t, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb' EXEC sp_addlinkedsrvlogin access1t, FALSE, Null, Admin, Null GO CREATE VIEW TI001APCE1265 AS SEL...

ORACLE Connect by clause equivalent in SQL Server

Is there a equivalent clause to CONNECT BY of Oracle in SQL Server. The requirement to build a category tree using a parentId field. ...

How to connect to database after failover?

I set up a database mirroring and then used this connectionstring to connect to it: Data Source={0};Failover Partner={1};Initial Catalog=AdventureWorks; Integrated Security=True; After adding some data into database, I shutdown the principal server, so the mirror server becomes the principal server. I open the connection again...

Using default values in an INSTEAD OF INSERT trigger

We are performing a database migration to SQL Server, and to support a legacy app we have defined views on the SQL Server table which present data as the legacy app expects. However, we're now having trouble with INSTEAD OF INSERT triggers defined on those views, when the fields may have default values. I'll try to give an example. A ...

Multiple Datasources in DataGrid (ASP.NET)

Hi, I wonder if we can implement and fetch data from different datasources into a DataGrid. Let's say for example i have a 3 stored procedures: What I did is drag 3 datasources and configured it in each stored procedures. 1st stored procedures : returns @id and @name 2nd stored procedures : returns @name, @server and @location 3rd stor...

using table in list data region for repeat data in rdlc

I use 2 table in a list data region for repeating data. I group data in list data region. My dataset that used in table have 3 data table. I can't use of all data tables as data provider for report. When I open Expression window and click on Datasets, all datasets shown to me but all of their fields use of an aggregate function like sum....

BCP Command gives different output in SQL Server 2005 compared to 2008?

Hello all, I have executed two identical bcp commands on two different setups with the same data. Machine A = Windows Vista machine which is running SQL Server 2008 Machine B = Windows Server 2003 machine running SQL Server 2005 The output text file of the bcp command is different! For a start, Machine B does not add column names to ...

SQL Management Studio won't recognize a table exists after scripted create

So if I create a new table in the query editor in SQL Management Studio after hitting refresh on the DB I can see and work with that table. However if I want to run another query referencign that table from withen the query editor it doesn't reconize that table exists. I've tried hitting refresh at the DB level, and the table level but i...

Slow query in SQL Server 2008 using linked server. What can I look at?

This query originally came from a VB6 program accessing MS Access tables which are linked to external databases through ODBC. It takes about 3:30 to run. Now I've setup a SQL Server 2008 Express box to evaluate how we can migrate to a better database system. So I setup a linked server to the external server (we call it DWPROD) and when ...

Getting messages 1-20, 21-40,... from a database

I'm trying to build a mailbox where we can group the messages in x. If you put x to 20 you'll see messages 1-20 on the first page, opening the second page will show message 21-40 etc. How do I efficiently query this? The best I could come up with is this: select top 20 * from tbl_messages where tnr_id not in ( select top 40 tnr_i...

SQL Server 2008 Filestream Win32 error without a network cable!

I have a SQL Server 2008 database utilizing Filestreaming and all works fine and dandy apart from under one very strange circumstance. If i have my database on, say a laptop, on a locally installed version of SQL Server 2008 and am connected to the network all works fine. If i unplug the network cable, after a while the SqlFileStream c...