I have an application that allows the user to select various fields to display and/or use as the search criteria to locate a record and I then build the entire SQL clause in code and execute the SQL clause from code. The records returned are actually a UNION of 4 different VIEWS. Is it pos...more >>

Hi, I have a problem with varchar(8000) in my trigger. It is a big problem for me that the X and Y variables below are limited by 8000 characters, how can I make them larger? I can illustrate it like this: Create database AuditTest Create table Audit ( AuditId int identity, ...more >>

Hi Experts, Is it possible to create a primary key without Clustered index? If yes, where exactly would you use it? Regards Reshma...more >>

Hi, Anyone of you know how to change the default value for an existing column? I have the script below but it might be a problem to run this script on different server because the default constraint name might be different. Any efficient way to solve this problem? ALTER TABLE AcctSettings D...more >>

Is there a way to get all the SQL Server instances with SQL SMO from a remote computer? I got few instances on some remote IP and how to list them, how to pass to the function this address - could it be done at all? Second question: Locally I'm using SmoApplication.EnumAvailableSqlServ...more >>

When I run a DTS, I get the following error. Error I get: "activex component can't create object: 'excel.application'" I was told not to load excel into sql server. Well, in any case, there should not be any need to load excel, it recognizes the excel component in the DTS, that means, it shou...more >>

Hello all. I know most of you won't believe this problem, but I've seen it with my own eyes: For our year-end processes we establish four tables in our SQL database and populate them. Two of the tables have millions of records, the other two only have a handful of items. One day, everythin...more >>

Hi I have these tables: Document Table DocumentID PK ....... List Table ID PK DocumentID FK Customer Notes For each Document Table record there can be up to 6 List Table records. Return List Records where for each instance of a Document one List.Notes record contains the te...more >>

if I run this declare @c int,@y int select @c=10005143,@y=2005 insert @hit select id=startmm,hit=count(id) from memlog where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y group by startmm it takes 10 seconds but put it in a function, like below, and...more >>

I posted a question here on 12/6/05 about importing data from .csv file and no one addressed it. Here is the link http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?pg=5&guid=&sloc=en-us&dg=microsoft.public.sqlserver.programming&fltr= And the title is Sub...more >>

I have a folder full of date-stamped files: 010101.txt, 010201.txt, 010301.txt, etc. I have a working bcp command that points to a specific file. declare @bcpCommand varchar(1000) select @bcpCommand = 'BCP "Import.dbo.NAV" in "d:\folder\010101.txt" -c -T -t, -S "SERVER1\SERVER1"' EXEC mast...more >>

Hi , I have a Stored procedure that dynamically creates a select statement. I keep getting 3 result sets and I only want one from the SELECT. The 2 unwanted ones come from 2 stored procedures that set variables that are required in the SELECT. They are EXEC @PostcodeRange = dbo.Syst...more >>

time stamp magicaly updated when ever update event occures on a row in sql server 2000 i have a table. when i do any update on the table, a field of the effected rows gets a new time stamp, there are no triggers on the table, nothing special in the DDL of the table, is there any other way bes...more >>

Hello, We bulk insert terabytes of data in delimited flat files every day. I recently heard from our DBA that using bcp especially in native format would result in reduction in file size and much faster inserts. Since we transfer these files across the network, I think we would save a lot of ...more >>

Hi all, I am looking at doing the MCDBA certification. Does any one have any training guides they suggest? Web links or resources that might help? General tips? Is 2005 certification avialable yet? Cheers, Adam ...more >>

Hi, I have been passing commandline information to replimerg.exe to initiate a pull merge synchronization from SQL express to a SQL Server. This works great over a network connection, but now we are trying to move to a HTTPS model of merge synchronization and when we run the replimerg.ex...more >>

I get an error when I try to do cast inside check constraint like the following: CREATE TABLE [dbo].[mytbl] ( [mytblid] [int] IDENTITY (1, 1) NOT NULL , [month] [int] NOT NULL , [year] [int] NULL ) GO ALTER TABLE [dbo].[mytbl] ADD CONSTRAINT [myconstraint] UNIQUE NONCLUSTERE...more >>

I need to set up an automated way to condense log files in a database. I successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I was thinking that if I can create a stored proc that runs through all the tables in a dabase, I could schedule that to run and it would always keep m...more >>

I have an EXEC like this and is part of the whole script: EXEC ('RESTORE LOG NETOX FROM DISK = ' + '''' + '\\10.81.60.2\D$\Des_LogShip\' + @logfiles + '''' + ' WITH DBO_ONLY, STANDBY = ' + '''' + 'D:\LogShip_Undo\undo_LOGNETOX.ldf' + '''') I want to know what the return status whether or n...more >>

Hi, I've a problem to check if there is a duplicate entry. In my table i have a computed column which checks for a certain type of entry which must be unique. E.g. there can be two types of entries. One which has a type of 1 where period can be 6 or 7. The other type is 2 where period ca...more >>

hi there, any sql code example out there to do this? thanks, Paul ...more >>

Greetings, I have a "newbie" question in relation to dates. I am trying to append the year, month, and day to one another in order to create a new date. The SQL I am using is below. The error message I am receiving is "The conversion of a char data type to a datetime data type resulted i...more >>

Greetings all. I'm a complete SQL novice, and well, I'm not even using SQL, but I have a dBase control which utilizes SQL SELECT statements to pull a rowset from a .DBF table. I have a small table with a few fields, but for this query I only need to deal with the fields NAME, EMAIL1, and EMAIL...more >>

Is there a way to search for a specific item through a various number of tables? I want to find out , for example, if there is a name of user in some of three tables in database. Hrcko ...more >>

I have a stored procedure which has 2 output parameters, namely @RecCnt and @RetCode. In the stored procedure, I am using the SET statements to pass the data back. I am calling the stored procedure from my VBA code. I use objCmd.Execute options:=adExecuteNoRecords. I am able to retrieve onl...more >>

Hi I have the following sql statement: BEGIN SET @SqlString =3D N'INSERT INTO ' + @NewAccountList1 +=20 ' ([Date], SubRef, Company_Company, Company_1_Company, PubCode, = StartIssue, EndIssue, Copies, RateCode, [Total=A3Received], VAT, = Royalty, TranType) SELECT Transactions.[Date] AS ...more >>

I have a table that looks like this: Customer | PartNumber | Jan | Jan$ | Feb | Feb$ | Mar | Mar$ ... The data in Jan, Feb, Mar... are the sales quantities for the month, and the data in Jan$, Feb$ and Mar$ are the sales dollars for the month. I would like to unpivot it, so that the Jan,F...more >>

How do I get the individual awrv sums for each region_group, I'm real close but no cigar.... I've used coalesce as each record will only have one install_date based on the sale stage of 50, 75 or 90 Each record has the total sum(awrv) not the specific region_group in my ddl & qry below. ...more >>

SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, GETDATE()) AS DaysOld, FROM TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) If you look at this SQL statement I created a new "field" called DaysOld with the AS keyword. Why does this not allow me to put parameters in the ...more >>

Hi, Does anybody know why Server Integrated Security Properties like "Default Domain" and "ImpersonateClient" returns "[SQL-DMO] This method or property is not implemented for this release." when queried through SQL - DMO on a machine having SQL 2005 (Yukon) . TIA Prasad. ...more >>

Hi, I am using a query tool that only allows me to define "custom sql" in the WHERE clause. I want to specify that sales_date be between the first of the year and last day of the most recent full month. The query will run on the 3rd day of the month. I have so far sales_date > = conve...more >>

Is is possible to change the sender when using XP_Sendmail? I am sending a notification that reports are available in reporting services in a single email and want to change the sender from SQLSERVICEADMIN to something users will recgonize easily. Thanks for the help!! Anthony...more >>

Hello- I am having a problem with a Web Service written in Visual Studio 2005. I have a typed dataset that I created using the Visual Studio 2005 Data Source tool. This retrieves its data from a SQL Server 2000 database. My client calls a web service which calls a business component to ret...more >>

Hi, I have column with values January, February and so on. I need to perform sort based on months instead the system sorts it by Alphabetical Order. Any Hint? Thanks Manny...more >>

Hi, In a previous post I've asked a question which in the end boiled down to the differences between clustered & non-clustered indexes. I am not using clustered indexes in general but people here suggested that I should. Here is an example of a query that should have made a difference ...more >>

I'm trying to extract data from our Accounting Database and use it in another database that's used for our web site. Last month I created a SQL Select Query to give me the product information I need to put into our web site database. I then used DTS to copy that data from one database to a...more >>

Hi, We occasionaly have performance problems on our SQL 2000 SP4 db, during these times we see CPU use shoot up, along with lock timeouts (upto 1000's) in perfmon. We suspect that the lock timeouts are the main problem, which then causes CPU to go up. We have run profiler during these perio...more >>

I have a front-end app in a small, closed domain where the users create tables owned by dbo through a stored procedure that calls sp_executesql aginst a second stored procedure. I had hoped that I could put a Grant line in this second stored procedure, but it refuses to allow it with "Grantor do...more >>

In VBS, I can use User = "Test, of, the, script" strUser = Split(user,";") strUser(0) would be "Test" strUser(1) would be "of" Is there an equivalent statement in SQL? ...more >>

hello everyone, this was so oddball, i didn't even try looking for it in the groups, because i wouldn't know what to search for. here's what i'm trying to do (abstractly - this is not a real table, what i'm curiosu about is the update statement): create table intvalues ( int value not null ...more >>

Hi, Check out the following statement... select * from master_client where gid not in (select gid from review_allocation) This statement doesn't return any rows back and I was surprised! However, the problem is that the column Gid does not exist in table review_allocation, so why on...more >>

I have a situation where attending a meeting could be either staff or coalition members. But don't know how to enforce a foreign key constraint. any ideas ? Table Meeting MeetingID int NOT NULL, AttendeeID int NOT NULL Primary Key (MeetingID, AttendeeID) Table Staff StaffID int...more >>

The select statement references the field CdeDiag1 which is an nvarchar(255) datatype. Simplified it looks like this: Select Distinct RecipBaseID, CdeDiag1 from Table Where CdeDiag1 Like '368%' Or CdeDiag1 Like '378%' What I'm trying to get is all diagnostic codes that begin with '368' or...more >>

Anyone got any idea why i can't copy and paste a record either in a form or datasheet when the table is in SQL Server? The Paste commands are also greyed out on the Edit menu drop down. They get greyed out after I do the the Menu Copy. Thanks. ...more >>

Hi Guys, Is there any way by which we can find out very recent deleted or inserted entry, but it should work for whole database not for a specific table. It is very easy to get this information from a single table, but how can we get this informa...more >>

Hi, I have a list of table which I need to replicate to another database. First of all, why do I need a primary key to achieve replication (sorry I am new with replication) Then I've noticed that if I just click the primary key button from EM on Design table, then it defaults to a cluste...more >>

Hi I need to use the VB function "strconv" in my SP. Is it possible? Can anyone help? Best regards David Lau...more >>

I have a single AFTER trigger for both an UPDATE and an INSERT action. If I want to know which action was actually issued, I do the following: IF ((SELECT COUNT(*) FROM deleted) > 0) Is there a better or more efficient way? There isn't some @@ variable I don't know about, is there? ...more >>

Hi All! I observed a very strange behaviour concerning the trigger handling (IMHO). Imagine you have a table with an delete trigger, which is performing a lot of business logic. So if you execute a delete statement which never matches a record in this table, the trigger will be fired nevert...more >>