We have a product which uses a lot of stored procedures as our business
logic layer.we don't want our customers see the boday of the stored
procedures or probably change it.I know that we can encrypt the sps but I
also know that there is a very easy method to decrypt it as well.Is there a
better way to protect our sps from being viewed and changed?
Is there any new thing in Yokun version in this regards?
ThanksHi
See today's thread "Encryption problem" in
microsoft.public.sqlserver.programming
Nothing new in SQL Server 2005.
Reards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ray5531" <RayAll@.microsft.com> wrote in message
news:ufEfqX0SFHA.3556@.TK2MSFTNGP10.phx.gbl...
> We have a product which uses a lot of stored procedures as our business
> logic layer.we don't want our customers see the boday of the stored
> procedures or probably change it.I know that we can encrypt the sps but I
> also know that there is a very easy method to decrypt it as well.Is there
> a better way to protect our sps from being viewed and changed?
> Is there any new thing in Yokun version in this regards?
> Thanks
>
Showing posts with label customers. Show all posts
Showing posts with label customers. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
How To Dynamically Switch Between Databases In Report?
I have a situation where I have about 5 different customers. Eachcustomer's database is structurally the same, though the actual datawill vary. Each customer will run exactly the same reports as othercustomers. What I'm wondering is... is there a way to share reportsbetween these companies, but switch the data sources dynamically (fromthe web app)? From what I understand I can only have one data sourceconnection. By the way I'm using SQL Server 2000.
I'm pretty new to this RS stuff.
Thanks,
csdietrich
There may be a way to switch the datasource dynamicaly but I don't know how...
You can however have your report hit a stored proc which can in turn hit the correct database depending on the parameters passed to it.
I'm pretty new to this RS stuff.
Thanks,
csdietrich
There may be a way to switch the datasource dynamicaly but I don't know how...
You can however have your report hit a stored proc which can in turn hit the correct database depending on the parameters passed to it.
Wednesday, March 7, 2012
How to do Top n and others
is it possible to make a query that select the TOP 3 customer by volume and present the other customers
in one chunk as others
Customer Volum
A 100
B 12
C 15
D 11
E 12
F 11
preferred result
C 15
B 12
E 12
Others 32
Thank
LasseThis is a multi-part message in MIME format.
--=_NextPart_000_02E3_01C3A9E4.F2818D00
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
One alternative:
select
Customer, Volume
from
(
select top 3
Customer, Volume
from
MyTable
order by
Volume desc
) as x
union all
select
'Others', sum (Volume)
from
MyTable
where
CustomerID not in
(
select top 3
CustomerID
from
MyTable
order by
Volume desc
)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lasse" <anonymous@.discussions.microsoft.com> wrote in message
news:B62AB6B1-096A-4BBC-9F19-C762B7E9AD89@.microsoft.com...
is it possible to make a query that select the TOP 3 customer by volume and
present the other customers
in one chunk as others?
Customer Volume
A 100
B 125
C 150
D 110
E 120
F 115
preferred result:
C 150
B 125
E 120
Others 325
Thanks
Lasse
--=_NextPart_000_02E3_01C3A9E4.F2818D00
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
One alternative:
select
Customer, Volume
from
(
select top 3
Customer, Volume
from
=MyTable
order by
Volume =desc
) as x
union all
select
'Others', =sum (Volume)
from
=MyTable
where
CustomerID =not in
(
select top 3
= CustomerID
=from
= MyTable
order =by
= Volume desc
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Lasse" wrote in message news:B62=AB6B1-096A-4BBC-9F19-C762B7E9AD89@.microsoft.com...is it possible to make a query that select the TOP 3 customer by volume and =present the other customers in one chunk as others?Customer &=nbsp; VolumeA 100 B 125C 150D 110E 120F =115preferred result:C 150B 125E 120Others 325ThanksLasse
--=_NextPart_000_02E3_01C3A9E4.F2818D00--|||Do:
SELECT Customer, SUM(Volume)
FROM (
SELECT CASE WHEN (SELECT COUNT(*)
FROM tbl t1
WHERE t1.Volume > tbl.Volume) < 3
THEN Customer ELSE 'Others'
END, Volume
FROM tbl ) D (Customer, Volume)
GROUP BY Customer ;
--
- Anith
( Please reply to newsgroups only )|||Tom,
I think, you'll have to re-write that along the lines of the following to
avoid the error on ORDER BY with UNION :
SELECT Customer, Volume
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
UNION
SELECT 'Others', SUM(Volume)
FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
WHERE x.Customer = tbl.Customer ) ;
--
- Anith
( Please reply to newsgroups only )|||This is a multi-part message in MIME format.
--=_NextPart_000_04B7_01C3A9FA.82C320D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Agreed. It always pays to have DDL and INSERT's of sample data...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#JdTouhqDHA.1928@.TK2MSFTNGP12.phx.gbl...
Tom,
I think, you'll have to re-write that along the lines of the following to
avoid the error on ORDER BY with UNION :
SELECT Customer, Volume
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
UNION
SELECT 'Others', SUM(Volume)
FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
WHERE x.Customer = tbl.Customer ) ;
--
- Anith
( Please reply to newsgroups only )
--=_NextPart_000_04B7_01C3A9FA.82C320D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Agreed. It always pays to have =DDL and INSERT's of sample data...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Anith Sen" wrote in message news:#JdTouhqDHA.1928=@.TK2MSFTNGP12.phx.gbl...Tom,I think, you'll have to re-write that along the lines of the following =toavoid the error on ORDER BY with UNION :SELECT Customer, =Volume FROM ( SELECT TOP 3 Customer, Volume =FROM MyTable ORDER =BY Volume DESC ) AS x UNIONSELECT 'Others', =SUM(Volume) FROM MyTable WHERE NOT EXISTS (SELECT * &n=bsp; FROM ( SELECT TOP 3 Customer, Volume &nb=sp; &nbs=p; FROM MyTable &n=bsp; &nb=sp; ORDER BY Volume DESC ) AS x &n=bsp; WHERE x.Customer =3D tbl.Customer ) ;-- - Anith( Please =reply to newsgroups only )
--=_NextPart_000_04B7_01C3A9FA.82C320D0--
in one chunk as others
Customer Volum
A 100
B 12
C 15
D 11
E 12
F 11
preferred result
C 15
B 12
E 12
Others 32
Thank
LasseThis is a multi-part message in MIME format.
--=_NextPart_000_02E3_01C3A9E4.F2818D00
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
One alternative:
select
Customer, Volume
from
(
select top 3
Customer, Volume
from
MyTable
order by
Volume desc
) as x
union all
select
'Others', sum (Volume)
from
MyTable
where
CustomerID not in
(
select top 3
CustomerID
from
MyTable
order by
Volume desc
)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lasse" <anonymous@.discussions.microsoft.com> wrote in message
news:B62AB6B1-096A-4BBC-9F19-C762B7E9AD89@.microsoft.com...
is it possible to make a query that select the TOP 3 customer by volume and
present the other customers
in one chunk as others?
Customer Volume
A 100
B 125
C 150
D 110
E 120
F 115
preferred result:
C 150
B 125
E 120
Others 325
Thanks
Lasse
--=_NextPart_000_02E3_01C3A9E4.F2818D00
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
One alternative:
select
Customer, Volume
from
(
select top 3
Customer, Volume
from
=MyTable
order by
Volume =desc
) as x
union all
select
'Others', =sum (Volume)
from
=MyTable
where
CustomerID =not in
(
select top 3
= CustomerID
=from
= MyTable
order =by
= Volume desc
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Lasse" wrote in message news:B62=AB6B1-096A-4BBC-9F19-C762B7E9AD89@.microsoft.com...is it possible to make a query that select the TOP 3 customer by volume and =present the other customers in one chunk as others?Customer &=nbsp; VolumeA 100 B 125C 150D 110E 120F =115preferred result:C 150B 125E 120Others 325ThanksLasse
--=_NextPart_000_02E3_01C3A9E4.F2818D00--|||Do:
SELECT Customer, SUM(Volume)
FROM (
SELECT CASE WHEN (SELECT COUNT(*)
FROM tbl t1
WHERE t1.Volume > tbl.Volume) < 3
THEN Customer ELSE 'Others'
END, Volume
FROM tbl ) D (Customer, Volume)
GROUP BY Customer ;
--
- Anith
( Please reply to newsgroups only )|||Tom,
I think, you'll have to re-write that along the lines of the following to
avoid the error on ORDER BY with UNION :
SELECT Customer, Volume
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
UNION
SELECT 'Others', SUM(Volume)
FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
WHERE x.Customer = tbl.Customer ) ;
--
- Anith
( Please reply to newsgroups only )|||This is a multi-part message in MIME format.
--=_NextPart_000_04B7_01C3A9FA.82C320D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Agreed. It always pays to have DDL and INSERT's of sample data...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#JdTouhqDHA.1928@.TK2MSFTNGP12.phx.gbl...
Tom,
I think, you'll have to re-write that along the lines of the following to
avoid the error on ORDER BY with UNION :
SELECT Customer, Volume
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
UNION
SELECT 'Others', SUM(Volume)
FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
WHERE x.Customer = tbl.Customer ) ;
--
- Anith
( Please reply to newsgroups only )
--=_NextPart_000_04B7_01C3A9FA.82C320D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Agreed. It always pays to have =DDL and INSERT's of sample data...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Anith Sen" wrote in message news:#JdTouhqDHA.1928=@.TK2MSFTNGP12.phx.gbl...Tom,I think, you'll have to re-write that along the lines of the following =toavoid the error on ORDER BY with UNION :SELECT Customer, =Volume FROM ( SELECT TOP 3 Customer, Volume =FROM MyTable ORDER =BY Volume DESC ) AS x UNIONSELECT 'Others', =SUM(Volume) FROM MyTable WHERE NOT EXISTS (SELECT * &n=bsp; FROM ( SELECT TOP 3 Customer, Volume &nb=sp; &nbs=p; FROM MyTable &n=bsp; &nb=sp; ORDER BY Volume DESC ) AS x &n=bsp; WHERE x.Customer =3D tbl.Customer ) ;-- - Anith( Please =reply to newsgroups only )
--=_NextPart_000_04B7_01C3A9FA.82C320D0--
How to do this update?
Greetings,
In the customers table in Northwind db, one can update PK
(customerid) and all other fields in the same table. My question is
how can you do this in the udpate stat. That is, if one wants to
write update query to update all fields including PK, how it can be
set? Using PK in the SET statement, gives an error, because this field
might have changed during the update?
MTIA,
Grawshaal (grawsha2000@.yahoo.com) writes:
> In the customers table in Northwind db, one can update PK
> (customerid) and all other fields in the same table. My question is
> how can you do this in the udpate stat. That is, if one wants to
> write update query to update all fields including PK, how it can be
> set? Using PK in the SET statement, gives an error, because this field
> might have changed during the update?
Updating the PK does not have to be a problem:
CREATE TABLE x (a int NOT NULL PRIMARY KEY,
b varchar(23) NOT NULL)
go
INSERT x VALUES( 1, 'KJK')
INSERT x VALUES( 2, 'NJJDGF')
go
UPDATE x
SET a = 10,
b = 'Hall!'
WHERE a = 1
go
SELECT * FROM x
However, this fails:
UPDATE Northwind..Customers
SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'
And the error message tells us why:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint
'FK_Orders_Customers'. The conflict occurred in database 'Northwind',
table 'Orders', column 'CustomerID'.
The statement has been terminated.
Since there is a reference to the table, you cannot change the id
of a customer that has orders. If you added a new customer to the table,
you could easily change its ID, as you long as you don't add orders for
it.
One way to handle this, is to change the foreign-key defintion to say
ON UPDATE CASCADE, in which case the change would be propagated to
Orders.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946D7C7374FAYazorman@.127.0.0.1>...
> al (grawsha2000@.yahoo.com) writes:
> > In the customers table in Northwind db, one can update PK
> > (customerid) and all other fields in the same table. My question is
> > how can you do this in the udpate stat. That is, if one wants to
> > write update query to update all fields including PK, how it can be
> > set? Using PK in the SET statement, gives an error, because this field
> > might have changed during the update?
> Updating the PK does not have to be a problem:
> CREATE TABLE x (a int NOT NULL PRIMARY KEY,
> b varchar(23) NOT NULL)
> go
> INSERT x VALUES( 1, 'KJK')
> INSERT x VALUES( 2, 'NJJDGF')
> go
> UPDATE x
> SET a = 10,
> b = 'Hall!'
> WHERE a = 1
> go
> SELECT * FROM x
> However, this fails:
> UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'
> And the error message tells us why:
> Server: Msg 547, Level 16, State 1, Line 1
> UPDATE statement conflicted with COLUMN REFERENCE constraint
> 'FK_Orders_Customers'. The conflict occurred in database 'Northwind',
> table 'Orders', column 'CustomerID'.
> The statement has been terminated.
> Since there is a reference to the table, you cannot change the id
> of a customer that has orders. If you added a new customer to the table,
> you could easily change its ID, as you long as you don't add orders for
> it.
> One way to handle this, is to change the foreign-key defintion to say
> ON UPDATE CASCADE, in which case the change would be propagated to
> Orders.
I don't have a problem with this. I did ticked the CascadeOnUpdate.
The problem is, the update woun't happen becuase there will be a
Concurency Violation. Try to do this(with all cascades, and still you
will recieve an err)
UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'|||> UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'
This UPDATE works for me once I've enabled Cascading updates on the child
tables (CustomerCustomerDemo and Orders). Exactly what error message are you
getting? Maybe you already have a row where CustomerID = 'KKKKK' so this
violates the primary key?
--
David Portas
----
Please reply only to the newsgroup
--|||al (grawsha2000@.yahoo.com) writes:
> I don't have a problem with this. I did ticked the CascadeOnUpdate.
> The problem is, the update woun't happen becuase there will be a
> Concurency Violation. Try to do this(with all cascades, and still you
> will recieve an err)
> UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'
Since I am lazy I did not even try this. I know that it does not produce
any error with with the appropriate cascade. Least of all concurrency
violation, because SQL Server does not produce any such errors.
However, some client tools and libraries are doing smart things behind
your back, and may be outsmarted by your manoevre.
So you need to tell us in which context you get the error message (as
well as of course the exact text of the error message). I have a strong
feeling that you are not submitting the above from Query Analyzer.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946DA7996D537Yazorman@.127.0.0.1>...
> al (grawsha2000@.yahoo.com) writes:
> > I don't have a problem with this. I did ticked the CascadeOnUpdate.
> > The problem is, the update woun't happen becuase there will be a
> > Concurency Violation. Try to do this(with all cascades, and still you
> > will recieve an err)
> > UPDATE Northwind..Customers
> > SET CustomerID = 'KKKKK'
> > WHERE CustomerID = 'ALFKI'
> Since I am lazy I did not even try this. I know that it does not produce
> any error with with the appropriate cascade. Least of all concurrency
> violation, because SQL Server does not produce any such errors.
> However, some client tools and libraries are doing smart things behind
> your back, and may be outsmarted by your manoevre.
> So you need to tell us in which context you get the error message (as
> well as of course the exact text of the error message). I have a strong
> feeling that you are not submitting the above from Query Analyzer.
You are right! I'm doing this from VB.NET. But since this is not the
group for such post and since I have found out about this late, how
can I fix this? I gusse I need to submit the original value+the
changed value..may be??|||al (grawsha2000@.yahoo.com) writes:
> You are right! I'm doing this from VB.NET. But since this is not the
> group for such post and since I have found out about this late, how
> can I fix this? I gusse I need to submit the original value+the
> changed value..may be??
I'm still a learner of ADO .Net, so maybe I am not the one to give
expert advice. But even as an expert, I would have problems without
your code at hand.
The answer to your question may be in David Sceppa's book on ADO .Net
which lies next to me on the table. I don't find anything on a quick
look, though. But it's a good book.
Being an SQL person, I would probably define my own UpdateCommand
for the DataAdapter, but there may be better support build into
ADO .Net. If you find some ADO .Net group, you might get better
answers there.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 19, 2012
how to do a wild card search with sqlserver
Is there syntax that lets you look for all names that include ell in them? That is, in Oracle it would be:
select id from customers where firstname like(%ell%)
Or, is there regular expression support in the select's where clause?
thanksTry:
select id from customers where firstname like '%ell%'
Subscribe to:
Posts (Atom)