I want to put a data file available on c:\ to an 'Image' datatype column in a table through an SQL query from my application And also I want retrive back that column data into a file on to c:\ through the SQL query ...more >>

Does ASP.net also use ADO.net to access data within databases ? ...more >>

Using the following query (hopefully it is self-explanatory): select school, schoolyear,class, gender, count(gender) from schools group by school, schoolyear,class, gender This gives two rows for each class, one for males, one for females. For example: SchoolOne, YearOne, ClassOne, Male,...more >>

How do I convert an integer to char with leading zeros? If the integer = 123, I want the char variable to be in the format 000123....more >>

Hi, I am quite new to SQL Programming.Is there any function or property in sql programming to autoincrement a number. I want the rowid to increment as and when a record is inserted and if any record in between is deleted the number must accordingly adjusted. Could someone let me know how...more >>

I have a table like create table budgets ( yea int, mth int, clientID, amt money, NonChargeAmt money ) I now want to do a report showing the month to date and year to date Actuals (from a transaction table) and Budgets. I currently do the Budgets in two queries but want to know if ther...more >>

How do you assign the result of openquery() to a variable? My query is select * from openquery(MYREMOTETEST, 'select interface_seq.nextval from dual' When I tried declare intNextVal intege set intNextVal = select * from openquery(MYREMOTETEST, 'select interface_seq.nextval from dual' I get an...more >>

I'm trying to get 1 recordset from DATABASE 1, capture 3 values from DATABASE 2 and then loop through Recordset 1 performing a calculation using the 3 values from DATABASE 2. FIGURE 1 shows Recordset 1 from DB1 and FIGURE2 shows Recordset 2. Both have the "Shift" field in common and FIGURE 3 sho...more >>

is it possible that the the collation type could be interfering with OLE-DB's ability to execute a SQL-Server SP ? development workstation: XP Pro SP1 SS2K SP3 collation=SQL_Latin1_General_CP1_CI_AS production server: W2K Advanced Server SP4 SS2K SP3 collation=SQL_Latin1_General_CP1_CI_AI...more >>

I need an sql server function to do the following. Data Change to 1.1 01.01 1.1.1 01.01.01 1.1.10 01.01.10 1.1.1.2 01.01.01.02 And vice versa, any idea please ? I tried following but could not succeed. Create Function itemCode (@Ac...more >>

I need an sql server function to do the following. Data Change to 1.1 01.01 1.1.1 01.01.01 1.1.10 01.01.10 1.1.1.2 01.01.01.02 And vice versa, any idea please ? I tried following but could not succeed. ALTER Function itemCode (@Acc...more >>

I have created a DTS script in the local packages area called S_P_Reporting_Import to transfer data into a SQL database. The script is working correctly, however I would like to know if I can control this script from a VB program using ADO and if yes what is the required code? Chris North C...more >>

I am looking for Transact Sql Debugger in my query analyzer but I could not find it, my Query Analyzer Version is : 8.00.194 and my MSDE Version is : 8.00.760 SP3, running on Windows XP Professional. Any idea please ? Best Regards, Luqman ...more >>

I am writing a program that will loop through each column and generate statistics on that column. (i.e. Min values, max values, Null count, etc). I need a way to be able to retrieve the table structure to be able to loop through each field within the program. Thanks, Scott ...more >>

I have 2 similar tables in MS SQL2K and a 3rd table which correlates the fields between the first two tables. When the first table has a modification (Insert or Update) I need the other table/fields updated based on the field correlation and vice versa. Any suggestions are greatly appreciated! ...more >>

I was finetuning my codes, and found UDF is really slow than SP, I could not understand. I had a table function, it populates data according to input parameters. When I used the same code to create a SP to do the same thing with a table variable, it only took one third of the time the UDF took...more >>

I want to load a table variable with the output of a table UDF. Here's the code I hoped would do the job: DECLARE @ATBL TABLE (strVal nvarchar(4000)) SET @ATBL = dbo.fnListToStringTable (@ATTRTXT, 1) I'm getting the following error when I try to save this in a proc: ADO error: Must declare ...more >>

Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please, share your experience in using IDENTITY as PK . Does SCOPE_IDENTITY makes life easier in SQL 2...more >>

Hi All! General statement: FK should not be nullabe to avoid orphans in DB. Real life: Business rule says that not every record will have a parent. It is implemented as a child record has FK that is null. It works, and it is simpler. The design that satisfy business rule and FK not n...more >>

Ok, I know the datatype should be text, ntext or image. That's fine. But how do I actually store a file? Does anyone have an example of a stored procedure that reads a file in (by passing a file path?) to SQL Server?...more >>

I want to keep some Microsoft Exchange Server 5.5 Group lists current based upon what people enter in a SQL Server 2000 database. Are there any existing routines that I can use to do this? Thank you....more >>

I am just curious to know as to why should we know the T-SQL commands for various things which we can do very easily and quickly with Enterprise Manager. For example, attaching/detaching, backup/restore, create logins, database users, provide permissions, etc.---- all these things can be done us...more >>

How can I number rows in a result set with a pseudocolumn or function I would like to have a select statement with the result set like this e.g. SELECT (???), USER_NAME from USER Result set 1, 'User 1 2, 'User 2 3, 'User 3 etc So the first column (???) should be a computed value always star...more >>

I have an ADO.Net dataset with a table that I am saving to a SQL 2000 table. A particular field is decimal and in the database table, I have the size, precision, scale set to 9(18,3). For some reason, even though my database field has a value such as 0.0654... nothing is getting saved into t...more >>

I have a user assigned multiple roles and a role can be inherited from multiple parents (see below). How do I answer such questions as "How many roles does the user belongs to?" (no re-enter allowed and no deep level restriction) I answered the above questions by using .NET but I think it ca...more >>

In 2002 was reported a bug in SQL Server 2000 that would not allow to run a distributed query on a remote server. This bug report was posted at the following URL: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us% 3B323711 I wanted to check if the bug was fixed but I did not find...more >>

Hi I have a trouble with some logic. I want a list of users who have never purchased a sony (so) laptop (lt). select * from ius_orders o inner join ius_cust c on c.ord=o.ord where mfr <> 'so' and category <> 'lt' Does this look correct or will this include all customers who have never p...more >>

I put a clustered index on a table with 2 columns -nationalityid int identity PK -nationality varchar(50) the clustered index is on nationality however; when i do a select i still get a record set back ordered by the nationality id...what am I doing wrong? -Jim...more >>

hello there! I want to generate an event in SQL Server when certain data appears in a table. I have though of various approaches to this but they all seem to have big drawbacks. What is the right way to solve this common problem? My ideas so far (with drawbacks) are: 1. Use xp_cmdshell in ...more >>

I want to insert or retrive(select) the data from a 'Image' data type column through a SQL query Can you suggest on this? Thanks in advance."...more >>

Hi there, When I had space issues, I had to split my database into 3 seperate data files. I've detached it, and finally moved all 3 files to a new, clean server with plenty of room. Is there any way to merge back 3 data files into 1 big file? is there any difference in speed or stability i...more >>

I have a crystal report that runs a stored procedure. The guy who ran our IT dept before me put together the code, and it's up to me to fix it. It's going to take a more experienced SQL coder than I am, to fix it i think. I need to make a minor adjustment to fix the problem, or change the whole thin...more >>

Hi I have to return dates formated in different ways in an asp page from sql server. What function can I use or how can I return dates formated like so I tried DATEPART ie.. but that does not work they way I would expect "dd/mm/yy" "dd/mm/ccyy" "mm/dd/ccyy" "mm/dd/yy" "dd mmm ccyy" "cc...more >>

Hi, I know how to do this in ASP while using SQL server but I want to have it done by a stored prodedure and sql agent. Maybe somebody can help me out: I want to select records that are not older than 6 months. This is calculated on the value of a record called Date. So Date minus 6 mont...more >>

Hi guys, Below is two related tables. If I need to delete a lot of records from the child table (EmailGroupMembers) but the deletion must not result in records on its own (without a EmailGroup member that is..) in the parent table (EmailAddresses), how should I think? Can I achieve that witho...more >>

I know I can put a 'case' statement in the 'select' portion of the sql query in a stored proc, but is there anyway that I can create the "Where" part based on a value passed into the proc? Every time I try to 'case' the where part, I get nasty errors and it won't save. Thanks in advance! ...more >>

I have a table with a primary key: PRIMARY KEY (USERID, ORDERID) Now I need to search for records with a certain ORDERID. Will the double-field PRIMARY KEY above support this search, or should I create a new index for just ORDERID (in addition to keeping the above primary key)? Thanks, ...more >>

I am Getting the error when imporing a table from access to Sql Server 2000 Timestamp error. Data Overflow what could be the problem ...more >>

can I perform a select into on one table? select a, b, c into (d, e, f) from tbl1 where d,e,f are the destination tables? ...more >>

Why is that database userid for both sa and master login ids are 'dbo'? ...more >>

Hi, i've sqlserver 7.0 with win2k and visual studio.net. i see in Server Explorer the stored procedure i want to debug. In the project properties i selected the SQL Server Debugging check box. I've not right problems because i can run the SP. MSDN says: To enable debugging with the SQL 2000...more >>

How can I insert the results of select statement into table? example, which don't work: insert into zalogeOlap (select z.zo_drzava_id,s.izd_id,zo_datum,zo_zaloga_malo,zo_zaloga_vele,zo_pr_mesec,z o_pr_teden from zalogeOlap z INNER JOIN izdelek_sestavljen s ON s.izdS_id=z.zo_izd_id) Tab...more >>

Hello I have a small problem you guys might fix in notime........ Im trying to get this result: Date Emp. Sum 2003-11-28 1 50 2003-11-29 1 20 2003-11-30 1 0 2003-11-28 2 10 2003-11-29 2 10 2003-11-30 2 0 Im using this example as a test...more >>

To the Yukon Development team: I noticed in the yukon beta1 docs that you can now use variables with the TOP command in a SELECT statement. This is very useful! I would like to offer one further enhancement to the top command - the ability to specify a range (ie: select top 10 to 20 username...more >>

Hello! I am trying to fix a table with repeating groups to get it in compliance with 1st normal form. I have executed an update operation, but it takes forever... Can you please tell me if there is a logical mistake in what I'm doing here? --update correct table with data from table vio...more >>

I have this stored procedure: CREATE PROC sp_Invoice @Vendor Char (4) = '%' AS BEGIN SELECT dbo.Hdr_InvHeader.Hdr_VdrNbr, dbo.Hdr_InvHeader.Hdr_InvNbr AS InvNbr FROM dbo.Hdr_InvHeader WHERE Hdr_VdrNbr LIKE @Vendor END RETURN GO My understanding is that if I use this statement it wil...more >>

Hi, SQL2K has a Notify Service? Thanks. ...more >>

I have a table like this create table dbo.Synonyms ( SearchWord varchar(50) not null, Synonym varchar(50) not null, Sequence integer not null, constraint SynonymsPKCO primary key nonclustered (Sequence) ) go create unique clus...more >>

We have a table with 100 million rows. Table has all necessary indexes (by tuning wizard and manually). Following query gives 40 minutes response time: SELECT datename(month,StartDateTime) + ' ' + str(datepart(year,StartDateTime)), COUNT(*) AS 'Count' , datepart(year,StartDateTime), date...more >>

How can I use the result of a stored procedure in a query? For example, if I have a s.p. that is named 'GetNextKey', how could I use it in a query like this: INSERT INTO <tablename> (ID, ... VALUES (GetNextKey(), ......more >>