I have a function that is designed to return a variable that contains concatenated values from a partinular field in the returned rows: DECLARE @output varchar(8000) SELECT @output = CASE WHEN @output IS NULL THEN CAST(TSD.ScheduledTime AS varchar(4)) ...more >>

I have a movie db in sql 7 that I like to keep sorted by title. I have been exporting to another table, and sorting output by title, then deleting the original table and renaming the new table to the old tablename. I would love to script this but dont know how to rename a table. I can drop a...more >>

Hi. I'm really new to MSSQL, so therefore my question can sound stupid. Is it possible to use a function written in a module in MS-ACCESS in a stored procedure? Or how can it be done, it is a complicated function with loop and more. I'll appreciate all answers also negatives ones. TIA J...more >>

I am looking for advice on what people recommend for a SQL-2000 EE data warehouse server. The box would be dedicated to nightly ETL from DB2, generated daily reports, and ad-hoc queries. We hope to route most queries through Analysis Services. We have a $ 20k budget for hardware and around...more >>

Looking for a decent source that has some examples of column formulas as well as a list of column formula functions that can be used and how to use them. Hard time finding something online. Any suggestions or references would be greatly appreciated. Fairly new to this. Thanks ...more >>

I have an ASP front end on SQL 2000 database. I have a form that submits to an insert query. The entry field is an "identity" and the primary key. I have used scope_identity() to display the entry# of the record just entered on the confirmation page. Now I need to insert the entry into anothe...more >>

Hi! I got an interesting situation. When I start SQL Agent, it seems fine from the icon and the status bar. But actually it's not working, althogh the icon shows a green arrow. The message I got is "Can not perform this operation while SQLServerAgent is starting". the message # is 14258. Does an...more >>

I came across an article in SQL Mag about Crosstab Queries. It works great in Query Analyzer, but I'm stuck on how to use it in an Access ADP. I need to use it as a Recordsource in a form and report. Can someone tell me how to use it, and please try to be as descriptive as possible. I'm new ...more >>

Question: Why would I not be able to import an Access 97 table in which some records have null values in fields that allow null values? Wouldn't the table's design be imported first, bringing the columns' "allow nulls" attribute with it? I'm dealing with both text and numeric columns. Not ...more >>

We are hosting a 140 GB database on SQL Server Version 7 and Windows 2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5 SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to the OS) and a 4 GB swap file. (The PeopleSoft CIS application will not permit us to upgrade to S...more >>

I have been searching for an escape character or a way of escaping double quotes that are actually in a string that I am using in the contains predicate. Here is an example select * from table where contains(field, '"he said "what is wrong", that is what he said"') I need the double qu...more >>

Hi! If doing online version upgrade from SQL 7.0 to SQl 2000, (1) do I need to shut down server? (2) after upgrading, is all login and db user, group information, scheduled Jobs, maintenance paln... still there? or I have to save the info before upgrading and recreate after upgrading? (3) any ot...more >>

I am trying to conditionally create a stored procedure in a Query Analyzer script. When I have the CREATE PROCEDURE statement by itself in the script, it compiles and runs ok. Once I put a condition on the script, it does not run. I have tried wrapping the script inside a BEGIN...END but it ...more >>

Hello, I would like to create more lines by concatenating values. When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'> the result is <This is an example> (on the same line). I woul like to get: <This is an example> (each 'word' on a new line, but in 1 field) Whis SQL st...more >>

CREATE PROCEDURE dbo.Synchronization_GetNewRecords ( @item varchar(50), @last datetime ) AS SET NOCOUNT ON DECLARE @sql nvarchar(4000) SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @last EXEC sp_executesql @sql, N'@Type varch...more >>

Greetings, I have been attempting to develop a useful and functional template for database tracing/profiling that will enable me to collect metrics for performance tuning. The database is used as an OLTP database as well as running reports. Below is a list of my trace properties and data ...more >>

I had link my 4 of workstations to server with MySql. 1 pc of my pc can run a software which can update MsSql perfectly but not others(3 failed). I tried to add System Dsn data source for Control Panel - Odbc data source 32. The pc which working fine with the software function but 3 of the rest...more >>

I'd really appreciate your help creating a parameterised view in SQL Server, which I can then invoke from VB5. I'm using DAO and ODBC to connect to SQL Server 2000. I can open Tables and Views, but can't find how to create or use a "query" parameter. If I use the "?" (in the SQL Enterprise m...more >>

Hi, How do I insert text that contains the ' character into a text column? I'm getting an Incorrect syntax error....more >>

Dear All, I have 2 SP's, one (let's call it sp_A) which returns a list of files and another (let's call it sp_B) which recursively looks through a menu table to give me the location of the file. I call sp_B within sp_A as I want to return the data in one table. I am having trouble in getting ...more >>

Hi, In the datawarehouse DB (under MS commerce server 2002) a table stores the referer domain name. Table structure is like refererdomainid <binary>,domainInternalFlag <0/1>,refererDomainName<varchar> e.g. <binary>|0|unknown <binary>|1|google.com <binary>|1|yahoo.com <binary>|1|goog...more >>

I'm a newbie to script writing. I'm trying to write a script to copy all data from a table to the same table in a 2nd database. Both databases are on the same server and are identical in design. I can do this with DTS but wanted a script I could email to a user to run in Query Analyzer. Exa...more >>

Hi, First of all my apologies if you have seen this mail already but I am re-sending as there were some initial problems. This query is related to defining indexes to be unique or not and consequences thereof. Some documented facts that I am aware of include 1. Defining uniqueness al...more >>

When running Profiler to save a trace to a table the field 'TextData' gets trunciated. The datatype for that field is as shown: Column name | Data Type | Length | Allow Null TextData ntext 16 yes I can not find a way to set the trace...more >>

I'd I have a problem I'd like to post CREATE TABLE and INSERT statements that will create my table and insert data into the table. I can use the scripting feature in Enterprise Manager to generate CREATE TABLE scripts. Is there a script I can run that will generate INSERT statements so I can...more >>

Does anyone know if it's possible to use the wizard or DTS Designer to accept a source file with the following simplified format: <field1label>: <record1field1value> <field2label>: <record1field2value> - - - - - - - <fieldNlabel>: <record1fieldNvalue> <field1label>: <record2field1value...more >>

I'm not sure if this is possible but what i want to do is as follows: I need to Select all values in a table that are smaller then the preceding one timewise or ID wise. Which ever. I have three columns. ID, timestamp and value. I'm using SQL server 2000. Is it possible to do this wit...more >>

Hi ng, we do have a MS-SQL Server in production use which I want to dump its contents nightly onto a ftp site. The ftp does not accept anonymous logins; can anyone point me in the appropiate direction on how to automate this ? The dump is drawn using the following: EXECUTE master.dbo.xp_...more >>

ASP.NET / VB.NET SQL Server 7.0 Our client has insisted that we change our established practice of building SQL in-line and move it all to SPROCs. Not a problem for 80% of the app. However, we have a number of Search/Filter forms that contain a number of controls to allow more or less focus...more >>

I'm sure there's a simple way to do it, I just haven't run into it yet: I just want to duplicate a table record (row) using a stored procedure. lq...more >>

Hello, I just installed a SQL server and playing with it. I was reading up on Collation and tried rebuilding the master database after changing to Latin 1. After the master database was built, I looked at the regional settings and realized that it was still English. When I tried to change the s...more >>

Can someone point me in the right direction to solve the following (basic) SQL problem below using SQL Server: Let's say I have a table like this that lists people's likes: CREATE TABLE likes ( myname VARCHAR (60), travel BIT, eatingout BIT, disco BIT, swimming BIT); Let's say I ...more >>

I know formatting should be handled in the client app and not in SQL Server but this is the situation I must develop in so any help would be appreciated. I'm running the following in Query Analyzer USE Pubs DECLARE @MessageSubject VARCHAR(50) SELECT @MessageSubject = 'Report' EXEC master....more >>

Hi All, I'm converting some stored procs from Sybase to SQL Server, and I'm having serious problems with Null comparisons. When I run the code below on Sybase, it returns both rows. When I run it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in the code (and on the sessi...more >>

I have 2 questions. I am trying to write a stored procedure to update a table. I am trying to pass a variable that represents the name of the column/field and another for the value that I am changing. For example: @FieldName VARCHAR(100) @FieldValue VARCHAR(100) AS UPDATE tblTHETABLE SE...more >>

Hi, I have got couple of questions about unicode settings for database and I would be thankful if someone can clear the same for me. Here I go - a. How can I find out whether unicode has been initialised for an existing SQL Server database? b. If unicode is initiated, would there...more >>

I'm having a bit of a SQL problem and I can't figure it out. I have a working solution, but it could be better. I've got two tables, tblInspections and tblViolations. Each inspection record has an associated establishment id. Each inspection record can have multiple violation records as...more >>

I have two SQL servers I am dealing with. I have some tables in one database on one SQL server that I want to transfer into a database on the other SQL server. I want to xerox copy the tables including all primary keys, identities, defaults etc. I used the export/import feature in Enterprise Man...more >>

Hi! There's a plenty free space on the drive, while the "unallocated space" column shows negative number when I use sp_spaceused. The database file has been setup as "automatically grow" already. Can anyone tell me what's happening? Is this going to hurt anything? Thanks. Saiyou...more >>

I have been searching for a way to associate a description with a column name. I have come across multiple posts regarding this question. Problem is that I have seen two different answers. One post mentioned using the undocumented system table named sysproperties while other posts mentioned us...more >>

Hi, I am experiencing strange SQL server behavior, The table has an index on (filed1, field2) - which is primary key. if I do select where field1 = 'a' and field2 like '123%' runs fast. if I do select where field1 = 'b' and field2 like '123%' runs fast too. if I do select where field1 in (...more >>

Does anyone know of a freeware version of the MS Enterprise Manager. I prefer one with a import/export wizard included. I do not mean a general ODBC manager, but one specific for SQL Server. Thanks! Jorrit ...more >>

Hi, I have a table of Locations around the country. My system produces reports based on these Locations. I also have a table containing Brick Codes e.g. Brick Post Code AB51 AB51 AB52 AB52 AB55 AB55 AB56 AB56 AL01 AL1 AL02 AL2 AL03 AL3 AL04 AL4 How can ...more >>

Okay, so I have a problem and I would be REALLY grateful for any assistance anyone can offer because I have found little or no help on the web anywhere. I want to access and do joins between tables in two different SQL db's on the same server. Heres what Im dealing with. In one database re...more >>

I've sucessfully installed Apache with 1.3.29-win32-x86-no_src. I've installed mySQL(3.23.58) but, to quote my textbook: "Before the mySQL database server can be started the configuration file C:\MySQL\my-example.cnf must be copied to the root directory at C:\ and renamed to mycnf.cnf. The opt...more >>

Hi, We have a SQL server db backend for our ERP system. I have written a Crystal Report to extract data from one particular table called itemspecbomw (c.30000 records) which contains amongst other things Bill of Materials costings. When I run the report I know that some rows are missing as wh...more >>

I get an error every so often with a DTS package on SQL 7. Error as follows. The connection is currently being used by a task. The connection cannot be closed or re-used. This doesn't happen all the time and I can sometimes (more often than not) get the DTS package to complete in it's enti...more >>

Hi, How do I find the max row size for a particular table? This was the error I recieved while execting my proc with the relevant i/p I need to: "cannot sort a row of size 8192, which is greater than the allowable maximum of 8094" I also understand that the max bytesize of a row is 806...more >>

is there a way to encrypt a propriatary database so that it may not be tampered with except for those who hold the key? ...more >>

Hi! When I make a connection to the sqlserver database, everything work fine. Recently I connected to a huge db 1.4 GB. The querys are executed with the same time however making a connection is taking a long time. I am using the following connection string. My_CONNECTION = "Provider=SQL...more >>

Hi, I have been trying to download the latest update to SQL Server BOL for quite some time without any success. I have tried downloading around 3-4 times. Every time (size of msi file would be around 25 KB) I try to extract the msi file, I get an error message to the effect that 'it is not a...more >>

Is there a way to copy/duplicate and rename a database on the same server? I need to make a copy for another application. I appreciate any tips or advice you can provide! TIA - Rob ...more >>

[crossposted] Hi, I wonder if anyone might lend me a brain. I have a stock database to build that covers over 1000 products, which might be said to exist in around 50 product families. Obviously, just to be awkward all the types of stock will have different attributes. So one product mig...more >>

Hi Guys Upon creating and running a view in the SQL Svr 2000, Enterprise manager that takes more than 30 secs to return, I get an 'ODBC SQL Driver Timeout expired'. I have tried resetting every timeout I can find but it makes no difference. Its damned irritating as this is an ENORMOUS DB and m...more >>

For some reason in Enterprise Manager for SQL Server 2000, I cannot put the following line into a trigger: select * into #deleted from deleted When I hit the Apply button I get the following error: Cannot use text, ntext, or image columns in the 'inserted' or 'deleted' tables This seems like...more >>

Suppose I have an MS access database with query1 and query2. Query2 uses query1 something like this: Select table.* from (table left join query1 on field1=field2) Now I want to make a change in query1 to change the behaviour of query2. But this cannot be done in a live environment because I ...more >>

I am writing a function which I hope to use as a column value in a select query. The function recursively walks a taxonomic heirarchy, extracting the name for an organism at the taxonomic level requested by the user. I'm having trouble figuring out the syntax to call the function from itself (se...more >>

I've designed a very basic SQL Server Stored Procedure that I'm using via a Visual Basic 6.0 front-end file in retrieving records into a data entry form. I can't for the life of me get the records retrieved via the Stored Procedure to be edited. I can't even run the stored procedure in MSDE...more >>

Hi (sorry this has turned into a bit of an epic...thought I'd as the experts!) SQL 2k, sp3a. Dual xeon 2.4. 2 gig ram. everything on a 3 disk raid 5. Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Enterprise Edition on Windows NT 5.0 (Build 2195: ) Problem seems to be some extended ...more >>

Hi All, I want to catch the next MSSQL error in my SQL code with following continue calculations Server: Msg 17, Level 16, State 1, Line 1 SQL Server does not exist or access denied. If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQL will not continue wit...more >>

I am interested if anyone can point me to "best practices" regarding the level of administrative authority a SQL Server database administrator should have. Alternatively, I'd be interested hearing what level of authority on the server you/your SQL Admin has. I have been told that in order to do ...more >>

Hello, How can I create a new user with password for the MS SQL - Server 2000 (and the MSDE) with a SQL - Statement? I use Borland Delphi 5 with the ADO - Components! Thanks. best regards. Stephan Jahrling...more >>

I've installed SQL Server 2000 with Arabic collation during the installation. Yet, I'm not able to search for Farsi strings. What should I do?...more >>

Hi guys, it's nice to join the forum. Can someone can help me? After 2 years of using a program, i suddenly got this error. I tried re-installing SQL but im still getting the message. Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server...more >>

is there a freebie utility/database that could benchmark a sqlserver? Would like to try out different disk configurations (raid, swapfile location, cluster size, etc) for getting the best results. Or would I be better off writing a load script to stress the server on a copy of my own database?...more >>

Take the following table ID Shipment ETA Updated 01 123 3/1/04 2/12/04 02 123 3/2/04 2/13/04 03 123 3/1/04 2/14/04 04 154 3/2/04 2/12/04 05 456 3/1/04 2/17/04 06 456 3/1/04 2...more >>

Hi, I have Access 2000 and now I'm converting database to Sql Server 2000. I have .adp in Access 2000 and database already in Sql Server. In old mdb application I have query which handles mysysobjects file (reading forms and reports information). How could I do it in Sql Server environment ? ...more >>

Hello! We have following situation; network with 100 users, aplication developed in Access, user DB deployed on SQL Server. Is it better to create query inside aplication (with code) and then pass it to SQL Server for execution or is it better to have all these queries save...more >>

To support remote transaction on SQL Server i have configured user options to 16384. However if i make changes in other properties of SQL Server, user option setting reverts to its original value. I would like to know 1. Is it possible to protect this setting so that change in other properties d...more >>

Hi, We have encountered deadlock on a table which is used to generate sequential numbers for different categories eg typical entries Category Value TRADE_NO 1456 JOB_NO 267 ..... The applications reference the relevant c...more >>

Hai Is it possiable to close the current connection screen in the query analyser , not to close the query analyser. With Thanks Raghu...more >>