With a variable declared and its value found by a select statement Say DECLARE @MyVar INT Now find variable Myvar in MyTable based on a foreign key value for instance Select @Myvar = Myvar From MyTAble where ID = (select IDFK from deleted) Now I want to assign @MyVar to a field in the curr...more >>

I am making a web site of a magazin and when i try to store data in the table ,the data was larger . and i asked how can i store the large text in the table and if it possible to store text file?...more >>

I face a problem of how storing alarge text (such as text page)in a table of sql data base...more >>

Hi All, The following string comparision fails: declare @prodCode as nvarchar(50) select @prodCode = 'ABZC001' if @prodCode NOT IN ( 'ABZC001,CBQA03,FG0023') BEGIN .... .... END The code within the BEGIN...END block is executed! Any suggestions? Thanks kd...more >>

Hi All, I have an update cursor; the query for the cursor selects all existing products where the closing date is null, from the products table and updates the closing date for the old product and inserts a new record for the new product. The problem I am facing is that, the cursor is refr...more >>

In one stored procedure i create temporary table and fill it with data, after this i call to other stored procedure to which i want pass table as parameter. How can i do it ?? -- Message posted via http://www.sqlmonster.com...more >>

I'm naming my constraints (check and indexes and such) as follows: Unique Index: IX_tblCustomers_CustName_Unique_ERRC5001ERRC_ Check Constraint: CK_tblCustomers_CustName_Size_ERRC5002ERRC_ The constraint is: (len(ltrim(rtrim([CustName]))) >= 5) Then I have a table of codes as follows: E...more >>

Hi, I am planning to build a search engine which works like GOOGLE. Can anybody send me a sample database .. regards Lara ...more >>

How to check . Suppose i have sp that one of it's parameter is Xml CREATE PROCEDURE MyProc @Xml nvarchar(4000) AS ************************************************************************ before i will get the information from this xml i want to check it throw XS...more >>

Hi, I execute this command in CMD: bcp pubs..authors out c:\test.dat -T -n I get the following error: Code page 720 is not supported by SQL Server. Unable to resolve column level collation. I'm using Personal Edition on Win XP Prof. I have not modified anything in pubs database. Any help...more >>

Hi, is it possible with MSSQL 2000 to: - Make roles in the database eg "user, manager, admin" - Connect these roles with Windows User Groups (user, manager, admin) in Enterprise Manager - Let User log on to the Database with integrated authentication - Would SQL Server match the groups and ...more >>

I have the following scenario I hope someone can help me with.. I have a database with a few tables and two major queries (stored procedures). The principal data object is a queue. Query #1 is responsible for locating a row in the queue and updating its status. Status may be Available, Pen...more >>

I use CHR(10) + CHR(13) to add CR/LF to my data, but it shows a special character like a vertical bar. any idea about this? ...more >>

I have a web application which connects to Microsoft SQL Server 2000 through JDBC-ODBC Driver. The application server is JBoss and I am using connection pooling. When the application connects to the database it provides userid and password which are 'sa' and 'password' respectively. They are con...more >>

I'm trying to set up a table that will convert default SQL error messages for check constraints into friendly front end messages. What I'm having trouble with is how to pick apart the default message so I can do this. Is the error stored anywhere that I can look at it's parts? Any suggestions on ...more >>

I'm confused by the difference between sp_createstats and sp_updatestats. I have several read-only databases. After these databases are re-built I need to make sure the statistics are up-to-date before they are made read-only. I've read that we cannot always rely on the auto update stats ...more >>

Inside of a stored proc, how would I issue a Contains using a variable and a wild card, this dosent work declare @myvar varchar(50) -- this would be an imput param set @myvar = 'hello' -- this would be an imput param select whatever from CONTAINS(fieldname, ' " ' + @myvar + ' *" ') than...more >>

I'm trying to eliminate (or at least reduce) deadlock issues. I've already ensured all stored procedures are accessing tables in the same order and now I am looking at locks and transaction levels. Are row level locks the default for stored procedures in SQL Server 2000 or do I need to issue s...more >>

create table #tab1 ( Cd varchar(10) , CdDate Datetime , id int ) create table #tab2 ( CdDate Datetime , idnum int ) INSERT INTO #tab1 values ('Fox','2005-04-25 14:38:07.000',1) INSERT INTO #tab1 values ('Fox' ,'2005-04-25 14:38:16.000',2) INSERT INTO #tab1 values ('Pen'...more >>

I have a table that has an id field and a description field... Unfortunately the id field does contain dupicate id entries... What I need to do is select each distinct id and then one of the many description fields that are assigned to the id number can anyone help with this ...more >>

CREATE TABLE [dbo].[Jobs] ( [MyLovelySurrogateKey_ID] [int] IDENTITY (1, 1) NOT NULL , [StartDate] [datetime] NULL , [StopDate] [datetime] NULL , [JobTitle] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [etc...] ) ON [PRIMARY] StartDate and StopDate are optional to users o...more >>

I have a SQL statement. SQL sever lets me to save my SQL. I got internal SQL server error while I run it. I think that my SQL is valid otherwise SQL will not let me save the SQL. My object and fileds name should be correct, otherwise SQL server should complain about this. Other than this, ...more >>

i've read lots of usenet and and microsoft support articles about how to remove duplicate rows from a table, but i am trying to modify that logic to delete "similar" rows. for example, consider the following: create table t1 ( col1 int, col2 bit, col3 bit) insert into t1 values (1, ...more >>

In my stored proc I need to check whether a empty or NULL value is sent and check for it in my sp. How can I do this? David ...more >>

What I want to do is to create a Web Interface to allow a User to click a link and execute a Stored Procedure, I think this would be straight forward. What I also want to do is to allow the user to select a check box to have that stored procedure run over night or even schedule the Stored proc...more >>

I basically need to send emails from MY DTS packages and also some of my stored procedures.There is no way of having any MAPI compliant software installed on the productions database server,so I came up with the idea of installing an SMTP server and use the extended stored procedure introduced...more >>

I noticed that there is something in master database called Extended stored procedure which can be dlls? what's this? How can we make our own sps a dll and put it somewhere like this? Thanks ...more >>

Hello, ALTER TABLE tb1 ALTER COLUMN fd1 VARCHAR(80) NOT NULL It's appended at the end, can I control fd1 it's the first field? thank... ...more >>

How can I query a database that checks for duplicate data in a combination of fields. For instance, LastName may have many duplicates but I want to find duplicates of LastName combined with FirstName. Thanks. ...more >>

I am used to storing Null when a value does not exist. I have been told to store "zero length strings" in some cases instead. Fine . . . I do not know how to generate a "zero length string" or a "zero length number" or anything else "zero length." But I suspect it is simple. Can you provid...more >>

I am struggling with developing a query to handle the following scenario. A transaction table has a foreign key field that stores the customer Id and employee Id for the particular transaction. The rules for this business are such that a retained client is someone who has three consecutive tr...more >>

Is there any way to modify this proc so that: If it recieves a non-negative centre_id the data is ordered by centre_name but with the centre whose id was passed, being first in the the list. If -1 is passed then just order by centre_name. thanks. CREATE PROCEDURE [dbo].[get_centres] @...more >>

Is there a way to show on the bottom of a table, the navigation buttons with the record number of total records like Access for example. thanks al...more >>

I've got this big giant script that drops all my default constraints, drops all indexes, then drops all clustered index constraints, then adds alot of new indexes, constraints, and clustered indexes. I've got it running without throwing any errors now. However, sometimes the database locks u...more >>

I have been working on creating templates to have for when creating databases, tables, views, etc. When I do a replace template values on this particular template all values get replaced except for the drop database line. The strange thing is if I replace the values and then do replace val...more >>

Can I perform an if..then on a parameter in my stored proc in the where clause? i.e. create procedure s_test @param int as select table1.a,table1.b,table2.f from table1 inner join table2 on table1.a = table2.a where a = 1 and -- pseudo code-- if @param = 1 then table1.b = 2 else ...more >>

I'm an asp/.net programmer and am having a problem using the like operator. I'm searching Title and Description columns in a Products table for terms like Jersey, Helmet, Hat, etc. When searching these fields I'm using the Like keyword as follows: WHERE Title Like '%' + @SearchParam + '%'...more >>

HELP!!!! I am using SQL in Access and need to pull all of the records that don't match in the key field. The key fields are the same name in both tables and I have built a relationship on a different field. Both tables have some matching records and some non matching. I want all of the record...more >>

I am calling a stored proc from a DTS package and the proc passed an output parameter into a global variable in the package. The procedure that is called, calls another procedure 4 times with different input parameters. This is the procedure that actually produces the output that I need. Th...more >>

I am trying this update query to update the table(Allfile) with a daily data feed we get. Both Table structures are identical. I get the following error: Server: Msg 8152, Level 16, State 9, Line 1 String or binary would be truncated. The statement has been terminated. Here is my updat...more >>

Hi, I'm trying to get a web page (on the company intranet) to connect to my sql server through windows authentication. When the script executes how do I ascertain what the user is going to be? Thanks Adam...more >>

how come that if I put in this value tblMyTable.currency is a decimal(8,4) insert into tblMyTable(currency) values ('0.4568') I get this in return .4568 or is this normal?...more >>

Dear gurus, We have created a couple of tasks (using vb 6 a sql2000 which create a olap cube in our side, here in Spain) That's fine and it is working properly but from now on that cube must be processed in another country. I suppose that there are two solutions for that: -Implement remot...more >>

Q1: When creating indexes, which is the best way: 1. Create one index for each column which needs to be indexed? 2. Create one index, which contains all the columns, which need to be indexed? Q2: Does indexex affect the datafile (physical file) size a lot? ...more >>

Back to our debate from two weeks ago. I left with a feeling that the sProc I was using to drop tables from a backup database if they were older than 31 days and then create the next set of backups on the fly with a suffix added to the table name in the format yyyymmdd was actually not so bad....more >>

I am attempting to make an update on a remote server table with a join on a local server table. The SQL looks like this: update [Remote-Server].RemoteDB.dbo.RemoteTable set Col1=LT.Col1 from LocalTable LT inner remote join [Remote-Server].RemoteDB.dbo.RemoteTable RT on LT.istringId=RT.istr...more >>

I receive the following error when I tried to add a "12:00" value to a SMALLDATETIME column: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error....more >>

Hi, I have a table with only one record in it looks someting like this Field Titles : Name Address PC Data : Bert A House CL4 Is it possible to reformat this so that it looks like Name Bert Address A House PC CL4 Usually I am doing this the other wa...more >>

How to debug user defined functions??? I mean i can't put a PRINT ... OR SELECT ... to check something . Thanks -- Message posted via http://www.sqlmonster.com...more >>

Hi I have a tre table setup in order to support dynamic attributs of users: user table: | *userid* | *username* | 1 | joe | 2 | james attributetype table: | *attributetypeid* | *attributename* | | 1 | height | 2 ...more >>