|
If you post your code we may be able to understand what you are trying to do, at the moment its rather unclear.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
delete from student where rollno='12';
this is generally how we delete....and in generalized format
delete from student where rollno=@rollno;
but the above line is giving me error stating tht @rollno should be declared as a variable.
i am unable to solve it.....
santosh
|
|
|
|
|
First,
CREATE PROCEDURE spx_Student_Delete (@rollno INT)
AS
delete from student where rollno=@rollno;
GO
Then use it like this
EXECUTE spx_Student_Delete 12
and it should work
modified on Monday, July 14, 2008 1:10 PM
|
|
|
|
|
Hi,
I am asking it again sorry if someone said it right and a got it wrong
I wrote a application in C# that connects to a sqlserver database.
The login to the application is wrote not in C# but SQL store procedures. I press a buttom that triggers a SP that manages the login.
Ok, but if someone copy and attach my database to other computer he can see everything in there! Because i enter my sqlserver by windows validation and i have no password in sqlserver.
my connection strings have both no password and user.
I want attach to be enabled only if you have user and password.
DO i have to create another login in my sqlserver and create with that new user a database? how i do that? i don ´t know how to create new user in sqlserver rather then the "master"
i spinning here
|
|
|
|
|
You see we can give you a Solution, but now, your English is not good and you dont get into detail about your problem, you just shoot us with long incomplete statements. take your time and explain your problem and we will step by step help you solve your problem. and lastly you should post this in C# Forum noy here. But as you already done it here you might as well continue
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
|
|
|
|
|
Hi
I already answered your first post on this, I'm sorry if you don't understand but here it isagain:
The best option in my opinion is to use windows groups to limit user access. It still uses windows authentication so no password verification etc, and you can then grant explicit permissions to the group. You can deny permissions as well as grant them, so deny select, insert, update, delete on your tables and grant exec on the stored procs.
Its a standard way of doing it and easy to maintain by scripts.
nelsonpaixao wrote: The login to the application is wrote not in C# but SQL store procedures. I press a buttom that triggers a SP that manages the login. my connection strings have both no password and user.
From this I must assume you are alreay using windows authentication, so you need to set the table permissions as I said. This will prevent someone attaching to you database and seeing the tables. I'm not sure how to explain thsi any clearer, but try google for granting permissions in sql server, it may help your understanding.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you Bob, i remember your last post!
|
|
|
|
|
Hi all,
I wanna write 2 procedure
1) is when I execute it with parameter its give me the records of that particular table e.g. EXEC sp_Showrecords tablename
2) is when I execute it shown be all tables when I enter datbase name as parameter e.g EXEC sp_ShowTableaList dbname
Can anybody help me.
|
|
|
|
|
hi,
i don´t understand want you want
Maybe i can help you there
What kind of database you have and what do you want your procedure to do?
Didn´t you found anything here in codeproject?
Do you want do match/join contents from diferent tables and display it?
try inner join in search code project.
|
|
|
|
|
I am using SQL server.
e.g.
<br />
Create procedure sp_Fetchrecords<br />
@tbname sysname=NULL<br />
AS<br />
Select * from @tbname<br />
go<br />
gives me error
|
|
|
|
|
check the store procedures folder if it is already there!
use alter procedure to change it.
i usualy set the database on which i write on sql server up-left corner combobox. i don´t not use code but you can do that, do you need to code that?
create procedures like this (with inner join)
--------------sp-----------
create procedure vendas_criadas
@data_ini as smalldatetime,
@data_fim as smalldatetime
as
begin
select ve.id_venda as 'ID Venda',al.nome as 'Album',cl.primeiro_nome+' '+cl.ultimo_nome as 'Cliente',ut1.primeiro_nome+' '+ut1.ultimo_nome as 'Criador',ve.id_utilizador_criador_data as 'Data Criação'
from dbo.vendas as ve
inner join dbo.albuns as al on ve.id_album = al.id_album
inner join dbo.clientes as cl on ve.id_cliente = cl.id_cliente
inner join dbo.SPY_utilizadores as ut1 on ve.id_utilizador_criador = ut1.id_utilizador
where ve.data_registo between @data_ini and @data_fim
end
go
-------------execute-------------------
exec vendas_criadas
@data_ini = '1/1/2007',
@data_fim = '1/1/2008'
|
|
|
|
|
this answer is not related with my question .
like simple select * statement inside procedure
I didn't get your answer
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
nO SORRY set is for
SET @cmd
|
|
|
|
|
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
|
|
|
|
|
Sorry if i can´t help you there, i sure more advanced programmer will help.
Good luck
|
|
|
|
|
thanks
|
|
|
|
|
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
|
|
|
|
|
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;
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Stop Reposting
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
|
|
|
|
|