Hi, I have 2 tables they both join on and "ID" field. 1 Table has a field "docketNo" Varchar(50). the Foramt for the data is as follow. YY-12345 AND YY-12345 AAA. I need help in selecting the docketNo but I am looking for the difference in data Format. Example let say I have these f...more >>

Hi, I have the following tables. DirTab ID ParentID Directory 1 c:\ 2 1 SubDir1 3 2 SubDir2 FileTab ID File 1 Datei.txt DirFileTab DirID FileID 3 1 Now, I would like a query with output like this: Direct...more >>

Hi, I am not sure if the 'subject' reflects the problem correctly. But I will try to explain it here: I have one view DB1.VIEW1 in Database DB1. The query in VIEW1 is a multiple join of different tables and views in DB1 and other databases. On executing VIEW1, I get the output result as: ...more >>

I have these tables: CREATE TABLE Properties (PropertID int NOT NULL, Property nvarchar(50)) CREATE TABLE OilProperties (PropertyID int NOT NULL, OilID int NOT NULL) CREATE TABLE Oils (OilID NOT NULL, OilName nvarchar(50)) Trying to select the max OilProperty.PropertID for each oils.o...more >>

I have a requirement to ship a backup of our database to a customer at regular intervals. Unfortunately our stored procedures are proprietary and can't go with the backup. Is there a product out there that can back up a database without the stored procedures? I can't encrypt all the store...more >>

Hi All, Is there a way to assign a value to a text type variable using select? Create proc test @text text output as select @text = Name_Text from Emp Here Name_Text is of datatype text I get the following error: The assignment operator operation cannot take a text data type as an ...more >>

I have an SQL-Server table that contains "date, name, and account number" records like: 22-Jan-2005 Bill BA39833J 22-Jan-2005 Bill RJ3399K 22-Jan-2005 Bill KL9833LL 22-Jan-2005 Bill BA39833J 23-Jan-2005 Bill HP54599K 23-Jan-2005 Bill AA9...more >>

I have the following four columns: 1 3 ok 1 1 2 ok 1 1 1 server error 3 2 3 ok 1 2 2 ok 1 2 1 ok 1 9 3 ok 1 9 2 ok 1 9 1 not found 3 13 3 ok 1 13 2 ok 1 13 1 ok 1 Their data types are int, int, string, int. Call the columns groupid, ordinalid, status, quantstatus. I want the a...more >>

Hello! I was wondering if there is a tool on the market that seamlessly integrates SQL Server and VSS. I would like to be able to check-out/check in stored procedures so that only one person can edit it etc. Does Microsoft plan to have SQL server integrated with VSS in the next version? ...more >>

Hello All, I am trying to create UDF that will take in tablename and columnname, maxlength as parameters. Based on the tablename and columnname, I want to return the length of the longest columndata. If the length value is bigger than the maxlength parameter, I pass in , I just want to re...more >>

Problem: I want to create a menu tree by looking up the table heirarchy from a single table. For instance I have one row that points to a previous to identify that it belongs in that "folder". Think of it as a file system type approach. Ideally I want to represent this as an XML file as I alre...more >>

Hello I'm trying to get a containstable to work with rank, rank and such works great and I pass the keywords as a parameter and replace all spaces with AND between them. problem is when I pass words such as 'is' 'and' 'or' 'for' 'a' and so on, how can I solve this issue? I have no idea how ...more >>

Hi all, New to SQL, but getting into it, converting Access queries to SQL. I have a query in access which includes the following SELECT .... Case when A.Z is not null then C1.Y else C2.Y END AS MNO .... FROM (A INNER JOIN (B LEFT JOIN C C1 ON B.Z = C1.PK) ON A.PK = B.PK) L...more >>

What is the best way to calculate time elapssed between two dates and factor in weekdays only (exclude Saturday and Sunday). Example: Friday Jan 28th at 10pm thru Monday Jan 31st at 2am should calculate to 4 hours. Friday Jan 21st at 10pm thru Monday Jan 31st at 2am should calculate to 12...more >>

Hello, In one of my stored procedures, I'm running xp_cmdshell, and I'm = interested in the return value of the program that is ran. It is my = understanding from Books Online that xp_cmdshell will either return a 1 = or 0 depending on the exit code of the application ran. This isn't the = ca...more >>

Hello PPl, I have 2 tables table A -------- CustomerID (PK) 1200 2400 3444 Table B -------- CustomerId (FK) itemID Date status 1200 30200 20/11/04 checked out 1200 30200 30/12/04 Due Date 1200 30200 1/01/05 Billsent 1200 30201 20/09/04 checked out 1200 30201 30/10/04 Due Date 1...more >>

Anyone have a nifty SQL function to left pad? I need to left pad numbers comming from a system with leading 0's. I get: 123 12345 1432 15234532 987987 I want: 00000123 00012345 00001432 15234532 00987987...more >>

Hi when I come to lock/objects branch in enterprise manager I find out so many tables and views name there ,does it mean that all these are locked? or does it mean all these are using by some users,but we can use it too? any help would be thankful ...more >>

Hi, What is OLAP Service ? What is the Advantage of OLAP ? How can i use it in my VB/SQL Server Application ? Where can i get details Idea about OLAP Service ? Thanks ,\ ...more >>

/*Copy and paste all of this into QA. See the bottom for desired output, and my current query.*/ IF OBJECT_ID('tempdb..#TestingInvoices') IS NOT NULL DROP TABLE #TestingInvoices CREATE TABLE #TestingInvoices ( InvoiceID int, PackingSlipID int, InvoiceDate datetime, IsVoided tinyint)...more >>

I am trying to run a query and put the results into an excel spreadsheet automatically using bcp command. bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword The command works beautifully except that it overwrites the header column of the excel file as well. Is th...more >>

I'm trying to update a table that has decimal values. They are defined at precision of 15 and scale of 2. When I use a T-Sql update query, I'm sending a value "with pennies", but the table is only reflecting the integer portion. I've even tried UPDATE (myTableName) SET myCost = CAS...more >>

I'm trying to see if this is possible. I have an employee table that contains employee ID and manager ID. I'm trying to string together the complete hierarchy for an employee in one row. So, if you have employee ID 1 reports to 2. Employee 2 reports to 3, then the result would be: Employe...more >>

I have a SELECT statement that returns the grade levels represented in a given homeroom: SELECT DISTINCT(ELEVEL) FROM STUDENTS WHERE HOMEROOM = @SECTION There are four possible scenarios in our elementary schools for how the homeroom is populated: 1 - All Kindergarten (ELEVEL is 0) 2 -...more >>

I have an existing collumn in a table that contains a 5 digit number used as an employee id field.(datatype nvarchar) I would like to auto fill the rows that are '00000' or Null with an number beginning with X0001 and incrementing by 1 thereafter.( I want the X as a prefix). Any Ideas?? ...more >>

background: sql2k and analysis service I know how to create a cube in analysis service, but how to view the cube in the Pivot Table services in Excel? when I go to Data|PivotTable and PivotChart report...|External Data Source| Get Data | OLAP cube, then browse to the server, but I don't see a...more >>

I use sa account to establish connection from .NET web application with SQL database. Now I have to use windows authentication and any combination of windows accounts with different rights have been failed. I'm always getting the same error: System.Data.SqlClient.SqlException: Login failed f...more >>

Hi, What's "Enforce relationship for replication" for? pls advice. ...more >>

hi, The password hashes generated by pwdencrypt() for SQL7.0 & SQL2000 are different. The password hash generated for SQL 2000 stores both the actual passsword & it's upper case version (David Litchfield's article on cracking SQL passwords). But the passwor hash for SQL 7.0 is of 16 bytes on...more >>

I have the following query and i'm selecting one record only based on the max value of a column. is this the best way to do this? or is there a faster one? select chartCsiID, chartXO, chartCol, chartLo, chartHi, chartMM, chartYYYY, chartDOTL, chartDOTH, chartBS, chartLineType from ch...more >>

What's the best and most efficient way to string these data into another table? Is there anyway i can do this without declaring a cursor. I have huge record set to work. Dat =========================================================================================================== Monday ...more >>

I am using SQLDMO in my ASP.NET project to connect to a SQL server. According to MSDN, I only need to set property LoginSecurity=Ture to enable Windows authentication. However, the SQLDMO always passes my workstation name instead of my user name to the SQL server. The web config of the pro...more >>

i have a table with an integer field that is of type PositiveInt_Type with a length of 4. I am having a problem where when alarger integer tries to get stored (let's say 125,000), it shows up in the database as 99,999. What datatype and length should I be using for an integer that could be u...more >>

according to sql 2k book online Date and time data from January 1, 1753 through December 31, 9999 what if a couple of records in a huge data file actually have dates backed to 1500 (and those are actual and valid publication dates), what should i do? I want to be able to query the data by date...more >>

Hi there. I have a table containing a datetime column whose data I need to use in the WHERE clause of my SELECT statement. When I use greater than '>' or less than '<' in the where, I am able to select the data for the correct range of dates, but when I try to use equals '=' the select retu...more >>

I have a Database called Products with 3 big tables Rims, Tires, and Stock my database is as follows. Products.mdf and Products.ldf How can I create file groups for the tables Rims, Tires, and Stock and attach these tables? Example: Products.mdf Rims.ndf Tires.ndf Stock.ndf ...more >>

Hi, I come from a strong SQL Server background. I am moving into a new role where the company use DB2. Is there much difference in terms of syntax between DB2 and SQL Server etc? What is DB2 like to work with (environment, reliability etc)? Any feedback is much appreciated. T...more >>

Hi, how to combine the two conditions into one: WHERE (StorageQty IS NOT NULL) AND (StorageQty <> 0) ...more >>

Hello I was about to backup a database and it shows a filename with fileextension ..TRN. shouldnt it be .bak? what's .trn? never seen it before when taking a backup /Lasse ...more >>

Hi All, I have an SP written in SQL 6.5 and I was trying to run the same in SQL 2000. I have one line where it is giving me error. declare @logId int set @logId=192968 declare @querystring char(200) select @querystring ="SELECT logid,substring(username,1,20) username,logtime,bytesrecvd,su...more >>

Think I'm having a brain lapse here, but need some help. :-) Probably best explained via example. Say I have multiple clients with a history of multiple orders for each. I want to retrieve the latest or most current order only for each client. How do I query that? Thanks...more >>

How can I tell how many column is returned in a query like this? Select *.general, lname.newbusiness, contact.newbusiness, …. Thanks ...more >>

Hi, How to look out for a string which contains % as a part of the text. I need to get all strings which has got % in them. thnks in advance anu...more >>

I'm new to SQL Server and converting a VB app from Access back end to SQL Server back end. In this app I use a lot of expressions in queries e.g. SELECT iif(isnull([MyText],"",[MyText]) as sRemoveNull or SELECT [SaleDate]>=#01-Jan-2004# as bUseSale I can't get anything of the sort to wo...more >>

Hi, I've a problem with SQL Server 2000 SP1. We've data in a table having 50 thousand records. The client is web application. Problem is, the Timeout error occurs when the search engines crawlers hit the website simulteneously and in multiple threads. At that time I get the error Time...more >>

TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records. I must compare these two tables and insert to TABLE3 and update TABLE1. Is there any other faster method can replace the following method? Thanks. ------------------------------------------------ CREATE PROCEDURE RMSTEST1 AS DECLARE...more >>

Hi All, Just a quick question for anyone, I have a stored procedure that uses a lot of variables in a where statement, the only problem with this is it's very slow, I am just listing them something like this WHERE AND (tblSurvey.disposal_method = @disposalRoute OR @disposalRoute = 'null')...more >>

Hi, I'm using SQL-DMO to view some properties from a SQL Server instance; I have a doubt about the results I see in profiler and I hope that any of you can help me with my question; When I connect to the instance I use the method Connect and when I finish I use the method Disconnect from th...more >>