sql-server

Query to pull sum from 2 different tables, is this possible in one query?

Thanks for all the help so far, now I just ran into a major problem! I have 2 tables that contains daily commissions, and for each day, I have to SUM the daily totals from each table. Daily limit is maxed at $100, so anything over $100 for the day is $100. Employee (employeeID INT PK, totalCommisions INT ) Now the 2 tables that conta...

Downsides to "WITH SCHEMABINDING" in SQL Server?

I have a database with hundreds of awkwardly named tables in it (CG001T, GH066L, etc), and I have views on every one with its "friendly" name (the view "CUSTOMERS" is "SELECT * FROM GG120T", for example). I want to add "WITH SCHEMABINDING" to my views so that I can have some of the advantages associated with it, like being able to index ...

SQL Server - two statements on one line??

How to I pass this to SQL Server, it seems to want the "Go 4" on a second line? Insert Into tbl Values (896,0) GO 6 ...

Generate missing dates + Sql Server (SET BASED)

I have the following id eventid startdate enddate 1 1 2009-01-03 2009-01-05 1 2 2009-01-05 2009-01-09 1 3 2009-01-12 2009-01-15 How to generate the missing dates pertaining to every eventid? Edit: The missing gaps are to be find out based on the eventid's. e.g. for eventid 1 the output should be 1/3/2009,1/4/2009,1/5/2...

database partiton Function (sql server 2008)

HI, I want to write partiton in sql server 2008 for my fleet management system. I want to write a partition function such that --values (vehicle number) like for example mh-30-q-126,mh-30-a-126,mh-12-fc-126 should be moved to respective partiton, depending upon middle values like ,q,a,fc respectively My trial function:- CREATE PA...

How do I use Win32::SqlServer to connect remotely?

I have been unable to make a successful connection to my SQL Server (2005) using Win32::SqlServer. The server is located on another machine. I am using the latest version of ActivePerl and the module. Using an ADO.NET provider (in C#), I can make a successful connection with the connection string: Data Source=1.2.3.4,1553;Initial Cata...

SQL Server 2005 Data Types and VB.NET

I would like to know how can I use tinyint in SQL Server 2005 with VB.NET. Please let me know if someone know all data types in SQL Server 2005 are matching to which VB.NET Data types. I face that problem now. For example: tinyint in VB.NET, money in VB.NET, smalldatetime in VB.NET. Thanks you all in advance! ...

In Sql Server 2005, how do I setup permissions using schemas and db roles?

Here is my current setup: Database Role - MyDbRole Schema - MySchema User - MyUser MySchema is owned by MyDbRole. MyUser is mapped to my database that contains MyDbRole and MySchema. It is mapped using the MyDbRole database role. I thought that MyUser would now have access to any object within the MySchema schema, because it is owned ...

How to insert sub root node in xml file

Hi guys hope all are doing good. I want to create one sub root node in my xml file like, <CapitalJobsList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt; <JobAds> -- element to create <JobAd> <AdvertiserDetails> <AdvertiserId>718508549</AdvertiserId> <AdvertiserName>ABC</AdvertiserName> </AdvertiserDetail...

Problem with update statement

I am trying to update all records in a column so that they start with 'CD' e.g. DCE206 would become CDE206. UPDATE table SET column = REPLACE(column1, 'DC', 'CD') WHERE column1 LIKE 'DC%' I am using the above update statement however the following error appears 'Subquery returned more than 1 value. This is not permit...

Upgrading from Access 2003 to Access 2007 and SQL Server

I am maintaining a pretty large Access 2003 project with a MDB data file on the server and linked tables. The plan is to upgrade to Access 2007 (or even 2010) and SQL Server as the data storage. As this is a two-step plan, which of these steps is best done first? Does it make a difference if I first upgrade to 2007, or should I link to ...

Does XML AUTO support what I am trying to do?

Hi At present we have a denormalised database. ie Name|NameID|EmployeeID|EmployeeType I'm normalising the database to resolve the EmployeeID from the Employee table rather than the Name Table. So we have a select at the moment SELECT Name, NameID, EmployeeID, EmployeeType FROM Name FOR XML AUTO Which will output: <Name Name...

Select Distinct Sql Server Question

I have the following table structure. A | B | C I wish to pull column A and B where all the results of A and B are distinct. I wish to ignor column C. My unerstading of the distinct keyword that it looks at the whole row not jst the columns you return. Any ideas how I could do this? ...

What is an elegant way to make two fields auto increment?

In SQL Server, I have two fields, one is primary and has an identity (AN_ID) then I have another one that for legacy reasons (don't ask) has to be updated reflecting the AN_ID. Currently they have the exact same value. I was wondering if there are pure T-SQL query (no stored procedures) to make it auto incremente. Thank you very much E...

Consolidated: SQL Pass comma separated values in SP for filtering

I'm here to share a consolidated analysis for the following scenario: I've an 'Item' table and I've a search SP for it. I want to be able to search for multiple ItemCodes like: - Table structure : Item(Id INT, ItemCode nvarchar(20)) - Filter query format: SELECT * FROM Item WHERE ItemCode IN ('xx','yy','zz') I want to do this dynamic...

What is wrong in sql2k8 cluster ?? SMO is failing to get Network instances in cluster Machine...??

Hi I have created SQL Server 2008 cluster(TestMachine1-->Name of Computer).and i have written a c# program to retrieve all the n/w instances in the lan. When i run the exe from TestMachine1(where SQLCluster is installed ) i am not getting the instances.when i Debug i am getting Null to the data table rows. the same exe when i run from...

How do you set permissions on a schema that has objects accessing other schemas?

I have 2 schemas and one of the objects in the first schema needs to access an object in the other schema. For example: CREATE VIEW I.ITest AS SELECT 1 as TestColumn GO CREATE VIEW O.OTest AS SELECT * FROM I.ITest GO EXEC ('SELECT * FROM O.OTest') AS USER = 'TestUser' DROP VIEW O.OTest DROP VIEW I.ITest In the above example...

SQL Server Extended Property MS_SubdatasheetName

Our application uses a SQL Server back-end with many stored procedures. Recently, while trying to fix an unrelated problem, we scripted a stored procedure to an ALTER statement to a SQL window to try something. Renaming our proc for testing purposes, we ran the script, and got this perplexing error: Msg 15233, Level 16, State 1, Proce...

How to get the attribute value with XQuery in MS SQL Server 2008

I have an XML like this: <EXP> <TITLES> <SUBTITLE CL="AXT4" FL="1" NB="Text 1"/> </TITLES> <TITLES> <SUBTITLE CL="BVT6" FL="2" NB="Text 2"/> </TITLES> <TITLES> <SUBTITLE CL="PLO7" FL="3" NB="Text 3"/> </TITLES> </EXP> Using XQuery in SQL Server 2008, How can I select Just the value of the attribute NB in a list...

SQL question : How do I pivot mutiple results to single row?

I have table in data base name "train delay, with columns train number(int), DelayTime(int), DelayReason(nchar) so the train may have more than one delaytime for each delay reason, for example: trainnumber,Delaytime,DelayReason 1 ,5 sec ,x 1 ,10 sec ,Z 1 ,70 sec ,TY I want to create a crystal rep...