I have an .asp page that passes four variables to another .asp page by using an HTML form. A SQL query then runs against an Access database using those four variables. The variables are: "cnty", "rte", "bgn", and "nd". My current SQL statement is as follows: SQL = "SELECT * FROM Constructi...more >>

I found an unusual problem between 2000 and 2005 I haven't been able to decipher from any documentation. The query structure is as follows: select * from tableA a join tableB b ON a.somekey = b.somekey where a.type = 'A' and datediff(yyyy, b.someD...more >>

Hi , I have a query below which I have ported across from Access in MSSQL. The syntax below executes fine under MSSQL 2000 but I do not understand what "FROM TABLEA,TABLEB" does or if I should even be using it in MSSQL The query is below INSERT INTO TEMPTABLE (CAPTURE_DATE, TYPE) SE...more >>

Hey all, I have a table with four columns representing different combinations of names. I need to get rid of any duplicate entries based upon the combinations of the names, regardless of the order. For example: ID Col1 Col2 Col3 1 Joe Jane John 1 Jane Joe Joh...more >>

Hey Sql Server Express / Sql Server 2005 Below is the source code of a SP I have in my webproject (ASP.NET 2.0). As you see from the source code the SP takes in a parameter containing a comma separated list of tags. The SP loops through this list of tags and insert them into the database...more >>

I'm issuing a RESTORE DATABASE command from an ASP webpage using the ASPNET account. The database restores successfully and the database owner is listed as ASPNET. However, if I issue the sp_databases command the newly restored database is not listed. If I login with the sa account and issue ...more >>

Hi; im pretty new with T-SQL, i dont know where to start with this.... i need to create a trigger for INSERT on the table DETAIL, i need to = perform various operations before the insert can be done. 1.. i need to find an autorizatiom key on AUTORIZATIONS table before = each DETAIL record...more >>

Hi There, I would like to create temproary table on the fly using exec, is it possible to create like this exec('Create table #temp Field1 int)') Thanks Ganapathi ...more >>

how does ado.net SqlDataReader.GetString() know which encoding to read the data into a string as? Does sql sever set this at the column data type level, server wide encoding setting, os encoding? ...more >>

Using ASP.NET 2.0 with SQL Server 2000 Inside my dropdown list box I am using an SQL DataSource SELECT RTRIM(c.Name_First) + ' ' + RTRIM(c.Name_Last) AS Contact, c.phone As PriPortPhone FROM mppaContacts c Now I am trying to UPDATE the table name mppaCont...more >>

Hi all, SELECT [businessID] FROM data WHERE [businessID]IN (select distinct([businessID]) FROM data) AND [country]='UK' GROUP BY [businessID] HAVING COUNT(*)>=1 The above query works - it returns a correct list of business IDs. The problem I'm having is that I need to return more then ...more >>

Hi, SQL server 2000 SP4 + WinXP I get the following error on a for a specific query in SQL Query analyser and a corresponding error in VB UPDATE tbProducts SET [ProductDescription2] = ' ... faily large text goes here total number of characters is 1795... ' WHERE [ID]='15' there error ...more >>

For some reason I have a hard time wrapping my head around joins. Here's what I'm trying to do. I have 3 tables names Type1, Type2 and Type3. The tables don't have any relationships established between them but the all have a common field named Account. I want my result set to cont...more >>

It is the easiest thing you might even think about it. I'm trying to select Loans_Table.BORR1 equal list of names I have (name1,name2,name3, .....) and loans_table.date_funded is not null. Where Loans_Table.Product is one of list (product1, productr2, product3,....) How can I write it in sql to ...more >>

So, say I have a situation like create table #mable(mid INT, token nvarchar(16)) INSERT INTO #mable(0, 'foo') INSERT INTO #mable(0, 'goo') INSERT INTO #mable(1, 'hoo') INSERT INTO #mable(1, 'moo') And I want a resultset like 0 foo, goo 1 hoo, moo I know I can get foo, goo, hoo, moo ...more >>

Hello, I want to make a report about an company that has more departments in differents countries. I want to get the SalesVolume in each country and an additional information about SalesVolume of the department in that country (example:DE 300 Department1-100;Department2-200 ) My problem...more >>

I need to call 2 stored procedures and return only 1 result set. How can this be accomplished? meaning: I have a proc that calls a proc with a start time and returns that data, then I call it again with an end time and returns a result set. I need to combine those two results into one. i...more >>

I'm using mssql server 2005 Ent. I have five tables with two columns that are of type: - varchar(250) - varchar(max) There are other columns in these tables but the above two are always there. What is the best way to look for keywords in all of these tables but only in the above two colu...more >>

Hi All, I have the function as defined below: CREATE FUNCTION dba.DetMonthly() RETURNS @detMonthlyTemp TABLE ( busyHour datetime NOT NULL, ResId int NOT NULL, DetId int NOT NULL, DetValue float NOT NULL PRIMARY KEY CLUSTERED ( busyHour, ResId, DetId ) ) AS ... It is ok without t...more >>

Ed just wanted to clarify a point to you. Table variables are not "stored" in the tempDB. They are in memory data structures that are not persisted to the disc except maybe during paging. This is why operations against them are not logged and you cannot reset the identity column. What...more >>

I have a 2000 SP4 server that I am wondering if it may need more RAM. But the truth is that I don't really know for sure, because I've never found what I thought was a really good test using perf mon for finding if you need more RAM. I've read links about comparing Target memory to Total m...more >>

I can logon to our SQL 200 Server fine, and so can the applications. However, when I try to modify this one stored procedure (and only the one proc), it gives the following error: "Server: Msg 18452, Level 14, State 1, Procedure XXXXXXX, Line 84 Login failed for user '(null)'. Reason: Not as...more >>

I have a temp table which i'm populating with a cursor thus OPEN c FETCH NEXT FROM c INTO @client_uno, @matter_uno --Fetch next record WHILE @@FETCH_STATUS =3D 0 BEGIN -- populate rate table INSERT INTO #r (EFF_DATE,RATE,MK_PCNT,GROUP_TYPE,MEMBER_TYPE,ALL_OFFICES,ALL_DEPT,RATE_LEV= ...more >>

Hello all, I have a table that I've just ran DBCC SHOWCONTIG ('Containers') against, the scan density is coming out as 20% which I understand is bad, as it should be above 75%, now I've dropped and rebuilt all 4 indexes, re ran the above and it's still coming out at 20%. Why is that and how...more >>

I am losing about 70% of my updates with no exceptions thrown. The platform is a multi-threaded app on a single cpu machine with the db on a 2-cpu machine. I am using the following C# syntax: Command.BeginExecuteNonQuery(null,null); Note the updates are simple sql updates, all of the sa...more >>

I am trying to select distinct columns and add a non-disticnt column to my list. This query works great in finding distinct records: SELECT DISTINCT firstname, lastname, middleinitial, address1, address2, city, state, zip, age, gender, FROM table ORDER by zip However, I hav...more >>

I used sp_spaceused on one of the table and here is the output. name,rows,reserved,data,index_size,unused CreditCard_Protect ,8041839,674793648 KB,665420648 KB,5219808 KB,4153192 KB In the data column, I see 665420648 KB which turns out to be > 600 GB which is not true. Actual size of the t...more >>

Hi, I need to insert data from Excel to SQL server. I can't use SSIS, because many reason on of them is to have an oline Process. My question is to know what is the best way between the 2 following solution: 1- To insert directly data with an insert wich is running after the user press a but...more >>

Hello - I am preparing to install SP2a. In the readme file, they indicate "If the autogrow option is not selected for the master and msdb system databases, these dbs each must have at least 500 MB of free space." I have not touched the original default installation of msdb. The data po...more >>

hi i am trying to write some SQL to work out the Direct Debit date from a date i.e date 12/01/07 so the DD date will be 01/02/07 as all dates <=15 = 1st DD date and all date >16 = 15th DD date so far i have this RIGHT('0' + CONVERT(Varchar(2),DAY(Start_Date)), 2) + '/' + RIGHT('0' + CON...more >>

Hi, Does anyone know why after backing up the trasnaction log (from ssms) and having checked the option for truncate transaction log the transaction log still stays the same size? My transaction is on Full recovery mode. I know what I can do to shrink the log, ie truncate and then shrink b...more >>

DefectTable DefectID DefectName DefectInjectedByIDs 12345 Def1 1001,1002,1004 12346 Def2 1003 12347 Def3 1003,1004 TeamTable TeamMemberID TeamMemberNa...more >>

Hi, I have configured Log shipping in my sql server, at the same time I want to use 3rd party tool for backup of same data base. I want to know is it possible to backup the logs and database from secondary server without affecting the Log shipping feature and without any data lose too? Or...more >>

Friends, I have plan to store application passwords in there database. So how i can Encrypt or decrypt our password. Is there any other way to store the password. Regards Rahul ...more >>

i have this sql in a web page: mySQL= "INSERT INTO precos (ano,nm_mes,n_semana,cod_casa,inicio,fim,preco)" mysql= mysql & " values (" & datepart("yyyy",dt_inic) & "," mysql= mysql & datepart("m",dt_inic) & "," mysql= mysql & n_semana & "," mysql= mysql & request("cod_casa") & ",co...more >>

Hi, sql server 2005 sp2 If I use the new data type varchar(max) to store data that is over the size of 8000 chars does sqls server uses UPDATETEXT behind the scens? Thanks, Yan ---------- ...more >>

Hello, I have tried many ways to overcome the following problem and can't seem to nail it, so any help would be much appreciated. I have 2 event tables that both have start and end datetimes and an event code. The start and end datetimes in each table can overlap. What I need to do is to...more >>

SQL Server 2005 express. To automate the backup process, I am using SQLCMD along with windows schedulers to do log/differential/full backup. At 12:00 AM, I do full backup, differential backup at every 4 hours (excluding 12:00 am when I do full backup), and log backup every 30 minutes. Ever...more >>

Hi all, I am trying to add tracking capabilities to a messaging system we have. I am struggling with the design, with a scenario which is pretty common I would think. We have 2 databases, one is the management database with all the configuration values for the system. This is modified by ad...more >>

I need to x-lock a specific record, so as to prevent other transactions from reading or writing. This is what I'm doing: BEGIN TRANS SELECT Value FROM MyTable WITH (ROWLOCK, XLOCK) WHERE Id = 1000 ... ... ... ... ... ... COMMIT I try this in SQL Server Management studio with two di...more >>

I have SQL 2000 and a number of PDF files I need to load into a database. Is there a way to point DTS (or TSQL) to a folder, and load everything with an extension in that folder into a table? I was going to create the field PDF_file as either Binary or Image. Most of the PDF's will be und...more >>

Hi, I have set up a Linked Server between SQL Server 2005 Express edition and Oracle ver. 10.2. When I do a select from a table in the oracle database that contains a column with a data type of NUMBER, I get this message: The OLE DB provider "MSDAORA" for linked server "MSORACLE1W" ...more >>