I have a query that return the following data: nafe Transportadora nºentregas cidade peso(kg) 100 tttt 12 Mariana 500 100 tttt 12 Patos 500 100 tttt 12 Corinto 500 etc... I need to do that the result appears: nafe Transportadora nºentregas cidade peso(kg) 100 tttt 12 Mariana,Patos,Corint...more >>

Hi all, This is with reference with to my previous post with same subject. Hello All, My database name is hriscomp. I wanted to increase the FILEGROWTH parameter of the database using EM. It failed saying that : Error 5041: MODIFY FILE failed. File 'comp_Data' does not exi...more >>

I have a query below that is able to select part of the data that is duped. SELECT ADDRESS+STATE , MAX (LASTRESPONSECODE) AS LASTRESPONSECODE FROM QASMANUALENTRIES WHERE OVERRIDE <> 1 GROUP BY ADDRESS+STATE HAVING COUNT (ADDRESS+STATE) > 1 What I would like to know is after selecting...more >>

Hi, I have two fields in a record, and I want to concat them together in an SQL select statemet(select field1 +' ' + field2). Problem is when one fields is null(which is the standard), the select returns null, even though field1 has a value. How do I get around this ? ...more >>

Hi, Any suggestions on a utility to convert existing Stored Procedures(Non Unicode) TO Unicode environment? Rao...more >>

Hi Friends, Is there any way to handle exceptions and errors raised as we do it in ORACLE. Thanx in advance, Amit Arora ...more >>

I have a field called Name which I would like to split into first,last,middleInit. All of the data has this format: Doe,John D I've tried using ParseName but working correctly. I need to update another table with FirstName,LastName and MiddleInit. any ideas ? Thanks....more >>

Hi, I am trying to calculate a due date from a table, I have been playing around with some code (below). But I would like to know how I can make a the query display the due date when it falls under 30 days? for example if I have a hosting account that is due in the 28th OCT (today) I would ...more >>

I use full blown (client tools) SQL Server at work. At home I have installed MSDE as part of the ASP.NET SDK. My question is..... How do I create and maintain DBs/tables/SPs etc? Jay ...more >>

I have an ASP .NET page that is capturing the rowcount of a stored procedure through the ExecuteNonQuery method of the command object. The stored procedure does three things. It first inserts something into a table, it then updates a table through an execute statement, and then updates another...more >>

I have a command line encrypt/decrypt application that I want to run via xp_cmdshell. I'm aware that I could use an INSERT EXEC statement into a temp table if I were writing a stored procedure. Instead, I'm trying to incorporate the xp_cmdshell command into a function, and functions do n...more >>

Hello, I'm looking for a way to assign a variable from a select statement. What i would like to do is to make a normal select that will return the rows, and assign the variable with a specific value from the last row that was returned at the same time. Ex: DECLARE @MyDate datetime SELECT...more >>

SQL 2K Using Active X script , created the source SQL statement for data-pump task and it worked. Inside Active X script I am looping thru to change the source SQL statements, how can I again execute this data-pump task. When I look the source SQL in data-pump task it got changed , but data-p...more >>

Hi SQL Gurus, In our application we use many stored procs for batch processing. Quite often, one same DML blocks (i.e : INSERT / UPDATE) need to be done on many stored procs. Is it a good practice to wrap that DML blocks in a separate 'generic stored proc' (using sp_executesql for dynamic s...more >>

Hi, I converted some fields in several tables from char to varchar. Now I need to trim trailing spaces from those field values, any query script samples?? HJ ...more >>

Hi, I'm trying to query SQL Server 2000. I need to get something out of a schedule that has a 'starttime' and an 'endtime'. I am using the datetime data type to store the information. My question is, how do I compare 'starttime' and 'endtime' to the current time? I was using access and I wo...more >>

SQL 2K DTS is in ServerA Using Active X script, I changed the source for my DataPump Task. Now the source SQL is 'Select * from ServerB.mydb.dbo.mytable' , When I click transformations tab, error message "Login failed for user '\'" From ServerA, I could successfully able to execute Sel...more >>

I'm trying to create a dynamic field with an SQL statement. I got the following idea off a forum, but it's not working. The premise is that IF SoftHard (field) = 'Soft' Then output 'A' ELSE output 'B' What am I doing wrong here? SELECT *, IF (SoftHard = 'Soft', 'A', 'B') AS ProdPage FROM...more >>

Hi all, I have alot of duplicates in one of my tables - the entire record is duplicated twice - pure duplicates. I want to just delete one of the two records that are duplicates. How do I delete just one of the duplicates records Just remember I do have records in between that are not duplica...more >>

I am just new to this, so bear with me...... The task: I am currently converting a VB application that previously worked with DAO and MS Access 97 to an application that work with ADO and SQL Server 2000 (The VB app and SQL Server 2000 are running on a P4 2.4GHz machine with 512MB of R...more >>

Hi, I have a VB6 project working on a SQL server 2000 database, I want to export data from some of the tables to xml files. It will be done under the control of the VB app with no user intervention. Ideally the xml files will be created using stored procedure calls, or within triggers if that is...more >>

Hi, (my question is if this can be done any better or not) I have a working database today, that has web clients and also clients coming in via a client software. Its a order/invoice/inventory/purchase system. It has > 1000 concurrent webclients, and > 150 concurrent application clients. ...more >>

How to code in SP like this : Start transancetion try begin insert into table1 .... update table2 set .... insert into table3 .... Commit transaction end except rollback transaction end Can anyone give me an example? Thanks in advance. ...more >>

Will there be any performance issues if we keep constraints in scalar udf's as compared to defining them in constraint itself Thanks ...more >>

Hello, Need advice on DeadLocks I saw Deadlock Chain SPID = 123 and another one Deadlock Chain SPID = 221 on Profiler. The application was getting extremely slow. What does these messages mean and What do I need to do? Can I find out who and how these deadlocks happen? Thanks ...more >>

Can any one tell me where i can find column(Field) naming standards(acceptable field names) for an sql table. Thanks...more >>

myFirstProc can for example - exec xp_cmdshell "ping 574.24.92.50", but squawks as follows in response to - exec xp_cmdshell "c:\project1.exe" 'c:\project1.exe' is not recognized as an internal or external command, operable program or batch file. Project1 has no GUI, is just ...more >>

I need help in inserting rows to the same table.. Is there a simple SQL command to insert rows in the same table? For example: Insert Into Table1 * (Select * from Table1 Where Table1ID=2) Thanks...more >>

I'm trying to included this IF statement in a rather complex sp. I'm getting an error, but don't know why. Do I have invalid syntax, or is it just illegal to use an aggegrate in an IF statement? What I'm trying to do is prevent a DIVIDE BY ZERO error that could result if count(*)-SUM (CASE ...more >>

Hi, I'm trying to execute this sql string , but I get this errormessage: Invalid length parameter passed to the substring function. Anyone know what is wrong ? TNX SELECT distinct TestTypeID, CAST(LEFT(TestTypeID,CHARINDEX('.',TestTypeID)-1) AS INTEGER),CAST(Replace( SUBSTRING(TestType...more >>

I am migrating an SQL Server application from the DB-Library API to ODBC. In order to avoid application-level code changes, I was planning to create an ODBC interface written to the DB-Lib specification as a replacement module for NTWDBLIB.DLL, where a call to "dbopen" would perform a "SQLDriver...more >>

Hi there! :O) I was wondering if there is a better way to get the same result as the following? Note that the table #wedding contains 2 relations to the table #person. -- here's the code create table #person([id] int, [firstname] varchar(50), [lastname] varchar(50)) insert into #person([i...more >>

I apologize if this is a repeat - I'm not certain of the previous news server I was using and have switched to one that I know works. Trying to perform addition on a "flags" field in a table, used for bitwise comparisons to make output decisions. Let's say we have the following flag values fo...more >>

There's 3 tables...the last one needs a constraint using an attribute from a previous table...there's a foreign key in the table, but I don't know how to write the constraint ... CREATE TABLE Autobus ( NoAutobus NUMBER CONSTRAINT pk_NoAutobus PRIMARY KEY, NoTypeAutobus Number, ...more >>

Does anyone know how to write a query that find duplicates that fall between 2 dates. If have the following: SELECT [CaplampNumber], [StartDateTime], [EndDateTime], [Address] FROM [Allocation] WHERE [CaplampNumber] In (SELECT [CaplampNumber] FROM [Allocation] As Tmp GROUP BY [C...more >>

There's 3 tables...the last one needs a constraint using an attribute from a previous table...there's a foreign key in the table, but I don't know how to write the constraint ... CREATE TABLE Autobus ( NoAutobus NUMBER CONSTRAINT pk_NoAutobus PRIMARY KEY, NoTypeAutobus Nu...more >>

Hello, I am seeking a way to submit one SQL statement to a Sequel Server database and return to different counts for the same field base on the value in the field. I have tried the following code and I don't get what I had hoped for. I have a field called GENDER it takes two values eithe...more >>

I am using SQL 2000. I need to convert the varchar column with values in scientific format into a decimal column. For example, the colA ='3.4120892E-05' I need to convert it into Decimal(15,12). So the result would be colB=0.000034120892 Does anybody know how to do it in SQL code? I tried...more >>

I I'm a newbie and I struggle a little bit with a check constraint on a table... I create a table Contract like this CREATE TABLE Autobus ( NoAutobus NUMBER CONSTRAINT pk_NoAutobus PRIMARY KEY, NoTypeAutobus Number, CONSTRAINT fk_noTypeAutobus FOREIGN KEY (NoTypeAutobus) ...more >>

Hello Everyone, I have the following table: CREATE TABLE tb_1 ( user_id int, user_email varchar(128), active tinyint ) The data in the tb_1 table: INSERT tb_1 (user_id, user_email, active) VALUES(1, 'mitra928@hotmail.com',1) INSERT tb_1 (user_id, user_email, active) ...more >>

Hi all, I am trying to create the user defined error. When something goes wrong in the stored procedure. Here in the fallowing examplet the table Tab1 does not exist in the database. If i try to delete the Tab1 then it should take me to the error: But it did not. Any idea Why. How can I ...more >>

We have a database (OLTP) that feeds a secondary database (OLAP) via triggers. In the OLTP db, we used to have a column entitled IS_ARCHIVED that we updated after we successfully wrote the record to the OLAP db. We encountered some locking so we removed that part of the trigger. Our con...more >>

Hi All! The query walks through all databases on server the user has access to, checks for correct value in table BMOptions and populates the temp table with info about DB. I have pretty strange bug with it: we have tested it and it works fine, but one of our clients reports an error "Line...more >>

hi everytime i start query analyser, the default db is set to master. can i change this so i dont have to choose my database everytime? thanx ...more >>

Hi All, I have application where databases/tables spread between two servers(different building, wireless connection). During data entries from front-end, the application must access the tables on both servers interactively. The question is : - is Linked Server is the only way to access ta...more >>

Hello, I need some help. I have a datetime field it has a duration time event, i need to get number of seconds. I know that i can do this: 60*60*datepart(hh,convert(varchar(8),HR_inicio-HR_chegada,108))+ 60*datepart(mi,convert(varchar(8),HR_inicio-HR_chegada,108))+ datepart(ss,convert(var...more >>

Can somebody please correct my query,,,,thanks SELECT COUNT(CType)AS Tot,CONVERT(varchar(10),Dt, 101) AS Date,CT, (SELECT COUNT(b.chl)FROM test b WHERE b.chl = 'chl' AND a.ct = b.ct AND CONVERT(varchar (10), a.dt, 101) = CONVERT(varchar(10), b.dt, 101))AS PH FROM dbo.test a GROUP BY CONV...more >>

Oops - forgot to add the StartTime in the select list: select p.*, x.StartTime from Patient as p join ( select a.PatientID , max (a.StartTime) as StartTime from Admissions as a where exists ( select * from admission as a2...more >>

Hi all, I am using Crystal report 7.0 and MS SQL 2000. I have a report which is links to a Stored procedure in SQL 2000. The sp is quite huge, and it take times to run. Somehow, there is a time out error prompts out during report initialzation (not every time, but somethimes) . I just wond...more >>

If such a thing is possible, can someone show how to use a Stored Procedure (EXECUTE?) command to fire off an .exe? Thanks for your help! ...more >>

Hi, I have very simple question for you all.. I have following Stored Procedure and When I am trying to execute this I get the error on Last Line ( From Clause of @ServerName and @DBName ) Create Procedure Test AS Begin Declare @ServerName as Varchar(50) Declare @DBName as Varchar(50) SET @Sever...more >>

Hai Everybody, I have a table structure as below AsstNo | Collection | CollectionDate AsstNo ---------> char(16) collection------> Money CollectionDate--> Smalldatetime A sample data is AsstNo | Collection | CollectionDate ------------------------------------------- 1 ...more >>

I have several databases with exactly same schema structures. The database I have are for for multiple clients and for build (development), test and production. I have a requirement where in I have to compare tables across multiple databases. I would like to create generic functions so that I ...more >>

Hello, I am having a bit of a problem building a result table in the form I need. I am running a query (see below) to insert monthly financial data into a table (tblFSData); however I would like the tblFSData to include zero values for items that are not included (Item in in tblGMADP but not...more >>

Hello! I'm inserting a single row into a table which has a rowversion column. It means that the SQL Server 2000 server generates a unique timestamp value which is the concrete value inserted to the column for the new row. I would like to get this value and I may obviously just select it, but i...more >>

Hai everybody, How to get the SQL command text for existing table Thanks Satya Rao ...more >>

I have table which I want that only one user can change on time. How can I lock that table during work and check is it locked? ...more >>

There is an existing app with a massive amnount of dynamic SQL in it, ready for a SQL injection attack. While the client gets rewritten to make SP calls, I was wondering if anyone out there had any regex code out there that would clean a SQL statement to mitigate SQL injections. Thanks. ...more >>

Hi! I'm doing something like this to parse XML in stored procedure: EXEC sp_xml_preparedocument @_XMLDoc OUTPUT, @_iNewsXML INSERT @News (Title, Description, pubDate, SrcUrl) SELECT Title, Description, pubDate, Link FROM OPENXML (@_XMLDoc , '/ROOT/Array', 1 ) WITH (Title Var...more >>

I wonder if we can Create Parameterized Views in SQL Server without making them part of a SP ? ...more >>

I am getting an error when trying to add a column to a table that has a user defined data type that has a default value associated with it and the column does not allow NULLS. Here is the SQL: ALTER TABLE [dbo].[TestTable] ADD [TestColumn] [dt_NoYes] NOT NULL I get the following error: ...more >>

//I have code like this int k= 0; CString sSQL,sU,sP; input(sU); input(sP); sSQL.Format( "select count(*) from users where users.user='%s' and users.password ='%s'" ,sU,sP); if(FromSQLInt(sSQL)!= 0) { enterInside(); } if user inserts in password window or ' or " symbols thi...more >>

Hi, I am working on a query (for a hospital environment) that should return all patients that were admitted after a specific date, sorted by admission date. I have created a patient table, which has quite a number of columns, and an admission table, which defines admission moments. One pati...more >>

I've got a simple vbs/wsh script that uses SQL DMO to output the tables definition(including indexes/constraints and NO collation details) from a given database. I do not think the SQLDMO_SCRIPT_TYPE and SQLDMO_SCRIPT2_TYPE are being used correctly and hence I am not getting the output correct...more >>

I am getting the error 80040e37 in a SP but I cannot find that error code in BOL. Any ideas on where to get information on that error? -- ------------------------------------ Wayne Wengert wayne@wengert.org ...more >>

I ran the following query in Query Analyzer for a 7 column table. SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY c.colid The results were: I_CSD 1 X_STE_XML 2 I_USR_LCK 4 T_CRT_RCD 5 I_USR_CRT_RCD 6 T_UDT_RCD ...more >>

We have some processes which seem to be blocking intermittently when they are executing sp_prepexec. Its an extended sproc which presumably SQL Server internally uses to prepare a statement prior to execution (please tell me if not so), but what does it actually do, and what steps can we tak...more >>

I am trying to use DMO in vbscripting and having problems in declaring the below... Dim oTable As SQLDMO.Table Dim oView As SQLDMO.View Dim flag As SQLDMO_SCRIPT_TYPE Is DMO supported in VB script? or how can the above be used in scripting(e.g. VB/WSH ...) cheers, Sandiyan...more >>

How to move the output of a query to a xls file. Thanks...more >>

Hi, I'm trying to create a stored procedure that searches a table with contact names - basically a staff directory for an intranet. The search is initiated from an ASP.NET Web Form. The idea is that a user can enter a first name or surname or part of a first name or part of a surname to g...more >>

sql2000: what would be the query to return max - min score from #Temp (grouped by name,name1,name2) to look like following:?. Fred A,A1,A21,math100,88 A,A1,A21,math104,45 A,A1,A21,math101,88 A,A1,A21,Chm100,86 A,A1,A21,chm102,44 A,A1,A22,math101,99 A,A1,A22,math100,86 ...more >>

Hello All, I have a problem with my database. The logical file name of the database is different when I use sp_helpdb 'databaseName' than the name in sysaltfiles. I know that the information in sysaltfiles is correct but how can I correct the sp_helpdb information? And can anyon...more >>

Hello: I have a table like this: Data Value1 --------------------- d1 m1 d2 m2 d3 m3 d4 m4 d5 m5 d6 m6 d7 m7 .... ... I need a query or stored procedure to get a result like this: Data Value1 ...more >>

Dear All, i have an MS SQL - Server. There are many character fields with a length of 20. If i enter a string with e.g. a length of 4 "test" then the server fills up the remaining space characters, it doesn't matter if i enter the data via ODBC with my Software or directly in the table. T...more >>

Hi, I would like to have a column in my table which gets updated whenever an Insert or Update happens on a record in that table. But what I don't want to end up in writing code myself . So I thought I could use the timestamp datatype. But I have read in the Books Online that timestamp is used typ...more >>

Can I convert ANSI varchar data into nvarchar (UNICODE) data with explicit codepage ??? It seems that SQL convert function is doing implicit conversion ... any way to override the default codepage ?...more >>

Hi Ive got a problem with the Distinct function I need to only select one column with the distinct function and leave the rest so they can have duplicates. For exampel Table_Orders OrderID | Orderdate | User | City Then I want to select the unique OrderID to list all current orders but at...more >>

Hi, i have created a trigger for insert with a dynamic query and i have this error: "INVALID OBJECT NAME 'inserted'". The code i used on my insert trigger is this: CREATE TRIGGER ADM.INSERT_ ON TMU_2 FOR INSERT AS DECLARE @TYPE AS VARCHAR(5) DECLARE @DOCUMENTCODE AS VARCHAR(4) DECLARE ...more >>

Hi! I would like to update the values of one column to another column. Ex. Col1 Col2 1 5000 2 5004 9 5007 I would like to replace all the values in Col1 with the respective values of Col2. So the table would look like: Col1 Col2 5000 5000 5004 ...more >>

i Want to track the activities of all the clients daily and for this i am tracking the hostname and the dbcc input buffer but due to the spid it is cumbersome so in future release if possible also include some features for this. Also there is no facility to construct a crosstab Query in...more >>

Hi all I'm looking at logging and a test program that I wrote does not behave as I expect. Using SQL server 2k sp3 on Win2k SP4 (or latest, whatever that is). Here's the prog. The outer nested loop could be collapsed into the inner to make s single loop of 500,000, but that's doesn't matter...more >>

How do i transfer a table from SQL server to access ? Normally i would use a query like SELECT dbo.PSD.* INTO dbo.psd IN 'c:\engineer.mdb' FROM dbo.PSD The problem is that this query verifies correctly, but when i run it i get Incorrect Syntax near the keyword IN An...more >>