Database Discussion Boards

First Prev Next

Re: Get Count Of Duplicate Records Using A Loop

Tim Carmichael15-Sep-09 8:29

Tim Carmichael

15-Sep-09 8:29

1. What type of database?
2. If using SQLServer, consider the following:
select count(*) from security_data
where charindex('Test',Resource_Desc) > 0

The table and column represent my data, but you should be able to modify it to your needs.

Tim

Re: Get Count Of Duplicate Records Using A Loop

rowdykuttan15-Sep-09 8:40

rowdykuttan

15-Sep-09 8:40

Hi tim

Thank your for your reply yes am using SQL2000

Re: Get Count Of Duplicate Records Using A Loop

Tim Carmichael15-Sep-09 8:53

Tim Carmichael

15-Sep-09 8:53

You're welcome.

Re: Get Count Of Duplicate Records Using A Loop

rowdykuttan15-Sep-09 9:11

rowdykuttan

15-Sep-09 9:11

Hi Tim

select count(*) from CustomerDetails where charindex('@',EmailAddress) > 0

Here its showing total record count, i wish to get the count of emails with @gmail.com @yahoo.com, but i cannot hard code this cause the list is so big that it has to run a loop by itself

Kind regards
James

Re: Get Count Of Duplicate Records Using A Loop

Tim Carmichael15-Sep-09 9:20

Tim Carmichael

15-Sep-09 9:20

Using a different table on my end...

select distinct substring(email_distribution,charindex('@',email_distribution),len(email_distribution)), count(*)
from bow_reports
where charindex('@',email_distribution) > 0
group by substring(email_distribution,charindex('@',email_distribution),len(email_distribution))

Again, customize to your use.

email_distribution should be equivalent to your emailaddress
bow_reports should be equivalent to your customerdetails

Tim

Re: Get Count Of Duplicate Records Using A Loop

rowdykuttan15-Sep-09 9:23

rowdykuttan

15-Sep-09 9:23

Dear Tim

Thank you very much it worked Smile | :)

Kind regards
James

Re: Get Count Of Duplicate Records Using A Loop

PIEBALDconsult17-Sep-09 5:40

PIEBALDconsult

17-Sep-09 5:40

I would write a user-defined function that returns the domain and then group by it.

Group header is not printing on everypage if subreport continues to next page.

Kumaran21cen15-Sep-09 3:26

Kumaran21cen

15-Sep-09 3:26

Hi Everybody,

I have an issue in the SQL Server Reporting Services
I created InvoiceReport.rdl

In the Page header displays Title, Page Footer displays Page Number
In the Body section, I created a Table,
The structure is

1) Table Header
2) Group header Row1
3) Detail Section
Row1 [SubReport1]
Row2 [SubReport2]

The problem is, if the subreport print more than a page then Group Header is not repeating in every page other-wise it is printing

I also tried to check only ONE, either (1) Row Headers – “Repeat header rows on each page” OR (2) Column Headers – “Repeat header columns on each page”,

Iam waiting for yours valuable respose.

Thanks & Regards
Kumaran

How to Change Color of the data???

Aman786Singh15-Sep-09 2:47

Aman786Singh

15-Sep-09 2:47

hi ,

I am sending a mail using SQL server 2005 msdb.dbo.sp_send_dbmail , below written is my code:-
I need to change the color of the data . depending upon the value , i want to change it's color.
Kindly help me out.

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Cummalative Airtel Report generated every half hour</H1>' +
N'<table width="100%" style="font-family:Arial; font-size:12px; color:#000066" border="1" cellspacing="0" cellpadding="0">' +
N'<tr><td width="20%"><b>Circle</b></td><td width="11%"><b>OfferedCalls</b></td>' +
N'<td width="11%"><b>AnsweredCalls</b></td><td width="11%"><b>AbandonedCalls</b></td>' +
N'<td width="11%"><b>PCA</b></td><td width="11%"><b>ATT</b></td>' +
N'<td width="11%"><b>FCR</b></td>' +
N'<td width="11%"><b>CQ</b></td>' +
N'</tr>' +
CAST ( (Select td =t.circle, '' ,td = t.offd, '' ,td =t.answ, '' ,td =t.abnd, '' ,
--td =Convert(char,Round(t.[%abnd],2)),
-- '' ,
td =Convert(char,Round(t.pca,2)), '' ,
td =Convert(char,round(tt.SumAtt/Answ,2)), '' ,--td =ttt.[login], '' ,
td =Convert(char,Round(ttttt.[CQScore],2)),'',
td=Convert(char,Round(fcr.[fcr],2))
from <table_name>

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name='MailProfile',
@recipients='abc@gmail.com',
@subject = 'TestReport',
@body = @tableHTML,
@body_format = 'HTML' ;

Regards,
Aman

Replication In SQL Server 2005?

cocoonwls15-Sep-09 0:44

cocoonwls

15-Sep-09 0:44

Hi all,

I am facing a problem on whether using Transactional replication or Merge replication technique in SQL Server 2005. In my senario, i have 2 database which located in one sql server,named D1 and D2. Now, i would like to sync some of the table between these 2 database, and the data will frequently update/delete/insert by user into D1 or D2 and user should have the latest data for proceed operation. So, may i know which technique should be use in this senario?

Any suggestion or tips are welcome.

Note: And what if the 2 database are located in different server(different location)?

thanks in advance
cocoonwls

Re: Replication In SQL Server 2005?

Hulicat15-Sep-09 7:39

Hulicat

15-Sep-09 7:39

I had the same scenario and ended up using transactional replication @ 2 hour intervals. I was going @ 15 minute intervals with no issues but I wanted to have some latency in the event the source DB has some problems it gives me time to shut down the replication.

I didn’t use merge because if one DB gets hosed or corrupted they both get hosed.

I hope that helps.

Regards,
Hulicat

Re: Replication In SQL Server 2005?

cocoonwls15-Sep-09 8:13

cocoonwls

15-Sep-09 8:13

Hi Hulicat,

Thanks for your helping hand. Is Transactional replication bidirectional? i need to do sync for both side, that mean either D1 or D2 update, then both of them are update. And the interval time is my piority concern, i have to do it as fast as possible to allow user to use the latest data, may be in 30sec or even per transaction.

thankx in advance.

regards
cocoonwls

Re: Replication In SQL Server 2005?

Hulicat15-Sep-09 10:08

Hulicat

15-Sep-09 10:08

You need to use "merge" if either database can be updated,

Regards,
Hulicat

Re: Replication In SQL Server 2005?

cocoonwls15-Sep-09 20:16

cocoonwls

15-Sep-09 20:16

Hi Hulicat,

Thanks for your information.I got it Wink | ;)

.But then is there any solution/suggestion to prevent the problem you were mentioned in your first post?

regards
cocoonwls

Re: Replication In SQL Server 2005?

Hulicat16-Sep-09 6:33

Hulicat

16-Sep-09 6:33

Not to my knowledge; it's one of the caveats of using "merge" replication.

Regards,
Hulicat

Oracle query

sandhya1414-Sep-09 23:22

sandhya14

14-Sep-09 23:22

I have a storedprocedure in oracle,in that am using
begin
OPEN RCT1 FOR
SELECT REPLACE(SelectStatus.SelectCommand, '@Location',
SelectStatus.Location) INTO
SelectStatus.SelectCommand FROM DUAL;

end;

My output is :-

Select * from area_master where location = '001' order by area_name

what is want is instead of getting this query as output,i want the result of this query as output..

how can i execute this query inside the SP?...

Any help will be appreciated
thanks in advance...

Re: Oracle query

sandhya1415-Sep-09 1:35

sandhya14

15-Sep-09 1:35

I have done it using cursor....
now its working... Smile | :)

sync services

amer shammout14-Sep-09 21:13

amer shammout

14-Sep-09 21:13

i have a tow server one of theme i set the main database and the ather i set the the application the target of the application is to get data from 50 site to main database i can't change this structure
and i can't access to main database server except from application
i want to use syncronization service to sync data between main db and 50 site can syncronization service help

local processing, where the control renders the RDL file itself

sashidhar14-Sep-09 19:51

sashidhar

14-Sep-09 19:51

Hi I am New To SSRS.

I Have Gone through the Wikipedia about SSRS In That I have seen two ways

The ReportViewer control processes reports in one of two ways: (a) server processing, where the report is rendered by and obtained from the Report Server; and (b) local processing, where the control renders the RDL file itself.

Please Can Any one Give me the links to work in the second way ie
local processing, where the control renders the RDL file itself
Any Help is Greatly Appreciated

Thanks

If It Helps Click It as Answer

Making a Specific Stored Procedure For Search Keyword

ivadak14-Sep-09 4:35

ivadak

14-Sep-09 4:35

Hi !
I want to create a stored procedure for Search keyword with UNION, Sub query.

My DataBase includes same types of Board Tables. (Such as board_a , board_b ...)

I tried to make as below.

CREATE PROCEDURE sp_Search
	@page int = 1,
	@pageSize int = 10,
	@keyword varchar(50)

AS

	Declare @Sql varchar(3000)
	Set @Sql = "Select Top " + Str(@pageSize) + " no, subject, content from  "
	Set @Sql = @Sql + " ( "
	Set @Sql = @Sql + "			Select tb, no, subject, content from ( "
	Set @Sql = @Sql + "				Select 'board_a' as tb, no,subject,content from board_a "
	Set @Sql = @Sql + "				UNION "
	Set @Sql = @Sql + "				select 'board_b' as tb, no,subject,content from board_b "
	Set @Sql = @Sql + "			) As A "
	Set @Sql = @Sql + " )   where no NOT IN "
	Set @Sql = @Sql + " ( "
	Set @Sql = @Sql + "		Select Top " + Str(@pageSize * (@page-1)) + " no from ( "
	Set @Sql = @Sql + "			Select tb, no, subject, content from ( "
	Set @Sql = @Sql + "				Select 'board_a' as tb, no,subject,content from board_a "
	Set @Sql = @Sql + "				UNION "
	Set @Sql = @Sql + "				select 'board_b' as tb, no,subject,content from board_b "
	Set @Sql = @Sql + "			) As A "
	Set @Sql = @Sql + "     )  "
	Set @Sql = @Sql + " ) where subject Like '%"+ @keyword +"%' or content Like '%"+ @keyword +"%' order by no ASC " 
	Execute(@Sql)

But I got a Error Message.
I am having extreme difficulty in working time.

Please can someone help me? Wink | ;)

Re: Making a Specific Stored Procedure For Search Keyword

Henry Minute14-Sep-09 5:59

Henry Minute

14-Sep-09 5:59

I do not have the skills to help you, but it might help others to do so if you edit your post to include the full error message, rather than just say that you get one. Cut and paste if you can, to avoid typos. Smile | :)

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: Making a Specific Stored Procedure For Search Keyword

Abhishek Sur14-Sep-09 9:43

Abhishek Sur

14-Sep-09 9:43

what is the error message ?? Confused | :confused:

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

Re: Making a Specific Stored Procedure For Search Keyword

Ashfield14-Sep-09 20:56

Ashfield

14-Sep-09 20:56

Try printing your sql (replace Execute(@Sql) with print @SQL), then paste it into query analyser and try running it from there to get more information on the error.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Converting .MDB file to .MDF in sql server 2005

sarang_k13-Sep-09 23:33

sarang_k

13-Sep-09 23:33

Hi all,

I want to convert a .mdb(MS-Access database) to .mdf (SQL Server 2005 database).How can i do it.

Thanks in advance.......

Re: Converting .MDB file to .MDF in sql server 2005

SeMartens14-Sep-09 0:36

SeMartens

14-Sep-09 0:36

Hi,

If you got the SQL Server manager, you can "Import" the mdb-file. Create a blank sql-database, right-click within the object explorer and select "Tasks->Import"...

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Last Visit: 31-Dec-99 18:00 Last Update: 23-Sep-24 16:12

Refresh

ᐊ Prev 1...764 765 766 767768769 770 771 772 773 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.