Database Discussion Boards

amistry_petlad13-Jul-08 14:47

13-Jul-08 14:47

this answer is not related with my question .

like simple select * statement inside procedure

I didn't get your answer

Re: help me to write procedure

leoinfo13-Jul-08 17:21

leoinfo

13-Jul-08 17:21

Create procedure sp_Fetchrecords
@tbname sysname=NULL
AS
SET NOCOUNT ON
IF @tbname IS NOT NULL 
BEGIN
	DECLARE @cmd NVARCHAR(4000)
	SET @cmd = 'Select * from '+@tbname
	EXEC(@cmd)
END
go

Re: help me to write procedure

amistry_petlad13-Jul-08 17:57

amistry_petlad

13-Jul-08 17:57

thanks
can you explain me why we have to execute the set command in procedure
and one more question what is dynamic procedure and why should we have to use it.
many thanks for your help Rose | [Rose]

Re: help me to write procedure

Paul Conrad13-Jul-08 18:06

Paul Conrad

13-Jul-08 18:06

amistry_petlad wrote:
explain me why we have to execute the set command in procedure

Here is an explanation for SET NOCOUNT ON[^].....

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: help me to write procedure

amistry_petlad13-Jul-08 18:12

amistry_petlad

13-Jul-08 18:12

nO SORRY set is for
SET @cmd

Re: help me to write procedure

Paul Conrad13-Jul-08 18:16

Paul Conrad

13-Jul-08 18:16

I just realized there was the second one. The second one sets a variable that is the sql string, and the exec(...) executes the sql string.

You could get really fancy with this stored proc, such as adding where clause, and order by as parameters into the stored proc, and add them to the @cmd variable.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: help me to write procedure

nelsonpaixao13-Jul-08 15:12

nelsonpaixao

13-Jul-08 15:12

Sorry if i can´t help you there, i sure more advanced programmer will help.
Good luck

Re: help me to write procedure

amistry_petlad13-Jul-08 17:58

amistry_petlad

13-Jul-08 17:58

thanks

decimal not show zero [modified]

tai-fun12-Jul-08 3:38

tai-fun

12-Jul-08 3:38

i can same englis so i hope you are under stand of my writes.

---
i have a decimal(8,3) value(sql server 2005). this decimal value showing as 3,400 45,670 etc of query.

i want show as 3,4 ... 45,67 of query.

modified on Saturday, July 12, 2008 9:46 AM

Re: decimal not show zero

leoinfo12-Jul-08 7:07

leoinfo

12-Jul-08 7:07

Use CAST or CONVERT. See below...

CREATE TABLE #T (x DECIMAL(8,3)); 
INSERT INTO #T (x) SELECT 3.400; 
INSERT INTO #T (x) SELECT 45.670; 

SELECT x, CAST(x as float), CONVERT(float, x) FROM #T; 

DROP TABLE #T;

row to column in sql

ravindra_ee24811-Jul-08 19:58

ravindra_ee248

11-Jul-08 19:58

SELECT clientid,sum( isnull([CDMAWeB],0)) as cdmaweb,sum(isnull( [GSMWEB],0)) gsmweb,sum(isnull([CDMAURL],0))cdmaurl,sum( isnull([GSMURL],0))gsmurl,sum(isnull([CDMAXML],0))cdmaxml,sum( isnull([GSMWXML],0))gsmxml FROM
( SELECT TBL_REQUEST_DETAILS.ClientID, SUM(TBL_TPM_CAMPAIGN_DETAILS.CountOfSMSSent) AS count,
CASE
WHEN LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4)
= '9193'and TBL_REQUEST_DETAILS.RequestReceivedVia ='WEB'
THEN 'CDMAWEB'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) = '9192'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='WEB'
THEN 'CDMAWEB'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) = '9193'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='URL'
THEN 'CDMAURL'
WHEN LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) = '9192'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='URL'
THEN 'CDMAURL'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4)
= '9193'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='XML'
THEN 'CDMAXML'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) = '9192'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='XML'

THEN 'CDMAXML'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) != '9192'
and LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) != '9193'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='WEB'
THEN 'GSMWEB'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) != '9192'
and LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) != '9193'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='URL'
THEN 'GSMURL'
WHEN
LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) != '9192'
and LEFT(TBL_TPM_CAMPAIGN_DETAILS.mobilenumber, 4) != '9193'
and TBL_REQUEST_DETAILS.RequestReceivedVia ='XML'
THEN 'GSMXML'
END AS 'bearer',
TBL_REQUEST_DETAILS.RequestReceivedVia

FROM TBL_TPM_CAMPAIGN_DETAILS INNER JOIN
TBL_TPM_SMSC ON TBL_TPM_CAMPAIGN_DETAILS.SMSCID = TBL_TPM_SMSC.SMSCID LEFT OUTER JOIN
TBL_REQUEST_DETAILS ON TBL_TPM_CAMPAIGN_DETAILS.RequestDetailsID = TBL_REQUEST_DETAILS.RequestID
GROUP BY TBL_TPM_CAMPAIGN_DETAILS.MobileNumber, TBL_REQUEST_DETAILS.ClientID,
TBL_REQUEST_DETAILS.RequestReceivedVia
)AS S PIVOT ( SUM (count) FOR bearer IN ( [CDMAWeB], [GSMWEB],[CDMAURL], [GSMURL],[CDMAXML], [GSMWXML]) ) AS pvt
group by clientid

Sunny

Re: row to column in sql

Vimalsoft(Pty) Ltd13-Jul-08 15:55

Vimalsoft(Pty) Ltd

13-Jul-08 15:55

Seriously no one will read or help you on this one, i thought you were publishing an article , if so its a wrong place , come on dude, explain you problem and point where you get an error, dont just post your ehole script

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Mail Triggering in SQL_Server Problem

sabaMCA11-Jul-08 13:43

sabaMCA

11-Jul-08 13:43

hi friends,

I need to trigger a mail when i update data from a particular table. I got a Procedure for mailing, it executed successfully. but, when i update the data from a table, below error was Accured.

xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

how can i solve this problem? can anybody faced this before, give me a reply.

Thanks in Advance!!!
Sabarees

Re: Mail Triggering in SQL_Server Problem

Vimalsoft(Pty) Ltd12-Jul-08 11:11

Vimalsoft(Pty) Ltd

12-Jul-08 11:11

Stop Reposting

Mail Triggering in SQL_Server Problem

sabaMCA11-Jul-08 13:42

sabaMCA

11-Jul-08 13:42

hi friends,

I need to trigger a mail when i update data from a particular table. I got a Procedure for mailing, it executed successfully. but, when i update the data from a table, below error was Occurred.

xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

how can i solve this problem? can anybody faced this before, give me a reply.

Thanks in Advance!!!
Sabarees

How to store stored proc results to a temp table

DotNetXenon11-Jul-08 10:52

DotNetXenon

11-Jul-08 10:52

insert into #temp exec dbo.MyStoredProc 89,1
(MyStoredProc requires 2 integer parameters.)

The above works fine but in my stored proc where I write this insert statement, I need to create the #temp table and MyStoredProc results in 100 columns.
Only 5 columns have to be stored in #temp.

I need something like below. (As you would know, am making it clear, below does not work!!)
select col1,col2,col3,col4 into #temp
exec dbo.MyStoredProc 89,1

------------------------------------------------------------
"The only true wisdom is in knowing you know nothing." --Socrates

Re: How to store stored proc results to a temp table

Vimalsoft(Pty) Ltd12-Jul-08 11:17

Vimalsoft(Pty) Ltd

12-Jul-08 11:17

If i understand you well, you want to insert records into a Temp table and you expect to have atleast 100 records because the Source have atleast 100 records and what you get in that temp is 5recs?

SQL Server connection timeout [modified]

dlarkin7711-Jul-08 3:44

dlarkin77

11-Jul-08 3:44

Hi,

This isn't a programming question so I hope it's okay to post it here.

I'm having trouble trying to connect to SQL Server on a colleague's PC. I keep getting a Timeout Expired error.

I decided to disable the firewall on her machine just to see if that was what was causing my problem. As soon as I disabled it I was able to connect.

Obviously I don't want to keep her firewall disabled so I re-enabled it. But I don't know what exceptions to add to it. I've already added sqlservr.exe but I still can't connect.

I'm not sure if this is related to my problem but I just noticed that the SQL Server Browser service isn't running. When I try to start it I get the following message:
Error 1067: The process terminated unexpectedly

Can you suggest what I should try next?

Thanks,

dlarkin77

Moved on Friday, July 11, 2008 11:59 AM

Re: SQL Server connection timeout

Simon P Stevens11-Jul-08 3:48

Simon P Stevens

11-Jul-08 3:48

dlarkin77 wrote:
Can you suggest what I should try next?

SQL server documentation
SQL server forums
SQL server online support
Google

Smile | :)

Simon

Re: SQL Server connection timeout

SimulationofSai11-Jul-08 4:07

SimulationofSai

11-Jul-08 4:07

Did you unblock port 1433?

Re: SQL Server connection timeout

dlarkin7711-Jul-08 4:26

dlarkin77

11-Jul-08 4:26

That did the job. Thanks very much.

Re: SQL Server connection timeout

Member 9611-Jul-08 5:46

Member 96

11-Jul-08 5:46

That's very old, very widespread common knowledge. You could have found it on Google in about 10 seconds rather than asking here in the lounge.

"It's so simple to be wise. Just think of something stupid to say and then don't say it."
-Sam Levenson

Re: SQL Server connection timeout

Tomz_KV11-Jul-08 5:48

Tomz_KV

11-Jul-08 5:48

Are you using Sql 2005? if yes, you may need to set up an alias in Sql configuration manager for the server you try to connect.

TOMZ_KV

System IP in Stored Procedure

Member 400849210-Jul-08 18:51

Member 4008492

10-Jul-08 18:51

Hi,

I have One SP in Sql Server 2005.In that SP i need to get the IP number of the Local System. Can anyone tell me How?

thanks in Advance
sri

Re: System IP in Stored Procedure

N a v a n e e t h10-Jul-08 22:46

N a v a n e e t h

10-Jul-08 22:46

Member 4008492 wrote:
IP number of the Local System

Local system ? You won't get IP address in a Stored Procedure. You can pass it as a parameter from outside.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

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