Hi All I am working on a database with Japanese_CI_UI collation and want to generate file in fixed byte(not characters) format. I have defined all columns as nchar and used bcp/queryout option. But I found that the file does not get generated in fixed byte format instead bcp onverts all possilbe d...more >>

From in a delete trigger I would like to allow deletions cascading from a parent table and reject any deletes done directly to the table with the trigger. Is there any way from within the trigger to distinguish these two conditions? Thanks Tom G....more >>

Hi there, I could do with a little help on a sql query. I have a table with 4 cols resolution, browser, datetime, ipaddress I am trying to establish how many unique visitors in a receiving and what browser and resolution they are using. CREATE VIEW dbo.view_www_visitor_stats AS SEL...more >>

Hi, i have got a problem with osql.exe (or isql.exe). Both have same problem running query with non english words (which have to be inserted into table). The same query but from Query Analyzer make it all right (non-english words are non-english). example file: CREATE TABLE test ( id in...more >>

I have data in following structure NUM Altpar01 Altpar02 Altpar03 Altpar04 1 11 21 31 41 2 12 22 32 42 3 13 23 33 ...more >>

If you have a field called Last_name which has data like this: Jones Jr,Bobby T. ---> becomes ----> Jones, Bobby T. Clark II,Bill N. ---> becomes ----> Clark, Bill N. Can I have a script that parses that "Last_Name" field and removes the Jr or II or III or Sr but leaves the ...more >>

I would like to know the best method (detailed if possible) for duplicating primary keys. I have a database with 5 tables, (tableA, tableB...) each with a primary key of JobNumber. If and when someone inserts a new JobNumber into Table A, I would like the corresponding field in the other four tab...more >>

Hi i have a little VB project that basically needs to find all SQL Servers on our Network from here it will then ask you to logon to the server then populate a combox box will all the db's for that server, for which the user has access too i want to do it by calling sp's, what do i need to d...more >>

Hi Freinds, SQL 2000 I attached a database which was in a replication before. All the tables has DEL / INS / UPD triggers. How can I delete all those triggers at once? and make the database usable for single mode use? Thanks, Pat ...more >>

OK, here's the problem. I have data from a 100 million word corpus of English, with all of the distinct two-word clusters in the corpus. Suppose it were just a ten word corpus composed of the sentence "I ate the ice cream and Mary ate the cake" -- the rows would be the following: ID word1 ...more >>

Hello, I have a table with three columns date1, date2, date3. I have to search the newest of the dates (this could be in any of the three columns) for every data set, in order to give it out in a new column 'newest date'. For example: number date1 date2 date3 ...more >>

Hi NG, i've got a mystic problem i can't solve, perhaps one of you has a good idea... i will explain with some code, so fo better understanding my probroblem: i'll use asp, but for testing the syntax i use the MS Query Aanalyzer. following Stored Procedure is ok and does it's job fine with "...more >>

EM reports table size and individual index size. Does it use "sysindexes.used" column to determine how many pages an index uses and then calculates the index size? Is there any other (programmatically) way to determine how much space an index uses?...more >>

Hi I'm working on a project to convert around 150 gig of word and .rtf files stored in various different directories into a SQL SERVER database, using text columns. I'm trying to figure out the best approach to take, I'm thinking maybe I could do this in DTS, or .NET? Alternatively does a...more >>

I have function like this: ALTER FUNCTION dbo.GetGPAValue ( @GPA varchar(5) ) RETURNS numeric(5,2) BEGIN DECLARE @FinalStatus numeric(5,2) SET @FinalStatus = CAST(@GPA as varchar(5)) IF @@error > 0 SET @FinalStatus = 0 RETURN @FinalStatus END The problem is wh...more >>

I have this table: create table Permission ( Id int, Type char(1), SubId int, Auth char(1), constraint PK_Permission primary key (Id, Type, SubId) ) inside which, lets say exist these values: insert Permission values (1, 'D', 1, 'P')...more >>

I must be missing something simple here. I create a table with sample data. I open a cursor on the sample data and insert into a results table. The sample data and the results data are not the same. Thanks DROP TABLE Testdata CREATE TABLE Testdata ( [testvalue] [char](1) ) INSERT IN...more >>

I am doing some data cleansing on email addresses. I am looking for a string function that will remove a trailing single quote. eg. someone@somwhere.com'. ...more >>

Hi, I have a sql 7 server want to get rid of, but, before that, I need to move one db off this sql 7 server onto sql 2000 server. Is this anything special I need to do during the db restore process onto sql 2000 server? many thanks. JJ ...more >>

Hi, What's the difference/performance difference when you create a index with two columns combined vs create two indexes with each one of them as showed below? 1. CREATE INDEX [index1] ON [dbo].[table1]([MARKET], [DATE]) WITH FILLFACTOR = 75 ON [primary] 2. CREATE INDEX [index1]...more >>

Hi, Can any one tell me how to call a user defined funtion from VB ? This is a partial Code. svValueString = "ABC" lngField = 1 svValue = "" svSql = "SELECT dbo.fn_GetValue(svValueString, lngField)" Set rsTemp = New ADODB.Recordset With rsTemp .ActiveConnection = conn .Cu...more >>

I'm studying for 70-229 and designing a database, and I'd like a little reassurance at this point. (Sorry if I'm nitpicking. Do I understand this correctly 1. A primary key is highly recommended but it is not essential that any one primary key column be an Identity column. (It is not essentia...more >>

Hi, I have an index based on column "Name" called IDX_Name. I know that if I have a query to search for specific column, IDX_Name will be used. Now I want to search for Names that begin with sepcific letters. If I use a query like: Select * from tblCustomer where Left(Name ,3) = 'ala' ...more >>

I have the Proc below which is supposed to perform a SELECT with different properties based on the parameters sent to the procedure. I understand that when "If" statements are used the result set is not sent back to the client. If I have logic which performs the desired select statement how do I...more >>

Hi all, Has you ever had to select a "random" set of records from a table? I need to retrieve groups of 10,000 records from a table (out of 500k) and put them into other tables. I'd like a decent variation in the returned rows, which is why I'm thinking of a randomized select statem...more >>

Hello. I'm writing a query that checks all databases on the SQL Server against some rules (it finds all DBs my program may use). In general it works good but some clients had a problem. I found the way to replicate the bug: DECLARE @Name sysname, @SQL nvarchar(1000), @Param nvarchar(50), @IsOu...more >>

Hi, I am really confused on how to use date in VB with SQL server 2000 application. My scenario is this: 1. I want to design an application which is system date format independent. What ever the system date format is the application should work. 2. I want to design an application which i...more >>

Hi Freinds, I am stocked on this : This is my table : id code1 code2 ------------------------ 350 office department 350 office office 350 office project 350 office csd 350 department department 35...more >>

can the recovery model of the database be changed from Full to Simple mode? what are the implications?...more >>

Does anyone know the reason why the result of conversion of datetime value is different between MSExcel and MSSQL2K. I've tried to import a column of datetime values from Excel to MSSQL database. However, the values after the conversion were not the same as in Excel. At first, I converted all ...more >>

I'm having some issues comparing data from 2 external sources, based on an mID field and a date field. I just got another twist. The data for an mID in source1 for 4/1/04, is related to the same mID in source2 for 4/2/04. I was joining tbl1 and tbl2 on mID and Datefld. Now I need to jo...more >>

what is the syntax to use count with a case statement? i need to perform a bunch of different counts on a table based on different criteria and i' like to do it in one query - this is what i mean: maybe i don't need to use a case statement, i just want to simplify this: select dealer_id i...more >>

Dear All Please Explain me diff between corelated query and subquery with a example Ramesh:) ...more >>

Hello all, Is there anyway I could trigger an MS Access macro (a.k.a. the Access Autoexec system macro) via the SQL Job Scheduler? If not, will I have to compile a store procedure? If yes, then can you direct me to any available code if possible? Is there any simpler way to run acce...more >>

Hi all, I am trying to create a trigger on a view from SQL Query Analyzer. But I keep getting an error: Invalid object name 'dbo.WV_Contsupp'. I thought may be the view did not exist. So I ran the following SELECT statement from the same sql connection(window) I am using to create ...more >>

Hello, below my example is a basic ddl and sample data. TblA contains 3 fields (for my question), Datefld (dateTime), mID (varchar - not unique), mIDCount (int). Datefld and mID get populated from an external data source. I want to count all mID's for a given day as below and then upda...more >>

i have 2 tables Calender (caldate, working day, dayofweek) calender would contain a date range, working day is set to N if it is a saturday/sunday, dayofweek is the name i.e monday, tuesday etc eg 01/01/2000, N, saturday 02/01/2000, N, sunday 03/01/2000, Y, monday ......... ......... ...more >>

Hi All I have 2 tables (one is candidates, the other is telephone calls) and i want to join them to show a row for every candidate joined with the latest phone conversation. I am currently doing it like this: select * From Candidates left outer join ( select CandidateID, Max (CallDate) As...more >>

I have a multithreaded service executing a stored procedure which updates the table shown below. SQL Server repeatedly runs into deadlocks on the Update statement given below. I have included the trace error log messages as well Is the Inner Join in the Update query causing locking issues? Any ins...more >>

Is it possible to import a XML-file to an SQL-table? reidarT ...more >>

Hi, all, In a stored procedure, is it possible to run a variable- based query. For example: declare @wholequery varchar(1000) set @wholequery = "select customername, address from customer" Is it possible to run @wholequery? Thanks a lot ...more >>

I have a stored procedure that uses BCP to load records using the xpshell cmd . how do I catch/test that all row are loaded. I tested one file, where one row was not loaded due to a invalid column. but all the rest where loaded. How do I test that all row where loaded ...more >>

We are currently setting up log shipping using SQL 2000. We have a local server on a 15 minute delay and a remote server on a 3 hour delay. I am curious as to the process of recovering the logs in the event of a failover. Has somebody ever done a script, or know of a way to automate the process...more >>

I have a Stored Procedure that I wrote a while ago that accepts 1 parameter, and at the end of the SP I do a SELECT so it returs a ?recordset? containing a value. I am using this SP from various ASP pages on my intranet site. Here is the code: Public Function GetID(ByRef Coum_cn, ByVal TblN...more >>

Anybody any idea why the third stored procedure is created but fails when you execute it. The second one - I have included to show that columns are correctly raised as errors but not when the tablename is wrong No Warning no nothing Should this not fail creation - I have been puzzled as to why ...more >>

I have a table as follows CREATE TABLE [dbo].[DebBacsFMPendingFiles] [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [ReportNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [RecievedDate] [datetime] NOT NULL [AppliedDate] [datetime] NULL [S...more >>

I've used the profiler a million times. I'm having a problem on a new Server that was set up recently. When I trace stored procedure events for a particular stored procedure (filtered in TextData) all I see is an occasional RPC:Starting and RPC:Completed sp_sproc_columns N'my_proceure', NULL, NULL...more >>

Hi all. ======= ERROR MESSAGE ================== Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string 'Entry Level 1'. /learningcentres/questionnaire/updatequestionnaire.asp, line 27...more >>

Anyone got a handy stored procedure that list the user stored procesure names containing a certain string (case sensitive or insensitive)? It would be nice if it could work like grep, that disaplayes the line containing the string. :) -- .~. Might, Courage, Vision. In Linux We T...more >>

Hello I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables The stored proc sp_helpconstraint shows all the constraint types, their user...more >>

I'm trying to write a text file using a SQL store procedure. I'm using the sp_oamethod to open a text file using the following sentenc declare @o int, @f in exec sp_oacreate 'scripting.filesystemobject', @o ou exec sp_oamethod @o, 'opentextfile', @f out, 'c:\test.txt', 8, I'm getting the erro...more >>

Let's say I have a query 'SELECT tableID, Comment FROM tbComments'. Let's also say I want to run a query that gets me all the items in a table (call it tbTable), and in the same row return all of the corresponding 'Comment' values from the tbComments table where tableID = tbTable.ID. Were 'co...more >>

I find it necessary to have to call a stored procedure, let's say procCheckID, using as a variable the contents of a column - let's call it ID - returned from a Query. At the moment I do this by looping through a recordset in ASP, calling the procedure each time. I'd prefer to write a specific...more >>