Hi, I created a user and when logged in under that user name I get the foll error Server: Msg 3704, Level 16, State 1, Line 2 User does not have permission to perform this operation on table 'dbo.sale' when I try to execute truncate table dbo.sale I grant select,update,delete permissio...more >>

Hello, can anyone point me in the right direction? I have .rtf files stored in a table as image or sql_longvarbinary data. I would like to be able to pull out the data and assemble an .rtf file using VBscript or VBA I'm not sure where to begin when working with image data types. Thanks B...more >>

I want to update a varbinary(max). If the column is null, then I will just set it, but if not null, then append with a .Write. Something like: row = select... if (row.Document = null) Update myTable Set Document = 0xFF where FileName = 'Text99.txt'; else UPDATE myTable SET Doc...more >>

Hello, Does anyone know a query I can run that will display all cascade actions (on delete or otherwise) for all tables/relationships in a given database? Thanks in advance -Kerry-...more >>

I'm trying to divide 2 int columns and am expecting a result in a decimal value, however, only whole values are being returned. First I tried returning a calculated value from a select statement like this: CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7)) INSERT INTO #tmp (col1,...more >>

I've got an update that sets columns to the value of other columns in the same table but in different rows. It is currently executed using two recordsets. I want to make it one UPDATE statement instead of looping through a recordset while repeatedly opening another on the same table. I've come u...more >>

I'm migrating some reports to reporting services, some of the reports use a bunch of conditional logic to produce the desired results, I'm trying to eliminate as much of that as possible... Here's my query... At the end of the post is the original sp... My problem is when I use the "@fdate" pa...more >>

What would be a reasonable primary key to use in the following scenario? I need to provide new functionality to an existing Web site. This new functionality will be questionnaires (containing from 3 to 35 questions each). Visitors to the site will open a questionnaire, answer the questions...more >>

Update Store procedure. I have a table which have 4 columns: (Unique Primary Key (SKU & Date)) SKU Date Qty Cost 01 20050101 5 10 01 20050201 10 ...more >>

I have a table that is updated on a regular interval. I need to set an overall ID that increments by 1, such as 0001, 0002, etc. I need a second ID that does similarly, incrementing by 1 (such as 20001, 20002, 20003), but which also resets to 20001 for each new update interval. Thanks...more >>

I have a table with 10 rows - one int column and 3 varchar cols. There is no unique data. How can I number/enumerate the rows from say 1 to 10 with Tsql? create table tbl1( RowNum int, fld1 varchar(5), fld2 varchar(5), fld3 varchar(5)) Insert into tbl1 Values(null, 'abc', 'def', 'ghi'...more >>

How long does a a query plan stay in the procedure cache? Is there someway we can reload a specific sp in SQL Server is restarted? TIA ...more >>

I am executing a stored procedure which takes way too long to complete. The problem arises when the procedure goes through the INSERT logic. I cannot understand why the AddressValid table is being used as part of the INSERT statement execution plan. I list the 4 tables referred to in the execut...more >>

Hi, How can I force queries below FIRST to execute statements S and T and then to make join of S and T? It makes me a performance problem on real data with a few million rows - second query will run much, much longer. On this sample from pubs database you can see that execution plan for se...more >>

Hi, I have sales line item detail like invoicedate, custid, ordernum Is there a way using SQL to only return records for the last three full months? For example, if today is 2/4/05, I would want records with invoicedate between 11/1/04 and 1/31/05. TIA, Dan...more >>

To all gurus, Thanx for the info concerning the dbcc statements. The only remaining question is there a command or code that is known to be used to defrag a table. It would be very good if this would be usable on the fly (ad-hoc) Thanx BillO *** Sent via Developersdex http://www.dev...more >>

I want to create a query to return all table name in my SQL database that has more than 0 records how can I do that ? Thanks FRED ...more >>

I have received a very large table that one of the columns has multiple entries in it separated by a special character. Any thoughts/suggestion on parsing this out in SQL and creating a new table? Many thanks Shahriar Example; ID Airlines 1 'UA;KLM;BA' 2 'AF;US A...more >>

I need to conbine the a first record with a secon record of the same table. For example EquipmentID Day AAA 2005-02-01 AAA 2005-02-01 AAA 2005-02-05 AAA 2005-02-07 BBB 2005-02-11 And I need to obtain ...more >>

I am using this procedure and do not want to return the message (1 row affected) each time the proc is executed. Even with set nocount on I am getting the message. Can it be surpressed? exec master..xp_fileexist @filename, @cmd_ret out ...more >>

Hi, I'd like to know differences between "left join" and "left outer join". It seems to me the same ? ...more >>

I have an aspx page that has a fairly long select statement that changes depending on what is passed to the page. The with statement, order by statements, some of the joins all could be different depending on what is passed. I usually have my sql in stored procedures, but am not sure if th...more >>

I need some help with the queries to move dup rows and keep only the rows with MAX status_fg. Please see desire result below. Any help would greatly appreciate. if exists (select * from dbo.sysobjects where id = object_id(N'[FinalTableX]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop ...more >>

Ok all gurus, Here is one that has me stumped I am going to need to defrag and clean several tables and indexes in some of those tables. I was going to run a query that would check the contig table levels for index as well as table defragging. I was going to use dbccindexdfrag and or DBCCcl...more >>

I want to search all the text of all the stored procedures in a database to find out what stored procedures do inserts on a certain table because I'm putting a trigger on that table for my own purposes, but i don't want it to interfere with existing automated inserts. when i run EXEC sp_depends...more >>

How can I create a script that updates a table with new fields without losing the data in the table (i.e., without dropping and recreating the table)? Thanks. ...more >>

Hi All, I have created a stored proc that is set to accept @username = varchar(40)...it fails when the username is FULLY qualified with Domain name ....ex: 'MyDomain\Username'...how do I get my procedure to except this FULL name? Thanks...M. ...more >>

Dear all, I've got this two fields and I would like to obtain a third value with the difference How can I do that? 2005-05-30 21:00:30.793 2005-05-30 21:02:30.640 Thanks a lot ...more >>

'Cannot convert a char value to money. The char value has incorrect syntax.' This is the message I get when I am trying to convert a char field to money data type by using the sql statement: select convert(money, amt_paid) Please advise. Thanks ...more >>

Hi I need to transfer a table from SQL SERVER to ORACLE, the table structure is identical in both databases. However the table has a TEXT column (or CLOB in Oracle), and it appears that DTS cannot handle that. Any suggestions on how I achieve this? I've also tried exporting to a text file...more >>

I would like some advice on a data and query problem I face. I have a data table with a "raw key" value which is not guaranteed to be valid at its source. Normally, this value will be 9 numeric digits and map to a "names" table where the entity is given assigned an "official name". My problem ...more >>

I am getting data from corporate, in the form of an Excel worksheet, that contains the list of facilities in the US. I need to be able to pull the building number, building name, building state, etc. from the worksheet. Unfortunately, when I use SELECT DISTINCT on a holding table, some hubs ...more >>

I am running the query below but the results show only the integer portion. I know the results should be values like "2.33" instead of "2". How do I get the correct result format? Wayne ======================================== SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCap...more >>

HI All, I am looking for a sample update trigger with inner join. I want to create a trigger on tblemployee. When employee changes any information in DB it should get inserted in new table called tbl_aud. In my tblemployee I do not have their Last & First Name. Only id. So I will need to ge...more >>

(Sorry if this is a duplicate post - my connection timed out when I clicked "Post" so I don't know if the prior attempt got posted or not). All, I have a scheduled SQL job which executes a stored procedure that purges large amounts of history data from a database. The procedure runs to ...more >>

I have some questions in regards to Error Handling: 1. How to utilize syscomments? 2. Are there special parameters that need to be considered or things to avoid when notifying users? 3. What happens when utilizing raiserror? Thanks for your help... ...more >>

Hi, I have a problem and am not sure if it sql can do what I want. The following sql highlights the issue I have and can be used for testing. The last select statement returns 5 rows. You can see the Vh_registration_id is 3 rows for 66316 and 2 rows for 676454 based on my filter. Without co...more >>

I am having problems returning records from a cursor created using the following: DECLARE myCursor CURSOR FOR SELECT DISTINCT * FROM [Irregular Frequencies] WHERE (PubCode = N' + @PubCode + ') ORDER BY Period However if I set this string as a variable and use sp_executesql it works fine....more >>

It is possible to move the result set of Exec to the table if the table is already created Insert into mytable(col1,...coln) Exec(query). This works well But is it possible to have Select * into mytable from Exec(query) Any ideas? Madhivanan ...more >>

Hi, I have a store proc which I want to convert to a function. However, the command I am using to declare my cursor (the example commented out below) I do not think is allowed within a function. Hence I have commented out and and conventionally declared my cursor. This however does not r...more >>

There doesn't seem to be consensus about when to put code in the database or in the middle tier. There was a long discussion about this in an Oracle newsgroup (message ID: ULcQb.466$KU5.37@nwrddc02.gnilink.net). Elsewhere there's been discussion about Microsoft SQL Server 2005 adding the CLR ...more >>

Hello, I am new to sql server. Where is sql server developer's documentation located. I have installed sql server 2000. Visula stdio.net 2003 MSDN Libriary. I got some documentation in following section SQL Server Books Online But it is not intended for application developer as it seems ...more >>

HI All, This is the first time Im trying an indexed view. As to start I went through BOL and created a view with schema binding and all other settings. But I get an error when I try to create an unique clustered index on the view. I tried dropping the same indexes on the base table. The e...more >>

Hi, I have an Access project front end connected to an SQL Server database. On the access front end is an input form. I'm trying to write the data input on the form into tables by passing the information into stored procedures using parameters and INSERT INTO. However, if the data string on t...more >>

Hi, I have a pbm while connecting to the remote SQL server. Recently we hired one win2003 based Dedicated server and installed SQl server 2000. I have the sa password and the ip of the server, and when i connect to this server from my local machine by configuring client n/w utility, its givin...more >>

In QA, SELECT Table_Name, View_Definition FROM Information_Schema.Views Table_Name View_Definition ThisView CREATE VIEW... ThatView CREATE VIEW... OtherView NULL <---- HUH?!? So I flip over to EM and double-click 'OtherView' and I can see...more >>

Hi I am trying to get this to work But it only does the first 135 records. Can I use Nested loops? Thanks Daren --------- declare @Counter int declare @Counter2 int select @Counter=1 select @Counter2=1 while @Counter < 136 begin while @Counter2 < 136 ...more >>

Hi, I have this problem in a stored procedure. I have a field called "PartDescription" in a database table which accepts both "_" and "%" characters, But when I write a query for searching based on "PartDescription" field and I enter "_" in the search field then it treats it as a wildcard ...more >>

In EM , i can browse two different SQL server, each of them got over 10-20 database I n SQL server A , in SQL analyzer , i want to select some data from SQL server B. like the following, select * from [DTS-SERVER].Dts_master.dbo.port The errror is Server: Msg 17, Level 16, State 1, Line 1...more >>