Hello, I am working on a SQL query and am not an expert, so I would like some advice. I have a table CREATE TABLE [dbo].[Registrations] ( [RegistrationID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [int] NOT NULL , [CourseID] [int] NOT NULL , [Status] [smallint] NOT NULL , ...more >>

Hi! I'm trying to get the NT group membership of a specified username from TSQL. I'm using the following to query the OS: SELECT * FROM OPENROWSET(NetUserGetGroups, @NTUserName) AS NT It works fine except the fact that it seems that the result is cached somewhere, because if I run it on...more >>

Hi, I am passing an XML text stream into a stored procedure which contains a timestamp field for me to use as the way to handle concurrency. I can retrieve each of the elements in the XML stream. My problem is the timestamp field comes in as a character string e.g. <row_timestamp>0x0000...more >>

Hi Guys, I need some help, I have to make an updateInsert query for a datagrid. It must be possible to change the exsisting rows and adding a couple new ones. is this possible?? and how?? Otherwise I was thing of making some checkboxes for update and insert. Thanks in advance. btw ...more >>

I am trying to update a few of my tables from a linked server. I use the exact same query to update 17 out of 20 DBs. The only thing I found in the answerbook, was related to SQL2000 and Win2003 server. I am running SQL2K SP3 on Win2K advanced server. When 3 out of the 20 starts I get ...more >>

I am having trouble writing a query that selects the data I want. Suppose I have tables A and B where apk and bpk are the primary key columns of A and B respectively. There is a one-to-many relationship from A to B (via apk). Both A and B will have very many rows. An example of table B migh...more >>

i have text longer than 4000 characters. what is a good datatype ...more >>

Suppose I have the following schema: Group Table GroupLocation Table Location Table GrpName GrpName LocationName LocationName So, I have a one to many relationship between groups and locations. A location can b...more >>

Is it possible to get the file version information from an executable via T-SQL? I am thinking along the lines of using ActiveX objects in a script to do this. Anybody ever try this? Brandon -- "In the beginning the universe was created. This has made a lot of people very angry, and has ...more >>

I want to apply extended properties to functions in the way that I do with tables. I have a report that reads metadata so that my data dictionary can always be read via this report. How is it that I can comment my UDF's so that I can read that back in the way that I do table comments? ...more >>

I'm getting a SQL VARCHAR result that has a date string in it: "2003-11-26T00:01:43Z" I'm needing to display this date/time in a grid, but it is rather ugly to leave it like it is. I've tried (unsuccessfully) to use VarDateFromStr(). I'm sure there is a way to convert this data to a C++ ...more >>

I have 3 sql servers that I would like to have the same groups on them which have the same permissions. Each server contains different databases. I've tried to create a script that would loop through each database and run the following sp, sp_revokedbaccess, sp_grantdbaccess and sp_addro...more >>

We had a problem recently where a query was selecting/updating too many records. We found out this was because the key field (SerialNo) was a text field, therefore... Select x,y,z from Serialnumbers where SerialNo between 'C12345' and 'C12350' ...was selecting unwanted IDs, eg 'C12', 'C...more >>

Hello All, I have the following table..... CREATE TABLE customer_list ( fname varchar(20), lname varchar(32), address1 varchar(30), address2 varchar(30), city varchar(30), state char(2), zip varchar(10), gender varchar(10), income varchar(20) ) ...more >>

I am seeing some very strange behavior with one of our stored procedures that uses dynamic SQL. The original stored procedure was throwing an error at run time because of incorrect syntax. However, this went undetected for a very long time because the developer never saw the error. When he execut...more >>

Hi Everyone, I have the following problem: I would like to generate a sql script that would insert data to a db (just later run it to add some records to a table, not to override a whole table). Could you give me some advice? Thank you in advance! Greetings Marek ...more >>

Hello all, I have a Contents table, where some records are inside. And then a ContentRelations table, which indicates the relations between Contents, they are graphs. For instance, ParentID ChildrenID -------- -------- 1 2 1 4 1 5 2 5 3 5 3 ...more >>

i have a simple trigger in a table, INSERT DATA_TEST SELECT 'I', * FROM INSERTED when inserting a single row into the table, this trigger worked, however, when i tried to import data (about 100 rows) into the table from a spreadsheet, the trigger didn't work. why? can someone pls help?...more >>

Hiya. I have a regular old duplicate query below from the northwind database. How do I alter it so that if all SHIPVIA in the group = 3 then exclude? Thanks!!! select * from orders a where customerid in (select customerid from orders b group by customerid, shipaddress having count(*)...more >>

hi how can i compare 2 tables??? i have to know if a subset of coloumns are the same thanks Carlo ...more >>

I am using a cursor in a stored procedure that I either (depending upon he parameters) insert a new record or update an existing record. The problem is when the update part is hit... it causes a lot of blocking until the record is updated. Is there a locking hint that i could use to prev...more >>

Hello, I am fairly new to sql and am trying to update all values in a field that contains the string \\r3edi01\gensrvnt\**** to \\r3edi02\gensrvnt\******. These paths change so I am unable to just update where pathname = value. Basically I just need to change the 2 to 1 anywhere value like...more >>

when I send an email using xp_sendmail with the contents coming from an sql query it puts loads of white-space (spaces) in-between each row returned from the query. How do I get rid of all this white-space, without affecting the content of the email? ...more >>

Hi! I'm looking for a fast and reliable way to fetch the latest entry pr. parent record from a table with both current and historic values. Here's an example of what I'm trying to do - it works, but I'm looking for a better solution: --- create table #tmp ([id] int identity primary key, [...more >>

In performance tuning our database, we've stumbled across a problem with our use of a user-defined function. The scenario is basically this: INSERT INTO Table1 (col1, col2) SELECT col1, some_scalar_function() FROM Table2 However, when I do this then some_scalar_function() is (according...more >>

How do determine if a column participates in/part of the primary key of a table. My sql script brings back all columns with the associated table name. How do I determine if the column participates in/part of the primary key of a table. SELECT sysObjects.name as Name, syscolumns.name AS [F...more >>

Hi, I've got following problem: We've got a table in which we are storing language information. The table contains 1 pk-column and x column for languages. the columns are named after the language. pk_id | en-US | de-DE | pt-BR | .... Now I want to have a dynamically selection over that tabl...more >>

Hello all, On this one, I will not post the complete DB architecture (because it is not needed to understand what I need)... Only the Table information of the table I am interested in. It is a simple table: TABLE [dbo].[batch_detail] ( [lineid] [tinyint] NOT NULL , [measurement] [f...more >>

Hi, ALL !!! Our problem is the following: We have N tables, M users. It is necessary to control the possibility of users to read/update/delete every row in any table. is it posible to solve this problem with existing means of MS SQL Server 2000, or we will have to think out something special....more >>

Is there a way to open a excel file from sql server (query window or DTS). I want to run a macro on a excel file before I load the data from the file to SQL server table using dts. If someone can post the code to that would be very helpful Thanks, MG ...more >>

I need to know how to build a dynamic temp table programmatically. I will create a script in which I create a table that has dynamic columns. The columns will have a 'Cause' field followed by a max of 24 columns denoting time 0 - 23 hours. Sometimes there will be missing hours. 00,01,04, 05, ......more >>

We have Customer named Willy's Place in our Database. How can i use select command that includes full customername?? SELECT * FROM TCOMPANY WHERE COMNAME = 'Willy's Place' Thank's for your anwers! -jari ...more >>

Hello again, While a trigger is running, is there a way I can determine the name of the table to which the trigger refers? You see, I have a truckload of tables that must have date columns updated whenever a row is created or modified. By way of example, I am using the following trigger to u...more >>

Hello my friend, I have been trying to clean up our Do not Call table and delete duplicates from them, howvere every attempt to do so has constantly filled up my tansaction log thus consuming my entire hard drive, can anyone tell me if it would be possible to accomplish this without writi...more >>

Hello my friend, I have been trying to clean up our Do not Call table and delete duplicates from them, howvere every attempt to do so has constantly filled up my tansaction log thus consuming my entire hard drive, can anyone tell me if it would be possible to accomplish this without writi...more >>

create vv select a,b,x,y,z,0,0,0 from vvTest Union all select p,q,0,0,0,r,s,t from vvTest1 ERROR:the column '' was specified multiple times for vv a, b and p,q are they key fields. Can somebody please tell me why would it not work and suggest an alternative. help appreciated -david...more >>

I am trying to insert records to a table in a database if the records are not existed in the table already. The source data is from a table in another database. My query runs fine when I test it in Query Analyzer but it doen't seem working in DTS package. I setup a job to run a DTS package nightly....more >>

Hi, is there anyway to see a temporary table content when I debug a procedure? Regards, Marco ...more >>

Hi, ....probably in the wrong group for this, but didn't know where else to go. A client has some data held in an oracle database. We'd like to import this data into our SQL Server database. I don't know anything how Oracle stores or exports its data, so can anyone advise how we could go abo...more >>

Does anybody know why long table names (16+ chars) in a turkish case-independent locale (Turkish_CI_AS) are case sensitive? It's interesting to note that the first 15 chars are case insensitive, but 16 and up is sensitive. Is there a bug fix available for that? create table ABC_DEFABCDEf(def i...more >>

Is there a way to copy all recs meeting my params to an archive table without specifying each field in the target table if both the source and the target have the same structure. Insert Into T1 Select * from T2 as opposed to Insert Into T1 (f1,f2,f3) Select f1, f2, f3 from T2 Th...more >>

Quote from a book: "Primary data fiile store data in tables and indexes and contains the startup information for the database..." "Before SQL Server2000 can store into a table or index, free space must be allocated from within a data file and assigned to that object...." Of course the wor...more >>

Hi. Quick Question about SQL Server and VB 6. I have multiple users using my VB6 program (with recordsets). If one updates a record, and then User 2 updates his record, user 1 updates are lost. Is there a way to ask if the record was changed or something like that? Thanks! ...more >>

Local server SQL server 7 SP 4, Win2K Server SP 4. Remote server SQL2000 SP3 Win2K Server SP 4. I have a report which uses information from the local server and a remote linked server (ppdweb2giga), which is on the same LAN. I am trying to return as few rows as possible across the network from...more >>

Hi! I need to write a script that will remove any constraints and triggers from any table in a specified database. My problem is that i don't know where are stored in the system tables the constraints and relations... I've been told that once this would be found, i would just have to use cur...more >>

I have a Stored Proc that calls an Extended stored proc. I will pass in an @signature_id using a cursor and I want to get the values of @image2 in a temp_table. How do I accomplish this? TIA, Jordan CREATE PROCEDURE signature_image_translate_select @signature_id int AS DECLARE...more >>

How do I search for "any" words? Assuming my search criteria is "word1 word2 word3", my results could be... word1 word2 word3 word1 word2 word2 word1 word3 word2 word1 etc etc etc... Any phrase containing any or all of the search criteria words and of course, in any order. How do I cr...more >>

Hello All, I have a messaging database that tracks text messages between users. (DDL and DML shown at bottom) When a user begins a conversation it creates a "conversation_id" which is used to tie the messages together. I would like a query that returns the username and number of distinct con...more >>

Hello, Someone knows URLs or examples in order to do a backups or synchronize tables or part of all databases in MSSQL 7 ??? This is the idea, and this is my case: I am working with a SQL Server 7 like a database server in a disk with an unique partition of 60 GB. My database size is a...more >>

Hi, Tried this on the XML newgroup, now I'm hoping for help here... The stored procedure below is complaining about conversion from character to uniqueidentifier types, but I thought that was already handled by the OpenXML function? Snip... DECLARE @xmlRepresentation int EXEC sp_xml_...more >>

I have a table with a trigger on it that includes a cursor to iterate through the inserted rows I can get a stored proc to make the trigger logic work but any updates through enterprise manager - table - show all rows roll back without any warning. Same behaviour in ms access Setting a tra...more >>

Hi , SQL 2000 I need to make a document of my database. A lot of my fields have description written. How can I generate a doumnet that contain those descriptions too. Regular script doesn't do it . Thanks in advance, Patrick ...more >>

Hi Friends, SQL 2000 WIN 2000 I am trying to do a DTS from excel file. The lenght one of the columns is 1000 char. When I run DTS I am getting : Column 3 'qualifications' too large for specified buffer size. What can I do? how to resolve this issue ? Thanks in advance, Patrick ...more >>

I am working on optimizing some queries for an existing system, and the execution plan showed some table scans were occuring. I created some indexes, and rid myself of the table scans, which seemed to eek out a second or so in run time, so in this case it helped to have an index and no table sca...more >>

I am working on optimizing the performance of some queries that are currently running as part of an existing production system. I am working on query right now that is part of a stored procedure. I have done some analysis, and I have been able to speed the query up a bit just by configuring some...more >>

Our sys admin just applied Microsoft's latest security patch to your server and now the SQLServerAgent won't start. Here's the error messages [165] ODBC Error: 0, Cannot generate SSPI context [SQLSTATE HY000] [000] Unable to connect to server '(local)'; SQLServerAgent cannot start ...more >>

Hi, I am trying to retrieve the following data from 6 tables using a stored procedure: NETWORK_NAME taken from table NETWORK. CORP_NAME taken from table CORPORATION. LEV1_NAME taken from table CORP_LVL1. LEV2_NAME taken from table CORP_LVL2. LEV3_NAME taken from table CORP_LVL3. Many fi...more >>

Hi all, If you have an online catalog, and you want to add the 'Other Related Items' feature to your catalog, how exactly is this implemented, also what is the table design for this? I'm guessing it's a cross-identifier, perhaps a column in the table specifically for related item...more >>

Hello all, I have this query. The table themas_link_producten is a n:n bridge between themas and products. I like to return the productname (productnaam) as well as as the themaid in a row. I know there might be redundant products in the resultset, this is by design. select p.productnaam fr...more >>

Hi, I hava an SQL Server 2000 database and have built a trigger on the product master table. As I am facing performance problems during daata loading, I would like to know what would be the better way to do what I want. The product master table is updated by 2 way: - one by night batch, I...more >>

Hello Everyone, Recently, I need to write a program for my company, which is about backup and restore SQL Server 2000 database through a VB6 application program. I use SQL DMO to do this program. The backup part can run successful, but I can't do the restore part. The problem is that ...more >>