Hi, I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to execute a piece of dynamic SQL. My dynamic SQL needs to return a resultset so I basically did the following: SET @vSQL = 'SELECT * FROM MyTable' EXEC sp_OAMethod @vObject, 'ExecuteWithResults', @vResult OUTPUT...more >>

hi, im basically looking some advice here for a small project we have. basically im looking at automating an attendance register in short want i want to do i build a matrix of students attendance on a course. this table of data will then be used to populate a Datagrid. Course Duration can vary...more >>

Hi, Can someone provide me a simple trigger where it updates a lastmodified column. It should update the column lastmodified whenever an update occurs on all the other columns. Thnx ...more >>

Is there a way to return the system date of the SQL Server? If I use SELECT GETDATE() as Today that returns the date of the client workstation. I would like to know if that date is more than a day before or after the date on the server. Is there a way to get that date?? -John ...more >>

I have a long comma delimited string (more than 8000 characters). like this "<SomeXML1>, <SomeXML2>, <SomeXML3>, <SomeXML4>". I need to parse through them and store them in different rows of same column. The only input parameter I can use is text/ntext since there are more than 8000 characters...more >>

I have a form that allows a user to update contact info. For example: First name Last name PO Box City State There are actually more fields (up to 50) but I have only used five for simplicity. Sometimes a user will submit an update for all fields. However, for a web service, some one m...more >>

table1 id message 1 abc table2 id message 1 cde 1 fgh 1 ijk Desired result id message 1 abcdefghijk...more >>

I'm doing what I thought was a simple GROUP BY summary of fairly simple data and the my numbers aren't working out Some results are showing up <NULL> when I know the data is in the database I'm no SQL expert, but if I'm summing (SUM) multiple fields and adding them together in my SE...more >>

I Need help on how to store a word document in sql server.what datatype and how to convert it to store it. Pleas Help Thank you ...more >>

first of all i like to know how to write trigger ? any reference is greatly welcome. here is my situation. When ever user submit my application for insertion of data, sometimes duplication occurs, i mean same row is inserted twice becoz of user refreshing the page or trying to save once again p...more >>

I have a text file named query.sql In this text file is the following command: Insert into [FixedText] ([FT_ID], [FT_TextNL], [FT_TextFR], [FT_TextUK]) values(1464, 'Domiciliëringen', NULL, NULL) Then I execute this text file using OSQL -E -i query.sql When I check back in the database ...more >>

HOW CAN I GET THE FULL LISTING OF ALL THE SQL STATEMENTS?...more >>

I have a grouping view and I want to be able to get the 1st value in a column. In Access I would use First(colname) but I couldn't fing anything similar in BOL. Thanks. David ...more >>

I would like to generate a report detailing permissions of users and groups per database on my server. What would be the best way to go about doing this ? (I dont want to do this through enterprise manager). A SQL Script to do this would be PERFECT :) Thanks, Justin ...more >>

Accounting has provided me with an Excel worksheet with several columns of data, one of which is AccountID. There is a table in SQL Sever which also has AccountID. I need to join (using AccountID) some additional columns from SQL Server into the worksheet as new columns, save, and then send the ...more >>

I have a table full of several thousand rows. I want to assign a sequence number based on the number of distinct groups of column values... In this case, Critical & Period, where critical is a bit and period is number from 1 to 12. I have a solution, but I was curious if I was making it more...more >>

Hi all. I am working on some crosstab logic and needs to get my result into a temporay table for further use and joins later in the prosedyre. It is dynamic crosstabs so I don't know the number of columns on beforehand. After my logic I can get a result that looks nice in QueryAnalyzer usning...more >>

I have a routine that is displays my record in date order. I want to be able to put a specific record from that set of rows on top, with the rest in date order (as it is now, but without the one I moved to the top. For example, I have the following select: Select JobID, jobTitle = (jo...more >>

I have an issue where i can do the following Select count(*) from table and it returns 181,000 Select count(*) from table where ncolumn is not null and it returns 174,000 Select count(*) from table where ncolumn is null and it returns 153,000 Now, those numbers should at up to the count...more >>

I've included the xml and my OpenXML statement below. Using XPath in the WITH portion of the OpenXML statement I can retrieve most values I need, but I am trying to retrieve the values 'Last Name of Client' and 'First Name of Client' from the xml and am not having any luck. I tried using '..' a...more >>

I have about 29 servers that I need to set all db_options for, and they will be the same on all servers. Do you know the syntax for changing all database recovery types to "simple"? Also, I need to change all db_options for AutoShrink to "false". I don't want to go one by one, so I was ho...more >>

yesterday i had requested for q query that was not working and the solution that i got was to concanetate the two fieds to get the distinct count here is DDL create table test( empid int), productid varchar(10)) insert into test values(22,'ABCDEF') insert into test values(22,'ABCD...more >>

I have a table which has 5 remarks fields which need to be a minimum of 2000 in size each. I have been using varchar(2000) for 3 of them, but need to make 2 more 2000 in size also. Should I be using a different data type for this? How do people get large blobs of text into a table row? T...more >>

>From within SQL Query analyzer, I can create the union of two different tables located in separate databases (on the same server). This works fine. How can I achieve the same result programmatically (using VB6)?? Since the tables are in different databases ... do I need one connection string fo...more >>

I have a VB6 application that use SQL server with ADO 2.5, the connection is established with sa account. The program executes succesfully a command that creates a temporary table: CREATE TABLE ##CC1234 ( IDTmp int NOT NULL IDENTITY (1, 1), AlphaKey varchar(16) Collate database_default NUL...more >>

What's your opinion? Should I encourage our developers to use CAST or CONVERT? Personally, I would think CAST should be preferred since it's the ANSI SQL-92 syntax and CONVERT should only be used where you need to specify the datetime conversion "style" when converting to a string. -- *m...more >>

Is it possible to create an extended sproc for SQL2K using .NET (ie C#, VB.NET, etc)? All of the examples I've found focus on C++. Cheers, Jon ...more >>

How can I convert SQL datetime to civilian time in a select statement? The statement below gives me only military time SELECT CONVERT(CHAR(12), dtLastAction, 108) FROM OrderedItems...more >>

I want a string that consist full account number as follow: account_num = 300.111000.10 But table only consist of the following: -gbmcu : 300 -gbobj : 111000 -gbsub : 10 So how to combine gbmcu,gbobj and gbsub into account_num? I had tried this T-SQL but fail : update Bank_Accoun...more >>

Is there any sort of rule of thumb for this situation? I have a fairly large table in my database (currently 21 fields with over 800,000 records and growing) that is selected from, inserted to and updated fairly often. I am adding some new features and cant decide if it would be more efficien...more >>

Hi i've created one table and one stroed procedure to insert data into that table. i've created table like this table Name=QA_QAuthor EmailId Varchar primarykey. Pwd varchar Comments varchar allows null values paypalacc varchar all...more >>

How can I set the database can read english, chinese Traditional and chinese simplified? Thanks ...more >>

I got a 6 long insert sql statment in one SP. e.g (insert..... select... where issuedate<@date) Now, I need to set another where clause condition..but with the same inser statment e.g (insert..... select... where transdate<@date) I think it is not good that I copy the 6 same statment and t...more >>

hi, I have 1 GB Log file, after I backup the log file I issue a CHECKPOINT command and the log file is back to 30MB used and 970 MB Free. Then I want to release part of the MBs back to the Operating system. I issue the DBCC ShrinkFile(2, 100), and it doesn't change anything. Then I backu...more >>

On a SELECT from a varchar(250) column, I want to insert <br> at every 50 characters. For example, if the string is 151 characters long, it would look like this: ....<br> ....<br> ....<br> .... The text in this column is URLs. I'm inserting line breaks at each 50 characters to keep ...more >>

hi, i have to add a photo path field to our database so that we can view/associate client pictures with their personal data. my question is, is there an unwritten rule that says whether or not we should add a new table (TBL_CLIENTPICTURES for example) with the client's ID as the key link to...more >>

I'm trying to resolve an issue involving the need to SET ARITHABORT ON in vb code. kbid=305333 says that the way it should be coded in VB code is: myconnection.execute SET ARITHABORT ON This does not work, of course, because there is no execute method for the connection object. This is...more >>

Does anyone know solution for this: if I insert recordset into database, depends on datetime, other record will be change value. Something like this: If I insert new record with datetime other record will change value and the same is when I update some record. 1;Nik;02.19.2005;1000;50 ...more >>

i trying to reconcile a log against files we recieve in from our clients. HeaderRecords contains the file Headers and DayRecLogs is the daytime log I need to show all files recieved that day and check tere is an enrty in the DayRecLogs for that file and if the reports were generated and sen...more >>

I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl. When I query "select MAX(IntColumn) from ..." I get the error below when IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9). No error for simple "select IntColumn from ..." on same tables. The ...more >>

dear jeff johnson , thanks for ur response. I have one table name fixtures in client machine .In that table one column is identity type .now i am insert one Row from server database to that client database now i have to on the identity_insert in client machine from server machine give som...more >>

Here's a simlple enough question: If somebody is deleting from a table in SQL (with a simple DELETE FROM... TSQL query), will there be a lock on this table such that another process can't - UPDATE that table - DELETE from that table - SELECT from that table (not sure about this one)...more >>

Hi I have a db field that I need to use to find a selection of records, based on whats in that field. The problem is that this field can contain a string of multiple values... "value&value&value&&" I want to test this against a field in another table.. WHERE table1.field = table2.fie...more >>

Hi all, Using: SQL Server 2000 SP3A Enterprise Edition I have setup a table that holds application information. One of the fields holds the Applications Version Information. I have created a default type called Version of nvarchar and length 43. Version information is made up of 2, 3 or...more >>

Dear Mike I've created this sp through Enterprise Manager. and when I execute sp_addextendedProc it show me Error message: Server: Msg 2714, Level 16, State 7, Procedure sp_addextendedproc, Line 26 There is already an object named 'xp_Session' in the database. ...more >>

Hi I execute an extended stored procedure in Query Analyzer and it show me this error message: ODBC: Msg 0, Level 16, State 1 Cannot load the DLL C:\WINNT\system32\Project1.dll, or one of the DLLs it references. Reason: 126(error not found). I've copied my dll (Project1.dll) to C:\WINNT\...more >>

Whilst importing some procedures, I have left the 'Transfer Dependent Object' checked and the 'Drop Destination Objects' result I have over-written certain tables Can it be undone with the transaction log and if so How ? regards Earnie...more >>

I need a stored procedure that include dynamic where clause. so I created these two sp. GetCampaigns calls Checkvalues sp to complete where clauseb but it doesn't work. any idea ? CREATE PROCEDURE GetCampaigns (@CampaignTypeId int,@CardTypeId int) AS SELECT Campaigns.* FROM Campaigns WHE...more >>

Hi does any one have a script that can script out all triggers in a database then delete all triggers in a database as I need this for a replication problem I have thanks for any help Sammy...more >>