Hi experts, recently i download the SQLExpress 2005 version to do some testing, when i try to run a new feature which all ROW_NUMBER, but SQL give me error 'ROW_NUMBER' is not a recognized function name. i wondering after install the 05, we need to add-on some package to...more >>

I have a very intensive sproc running on a ss2k instance that starts throwing intra-query parallelism errors. If I include OPTION(MAXDOP 1) after each query in the sproc, I no longer get the errors ... But the server only has 1 cpu ! What could be going on ? ...more >>

I have a stored procedure in MS SQL 2000 Server to insert a record in a table. I am using ADODB in VB, I get the message that the parameter is not supplied and it is expected but I have supplied all the parameters. this is the stored procedure: CREATE PROCEDURE Insert_Test @A1 AS CHAR(1) , ...more >>

I've created a DTS task. I want to write VB program to execute the DTS task. How can i do it ? ...more >>

Hi all, I have a query which I am trying to optomize by running Index Tuninig Wizard against it. The query is: declare @al_Dept T_Code, @adt_Start_Date DateTime, @adt_End_Date DateTime, @al_Language T_Code, @v_noofseconds T_Code set @al_Dept = null set @adt_start_date = '5-8...more >>

Good Evening, I have a pivot query that will return the names of the columns in a columns table. However I can't seem to get it to return them all. Is there a statement I can put in the for that will return all the columns (good) or a subset say like where PK greater that 30(Best)? Here is wh...more >>

Hi all, I have a PERSON table that stores personal info, and a RELATIONSHIP table which just has a list of relationships. Now I want to be able to define a family by means of a crossref table between PERSON and PERSON using the RELATIONSHIP. PERSON PersonID Name Sex Birthdat...more >>

I have two tables in seperate databases that I need to do a join on. I have table [ASIUTILS].[DBO].[DEALERS] with a primary key on [ACNO]. I have table [ASISTATS].[DBO].[UPDATESUBMIT] with multiple records for each account that can be joined to [ASIUTILS].[DBO].[DEALERS] on field [ACNO]. I...more >>

argh... A simple update query, lots of records (38 million), fast server, this is the only thing executing on the machine. Tbl1 has a primary key and no indexes. UPDATE Tbl1 SET FldToUpdate = #Tbl2.FldToUpdate FROM #Tbl2 WHERE #Tbl2.PrimaryKeyFld = Tbl1.PrimaryKeyFld The que...more >>

Help! I've tried to retrieve unicode string from the file system. TextCopy doesn't work, of course, so I just took standard output from the utility that generated it using xp_cmdshell. The 'output' column of xp_cmdshell, however, seems doggedly single byte. So, I have all these lines with...more >>

I'm interesting in learning more about the capabilities of SQL Server 2005 integration with POP3 mail servers, but I'm having difficulty finding any good articles on the net. I'm especially interested in (1) how the server is configured to retrieve emails from a POP3 server, and (2) how the...more >>

I am upgrading to SQL Server 2005. My application uses xp_cmdshell for a number of things and I need to non-sa users to be able to excute it. I have tried turning it on via script and via Surface Area Configuration + Management Studio. When I connect using an account that is in the sysadmin...more >>

Hi, I have a problem about "userlogs" table . this table contains some information about users actions .The table has nearly 800.000.000 record in it and ~100Gb data , 50GB index. i have tried to copy some data ( like 1M records) to a temporary database, i couldnt manage it This is th...more >>

Hello All, I would like to use the UPDLOCK hint on an SQL query to prevent concurrent connexion from selecting the same records in a table. This table will contains jobs order, and the table will be processed by many clients at the same time. The goal is to SELECT a record with the TOP 1 clau...more >>

Dear Friends, I am getting problem in the following statement. Please help. ALTER PROCEDURE dbo.Defaulters (@Stdt smalldatetime) AS declare @CNT int select @cnt = 1 FOR @cnt <= 7 Select dbo.Employee_Master.Employee_Name @stdt =@stdt +1 @cnt = @cnt + 1 Next ...more >>

I have a complex query I use to populate reports. I need to move the reporting environment from actual production database that gets updated frequently. I have converted the reporting query into a view that pulls information from many tables with complex joins In my reporting databases, i have...more >>

I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column "ID". I want to get the Max(ID) across both tables. I know I could declare variable(s) and execute separate query for each table, but I would prefer to keep it to one, simple query. I tried: select Max(ID) from (sel...more >>

I would like your help with the Foreach loop container. Boy, am I having issues with using it for looping through an ADO.NET dataset or what!?!? My control flow has a data flow task that is executing a Data Reader task (creating a .NET dataset for me). Now I go back to the control flow and add...more >>

Hi, i want to write an 'universal' audit trigger in C# or VB. For that i need the tablename. But i found nothing in SqlContext.TriggerContext. How can i get this tablename? thanks, Helmut...more >>

I have my own SqlUserDefinedType, for example MYTYPE. It has implemented INullable, IBinarySerialize. I have SqlFunction, that returns next possible MYTYPE Value, as like as an identity. I can use it by this way "INSERT INTO myTable(id, description) VALUES (GetMYTYPE(), 'this is the descrip...more >>

Hi, Does any one know of a script that can autogenerate test data. Does not have to be real intellagible data at this stage, just to fill tables. Thanks Robert ...more >>

Using SS2000 SP4. We're using a .NET application. What permissions should be needed for a user to fire a trigger. Ideally, I wanted the user (userWill) to only have execute permissions on the stored procedures and select permissions on the views. But all views, stored procedures and triggers a...more >>

Environment: Two SQL 2000 servers, sp3a Windows 2k ADV Server 1: Has a linked server to Server 2. Linked server security is set to use an remote id with system admin level rights. An Trigger is created on Server 1 user database table to make a call accross the link server and start a ...more >>

I am making the adjustment from SQL Server 2000 to SQL Server 2005. In the past I have created named ranges in Excel that look like data tables (header row followed by multiple data rows) and used DTS to create database tables in SQL Server 2000. It has worked for several years. Now, using...more >>

Is there any value to having multiple DECLARE sections within a single stored proc/statement block? I understand the value of multiple DECLARE sections that are related to local variable within specific statement blocks. However, in working with existing SP's, I run across the following on a...more >>

I'm creating an application with user accounts and I will be using MD5 to hash the passwords and salting them with the user ID first (as seen at http://aspnet.4guysfromrolla.com/articles/112002-1.aspx). Does storing the user ID as plain text in the database compromise the intended encryption? ...more >>

I have recently seen the OPENQUERY function in a stored procedure which was used to INSERT values into a table on a remote server. However, I normally use the sp_executesql stored proc. Can any one shed some light on which method is better and in what cases? The following code is from Micro...more >>

Hi everyone! I am a computer engineer and have recently upgraded to .net 2005. With new data components available, I have decided to write a simple database application in order to get familiar with the new aspects of ..net 2005. Here is my problem : When I try to update a record on an sql ...more >>

Hi all Maybe somemone needs to know how to create a dynamic crosstab query without aggregate functions. The following sample is dynamic and can generate crosstab data in many variants. I could not figure out, how to insert a dynamic statement in the cursor so this one's still hard coded - sor...more >>

Hi, I can't figure how to do this request: I have a table that contains a field of typ ntext. The records contain the content of html pages, so the string looks like the source code of a html page: <html> <head> <\head> <body> stuff <\body> <\html> what i want to do is to select al...more >>

Hi, I am working as a production support for SQL Server. I dont have access to use Enterpriese manage in this server, Please help me To Change the recovery model of master database using scripts. It will be great if u provide me the scripts Its very urgent !!!!! Thanks in advance Regar...more >>

Hi i need to write a store procdure to return records..... I have two tables both has 35 fields mean copy of each other... one table is called original table and other table called valuetobeused table... i am allow to change data in value to be used table which is fine ...... i need to writ...more >>

I want a stored procedure that wioll attempt to delete a record and if it fails because of a foreign key thats doesnt allow delete cascades and related records, I want it to continue and do an update instread - e.g setting a deleted flag. I have tried using this create procedure doo.dele...more >>

hi can we use sql dynamic query with curosr like set @cur=cursor for exec(@sql) .... some thing like thanks...more >>

What am I losing if I dont back it up ? I have restored main databases without master abnd it works OK, other thanm some SQL Accounts. Does it include the backup jobs, user accounts etc Thanks. ...more >>

Hello all, my main question is i guess, is this the easiest way? INSERT INTO table1 (c1, c2, c3...) SELECT number1 AS c1, c2, c3... FROM table1 WHERE c1 = number2 Just so there is no confusion: I am only working with one table here. I basically want to copy a row where c1 = some_number, ...more >>