hi all, i had a problem to process a sql query. when the query is executed from a vb command (vb program), it takes a long time and sometimes it terminates the program by giving 'runtime error'. now, i tried to put the query in a stored proc. the stored proc now is executed by a dts packag...more >>

Hi, I have a multiline column which write from xml send by ntext parameter, problem is in lines separatror, because carriage return char -CHAR(13)- is omitted, only line feed char(10) is witten. Column is show like this: Text♫Another Text... I had use enconding uft-16, utf-8 but neither w...more >>

Allow me to preface this by saying I am an XML newbie. I have no idea where to start but have been saddled with a project that I am way incapable of even beginning. So... I would like to get a roadmap on learning from you folks. I have an SQL view, constructed of INNER JOINs and CASE statem...more >>

I need to insert a new row into a table using a select from another table AND some values that are being passed into the SP. A simplified example is... create proc udpTest @phone varchar(20) AS INSERT INTO tbl2 (last2, first2, phone2) (SELECT last1, first1 FROM tbl1 where ID1=ID2), VA...more >>

Below I'm trying to take the amount of all the sales by a salesperson in two days and create an alias for sum(saleamount) (which would become allsales) to put the grand total in. select thedate, sum(saleamount) as allsales , salesperson, orderID from transactions WHERE (thedat...more >>

Is there anyone here that knows both Oracle and SQLserver? I need to convert an Oracle trigger to SQLserver. I'm not looking for anyone to do my work for me - just point me to a good place to quickly learn the gist of Oracle sql code so I can first figure out what this trigger is doing, and ...more >>

can anyone help? ----------------------------------- drop table [dbo].[table1] result: Server: Msg 823, Level 24, State 2, Line 1 I/O error (torn page) detected during read at offset 0x000001a8c64000 in file 'e:\MSSQL\data\Data.MDF'. Connection Broken ...more >>

Based on the documentation I found so far, I cannot use 'USE <database>' statement to switch between databases in the SQL batch or the stored procedure. However, I have to switch between databases to apply certain database-specific commands, such as sp_addlogin, sp_option, sp_grantdbaccess,...more >>

Hello, BCP is generally used for data transfer from an instance to datafile and datafile to an instance right. And BULK INSERT to transfer data from data file to an instance. And then what is this statement doing? I am able to do something like this. BCP "select * into tableB from tableA"...more >>

I am fairly new to writting sql sp's and triggers. I am needing to roll up our detailed inventory table into a summary of different kinds to be used by differing systems. But, this cannot happen at night in a batch process. These numbers must be exactly what our inventory is currently to the minu...more >>

How can I test if a column aleardy exists, and if it does not exist add it? if not exists(?) begin ALTER TABLE Mapping ADD ConversionType int NULL end ...more >>

We keep track of database changes with a change trigger that looks at the "userid" of the connection. The line in the trigger is: select @UserID = case when isnumeric(HOST_NAME()) = 1 then HOST_NAME() else -1 end In our VB6 programs, we set that "HOST_NAME" with code like: cn.Pr...more >>

Hello! I have something like this: sql = "select count(*) from user_info where user_id = @userid" and then I have: myCommand.CommandText = sql; SqlParameter param0 = new SqlParameter("@userid", SqlDbType.NVarChar,50 ); param0.Value = Session["userid"]; myCommand.Parameters.Add( param0...more >>

I have written a trigger that check the availability for a paricular item before the insert statement is committed. If the availability returns 0 (or less) the trigger rolls back the insert. However, in our application I would like to update a column in a grid with the actual availability ...more >>

Hi all, What are the invalid characters that are not allowed in a SQL statement? Thanks, Ivan ...more >>

Hi, Following problem that I cannot quite get to grips with writing a stored proc in SQL Server 2000 ...... transposing the following: I have a table arranged for ease of user input for values on a monthly basis as follows: Key1 - Key2 - Key3 - Mth01 - Mth02 - Mth03 - Mth04 .... etc ...more >>

I have a need to join 2 tables on a code and date. The code match works ok but I am not getting all the records that also match on date. I assume it has something to do with how SQL stores dates and times. Below is my SQL. Thanks. SELECT dbo.EmployeeTimeOff.* FROM dbo.Repair...more >>

I have a view that returns a datetime field from SQL 2000 database. I would like to have it return a time (hh:mm AM/PM) e.g. 10:30 AM if the date is today, otherwise return the date only, e.g. 7/18/2005. I am thinking I need a CASE statement or something. My SQL is something like this: S...more >>

Hi, How would I get a list of databases that a particular user currently has access to? Example: SELECT database_name FROM some_table WHERE user_id = ? TIA, Eric...more >>

Hi. I'm writing a SP that has to retrieve information from a table and register it in two tables in other database: one has information about tables and the other about the columns. I have no problems with the registration in Tables, but have some in Columns. One of the columns in that t...more >>

Hi Can 1 stored procedure call tables on seperate databases on the same or different servers? In other words can I use a procedure to list all the employees in Pubs and Northwind? Thanks ...more >>

I can go with one SELECT statement like this : select paag.paagpk_weekday_code, paag.weekday_name, paag.sort_order, paag.external_value, paaq.paap_language_cd, paaq.short_desc, paaq.description long_desc from cd_weekday_paag paag LEFT JOIN text_translation_paaq paaq ON pa...more >>

I have an asp web app that calls a simple stored procedure (inserts 4 fields) This works fine on all computer accept one. The one workstation in question inserts double records everytime. I have logged on to windows under a different username and it still happens. I have also logged on as ...more >>

In my procedures I currently check @@Error after every DML statement. I have been criticised for doing so with the assertion that it is a waste of code because such code will often NEVER be executed. I come from an Oracle background, where almost all errors may be caught with EXCEPTION proces...more >>

I have a query that takes the freespace and divides by 1048576 because the value is in (K) and need to display in (MB) Select SystemName,Win32_DiskDrive_Model,Win32_LogicalDisk_FreeSpace/1048576 AS [Free Space (MB)] From SystemInfo Where (Win32_LogicalDisk_FreeSpace/1048576) < 500 When ...more >>

EXEC sp_Who GO We have a SPID loginname = TNS_IBM_app listed as "Sleeping", "Awaiting Command" TNS_IBM_app is a SQL Server Standard Login that we have here that represents some internal web apps. This is the *only* context in which this login is used. The server has been bugging out o...more >>

Hi guys, When I run the following statement to repair data, it failed. I guess this database is too damaged therefore I can't fix it? =20 I ran dbCC CHECKDB('stock','REPAIR_REBUILD ') before too, it failed as = well.. What should I do now? how do I fix those data or it's not possible? =...more >>

Hi I am trying to update a table on a linked server whilst joining to a table on a local server. The servers are both SQL2000, SP3a I am trying to use OPENQUERY to do this. The code looks similar to this update openquery([LINKEDSERVER], 'SELECT DATABASE.databaseowner.table.column1...more >>

Hi people, i have a question for u: I want to create a function that use a XML read like this.. CREATE FUNTION xmldatafunction(@pXMLData NTEXT) ..... DECLARE @lvDocID INT EXEC sp_xml_preparedocument @lvDocID OUTPUT, @pXMLData ....... AND C.n_plan_id IN (SELECT [id] FRO...more >>

Does anyone know of a programmatic way of finding which server(s) may be running, so a new client installation can be provided with a drop-down list of where the data may be held? There will be a defined instance in each case, so all servers to be found will be of the form "machinename\myinsta...more >>

Could some help me on this, I just could not figure it out in SQL: Table 1 looks like: ID ParentID EmpName ----------------------------- 2 1 Emp1 3 1 Emp2 4 1 Emp3 5 2 Emp5 6 2 Emp6 7 ...more >>

If two transactions are trying to operate on the same table at the same time what happens? ...more >>

Hello, Is there to just use a variable as a temporary variable inside a stored procedure without wanting the value to INPUT, OUTPUT, or RETURN? I want to set the variable and then use it as part of a new record on an INSERT. The value of this temorary variable is the new index of another ...more >>

I wrote a trigger that fires evertime Table(A) is updated and writes the relevant information to Table(B). I can get the user name and audit what has changed but I would like to add a field that tells me which Stored Procedure caused the update. We have a few home built .net applications that up...more >>

I have a query that populates a #temp. After the tables populated and need to select rows from several different types. The tpes appear in the middle of the string that Im testing. How/Can I do the following: where myNumber IN LIKE ('%CC%','%TR%') Obviously SQL doesnt allow LIKE when us...more >>

Hi, I have an input form containing a list of languages that someone might speak. None, one or many of these languages can be selected. I'd like to pass the IDs of all of the selected languages to a stored procedure and build a query statement to select people who speak all the selected l...more >>

I have this which displays just fine (but without summing the columns): SELECT t1.MemberId, CASE WHEN t2.amountTypeId =7 THEN t2.amount END AS 'PurchaseCR', CASE WHEN t2.amountTypeId =23 THEN t2.amount END AS 'PurchaseDB', CASE WHEN t2.amountTypeId =8 THEN t2.amount END...more >>

Hi, i have a problem during import data from Oracle to SqlServer. I import, succesfuly, 95 tables on 101. 6 tables return this error: - Invalid Expression. All the columns of that tables have null value for default. The import data creates the tables but when copying the data from a DB to t...more >>

Hi, I have an exsisting visual C++ 6.0 application acessing sql server 7.0 and above using odbc , acceesing char, varchar fields. Its an Ascii build. Its planned to update this to access unicode columns i.e nchar, nvarchar , using a unicode build. The sql is currently formed using string...more >>

Hi, I've currently got an SQL script that allows me to grant access to a different db than I'm logged into now. In order to accomplish this I change the database using the USE function. For example, I'm currently logged into database A and I want to, using sp_grantdbaccess, grant access to ...more >>

I want to insert column 'email' from Table A to Table B (both have 1 column) such that there are no duplicate values in Table B. How can I do this? ...more >>

I am currently using the 'executesql' command to run some dynamic sql strings. However it falls over when it encounters fields that contain an apostrophe (i.e. the name O'Brien). This makes sense but wondered if anyone could shed any light on the best way to code around this issue. Than...more >>

select isnumeric('1') select isnumeric('a1a') select isnumeric('31113d45') select isnumeric('1d45') Except second select others return 1 Why is the presence of d not considered as non-numeric? Madhivanan ...more >>

An error message would normally be displayed if I tried to read a db field which didn't exist in a table trought asp code. i.e.: response.write rs("Status") where the field Status does not exist in the table This used to raise an error showing which line of code the error occured, but now ...more >>

Hi, Whats the easiest way to create a relational database schema on SQL Server Express? Which tools (preferably with E-R diagramming!) could I use? As I don't really want to have to bother with the raw SQL Thanks...more >>

Hi, I am looking for a small example for Global Cursors. Can anyone help me in this regard? Regards, Sevugan.C...more >>

I've got a real brain teaser which I'm trying to work out. Basically I recieve a merge table in every night which supplies a list of urns which have been merged into other urns. I'm using a .net application to search on urns and return results but when a user input a urn my stored procedures l...more >>

Hi , How can I read registry and set it to Globle variable in DTS? Can anybody provide me a sample code Regards Dishan ...more >>

Hello, I have a table in the structure indexno identity cardno varchar trdate datetime trtime datetime I am getting the data from the scanner(Attendance scanner) using some frontend(ASP) I want to calculate number of hours worked in tat month. For this i want to wri...more >>

Hi, I have written a Stored Procedure. I do not want the other user's to see the content of the same. Can I protect the content of the same? If so How Can I do it? Regards, Sevugan.C...more >>

I have been tasked to design a new DB for an upgraded version of our companies main product which is a jobsboard. The main feature for this new and improved design is going to be to make it easier to customise the system according to customer requirements. We have a set of generic web pages w...more >>

Dear all, I've got just a number (1,2,3..n) Things such as these are not useful: select DATENAME(month,2) from table select getdate() select month(2) Thanks in advance and best regards,...more >>

Hi, How can I convert values like 3.75 to 03:30 (as in 3 and a half hours). Or any value e.g 3.25 to 03:15 etc... Thanks N...more >>

Hi, I have a table PersonName like this: ID Name ----------------- 1 John Smith (Volt) 2 Jennifer Widom 3 John Smith (Education) 4 Jeffrey Ullman 5 Steve Huntsberry 6 Jennifer Widom (Transportation) I want to find out all the similar names using sql queries. For example,...more >>

Hi. I'm getting an error trying to do that: ALTER TABLE TempTable ADD CONSTRAINT @keyId PRIMARY KEY @field Can't I use variables to set the primary key? -- Regards, Diego F. ...more >>

Hi, Could someone tell me if the limit on nested-view levels (32) in SQL 2000 will be kept with SQL2005 ? JN. ...more >>

hi, I need to create a procedure which will run automatically as a job. The problem is that I need to run this script for xx different companies on a single db (this is Navision application). The table names where created like this “aa super company$table name sss$xx” (with spaces). I ...more >>

Hi, I have result set from couple tables. One of the computed columns in result set is clmValue decimal (15,5). Now I would like to get only rows until sum(clmValue)>=1000 (ordered by some columns). Something like this: select T4.* from (SELECT T1.col1,(T2.col4-T1.col3)*T3.col2 as clm...more >>