Database

Maybe I'm missing something here. How can that ever return 0? If I have understood it correctly, this is what the query will do: Select the rows from TABLES with type "BASE TABLE" Group them by TABLE_NAME Counts the number of rows for each TABLE_NAME If the count is 0, return 0 If the count is not 0, return the identity of the table But, surely the only way COUNT(TABLE_NAME) can be 0 is if there are no rows in TABLES with this TABLE_NAME and type "BASE TABLE". In which case, this query would never pick up that table name, so it would never appear in the result set. So for any TABLE_NAME that this query picks up, the count will always be greater than 0.

But how are you importing the data. What encoding are you using when you read the data in? Just because the database is storing these values doesn't mean that the problem is at the database end - it's entirely possible that the problem is at the data population end. I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be Forgive your enemies - it messes with their heads My blog | My articles | MoXAML PowerToys | Onyx

Here i am giving a scalar function to active this.., GO /****** Object: UserDefinedFunction [dbo].[SplitGETPosFunc] Script Date: 10/20/2010 15:22:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[SplitGETPosFunc](@String varchar(8000), @Delimiter char(1), @POSITION INT) RETURNS varchar(1000) WITH EXECUTE AS CALLER AS begin declare @idx int; DECLARE @CHARIDX INT; DECLARE @INPUTSTR VARCHAR(8000); declare @OUTPUT varchar(1000); SET @INPUTSTR=@String; SET @CHARIDX =0; SET @idx=0; if (substring(@String,1,1)='S') begin while @idx < @POSITION begin SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR); SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1) SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR)); SET @idx=@idx+1; end end else set @OUTPUT=@String; return @OUTPUT; end @String = original string @Delimiter = delemeter (in your case space) @POSITION INT = position of the word(starts from 1) function will return the word in the specified location Thanks & Regards Rajesh B Rajesh B --> A Poor Workman Blames His Tools <--

thanks a lot Ryan

What iam sure of is that it is not an issue from SQL server. i have tried to ping the AS400 server and the reply is <1 ms then i wrote a query to check the linked server and it is giving an ok reply b4 it stops suddenly. i have seen soo many possible issues and read articles abt the same error Msg 7303 and i will be trying to update the server with hotfixes. Are there any fixes that came to your mind?

SELECT * FROM [Table] WHERE [Field] LIKE '%[%]' thatraja |Chennai|India| Tips/Tricks|Brainbench certifications Do what you want quickly because the Doomsday on 2012 :-)

I think you can add two unique constaint in File..ClientID and Data..FileID to achieve your goal. By your data design, if you want the DataField value to be unique only per client, the infomation in [File] should also unique match one [Client] record. Client: ID Name File: ID Name Bytes ClientID <<< Data: ID DataField FileID <<<

I agree with the statement that I think your reasoning is flawed. More often then not you are going to see multiple owners to a home (or any other entity) simply because of things like marriage, partnerships, trusts, etc... Also, keeping history now requires that you set up 2 tables that contain the same data linked to a building. Keeping it all in one table and adding an attribute to show current vs. past ownership I think is the better option. In fact, you are probably really going to want to separate out the table that tracks the data about the building, the tables that track the address data of all the possible owners and then use a join table to crate the linking between the two. That I think is the best way to achieve a clean design. When you consider what else you can store in this third reference table (IE: the data about the sales agent(s) involved, the inspectors used durring the sales, etc...) you start to see how it works cleaner. LinkedIn[^] | Blog[^] | Twitter[^]

why MIN?

:-D you are welcome

It was a bit of a headache but my colleague came up with materialized path design pattern: WITH ParentChildRels (ParentID, ChildID, KeyField, [Path], HierarchyLevel, Doc) AS ( SELECT ParentID, ChildID, KeyField, CAST('root/' + Folder AS nvarchar(255)) AS Path, 1 AS HierarchyLevel, Doc FROM ( SELECT dbo.Folder.ParentID, dbo.Folder.ID AS ChildID, dbo.Folder.Folder, dbo.[Document].DocumentID AS KeyField, dbo.[Document].[Document] AS Doc FROM dbo.[Document] RIGHT OUTER JOIN dbo.Folder ON dbo.[Document].FolderID = dbo.Folder.ID ) AS x WHERE (ParentID = 0) UNION ALL SELECT r.ParentID, r.ChildID, r.KeyField, CAST(RTRIM(pr.Path) + '/' + r.Folder AS nvarchar(255)) AS Path, pr.HierarchyLevel + 1 AS HierarchyLevel, r.Doc FROM ( SELECT Folder_1.ParentID, Folder_1.ID AS ChildID, Folder_1.Folder, Document_1.DocumentID AS KeyField, Document_1.[Document] AS Doc FROM dbo.[Document] AS Document_1 INNER JOIN dbo.[Folder] AS Folder_1 ON Document_1.FolderID = Folder_1.ID ) AS r INNER JOIN ParentChildRels AS pr ON r.ParentID = pr.ChildID ) SELECT DISTINCT TOP (100) PERCENT ParentID, ChildID, KeyField, RTRIM(RTRIM([Path]) + '/' + Doc) FROM ParentChildRels AS ParentChildRels_1 WHERE Keyfield is not null ORDER BY RTRIM(RTRIM([Path]) + '/' + Doc), ParentID, ChildID, KeyField I thought to share it with you all if you ever come into the same situation In Word you can only store 2 bytes. That is why I use Writer. modified on Monday, October 18, 2010 4:59 AM

How about "case when" ? case when cast(right(@dates,2) as int) > 1 then convert(char(8),dateadd(month,1,cast(left(@dates,6) + '01' as datetime(8))),112) else @dates end

If you are referring to change the ms sql server instance's collation refer this link: http://msdn.microsoft.com/en-us/library/ms179254.aspx[^] But remember to "detach" user defined databases as they will be dropped when you execute these steps. You can "reattach" the databases when you are done with changing the collation. If you want to change the collation of a database alone you could use the alter database command. Check out this link: http://msdn.microsoft.com/en-us/library/ms174269.aspx[^] As you said master collation, I guess you mean the server instance's collation. Either ways remember to detach (in case changing the server collation) or take a backup (in case you are using alter database cmd) of user databases. Cheers, Karthik

This one does execute. DECLARE @SomeLongName VarChar(100) = 'Some long text' DECLARE @Sql VarChar(1000) SET @Sql='DECLARE @temp TABLE( someCode VarChar(10) )' SET @Sql=@Sql+ 'INSERT @temp SELECT MAX(SomeCode) ' + 'FROM MyTable ' + 'WHERE \[Name\] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%''' EXEC (@Sql) The problem here is the scope in which the sql statement executes. The @temp table variable declared is different from the @temp variable used in the inline query as the scope of both the queries are different. To make the query work we need to run the query in same scope ie declare the table variable using the same SQL satements as one used for the query. When you fail to plan, you are planning to fail.

1. Technically No. Although if the distributor is in the same server, there'll be the inevitable resource waits. The publisher DB transactions themselves are not delayed per se, but the underlying resource contention can delay the transaction as a whole. Queuing is managed by the distributor. The publisher actually does nothing. The distributor will monitor the DB log of the publisher and replicate those changes to the subscribers. If the distributor is located in a separate physical server, there wouldn't be any waits. 2. Nope SG Aham Brahmasmi!

This looks like a Permission exception. Try to give your aspnet user permission and the "Administrator" user of the machine you are running the package. Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

You are welcome. :) I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

Thanks for your response. I think each set will have at most 500 elements and the super set will have at most 2000. And the set count may be about 100 in all. So I might be able to use Access. Could you please give some pointers on the design? Some brief thing about tables etc will surely help me. ...byte till it megahertz... my donation to web rubbish

Actually not. Using the results I got using J4amieC's answer I am going to try and figure out which other column in the table will make these rows unique. You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!