Hi, I need to store a 2D Array in a Table. What is the easiest way to to this. the array would only contain integer values.. anyone a better idea than "implode" the Array in a string and "explode" it again in the program code? Nils ...more >>

Hi, I'm trying to Bulk copy a dat file into my SQL table through a BCP or Bulk Copy and I get the error messages above, but when I use DTS and points it to the same path it works. Why isn't BCP and BULK Copy working for me. e.g.1: BULK INSERT DBA..publisher FROM 'c:\newpubs.dat' WITH ( ...more >>

Hi, I want to write a query such that part of the output is displayed and the complete output is put into a temp table. For example i have a table customer and i want the total customer details to be put in a temp table and the top 5 to be displayed. select * into #temp1 from customer ...more >>

I'm trying to create a couple of SQL commands that create and then destory a database (for unit testing purposes). I am currently just checking this all out (as my SQL isn't that great) in Query Analyzer to make sure it works properly and have come across an issue. This is my code-------------...more >>

Hi Everyone, I have a bit of a head scratcher. In SQL 2K I have the following table called NAMES: nameid firstname lastname 1 Paul Smith 2 Mary Peters 3 John Doe 4 Jane Doe ...more >>

Hello, I am trying to debug a stored procedure on remote server. The debugger starts and passes through without stopping. No error messages are generated on the client. On the server the following error appears in the Application Log: Event Type: Error Event Source: SQLDebugging98 Eve...more >>

Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried) 3-4 times and give up if after that it's still in deadlock. I'm very sure that many experienced peop...more >>

Here's a contact sent from a client whose JobTitle says "DECEASED". WTF?!? I suppose the survey people will have to use a Ouija Board instead of a telephone to find out what he thought about the IBM Z-Series Servers? -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man,...more >>

I'm hoping there's an easy solution to this. When ordering with a case statement this works: select * from blah order by case when @sort = 1 then customer_no else lastname end But if I want the case statement to sort on multiple columns: select * from blah order by case when @sort = 1...more >>

I am refreshing records on a target table from a similar source table (two databases, same server) and having problems with the Text fields. I am using INSERTas follows: delete from Trackpad4..Archived set identity_insert Trackpad4..Archived on insert into Trackpad4..Archived (....more >>

Is there any way I can convert a letter of the alphabet to its numerical position in the alphabet. So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26 I'd prefer not to join to a seperate reference table if possible. ...more >>

Which is better to use? I heard that <= and >= are better because it allows SQL Server to retain the query plan in cache, or something like that. This is in reference to a date comparison. SELECT * FROM <table> WHERE <date> between <start_date> and <end_date> SELECT * FROM <table> WHERE <da...more >>

According to the 2K5 BOL: "A CTE must be followed by a SELECT, INSERT, UPDATE, or DELETE statement..." Does anyone know the reason behind this? I was thinking a CTE was much like a table variable, but given this requirement that doesn't seem to be the case....more >>

I have phone data in a table that is in the format of (123) 555-1212 and some are empty fields. and I need it to be in this format 1235551212. I will need to do this periodically due to no control of how the data comes in. I will have to clean up after the fact. I came up with this ...more >>

Sample Table: Table1 -------- ID YN 123 Y 123 124 Y 124 Y 125 N 125 126 Y 127 Y 128 Y How do I select those IDs with multiple records, each having a Y entry in the YN field? In the sample table, 124 should be captured in the Select. Thanks. -tom ...more >>

Does anyone know of a way to do this without using DTS? Simply by SELECT * FROM <table> ...more >>

Trying to do this all day and googling for answers but found none, hope someone can help. Thanks in advance. select * into OPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTable Reason: I am joining local tables with linked server tables using the format "LinkedServer.database....more >>

I need to be able to delete records from 1 table that do not match 2 fields on a 2nd table. I was trying to get the SELECT to work first to find the records that are not full matches. Below is my code and would appreciate any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code,...more >>

I am designing the database for a client-server enterprise application. The database contains tables which fall into four main categories: - Data Source - Data Results - User Management - Payments/Ledger Performance could potentially be a major issue with this system, with the main "dat...more >>

Looking at the Northwing Employees table there is a PK-FK relationship between Field ReportsTo en EmployeeId ( the script for table) is at end of this) I have some questions about this approach. I have also seen same approach used in some accounting packages where it allowed muti-companys whe...more >>

I would like to execute a stored procedure (sp_spaceUsed tablename) within another strored procedure and capture the returned values into variables. Can this be done? Thanks....more >>

Have 2 tables that are joined by a 3rd. I want to get all the date in both tables whether they are connected or not. There can be many policies per role. RolePolicies is the table that connects the two. CREATE TABLE [dbo].[Roles] ( [RoleID] [int] IDENTITY (1, 1) NOT NULL , [Descripti...more >>

If I understand correctly, I can't reference a view from an indexed view. Am I allowed to reference another indexed view (by definition having a more table-like nature) from an indexed view? ...more >>

Hi I am aware that I am unable to declare TEXT or NTEXT local variable datatypes in a Function. Is there a method of returning more than 8000 chars? Any help would be much appreciated. Thanks B ...more >>

Can anyone see anything that may be causing this view to return data extreamly slowly, or have any tips on increasing the performance of this particular view? Alter View dbo.PreRegistration_V as SELECT C.Customer, C.Mail_Name, C.First_Name, C.Last_Name, NULL Level1, Null L...more >>

Hi We are using the code below to simulate a cursor using the Table Datatype but we are getting an error when inserting the records. Server: Msg 8101, Level 16, State 1, Line 19 An explicit value for the identity column in table '@tblImports' can only be specified when a column list...more >>

I need to delete particular rows from a table. I would use TRUNCATE since it's much faster. But I need to selectively do so. Is there a way to do this? Thanks Jiho ...more >>

I want to make some UDF()s to preset dates for a slew of reports that I need to do. In SQL Server I'm missing the mark in date math. I need to get a few basic dates and I'll roll with the others StartofLastMonth EndofLastMonth StartofLastWeek EndofLastWeek StartofCurrentMonth EndofCurr...more >>

OK, here is what I have. Proc1 defines some variables within it, say one is @Amount1 and @Amount2. (These are not parameters passed to proc1, they are variables within it) Proc2 is defined to take some parameters, the last of which are defined as output parameters: @Amt1, @Amt2. When ex...more >>

I have table Stock which represents products in stock. When new order came (table orders) I must reserve items in stock for that order (table reservations). The way I work is, that I first find all appropriate stock for my order, walk through it and reserve the quantity until I reach orde...more >>

Hi, I have enabled free text searching on my table in SQL 2000. I am testing my query in query analyzer to see some results based on searching for certain words in a description field. In paticular I am seearching for '316' and it returns some records however it is leaving out records wit...more >>

Can anybody enlighten me to what the difference would be between these two queries: SELECT Distinct vONYX_Individual_PrimaryEmailOnly.iEntityID, 194 AS iListID FROM vONYX_Individual_PrimaryEmailOnly LEFT OUTER JOIN vOnyx_Products_SimplyAccounting_Only ON vONYX_Individual_PrimaryEma...more >>

Hello, I have a question about Merge/Hash/Nested Loop Joins. If you have to join some tables where there is no index - which of these joins would be able to perform such a join? I read BOL, but it just wasn't clear to me. Even though the explanations mentioned stuff about Indexes, none o...more >>

Is there a way besides truncate table to reset the identity column of a table? Thanks, Joe ...more >>

Is it possible to execute a Stored Procedure from within a user defined function. The purpose of the user defined function is to be able to use the results of the Stored Procedure in a select statement. The user defined function should return a table....more >>

Folks I'm pretty sure is a very basic question... I tried to find an answer for one hour without success (honest !) :) Having a master / child table setup, how do I query all items in master NOT having linked records in child ? Actually I want to create a stored proc that will create a ch...more >>

Dear Newsgroup, I have a plerplexing problem here... I have a table indexed on bed, timestamp, patuniq.. the record contains patname, and dbdata. patuniq contains a unique message id. Here is the current query: use scratch ; SELECT RinasRawData.PATBED, RinasRawData.PATID, RinasRawDat...more >>

Hi, I've got a table UDF which takes two parameters and returns a table, as follows: CREATE FUNCTION dbo.ftblPeriodYear (@pCompanyID varchar(15), @pDate datetime) RETURNS @tblPeriodYear TABLE ( Period tinyint, Year smallint ) AS BEGIN <snipped to save space> RETURN END...more >>

insert into dane select * from OpenRowset ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\praxis\','select * from [aaa.csv]') this code above works properly when I run it from query analyzer. It insert about 40 rekords to my database. If I try to execute the same (...more >>

I am trying to use If exists to return 1 when data is found or 0 if not, to my front end If Exists(select id from dbo.table where id = @_id) Return 1 else return 0 what is wong here so I can move on? Thanks...more >>

I want to make sure that only month end dates make it into the table. I could put if conditions in my insert/update stored procs or I could do that with a constraint. Is it possible to put a constraint in table that checks for certain condition using user defined functions? for example:...more >>

I am looking to create a querty that searches for the max value a field called listnum in a table called tbl_listing and then interests 25 new records with listnum's starting 1 higher then the max value. ie if the highest listnum is 1000 it should insert new records with a listnum of 1001-1024...more >>

Can someone point me to some sql script that drops all objects for a given user ? -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

I have a web form that access to a table in an Sql Server's database. When a user opens the form he get the values in the record actually selected and can modify that. If he press a "submit" button the record is updated with the new values. I will check if the users has really modified the val...more >>

I have numbers like 16, 30. Now I have to convert them to year and months like 16 would be 1 year 4 months. Thanks. ...more >>

Probably an easy question... I have a table: CREATE TABLE [dbo].[table1] ( [Company] [varchar] (100), [Revenue [money], [FiscalYear] [int] ) ON [PRIMARY] GO in which I would like to construct a query that will have the following output: Company FY 2004 Revenue FY 20...more >>

Which is better to use? I heard that <= and >= are better because it allows SQL Server to retain the query plan in cache, or something like that. This is in reference to a date comparison. SELECT * FROM <table> WHERE <date> between <start_date> and <end_date> SELECT * FROM <table> WHERE <da...more >>

Is there a tool, or does anyone have a script format that might automatically pull in AD accounts from an OU and put them in the Users group of a specific database. If there is an article or existing document on this I have not been able to find it. I would appreciate any help anyone can...more >>

I have a simple table that includes an ID and 8 user-defined data fields. The fields are nvarchar(255) and the application allows the user to store any data they like in them. The application also allows searching on any combination of up to 5 of the fields, with an AND or OR logical combinati...more >>

Hi I have an application in Access2003 with linked tables. When I create the links manually (picking DNS etc) I have no problems. When I then try to recreate the links programatically (different users) I am not able to add any records to any of the tables! If I erase all the linked tables a...more >>

I have a problem with SQL Triggers on SQL 2000. I have created a trigger on a view that I want to use to add some rows to a separate table. When I test it the data is updated on the view OK from the source but the trigger never seems to fire. Can anyone point me in the right direction to solve...more >>

a quick qn : i have this filter in my where clause and i know this is not sargable. how can i make it one? ( i want to make use of a covered index on prodid column) any help is appreciated SELECT .... FROM ... WHERE (left(prodid,11) like replace(left(@pegid,11), '*', '%')) col/var dat...more >>

Hello all, I have a question on DB versioning. When deploying our .NET application, we also provide .sql files including all changes to the local DB for this new application version. These files are ran by a DOS batch file. To be sure that every user has all the required changes in DB, we inc...more >>

Dear all, I've got a table of which I would need obtain a XML file. How do I such thing? I mean, instead of to obtain a .DAT or .CSV from that table as it customary, a xml. Any advice or though woud be greatly. Regards,...more >>

I often have enums in my applications like public enum Colurs {Red, Green, Blue, White, Black}; Usually I user the corresponding int-value of the enum to store it in a database in a Int-Field. When reading the int-value from the database I easyly can convert this value to the corresponding...more >>

Which is the better way of removing the "Time" element from a DATETIME field? DECLARE @Date DATETIME , @Method1 DATETIME , @Method2 DATETIME , @Method3 DATETIME SET DATEFORMAT YMD SELECT @Date = {ts '2005-10-07 23:59:59.997'} , @Method1 = CAST( SUBSTRING( CAST( @Date AS BINARY(8)) , 1...more >>

Hi all, In the following query i want to compare only the date value portions of the 'attempt_dt' columns in the where clause. All time related information information is to be ignored!!!! SELECT a.attempt_dt, (SELECT Count(asmt_v2_employee_id) FROM ...more >>

Howdy I have two tables A and B with a primary(A) / foreign(B) key relationship. I want to select some rows in table A and a left outer join with table B, and for any rows that match I want to put a 1 in a column in the result set. Does that make sense? Having a mental block Thanks for...more >>

Hi, I'm trying to code an sql statement that tests the existance of duplicate key values in a table. I have tried the following with no success: Declare @NbRecs int, SET @NbRecs = (SELECT COUNT(*) FROM tablename GROUP BY column1,clolumn2,column3,cloumn4 ...more >>

Hi all, Can i insert a date into SQLServer using this format 10072005? (mm/dd/yyyy) Can i reference data in a search (WHERE) using this format? IE: WHERE startdt >=01011753 AND enddt <= 10072005 In otherwords a date that doesn't include any separators? Is this a bad idea? Cheers, Adam...more >>

I have the following table named "Notes": Notes (3 fields) ------------------------------------------- CASEID (int) TIME (smalldatetime) WHO_WROTE (tinyint) There may be many note records for a single CASEID. I want to generate a result set that shows only the most recent TIME rec...more >>