I'd like to produce dynamic reports via SQL Server 2k5 Reporting Services that can be rendered in a web browser. However, I don't want anything to do with IIS or a web server. From what I've heard, previous version of SQL Server Reporting Services needed IIS. Is this true for 2k5? Thanks, ...more >>

The table got the company name "ABC TRADING LTD" , "Well Known SHIPPING LTD" I want to Create the shortname like "ABC TRADING","WELL know" Just need to first two name from the company name string. How can I do that ?? thanks ...more >>

Hello, I receive an error "Cannot sort a row of size 8095, which is greater than the allowable maximum of 8094." when selecting values from a table in the database. If I delete the existing data and try it, I'm not able to reproduce it again. I wonder how could this error have possibly occured...more >>

I have a sp to update all columns such as this up_update_tbl_x (@key, @col1, @col2,@col3...@col20). Now programmers want sp's like this up_update_tbl_x_1(@key, @col2), up_update_tbl_x_2(@key, @col3,@col4).... I don't want to create a bunch of sp's to update one single table. I want to consolidat...more >>

Hi there, How do I insert into Customers and Orders table in Northwind database like One customer and three orders at the same time like Insert into Customers(ComapnyName,ContactName) Values('abc','xyz') Insert into Orders (CustomerID,OrderDate) VAULES ('abc','1/1/2006') ('abc','2/1/200...more >>

According to Books online... "To remove trailing zeros from a result set when you convert from numeric or decimal data to character data, use the value 128 for style." So how come this code returns 10.2500000000? And more to the point how do I get 10.25? create table #test( Val Decim...more >>

sql server 2000. i want to generate reports for a human resource database. the report is generated every week, and it contains data from many tables (eg: employee overtime, late arrivals, leave, etc.). the report is stored for in a TEXT column to be used later. this can be done via stored...more >>

Hi Everyone, I'm running into an issue trying to update a column on a remote database. The server is linked from the server I'm calling this update statement. The syntax is as follows: UPDATE Server1.Test.dbo.Testdata SET dtUpdated = GETDATE() WHERE intID = 111 The problem is that t...more >>

Hello there Is there a way to build sql script that changes all the file growth of all the databases in the current server? ...more >>

I want to check my CATID1 field and assing a value to my VAT variable by checking some parameters. The Code below returns error 'INCORRECT SYNTAX NEAR SELECT' How can i correct that query SELECT CATID1 = CASE WHEN ProductCategory_Name ='Esofman' AND ProductCategory_ParentID=2 THEN S...more >>

Hello there I've build new datatabase for my use. I know that the file will have a lot of data: approx 10Gb min, the transfare of data will be very large. What is the best recomendation for setting file growth for the mdf file, log file and tempdb database? ...more >>

Is it possible to create a user-defined aggregate function in Sql 2005 using the CLR that accepts multiple parameters? I have code that seems like it would do the job, but I can't seem to get CREATE AGGREGATE to accept multiple parameters. Currently I'm trying to create an aggregate for a ...more >>

does anyone know if there is a limit on a table name length in SQL server 2000 when you want to BCP into the table? ...more >>

hi, Does anyone here know where the code of triggers stored? Thanks, ...more >>

I am trying to bring down my column sizes in SQL Server 2000. Original creater made them all varchar(1000) and that's way to much. A lot of these have static lengths and some have a range. What I'm trying to do is to pick a column, say last_name, and find out the maximum amount of characters ...more >>

I would like to pad the output of query result with spaces, can someone help me? ex: Name = "Abc " select name from <tablename> something like the following - select lpad(name, 60) from <tablename> so that the output is 60 characters instead of 3, it should get padded with spaces. ...more >>

I am try to write a query that would group my data this is what I have SELECT [CORP-NUM], [GL-ACCT-NUM-II-PRIN], [GL-ACCT-NUM-II-IE], [CURRENT-BOOK-PRINCIPAL], sum (dbo.current_month_09_2005.BookAmount)as [Book Amount], (dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]- dbo.current_mon...more >>

been working on a audit trigger, have the code figured out to insert what caused the trigger to fire, the value of the change, the date of change but i cant figure out how to insert who made the change, i took a stab at adding it but no dice, the code in question is under "add the audit fields" ...more >>

Hello, when i send a mail with an attachment greater than 100000 bytes using sp_send_dbmail, i get the following error message: Msg 22051, Level 16, State 1, Line 0 File attachment or query results size exceeds allowable value of 100000 bytes. 100 KB is not much nowadays. Does anybody kn...more >>

I’m trying to retrieve data from between two separate SQL server machines. I have Server A and B on separate machine. Server A contains the majority of my data and I’m developing my select on that server. I’ve discovered that I also need data from Server B. How do I accomplish this? ...more >>

hi, im tryin to create a query that will select all objects created by a particular user so.... select * from (objects) where (created by = jsmith) like that, just not sure of the exact context or if its possible ...more >>

How can I get the actual column names for granted privs. on a table? There is a column in the sysprotects table/view called COLUMNS that has a bit value in it, but I need the actual column name. Thanks, -- Jason...more >>

I have three really small tables, which gave me a lot of headache. They reside in a MS Access database and a I am accessing them through ADO.NET(VB). Can't figure out how to make a "Select" query on them. These are the tables: 1St - "Transactions" TransactionID - primary key, autogen Transact...more >>

Hi all. I'm creating a stored procedure with several input parameters. I'm creating a temp table and inserting the values of the paramters into the table. The problem is, I'm getting a must declare variable error on the parameters in the INSERT statement. I declared variables for each parame...more >>

Need some help with the following My goal is to create a unique field by Concatenating two columns. I am getting the following error "Warning: The table 'Copy_AFS' has been created but its maximum row size (928267) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE ...more >>

i have a zip code table downloaded from the usps in excel and imported into a sql database table. zipcode is not a primary key in the table, as it lists all the cities and aliases for cities that include each zipcode. however there is a rule in the data: for any given zipcode, only ONE ro...more >>

I have the following query: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[udFollowupByContactID] @ContactID int AS SELECT FollowupID AS "FollowupID", T_Followup.ContactID AS "ContactID", T_Followup.FollowupReasonTypeID AS "FollowupReasonTyp...more >>

Most if not all of the sql servers that I support are not on a SAN, and are suffering from not having enough local space. This could be mitigated if we were using some form of database dump compression. The optimum solution would be one which compresses the data as it was being initially wri...more >>

Hello! I've 3 sql server connections in the DTS package. They are: conn1 conn2 conn3 Using dynamic properties task, I set up three global variables (gv1, gv2 and gv3). For conn1 - I am getting server value from @@servername. (I am running this pkg under a sql server job). see the pk...more >>

is there a way to find out what how many read operations can be satisfied in cached memory, versus how many must hit the disk? a colleague at work said that oracle has something that will report an XX% cache hit ratio, which is exactly what i'm going for. several microsoft sql server article...more >>

I need some help with this query - I am try to get all the data where the following columns where column "sysid is equal to AFS096 but I am getting this error Invalid column name 'afs096'. afs096 is not a column its the data in column sysid column. Select corp,gl_prin_num,gl_prin_...more >>

I am trying to figure out how to communicate to some web clients when a SQL Server table has changed. I am connecting to the database through ADO embedded on the clients in web pages. I want to be able to refresh data on the client only when needed. I have researched all sorts of methodologies a...more >>

This error crept up in SQL 2005 and does not appear in earlier versions of SQL Server. The below code looks like: SELECT DISTINCT 'Test' As TestName, .... FROM .... ORDER BY 'Test' I had to change the ORDER BY to the alias TestName to get is to compile and run in SQL2005. Is there a SQ...more >>

I have the following example to do "case sensitive" search query. Instead of searching CustID and Password I would like to search "FirstName" and "LastName". Also I want the "FirstName" field becomes a "Wildcard" search before and after the "FirstName". Thanks DECLARE @CustID char(8), @Cust...more >>

create table #temp(id int,col1 int,col2 int) go select id,col1[Field1],col2[Field2] from #temp select id,col1 as [Field1],col2 as [Field2] from #temp go drop table #temp go Is there a difference between these 2 select statements in terms of the column names and results ret...more >>

I need some help with renaming about 115 columns Here is what I have thus far EXEC sp_rename 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE', 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY', 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY', 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE', 'Copy...more >>

Hello, I need to find where an sp is called from another sp or how many sp's a main sp is calling. Here is something that I tried with no luck. I know that this sp is calling other sp's or is being called by other sp's. How can I get a list of sp's related to this?? --this does not re...more >>

I have been trying to identify which version of SQL Server 2005 to purchase. I am wanting to study for the 70-431 exam. I am aware of each version of the software and features. But can not seem to find out if the SQL Server 2005 Developer Edition ($49) would be suitable for learning. It so...more >>

This is very strange... I have a stored procedure that truncates a table and does an insert back into it every 30 minutes. See below. When I run the stored procedure using EXEC UD_BUILD_UD_HIGHESTTITLEUpdated from query analyzer it takes several minutes (10+ minutes). If I run just the trunc...more >>

I need some help to write a query that will help me accomplish the following task. I need to create a Key field for one or my table – I would like to do this by joining a number or columns together ("P02_005_ACCOUNT-REFERENCE-NUMBER") & _ "P02_051_RESP-CNTR-NUM-PRIN") & _"P02_093_reg...more >>

Hi, I am using SQL Server 2000 I need to select values from a table that belongs to other database from within a stored procedure. Hardcoding table's fully qualified name is not appropriate. Table's database name must be a parameter of a stored procedure. Is there any better way than usi...more >>

Hi I would like to use the soundex function in a search facility in the company page in our CRM. Company names could be for example Smith Limited, when using soundex, it looks up in the company company column where company name begin with Smith or sound like Smith howevre the search resul...more >>

I can create this, but not run it. ALTER FUNCTION dbo.fnCompanies2 (@StartDt DATETIME, @EndDt DATETIME) RETURNS @Companies TABLE (CompanyNames VARCHAR(100)) AS BEGIN INSERT @Companies SELECT dbo.fnCompanies(ed.EndDt) FROM (SELEC...more >>

I need to compare a date field in a WHERE clause and I'm finding it very difficult to do so. I have a date stored in tables as integers like so: 20060317 etc I need to compare todays date (less a day) to get the data out of the table...so my query looks like this: WHERE IDPERI > 200601 ...more >>

Hi, I need to select data from a table in my stored procedure. The problem is that the table I am interested in resides in another database. Hardcoding fully qualified name is not an option. Database name where the table is - must be a parameter of SP. Is there any other way than using dyn...more >>

I have three integer columns in a table of about 2000 records, and I want to update a 4th column with the total of the three integer columns for each record. I tried using sum but then I get a total for the entire table, not just a total for each record. How can I do that? Thank you for y...more >>

Let's say I run a query that returns a 100k records. Is there a way to control where the cursor is in the result set. For example, can I get it to jump to row 50k and return the next 500 rows?...more >>

Hi, I have on NCHAR column which has value like.. 1:25:234567. I have to extract last part of value i. e. 234567 which comes after sign ':' . The no. of digits vary. How can i do that using TSQL . Please Help !! Thanks NJ ...more >>

I have a requirement of instantiating a job which will copy and move the newly created file once a file has been created in a folder. How can I perform this in SQL Server. Can you please give your thoughts about this. What I was planning was to write a visual basic code which will pool the ...more >>

SELECT DISTINCT(xxx) from table1 result: a b c I want the result if a then x if b then y if c then z ...more >>

How can you find UNICODE date within char & varchar fields in table. I am in the midst of a migration and need to evaluate all the fields that may have € chinese, french and thus require more char space in the new structure. This is a matter of great urgency so any help greatly appreciated. ...more >>

Hi all, Okay... this should be a pretty question, but, can't seem to figure out how to do it. In this database I'm working with, they have created 8 columns (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4). I need to get the max date (Date1, Date2, Date3 or Da...more >>

Hi, We are using SQL2000 SP3. I'm puting data into an empty fact table of a datawarehouse. There is a non-clustered PK on the table. Starting with an empty table, INSERTing 600,000 with a single select, a parallel query plan is used and it takes approx 120 sec. When using OPTION (MAXDOP ...more >>

Hi, I'm using sql server 2000 with Enterprise Manager. Lets say i have table with data inside. I want to generate script from that table that will both create the database (i know how to do that) and both will insert all the current data to the new created table. How can i do that? ...more >>

Hello, Thanks for taking the time to read my question. I am trying to modify a job so that it runs every 9 seconds. According to the documentation that is undocumented, i should be able to use @freq_subday_type = 2 ( for seconds) and any integer value for the @freq_subday_interval ( i chose 9)...more >>

I originally posted this in the DTS forum, but then thought this particular issue is more "programming" than "DTS" oriented...so I am not multi posting. I've also changed the code slightly (found a couple of glaringly obvious errors!) I need to write a DTS package which is date oriented and...more >>

Hi, I've noticed problems when trying to print out a database diagram from SQL Server 2005. It used to work OK in 2000 but now you get a greyed out area at the bottom of your table and it doesn't look clear. Basically it doesn't appear the same way it appears on the screen. It may just t...more >>

Hi all, I have a SQL 2005 instance with 4 dbs in it. In three databases, the structure is identical (data is from 3 seperate companies). Is it possible to access any of the three databases, from sprocs in the fourth (master) database, so I don't have to rewrite all the sprocs three times? ...more >>

Hi , I have created a trigger as follows : ================================================================= SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER trigger Usr_Trig_GL53 ON Gl530106 FOR INSERT , UPDATE NOT FOR REPLICATION AS IF UPDATE(GL53001) OR UPDATE(GL53003...more >>

I'm working with an organisation chart splitted in two tables. Groups and Users as follow Groups /****** Object: Table [dbo].[CustomerGroups] Script Date: 03/20/2006 11:37:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CustomerGroups]( [CustomerG...more >>

Hello, I have a script that runs as a logon procedure and writes information to a sql 2005 database. For some reason users are now getting access denied. The script runs just fine for domain admins. Users who need to run this script have been defined as data writers on the sql database and th...more >>

update DTS_ACCOUNT_SZ.dbo.arinvchg set chgcode = (select SRC.chgcode from DTS_TEST.dbo.chginfo_src SRC where DTS_ACCOUNT_SZ.dbo.arinvchg.chgcode = SRC.oldchgcode) I got a matchtable , named chginfo_src with two fields, chgcode and oldchgcode I want to update the arinvchg tables. I try the...more >>

Hi, I have some kind of stored procedure AddToMyAssortment @ShopID int, @ProductID int. In some other stored procedure i have a query like this SELECT [productid] FROM [product] WHERE [AddToAssortment] = 1 Now i need every productid that the query returned execute with the first sp. I'...more >>

I do not know the technical term for the type of SPROC of the type: SELECT Products.ProductID, Products.ProductName, Products.StoreID, Products.Price, Products.Description FROM Products INNER JOIN Media ON Products.ProductID = Media.ProductID WHERE (Prod...more >>

Can i use 'Like' in a Join ? I have two tables one has kewords in and the second has descriptions in, i want to return the id of the first Table if the keyword is contaied in the desription field of the second ie Table 1 ID Keyword 1 Green 2 Red 3 Blue Table ...more >>

i used to use Query Analyzer to sort out data. Can i export the result "WITH THE HEADER"? Thanks a lot. tony ...more >>

Hi, I am running sql2000 sp3a in a web enviroment. If I change the transaction isolation level to READ UNCOMMITTED inside a stored procedure do I have to change it back to READ COMMITTED before existing the stored procedure in order to bassure that the client (a browser) will be runnin...more >>

Hello there I've been heard that after amount of data on table (Approx 1,000,000 records and more) according to the type of index, the index performance is become mutch more slower. Does someone knows what is the limitation and how can i handle of it? ...more >>

Before formatting the server the user took the backup of the database. After the installation the database was restored but we can find the all the user in resored database obivously the all user are not in the logins under the security. Is there anyway to copy the all the user from resoted dat...more >>

update arinvchg set chgcode = (select pTable.chgcode from pTable ,arinvchg where pTable.oldchgcode = arinvchg.chgcode) As i process the above statment, I got an error about " ¤l¬d‰{ªð¦^ªº­È¦h¤_¤@ŸÄ¡C?¤l¬d‰{¸ò•ò¦b =¡B!=¡B<¡B<=¡B>¡B>= ¤§¦Z¡A©Î¤l¬d‰{¥Î§@ªí}¦¡?¡A?Ïú±¡úG¬O¤£¤¹?ªº¡C ?¥y¤w?¤î¡C" ...more >>

select myTableA.chgcode from myTableA ,myTable B where myTableA.chgcode = myTableB.chgcode (where myTableB is import from excel) and I got the following error,. üÓªk¸Ñú¨ equal to ¾Þ§@ªº±Æ§Ç??ú}¬ð¡C (In English, it seems said "it cannot solve the (equal to) order problem ) Please help ...more >>

Hi All, (It's Monday morning, so please forgive the brain-lapse). I need to update a table en masse with records from another table. I have: [SSM01] Supplier Code, Supplier Name that needs to update: [SSW85] Supplier Code, Supplier Name. I need to only use the first record found in SSM...more >>

declare @oldchgcode nchar(20),@chgcode nchar(20),@tmpchgcode_branchid nvarchar(50) declare tmp cursor for select oldchgcode ,chgcode from DTS_MASTER.dbo.chginfo where oldchgcode is not null and oldchgcode <> chgcode order by oldchgcode I can declare a cursor very well and run in the first ...more >>

Dear Friends, I am having Sql Server 2005 Express Edition. Also I have installed "Sql Server Management Studio Express". In "Sql Server Management Studio Express", how to export a table to a csv file, or to another table? Thanks & Regards, Thirumalai. ...more >>