query database table: range

Showing posts with label range. Show all posts

Wednesday, March 28, 2012

How to remove a table after merge replication

I want to remove a table after merge replication, and i want to change the
identity range for replication
How can I modify the table or remove it from the Publication so that it can
be modified?
Regards
Srinivasan K
drop the subscriptions and run sp_dropmergearticle to drop the article.
If you are using automatic identity range management you will have to issue
the following commands.
sp_changemergearticle 'northwind','categories','pub_identity_range',300
go
sp_changemergearticle 'northwind','categories','identity_range',300
go
sp_changemergearticle 'northwind','categories','threshold',90
go
If you are not you can issue dbcc checkident to reseed the identity ranges
on both sides of the equation(s).
ie
dbcc checkident('categories',reseed,300)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Remove a table after merge replication" <Remove a table after merge
replication@.discussions.microsoft.com> wrote in message
news:F692A05B-5D94-46C7-96F0-6D7B06E07BBE@.microsoft.com...
> I want to remove a table after merge replication, and i want to change the
> identity range for replication
> How can I modify the table or remove it from the Publication so that it
can
> be modified?
>
> Regards
> Srinivasan K
>
>

Friday, February 24, 2012

How to query on a date range, display matching records but also display companies with no matchi

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

UNION ALL

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham

How to query on a date range, display matching records but also display companies with no ma

Jambi,

you can basically write an Union Sql query

UNION ALL

WHERE some 7 other company exist

The union statement will return your date range and the other 7 company data if they exist.

Ham

Sunday, February 19, 2012

How to query data by various amount of filter value ??

Generally, on any screen, we design filter screen by allowing user to identify range or one value to search.

But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.

For example

On screen which have information of people in any province.

So user would like to search it by identify no matter how value to search.

There are check box of any province on filter part which enable users choose it.

Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province.

and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.

Please give me any idea for create Stored Procedure or any tecnique to complete my idea...

Help me Pleaseeeee

Crying Crying Crying

If you want to use a stored procedure, the problem is a little more complicated, because you must use dynamic SQL. If you are using SQL 2005, see sp_executesql. If you just want to build an SQL string in your ASP page, you can set the checkboxes to postback, and handle the CheckChanged event. Build up a Comma seperated list of the values that are checked, and add a where clause to the SQL statement that looks like "WHERE province IN (" + ListOfProvinces + ")". Of course, if there is nothing checked, perhaps you want to show all the records, so you should drop the where clause, or maybe you don't want to show any, so you could just set the ListOfProvinces to an empty string.