Is it possible to difine a new global variable like @@MYVARIABLE accesible from all my stored procedure in my catalog? Thanks...more >>

Eg: Create table test (col1 char(20)) insert test values ('test') insert test values ('test1') insert test values ('te1st') insert test values ('tes') Basically I want the output to return those values that do not contain a number in the entire value.. So the output from above shou...more >>

Hi! I'm trying to figure out this convert, never did one before. The SP I have I have I would like to add to a value. If 50 is in the credits column, and the new value is 25, then it should update to 25. Here is the SP. CREATE PROCEDURE dbo.NuCred (@UserID varChar(50), @Credits Money )...more >>

I have problem with indexed view: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT' When insert, also some other options could affects. I can't change the server properties for all required options because it affects also other databases which are not...more >>

Is there a manual or code way to copy stored procedures from 1one sql db to another without opening each one, copying, creating a new SPROC in target db and pasting? Can DTS accomplish this? ...more >>

SQL 2000: OK, many problems with setting Identity_Insert on and off on a linked server. One problem is using the set command and getting the error "contains more than the maximum number of prefixes" when I do linkedserver.database.dbo.tablename - dynamic sql does not seem to work well at a...more >>

Hello, I am trying to query a NTEXT field for a 4-character string and return only those records that DO NOT contain this string. I have converted the NTEXT field to VARCHAR for this purpose, but the query still returns records with the string. (NOT(UPPER(CAST(A.COMMENT AS varchar(8000))) ...more >>

CASE(@CustomText) WHEN NULL THEN 'Status changed to ' + @StatusDescription ELSE @CustomText + ' ' + @StatusDescription END Now, @CustomText is either null or has value. WHEN NULL does not work. How do i get it to work? ...more >>

Hi, I need to do the following. 1)Query a locally stored MS-Access table based on date. 2)Create 2 temp tables in SQL Server 2005 express edition(installed on local machine) called #yesterday and #today(which contain data for yesterday and data for today) 3)Compare each row and each colu...more >>

how do you deal with case null? CASE @test WHEN NULL THEN ........... ...more >>

I want to turn off constraints on all tables on a linked server using: "SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'" Should I make a connection to the linked server, and/or use OpenQuery to pull this off? ...more >>

Hi, I need to access a MS Access table from SQL Server 2000. Basically this is what I am trying to do. I do not have rights to create anything on the server except temp tables. So I read from the server's SQL table and copy data to MS-Access table on my hard disk. I append data to the M...more >>

hey all, what's the best way to express this in a query? for each employee take the salary and determine which range the particular salary falls in. for instance: 40k falls between 35-40k so the category is 1 number of categories are 1-12 thanks, rodchar...more >>

Hi, I have a stored prod that's doing a bunch of BULK INSERTs. The problem I'm having is if one the BULK INSERTs fails, then the rest of the stored proc isn't executed. I'd like it to proceed to the end and then I can take care of the failed section. I don't want any transactions here. T...more >>

I am trying to get the following all into a single line, so i can run a larger Query and have a nice simple table listing all the SQL servies going on and when it was started. -TIA- CREATE TABLE #System_Monitor_Information_SQL_Information SystemName varchar(50) MSSQLServer varchar...more >>

I AM USING MS SQL 2000 INSERT INTO #temp SELECT * FROM openxml(@iTree, 'ComputerScan/scans/scan/scanattributes/scanattribute', 1) WITH( ID nvarchar(50) './@ID', ParentID nvarchar(50) './@ParentID', Name nvarchar(50) './@Name', scanattribute nvarchar(50) '.' ) ...more >>

SQL Server 2000 I have a set of data and I want to build a SELECT statement that will return a single row in cross-tab format. Below is my sample data and desired results. Thanks to anyone who could help CREATE TABLE #TestCrosstab ( Account char(5), SubAccount char(3), Class char ...more >>

Hi, I have a SELECT statement whose WHERE clause depends on a condition. I want the following to work: DECLARE @condition bit .... (some code setting condition here SELECT FullName FROM Customers IF (@condition = 0) WHERE Deleted = 0 ELSE WHERE Deleted = 1 but this does not work. H...more >>

I am trying to clean up a set of a table where there are some duplicate data with different ids. What's the easiest way to eliminate the duplicate rows, for there are several hundred rows and only about 400 rows are unique. Thanks -- bic...more >>

Hi, I have a strange situation. I must run the same script in SQL 2000 and SQL 2005. Basically the script does it: IF THE SQL SERVER IS SQL 2005, IT CREATES A NEW DATABASE AND USE IT, BUT IF SQL SERVER IS SQL 2000, IT MUST DOES NOTHING. USE master GO --// Create the DATABASE20...more >>

Is there a SQL statement that can detect if a linked server exists? ...more >>

Any idea why this is telling me that the field value I want to pass in is an invalid column name? This is for a "Search By" query. I have a drop down of choices and a text box for the value. I need to search for the value entered in the text box in the drop down field. The list is populated...more >>

Hello, I copied a database from our live server to a test server where I could study the database, experiment and so on. I cannot delete/remove a row from the primary table. The table had several relationships, dozens of triggers, a constraint, and was under replication on the live serve...more >>

I have a rather complex looking sql statement and I've been debugging it for about a day now... So if the answer to my question is obvious forgive me. SELECT t.ScanAttributeID, t.ScanSectionId, t2.scanattribute, (select COUNT(*) FROM #dup, tblScanAttribute s1, #temp t1 WHERE #dup.attid = s1.S...more >>

I posted here 2 days ago about finding the most recent row in a one-to-many table. My query for that looks like this, select * from Admissions c join (select RegNo,max(AdmissionNo) As AdmNo from RecordSummary group by RegNo) as RecSum on RecSum.RegNo =c.RegNo order by c.RegNo I would l...more >>

I want all of the columns in a select statement to be converted to upper case. What is the proper syntax for that? ...more >>

I'm trying to write a Query that will Update all the Null fields in Table1 column1 to 'P' and a 6 digit sequential number starting from 000001 including the leading zeros. Can someone help me figure out the correct syntax? So far, nothing I've come up with is working right. TIA Matt ...more >>

SLQ Server 2000 I have a table 'Route' with schema CREATE TABLE [dbo].[ROUTE] ( Carid integer not null, seq_num integer not null, State char(30), City Char(30) ) Sample Data (This table can have any number of records for a given Carid) insert into route values (100,1,'AA', 'City1') ...more >>

Hello group, We are trying to create a dynamic procedure that will allow us to copy all the FK DDL generation into a temporary table, drop all the FK's, truncate all the tables, then recreate all the FK's. I tried searching for a solution, and couldn't find an answer (besides the sp_MSFo...more >>

I have a bunch flat files that I load into a temp table daily for processing before inserting into a mater table. The flat file is in sequential order as follows (Header row then below its detail rows) RecordType Col1 H Some Header Text 1 D ...more >>

Hi, SQL 2000, SP3a According to BOL, UPDLOCK will not block readers. However, if I do the following: 1.) Open Query Analyzer 2.) BEGIN TRANSACTION 3.) SELECT * FROM MyTable WITH(UPDLOCK)WHERE (Col = 'Predicate1') 4.) Leave this window open, with the open transaction 5.) Open new query ...more >>

I need to break apart the following data into multiple records but I am not sure how to write the code. The record identifier is the ; RecordID DataInfo 1 "abc", "def"; "ghi", "jkl" 2 "abc", "def", "ghi"; "jkl", "mno" Data in a new table will ...more >>

I have a file named "posting.dts" in my c:\test\ directory, how do I write a script to apply this dts package to SQL server "server1"? thanks ...more >>

hi, being new to triggers I'm sure this is an easy question for you... I have a table. When a record is updated or inserted I want to check a field value. If the new value matched some criteria I want to send an email. This is what I have so far... ----------------------------------------...more >>

Several stored procs get a string of IDs which are then placed in a temporary table, and later used in SELECT queries to retrieve matching rows from other tables. ----------------- Example: CREATE PROCEDURE dbo.Select_Companies @IDs ntext as begin CREATE TABLE #companies ([Compan...more >>

Instead of explaining my example I took the one that i used to get as far as I am now to demonstrate what the task is I'm trying to complete. Consider the following: CREATE TABLE #teams ( city VARCHAR(20), team VARCHAR(20) ) SET NOCOUNT ON INSERT #teams SELECT 'Boston', 'Celt...more >>

Hi There, I've to alter the default constraint, So i drop then create agains. Before applying this changes to all database, do i need to tell users to log off. or will it work even they are connected to the system -- Thanks Ganesh...more >>

Several stored procs get a string of IDs which are then placed in a temporary table, and later used in SELECT queries to retrieve matching rows from other tables. ----------------- Example: CREATE PROCEDURE ExSp.Init_Companies @IDs ntext as begin CREATE TABLE #companies ([CompanyID] [in...more >>

Hello! I am looking for someone who has solved this multi-million people's problem. EVERYONE seems to ahve this problem. Im a creating a data set and populating it with a call to a store proc. Its a complex stored proc with the end result as an insert to a temp table. Then I do a select fro...more >>

This query run on sql server 2000 but doesn't work on sql server 2005: select * from (select * from inventory where (description like 'Ram%') ) drvtbl where convert(integer,isnull(value,0)) > 273000 This is the table Id description value ------------------------------ 1 Operatin...more >>

Hello all! I'm having some issues trying to convert a varchar string into datetime. What I'm wanting to do is take the string '07/11/48' and convert it to datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is returning 2048 as the year! The function I'm using is: SELECT CON...more >>

Hi, What would be possible advantages of having Identity column as primary key than having another unique column as primary key.? ...more >>

I used the following SQL to extract data from our Firms emplyee database and creates a text file that gets imported into our copy machine controllers. Output lines look like this: A|U|4111|MPLS, PBMS|01|0|| A|U|4222|RIC, PBMS|06|0|| A|U|2036|user, name|01|0|N|1234562| The last number is t...more >>

Hi I'm stuck on a TSQL statement in SQL Server 2000. Basically I want to copy data from CustomerAccountsTable into Contacts table. At the same time I want to retreive the primary key value autogenerated from the newly inserted row and put that value back into the CustomerAccounts table (Co...more >>

I have a long where statement. Is there a way to reference all the fields with one name and then use this further down in the sql statement instead of repeating all the fields? Thanks in advance ...more >>

Hi , I have a web based sql sending data to an internal sql server and then using bulk insert. I ned to ensure record ids are preserved and prevent duplicates. Is it possible to maintain two distinct ranges for primaruy keys in one table? For example I want the web based records ids to start a...more >>

When the SQL 2005 server was set up , the collation wasnt set correctly. A lot of tables have been created by using import wizard from a dev server, I am now getting a lot of errors on all my Queries; Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_Ge...more >>

Hello. I have a big problem which may be easy for some one who did it before. I'm trying to write a view with embedded Cases. Resp = CASE WHEN (case WHEN [FixVndr]is null THEN (case when [Material] is null then [PurGrp] else [MRP]) ELSE (case when [FixVndr]<9999 then [...more >>

Hi, I have a lot of reports on MS Access which consolidate data from various sources using linked tables. I'm planning to move the reports to some other platform (preferrably Crystal reports) and was planning to use SQL server as the target DBMS. To consolidate/fetch data from other sources ...more >>

I'm using MS SQL Server 2000. I have a simple table (Table1): Table1: ID Name PostCode 1 James 12345 2 Mandy 99100 3 John NULL 4 Alex NULL how to build a query that list all Table1 data replace those with PostCode NULL with Postcode '00000' ? Result expected: ...more >>

I am making a program using DB_Library. But now i encounter a question. that is, IN database a char column is presented, and the max-length is more than 256 ,is 400. I get the value using dbdata function, however, the result is always 255. and i know that As strings. Values longer ...more >>

I have been trying to get this to work but am failing can anybody help SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON INSERT INTO bossdata.dbo.DailyOverLimits SELECT * FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=@Server;User ID=@UserName;Password=@Psw' ).Bossdata.dbo.Dail...more >>

Hi, We have some problems with the fact that SQL Server reads "aa" and "Ã¥" as the character. I use the Danish_Norwegian_CI_AI collation on the table. First i'd like to find a good chart of the different collations? Secondly I'd like to know if someone knows a good way a round this? Th...more >>

Hi I'm working with SQL2000, and I want to have a following result I tried it with case clause but I couldn't get the desired result. Table1(code1,date1) I want to check the variable @status if it's 0 then I want to fetch all rows without any condition but if it's 1 then I want the range ...more >>

Sql7.0 sp4 Old N/T 4 machine I have a VB(6) app that accesses the DB thru and ODBC(system DSN) connection. It only uses a few SP's in the code and the rest inline SQL statements. When I turned on Profiler I noticed calls to sp_columns with a TableName as the Apps running. They complain the...more >>

how can i combine and make the the following to a single query? --------------------------------------------------------------- insert into #RegionPeriod select PeriodCode from tblRatesHeader where AllMarket=1 insert into #RegionPeriod select PeriodCode from tblRatesHeader r,tblProductMaster P...more >>