I am trying to set up Access as a frontend to a sql server 2000 database, what I have done is converted an access database to sql server, and I am trying to set up the front end so that the client can still update the database the way they use to do. However, I am running into a wee problem, ...more >>

Out of the 3 tables below. I need to get a tabular report like this First Name Last Name Email = Can you receive HTML email Address = State Zip Tom Thompson...more >>

Hello Is it possible to concatenate a number to a decimal field? I want to concatenate the two digit system month to a decimal field (within a query) but am struggling. Thank you. ...more >>

Hi, I have a VB .NET Application accessing a SQL Server 2000. This application uses stored procedures extensively to access the data. My question concerns the processes on the server. I've been observing 15 - 30 sleeping processes running just after starting my application. Many of thes...more >>

We just moved a database from SQL7 to 2000.I ran sp_updatestats after updating. One table has about 4000 rows, a single column primary key of type t_Ticker which is char 10. A query wants to update several items in one row. A sample is UPDATE tblTable set CharColumn='12345'. Yet even that...more >>

Hi, In simple terms, in SQL2K, I have 2 header tables A and B and a junction table AB. All of keys are defined with the Cascade Delete constraint. Table A has a one-to-many relationship with table B. My problem is that if a row in table A is deleted, the rows that reference it in AB are delet...more >>

I have been browsing the newsgroups trying to find a good solution for this problem. I have a resultset and I need to show that information in pages. I also need to have this sorted by a specific column (Date for example) I found the following solution written by Don Arsenault that works v...more >>

Is their a way to use vbscript code within a stored procedure? -- Message posted via http://www.sqlmonster.com...more >>

I'm trying to run sp_dbcmptlevel @dbname='master' from within a perl script. What I've tried: my $sth=$oa->prepare(qq[sp_dbcmptlevel @dbname='master']); $sth->execute(); while (@row = $sth->fetchrow_array) { #do something; } $sth->finish(); my $output; my $sth=$oa->prepare(qq[? =...more >>

I'm writing a data collection script to gather database and data file information. This information will be used for reporting and analysis. I want to be able to tell what data files are associated with each of the databases. sysfiles doesn't include the database name and sysaltfiles doesn't ...more >>

Hello, I have SQL 2000 with service pack 3a: When I use xp_sendmail with a file attached, it doesn't save to the specified file system folder, SQL 7 does. Here is the query: set quoted_identifier off Declare @StringMonth varchar(5) , @TableNm varchar(30) , @SQL varchar...more >>

Can someone please tell me why i get the above error when i run the is code designed for a web service. Connection is established at design time, eaConn, to sql server database whose table is called myParam: Imports System.Web.Services imports System.Data Imports System.Data.SqlClient Impo...more >>

I am running xp_getfiledetails and am getting an unexpected return, and was hoping for an explanation. I run exec master..xp_getfiledetails 'e:\backups\cms user messaging\ CMSUserMessaging20050206.BAK' and the size returned is -1951854081 Bytes. When I navigate to the file itself the size i...more >>

Hi, As Aaron suggested I tried to use an insert into statement to update a table in a linked server, but when i do insert into SERVER2.database2.dbo.rtable select field1,field2 from localtable query analyzer says that the provider does not contain rtable... i can see the table in the ent...more >>

Hi! I know that varchar is only able to store string data up to 8000 bytes, whereas text up to 2 Gig or so. Somebody told me that you cannot search in text-type fields, i.e. you cannot use LIKE '%abc%' and things like that. I tried it (on SQL Server 7), inserted some short text, and LIKE worked...more >>

I use Delphi to connect to SQL Server 2000 using the ADO components. I'm executing stored procedure that makes some calls to another stored procs. In one of the sub called stored procs foreign key is violated and error message is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY...more >>

Hello, I think i am missing something. This code give's an error.... Can someone tell me what it is?? IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown', DATEPART(yyyy.SalesAnalyse.Verzenddatum)) tnx in advance.. eric ...more >>

I hable that has a ProOnwerID and ProFinOwner, when I create the following view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the ProOwer shows up.. Any Idea why ?.... is it because I have two ID for Clients en same table ? SELECT dbo.projects.*, dbo.employee.Em...more >>

Hi, I'm trying to get the nt_username from sysprocesses put in a table by a trigger. Unfortunately, when I call the stored proc to update the table (which fires the trigger), the nt_username is not being put in the sysprocesses table. I'm using the spid to get that record in sysprocesses. Is ...more >>

I'm looking for a way to generate at least a seemingly random value that I can use for issuance of temporary passwords and passcodes (numbers and letters, not too long). I'd like to do this in the database instead of an outside algorithm. Any ideas? ...more >>

How can I save the EXEC Result to a Variable. For example: declare @myString as varchar(50) declare @myValue as decimal(12,2) set @myString='Select ' + '10-5' EXEC (@myString) Print @myalue <--- Should print 5 ...more >>

StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to now. Set @StartDate = '1899-12-30 02:15:00.000' Select GetDate() now, GetDate() + @StartDate as added -- Result is 2005-02-08 14:11:07.860 ----- 2005-02-06 16:26:07.860 Thanks Murat BUDAK ...more >>

I insert data into a temporary table, alter the table structure, then do an update. However when doing this in a stored proc, I get the error that the altered columns do not exist. If I run the stored proc section by section it works perfectly, however in its entirity it does not run?!?! any idea...more >>

I trying to insert values from a temporary table into a permanent table. The problem is the temporary table has duplicate UpdateTime values (issues with the database used to populate the temporary table) and the UpdateTime is a primary key in the permanent table. Is there a way I can remov...more >>

i constantly select the top 10 rows of a table ordered by date descending order, is there any way to make this query faster? perhaps some kind of index that keeps the table stored in descending order by the date column? ...more >>

Hi, I need to do something like this : SELECT * INTO #ResultsDetails FROM M WHERE [ID], IDExtra IN (SELECT ID, IDExtra FROM #Results) i.e. in WHERE clause I need search for ID and IDExtra which are results from query : SELECT ID, IDExtra FROM #Results Thanks...more >>

I've built a stored procedure where I'm inserting a row into two tables. Both tables have a number of columns - and so I have to pass a rather larger number of parameters to the stored proc. Like follows INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@cParm1, @cParm2, .... @...more >>

Hello all, I have a database design problem I have a hierarchy that includes 5 levels and each level have a table EX : TABLE_L1 L1_ID INT AUTO L1_CODE nvarchar(50) L1_NAME nvarchar(255) TABLE_L2 L2_ID INT AUTO L1_ID INT L2_CODE nvarchar(50) L2_NAM...more >>

Hi I am having a problem using the index tuning wizard. I will capture an SQL Batch using Profiler (SQLProfilerTuning Template). I will then use the index tuning wizard on this, but it moans with the following error "The workload does not contain any events or queries that can be tuned agains...more >>

Hello, I'm doing something wrong with SUM and OUTER JOINS. Table Hours contains Hours_Proj_ID : project ID Hours_Task_ID : task ID Hours_User_ID : user ID Hours_Minutes : time registration Table PTAllow tells me who can use which project/task (fields PT_Proj_ID, PT_Task_ID and PT...more >>

In SQL Server Books Online, there is a great programming example where you can modify the properties of a data pump task to add new transformations. My question is how do you remove them when done? Those transformations stay saved so you cannot recreate them later. Here is the code I am usin...more >>

Hi all, I get the following error when I try to update the table from asp page: Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E14) UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT' I try the same update statement in query analyzer and it...more >>

Hi all, I have one question to upload data to the database. The way I use is the C++ extension for ADO. I create an interface class and bind the interface class with a CADORecordBinding class. And then I use this class to upload binary data to database. I compiled and run the application an...more >>

I am trying to create a new database using a variable name for the database name and then switch to the new database to create a set of tables, views and stored procedures. DECLARE @NewDB varchar(35),@SQLStr varchar(1000) SELECT @NewDB = 'MyDatabase' IF NOT EXISTS (SELECT Name FROM Mast...more >>

Hello, I was wondering what would be the best practice to integrate multiple application-specific objects into one database. The most obvious way would be to prefix the object names for example App1_tblUsers and App2_tblUsers. Is there a better way to ultimately come to some kind of names...more >>

We have a SP that runs every night. It basically inserts, update data from various tables, has a bunch of SQL statements. The total records it is working with are between 6-7 million. But it randomly gets stuck at different places in the procedure. This is a random happening. Some days are good,...more >>

hi, how to cast or convert an nvarchar into a datetime ...' as in: (see WHEN 'datecreated' then Cast(d_date_created as datetime)) ----------------------------------- ALTER PROCEDURE [dbo].[SelectContactListTEST] @activeSearch bit, @activeSearch2 bit, @hiddenSearch bit, @or...more >>

Hello all, I need to generate a result based on 2 fields: 1. RelationshipID & 2. MaritalStatusID The above fields are not embedded in the application as a mandatory check box so some of the values maybe empty. I need to get a list of all the members who have a RelationshipID = 2 a...more >>

Hello, My Sql database server have 8 GB HDD I running a small application which is sql based. now my transaction log is more than 4 GB.data file is 55 MB only. is any possibilites to deleted old transaction logs from the transaction log file. due to this problem my server is very slow. plea...more >>

Does a trigger fire once for a set statement, or for every row affected? I presume that using a client like Access and doing a line by line manual adjustment, would fire a trigger for each transaction, but does a set statement (like below) fire the trigger once? eg Table_A (column_a, co...more >>

Hiya, I´m wondering how I could tell if a file exists before processing it with a bulk insert statement. TIA -- Jorge Luzarraga C Fidens S.A. 321 7610 Anx 23 "I can do it quick. I can do it cheap. I can do it well. Pick any two." ...more >>

Hi, I'm trying to execute a select into and save the result in a database in a linked server. When i do: "select * into server2.database2.dbo.myselect from dbo.tdata" query analyzer always says that server2.database2.dbo.myselect has too many prefixes. Process has to be done in server1, what...more >>

I have a table, Message, which has an FK relationshp to the table UserCDV (on MessageID) and also an FK relationship to the table Document (on DocumentID). Doing a join on these tables associates a message (from Message) with a particular user (from UserCDV) and what type of document the mess...more >>

Hi All, I am trying to perform a simple search but not sure what the best way is to do it. In my stored procedure I have 3 parameters. 1. Newspapers (if param is 0 search all) 2. Companies (if param is 0 search all) 3. Keywords Is the only way to do these queries by using dynamic sql?...more >>

I have an access database in access2000. If they want to convert this to a webbased database - where do I begin to learn this process. Do I need to re-write the entire program? And where do I learn how to do this - where do I begin? Thanks...more >>

Using a Stored procedure, i want to sort my records according to a dynamic field... Could be sort by firstName, CompanyName, UserID... and so on... Receiving value in @orderingBy, but i can't seem to find a way to properly integrate it into my query, juse into the 'order by'.... If any could...more >>

Hello, I have a table like Name (varchar 50), IsCritical (bit), IsSerious (bit), ContactDate (datetime) I need to return a prioritized list of all names. First come all the records with IsCritical=1 in ContactDate order, then come the records with IsSerious=1 in ContactDate order then the ...more >>

I want to query the dB for x number of people. To explain the problem better, let me assume x to be 100. I want to query the dB for 100 people of type 1, If I cannot get 100 then the rest should be of type 2 or type 3 or so on. How can I do that. query = select top 100 name,id,email from main...more >>

We are experiencing blocking situations with a application that issues GRANT statements. It becomes blocked trying to take out a Sch-M lock while other long running report processes are holding Sch-S locks. I'm wondering: 1) Why is the non-DDL GRANT statement wanting to lock the schema? 2)...more >>

Hi there. I've got a number of processes that are stuck on my server and are locking up a table or two and I'd like to kill them. Unfortunately, after trying to "Kill process" in SQL ENterprise manager, the processes involved are still there with the following details: Status: runnable...more >>

Im trying to convert a datetime value to a varchar Ive used cast( datetimevalue as varchar(10)) but am not geting the desired result. Im looking for a DD/MM/YYYY result...more >>

Hi, I have a stored precedure which for some odd reason is not being consistant with the results. there are two main parts to the procedure, Firstly you find out the total number of payments made in a two year period. This puts the result in the temporary table URN_STEP05. This bit work...more >>

Hi there, What I have is an appointments diary, what I want is to be able to move details from one record to another (when someone calls to move their appointment from one day & time to another). Tablename: AppDetails includes the following fields: Appdate smalldatetime (PK) Apptime varchar(...more >>

Hi, I want to create function which will return column of maximal distance. SELECT MaxDistance(X,Y) FROM Table; Is it possible ? (Something like MAX function but I will have two arguments in MaxDistance on which will be calculated distance e.g.: x-y). Thank you very much....more >>

Hi! If you are professional ARHITECT, ENGINEER, DESIGNER, PROGRAMMER o User, and you can't buy expensive professional software - we will solve this problem. Our prices are very low. You pay only about 1%-15% of the price for the original software. Ou prices very from $20 to $150 per...more >>

When I create a procedure that references a user defined procedure it appears in sysdepends, but disapears after being refreshed. Is this expected and is there an alternative to sp_refreshview that correctly refreshes sysdepends -----------------------------------0--0--------------------------...more >>

I have a SQL 7 db with a union query (view), and I'm getting the error, "The query processor could not start the necessary thread resources for parallel query execution." This union query has been in place for about two years now with no problems until just now, though I haven't changed anythi...more >>

I want to use a user-defined function to work with data in a field, but can't seem to be able to use it in the FROM clause. I've presented a simple dummy function (udf_initials) to demonstrate the point. Any ideas as to why this won't work, and what's the solution? The function returns ...more >>

We need articles on moving database from sql server 7.0 to sql server 2000.... all the details Our database has got jobs also along with procedures, etc. Moreover, some custom settings or tables from master needs to be ported in sql server 2000 too. Please help! Thanking you, Piyush ...more >>

Dear Friends My password of the SA account is reset by some other admin and i want to check when the password was reset as this is a security breach. Can you please suggest how i can check the same. Best regards Sharad...more >>

I have a simple DTS that sends a mail using XSMTP. what id like to do is get a stored proc to execute the DTS. The DTS has a few parametes i need to parm in like address varchar (60) , and QueryString varchar 4000 Can any one show me a simple example to fire this off...more >>

Get free braindumps, real exam questions free books, notes and exam information for all certification exams. -- jeff ------------------------------------------------------------------------ Posted via http://www.codecomments.com ---------------------------------------------------------...more >>