Help on string sorting

Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/default.aspx?scid=kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra MudugalPlease post a repro script.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/default.aspx?scid=kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra Mudugal|||Hi
This does sounds like you have a binary collation and mixed case data. You
may want to change the collation to something more intuitive or use
something like:
SELECT customerid
FROM MyTable
ORDER BY customerid COLLATE Latin1_General_CI_AS
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> Hello
> I have a customer table created in the mssql server 2000, in this table i
> am
> using
> customerid as "CUST_0001" and so on..
> When i say order by on customerid column, the IDs are not sorted in a
> proper
> way
> Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> order by customerid puts "CUST_0010" in the second row. but it supposed to
> in the 10thr row.
> Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> on
> this
> column. but nothing helped.
> Please advise me on this and what way it can be achived. I am not going to
> change the format of the ID which i am storing. that confirmed. but i need
> that to be sorted
> in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> changed.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;322112
> the above link where i looked to setting up the COLLATION.
> Hope to receive your advice as soon. Its urgent.
> --
> With Regards,
> Raghavendra Mudugal|||Hello John Bell,
Thanks for your reply.
I tried your suggetion,, but its not helping me in any way.
I looked in the "SQL Collation Name" list in SQL books on-line and tried
all names giving with that "customerid" column, but all resulted the same,
no difference at all. and its still showing the "CUST_0010" customer id
in the 2nd place.
please help.
--
With Regards,
Raghavendra Mudugal
"John Bell" wrote:
> Hi
> This does sounds like you have a binary collation and mixed case data. You
> may want to change the collation to something more intuitive or use
> something like:
> SELECT customerid
> FROM MyTable
> ORDER BY customerid COLLATE Latin1_General_CI_AS
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data.
> John
> "Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
> in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> > Hello
> >
> > I have a customer table created in the mssql server 2000, in this table i
> > am
> > using
> > customerid as "CUST_0001" and so on..
> >
> > When i say order by on customerid column, the IDs are not sorted in a
> > proper
> > way
> >
> > Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> > order by customerid puts "CUST_0010" in the second row. but it supposed to
> > in the 10thr row.
> >
> > Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> > on
> > this
> > column. but nothing helped.
> >
> > Please advise me on this and what way it can be achived. I am not going to
> > change the format of the ID which i am storing. that confirmed. but i need
> > that to be sorted
> > in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> > changed.
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;322112
> >
> > the above link where i looked to setting up the COLLATION.
> >
> > Hope to receive your advice as soon. Its urgent.
> > --
> > With Regards,
> > Raghavendra Mudugal
>
>|||If it always starts with CUST_ and it always ends with digits you could
do
order by convert(int,replace(CustomerID,'CUST_",''))
http://sqlservercode.blogspot.com/|||well an annoying cheat kinda way u could do it i suppose is in the slect
statement, sort out the integers with substring (CustomerID, 6,10)-(is that
right? i dont remember the format it was, anyway u get the concept, the
column name then the character it starts and character it ends and all should
be integers) and Cast() the whole thing as INT, and make is AS SortNumber or
something. THen order by SortNumber instead of costomerID
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Lol well SQL beat me to it but i recommend CAST function over CONVERT because
the books say that CONVERT was only used for backward compatibility and CAST
should perform faster and avoid getting obsolete in later SQL versions
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Hello,
And thanks for your reply. I am ok with this Idea, but is this the only way
to order the customerid just by taking those four digits and just sorting them
as numerical sort and use it?
Because, presently I have 1.5 to 1.6 lac of customers listed in the database.
And is that code works faster? I had no problems using the string sort in
SQL6.5, and i never used to convert the id to int . in 2000 lots of things are
changed.
Hope to receive your valuabel response as soon.
Thanks for your time.
--
With Regards,
Raghavendra Mudugal
"SQL" wrote:
> If it always starts with CUST_ and it always ends with digits you could
> do
> order by convert(int,replace(CustomerID,'CUST_",''))
> http://sqlservercode.blogspot.com/
>|||Hi
You may want to post DDL and sample that will re-create your problem.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:8ED10297-AFDE-4502-AC78-6C2729BDA1FB@.microsoft.com...
> Hello,
> And thanks for your reply. I am ok with this Idea, but is this the only
> way
> to order the customerid just by taking those four digits and just sorting
> them
> as numerical sort and use it?
> Because, presently I have 1.5 to 1.6 lac of customers listed in the
> database.
> And is that code works faster? I had no problems using the string sort in
> SQL6.5, and i never used to convert the id to int . in 2000 lots of things
> are
> changed.
> Hope to receive your valuabel response as soon.
> Thanks for your time.
> --
> With Regards,
> Raghavendra Mudugal
>
> "SQL" wrote:
>> If it always starts with CUST_ and it always ends with digits you could
>> do
>> order by convert(int,replace(CustomerID,'CUST_",''))
>> http://sqlservercode.blogspot.com/
>>

Help on string sorting

Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/defaul...kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra MudugalPlease post a repro script.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/defaul...kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra Mudugal|||Hi
This does sounds like you have a binary collation and mixed case data. You
may want to change the collation to something more intuitive or use
something like:
SELECT customerid
FROM MyTable
ORDER BY customerid COLLATE Latin1_General_CI_AS
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> Hello
> I have a customer table created in the mssql server 2000, in this table i
> am
> using
> customerid as "CUST_0001" and so on..
> When i say order by on customerid column, the IDs are not sorted in a
> proper
> way
> Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> order by customerid puts "CUST_0010" in the second row. but it supposed to
> in the 10thr row.
> Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> on
> this
> column. but nothing helped.
> Please advise me on this and what way it can be achived. I am not going to
> change the format of the ID which i am storing. that confirmed. but i need
> that to be sorted
> in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> changed.
> http://support.microsoft.com/defaul...kb;en-us;322112
> the above link where i looked to setting up the COLLATION.
> Hope to receive your advice as soon. Its urgent.
> --
> With Regards,
> Raghavendra Mudugal|||Hello John Bell,
Thanks for your reply.
I tried your suggetion,, but its not helping me in any way.
I looked in the "SQL Collation Name" list in SQL books on-line and tried
all names giving with that "customerid" column, but all resulted the same,
no difference at all. and its still showing the "CUST_0010" customer id
in the 2nd place.
please help.
With Regards,
Raghavendra Mudugal
"John Bell" wrote:

> Hi
> This does sounds like you have a binary collation and mixed case data. You
> may want to change the collation to something more intuitive or use
> something like:
> SELECT customerid
> FROM MyTable
> ORDER BY customerid COLLATE Latin1_General_CI_AS
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data.
> John
> "Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
> in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
>
>|||If it always starts with CUST_ and it always ends with digits you could
do
order by convert(int,replace(CustomerID,'CUST_",''))
http://sqlservercode.blogspot.com/|||well an annoying cheat kinda way u could do it i suppose is in the slect
statement, sort out the integers with substring (CustomerID, 6,10)-(is that
right? i dont remember the format it was, anyway u get the concept, the
column name then the character it starts and character it ends and all shoul
d
be integers) and Cast() the whole thing as INT, and make is AS SortNumber or
something. THen order by SortNumber instead of costomerID
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Lol well SQL beat me to it but i recommend CAST function over CONVERT becaus
e
the books say that CONVERT was only used for backward compatibility and CAST
should perform faster and avoid getting obsolete in later SQL versions
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Hello,
And thanks for your reply. I am ok with this Idea, but is this the only way
to order the customerid just by taking those four digits and just sorting th
em
as numerical sort and use it?
Because, presently I have 1.5 to 1.6 lac of customers listed in the database
.
And is that code works faster? I had no problems using the string sort in
SQL6.5, and i never used to convert the id to int . in 2000 lots of things a
re
changed.
Hope to receive your valuabel response as soon.
Thanks for your time.
--
With Regards,
Raghavendra Mudugal
"SQL" wrote:

> If it always starts with CUST_ and it always ends with digits you could
> do
> order by convert(int,replace(CustomerID,'CUST_",''))
> http://sqlservercode.blogspot.com/
>|||Hi
You may want to post DDL and sample that will re-create your problem.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:8ED10297-AFDE-4502-AC78-6C2729BDA1FB@.microsoft.com...[vbcol=seagreen]
> Hello,
> And thanks for your reply. I am ok with this Idea, but is this the only
> way
> to order the customerid just by taking those four digits and just sorting
> them
> as numerical sort and use it?
> Because, presently I have 1.5 to 1.6 lac of customers listed in the
> database.
> And is that code works faster? I had no problems using the string sort in
> SQL6.5, and i never used to convert the id to int . in 2000 lots of things
> are
> changed.
> Hope to receive your valuabel response as soon.
> Thanks for your time.
> --
> With Regards,
> Raghavendra Mudugal
>
> "SQL" wrote:
>sql

Help on string sorting

Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/default...b;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
With Regards,
Raghavendra Mudugal
Please post a repro script.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/default...b;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
With Regards,
Raghavendra Mudugal
|||Hi
This does sounds like you have a binary collation and mixed case data. You
may want to change the collation to something more intuitive or use
something like:
SELECT customerid
FROM MyTable
ORDER BY customerid COLLATE Latin1_General_CI_AS
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> Hello
> I have a customer table created in the mssql server 2000, in this table i
> am
> using
> customerid as "CUST_0001" and so on..
> When i say order by on customerid column, the IDs are not sorted in a
> proper
> way
> Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> order by customerid puts "CUST_0010" in the second row. but it supposed to
> in the 10thr row.
> Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> on
> this
> column. but nothing helped.
> Please advise me on this and what way it can be achived. I am not going to
> change the format of the ID which i am storing. that confirmed. but i need
> that to be sorted
> in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> changed.
> http://support.microsoft.com/default...b;en-us;322112
> the above link where i looked to setting up the COLLATION.
> Hope to receive your advice as soon. Its urgent.
> --
> With Regards,
> Raghavendra Mudugal
|||Hello John Bell,
Thanks for your reply.
I tried your suggetion,, but its not helping me in any way.
I looked in the "SQL Collation Name" list in SQL books on-line and tried
all names giving with that "customerid" column, but all resulted the same,
no difference at all. and its still showing the "CUST_0010" customer id
in the 2nd place.
please help.
With Regards,
Raghavendra Mudugal
"John Bell" wrote:

> Hi
> This does sounds like you have a binary collation and mixed case data. You
> may want to change the collation to something more intuitive or use
> something like:
> SELECT customerid
> FROM MyTable
> ORDER BY customerid COLLATE Latin1_General_CI_AS
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data.
> John
> "Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
> in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
>
>
|||If it always starts with CUST_ and it always ends with digits you could
do
order by convert(int,replace(CustomerID,'CUST_",''))
http://sqlservercode.blogspot.com/
|||well an annoying cheat kinda way u could do it i suppose is in the slect
statement, sort out the integers with substring (CustomerID, 6,10)-(is that
right? i dont remember the format it was, anyway u get the concept, the
column name then the character it starts and character it ends and all should
be integers) and Cast() the whole thing as INT, and make is AS SortNumber or
something. THen order by SortNumber instead of costomerID
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Lol well SQL beat me to it but i recommend CAST function over CONVERT because
the books say that CONVERT was only used for backward compatibility and CAST
should perform faster and avoid getting obsolete in later SQL versions
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Hello,
And thanks for your reply. I am ok with this Idea, but is this the only way
to order the customerid just by taking those four digits and just sorting them
as numerical sort and use it?
Because, presently I have 1.5 to 1.6 lac of customers listed in the database.
And is that code works faster? I had no problems using the string sort in
SQL6.5, and i never used to convert the id to int . in 2000 lots of things are
changed.
Hope to receive your valuabel response as soon.
Thanks for your time.
With Regards,
Raghavendra Mudugal
"SQL" wrote:

> If it always starts with CUST_ and it always ends with digits you could
> do
> order by convert(int,replace(CustomerID,'CUST_",''))
> http://sqlservercode.blogspot.com/
>
|||Hi
You may want to post DDL and sample that will re-create your problem.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:8ED10297-AFDE-4502-AC78-6C2729BDA1FB@.microsoft.com...[vbcol=seagreen]
> Hello,
> And thanks for your reply. I am ok with this Idea, but is this the only
> way
> to order the customerid just by taking those four digits and just sorting
> them
> as numerical sort and use it?
> Because, presently I have 1.5 to 1.6 lac of customers listed in the
> database.
> And is that code works faster? I had no problems using the string sort in
> SQL6.5, and i never used to convert the id to int . in 2000 lots of things
> are
> changed.
> Hope to receive your valuabel response as soon.
> Thanks for your time.
> --
> With Regards,
> Raghavendra Mudugal
>
> "SQL" wrote:

Help on one colum

Hi,

I have a column in SQL that needs to be created. This column needs to start with 10 zero's. If I add a number, say 135 the column needs to read : 0000000135.

Is this possible? If it is how do I need to go about to do this. If I need to create a view or something like that I do not mind. I am open to all suggestions and appreciate the help.

Use the following query..

Select Replicate('0',10-len(Convert(Varchar,ColumnName))) + Convert(Varchar,ColumnName) From TableName

|||

DECLARE @.AnyStringNo NVARCHAR(10)
SET @.AnyStringNo = '135'
SELECT REPLICATE(0,10-LEN(@.AnyStringNo)) + @.AnyStringNo
SET @.AnyStringNo = '47593'
SELECT REPLICATE(0,10-LEN(@.AnyStringNo)) + @.AnyStringNo

Will give ever output in 10 digits with replicating of 0s.

|||

Thanks Mani,

This is exactly what I need.

|||

Thanks Bhudev,

This was also very helpful!!

|||

One more question Mani,

If you have a currency field with the following : 1.35 it imports the . as well. What do I need to do to get the . out of the query?

|||This one should remove the dot and pad the necessary 0s in front.

SELECT RIGHT('0000000000' + REPLACE(CONVERT(VARCHAR(10),col1),'.', '') , 10) FROM yourTable|||

Thanks Limno,

That is exactly what I wanted!

|||You should do the formatting on the client-side. It is much easier, simpler and flexible. So return the ID as integer to the client and let it to do the formatting as required. This also provides better performance since you will be sending less data from the server to the client(s) over the network.|||

Umachandar,

Forgive me, but I am kinda new to VB programming. How do I get the client side to do the formatting?

Help needed: Granting Create table permisions on specific Schema Options

Having some trouble getting my head around setting access to specific
schemas- here's my problem:

I've created a specific schema that I only want certain users to
control

Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):

GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'gdev (paul.afamdi.okeke@.gmail.com) writes:

Quote:

Originally Posted by

Having some trouble getting my head around setting access to specific
schemas- here's my problem:
>
I've created a specific schema that I only want certain users to
control
>
>
Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):
>
GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

You also need:

GRANT CREATE TABLE TO eflyerAdmin

And it's sufficient to grant CONTROL on the schema, since CONTROL implies
the rest.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help needed with Primary Key and Identity

I have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc...:

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

End If

' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()

End If

End Subds.Tables(0).Columns("YourPrimaryKey").IncrementSeed = 1
ds.Tables(0).Columns("YourPrimaryKey")... other properties you need to set to make it an identity.|||Right now I'm using an "ID" field as the primary key and it is setup in SQL as an identity but when I try to add a row...it says..

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'CustomerInfo' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.OrderEdit_aspx.DataGrid_Update(Object Sender, DataGridCommandEventArgs E

Would I need to remove the identity setting in SQL and create the identity through my code or is there a way to specify that my "ID" field is a primary key and it needs to be incremented by 1 whenever a new row is added?

Thanks for the help!!

Help needed !!!! the colors used by a bar chart in SQL Server 2005 Reporting Services.

I have created a linked report in which I have two screen:

First Screen: Clicking on the bar of particular country the details of that particular country should be visible (the second screen)

Second Screen: When I clicked on a bar. All the count values are very well correct. In first chart Count of values under one bar "Pending decision"(yellow) was 337 for a country and in second screen its 337 again(shown in light green bar).

<!--[if !vml]--><!--[endif]-->

Problem: My problem is to make it sure that the colors that the first screen is having for particular status (as shown in the legend) should remain same in second screen also. For example for a status say "pending decision" the chart is using yellow color, so in screen two as well it must be shown with yellow color and not with lany other color. Can anyone help me in this context. Is there any way to customize colors used by bar chart. Please note that the question is not about using appropriate color scheme(palette) the question is how to "capture/ Re-use/ customize/ pass as a parameter" the colors used by a bar chart in SQL Server 2005 Reporting Services.

This may help you.

http://www.cubido.at/Default.aspx?tabid=176&EntryID=29

cheers,

Andrew

Help me please.....

I'm using Visual Web Developer 2005 and I've created a simple SQL DB directly in VWD. When I run it through local host it works, but when I put my page for adding records and my SQL DB online the browser send me an error saying that by default SQL Server doesn't accept remote connections. I've just enabled through SQL Configuration Manager TCP/IP connections, but it doesn't work yet...Help me please...try to enable other protocols too: Named Pipes|||I've also tryed doing this but doesn't work yet...

HELP ME PLEASE! GODADDY - ASPNETDB Security DB

Hi,

I have two sql db's. One for company record info, other for users (aspnetdb) I created the users one using the login control and web administration wizard. This all worked well until I had to publish to godaddy. I had to rebuild my company db, but eventually I was able to pull info and all that is working. The problem is, how the heck do I add users? I noticed that it put users table in my db to start. So I manually went into the db on godaddy's servers, and added an application. Then when I tried to add a user manually, it kept giving me errors. Is there an easier way to add website users through godaddy? Something like the web admin tool that comes with visual studio? Man it is driving me crazy!!!

For the poor man's approach, you can upload a file from the learnvisualstudio.net tutorials (begennirs lesson 09),

Signup.aspx

<%@. Page Language="C#" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<script runat="server"
</script
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#F7F7DE" BorderColor="#CCCC99"
BorderStyle="Solid" BorderWidth="1px" ContinueDestinationPageUrl="~/Default.aspx"
Font-Names="Verdana" Font-Size="10pt" Style="z-index: 100; left: 241px; position: absolute;
top: 152px">
<WizardSteps>
<asp:CreateUserWizardStep runat="server">
</asp:CreateUserWizardStep>
<asp:CompleteWizardStep runat="server">
</asp:CompleteWizardStep>
</WizardSteps>
<SideBarStyle BackColor="#7C6F57" BorderWidth="0px" Font-Size="0.9em" VerticalAlign="Top" />
<TitleTextStyle BackColor="#6B696B" Font-Bold="True" ForeColor="#FFFFFF" />
<SideBarButtonStyle BorderWidth="0px" Font-Names="Verdana" ForeColor="#FFFFFF" />
<NavigationButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="#FFFFFF" HorizontalAlign="Center" />
<CreateUserButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<ContinueButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<StepStyle BorderWidth="0px" />
</asp:CreateUserWizard>

</div>
</form>
</body>
</html
And here's my Web.config file:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<system.web>
<!--
<trace enabled="true" requestLimit="50" localOnly="false"/>
-->
<compilation debug="true"/>
<roleManager enabled="true"/>
<customErrors mode="Off"/>
<authentication mode="Forms"/
<membership defaultProvider="MySqlMembershipProvider" >
<providers>
<clear/>
<add name="MySqlMembershipProvider"
connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
</system.web
<connectionStrings>
<add name="MyLocalSQLServer"
connectionString="Data Source=whsql-v01.prod.mesa1.secureserver.net;Initial Catalog=DB_nnnnn;User ID=user;Password=password;" />
</connectionStrings
</configuration
|||I'd like to know if there is decent user management app for this.

Also when I work with Roles I run into errors. Since I don't have access to the ASP.NET configuration tool I'm notsure if Role Management Enabled is checked on godaddy servers or ifthis would even matter for this app.

Here's my error screen.

Server Error in '/' Application.

---

TheSSE Provider did not find the database file specified in the connectionstring. At the configured trust level (below High trust level), the SSEprovider can not automatically create the database file.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Configuration.Provider.ProviderException:The SSE Provider did not find the database file specified in theconnection string. At the configured trust level (below High trustlevel), the SSE provider can not automatically create the database file.

Source Error:

Line 11: }
Line 12:
Line 13: if (Roles.IsUserInRole("Basic User") == false)
Line 14: Server.Transfer("unauthorized.aspx");
Line 15: }

Source File: d:\Hosting\javagrinder\SecurePage.aspx Line: 13

Stack Trace:

[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.]
 System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2466581
 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87
 System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +771
 System.Web.Security.RolePrincipal.IsInRole(String role) +272
 System.Web.Security.Roles.IsUserInRole(String username, String roleName) +533
 System.Web.Security.Roles.IsUserInRole(String roleName) +16
 ASP.securepage_aspx.form1_Load(Object sender, EventArgs e) in d:\Hosting\javagrinder\SecurePage.aspx:13
 System.Web.UI.Control.OnLoad(EventArgs e) +99
 System.Web.UI.Control.LoadRecursive() +47
 System.Web.UI.Control.LoadRecursive() +131
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||Hi JavaGrinder, have you tried setting the trust level higher (e.g. "Full")?|||

Lori,

Can you explain how to do that? That may solve another problem I am running into...

Thanks!

|||Ah, that's probably the problem. I get the following error, and am contacting godaddy to see if I can have them unlock this and allow overrides for sites upon request.

I suspect there is something more to it since I'm having troubles adding roles to the tables from their SQL server manager. I can add users and applications from that panel, but not Roles.

Server Error in '/' Application.

---

Configuration Error

Description:Anerror occurred during the processing of a configuration file requiredto service this request. Please review the specific error details belowand modify your configuration file appropriately.

Parser Error Message:Thisconfiguration section cannot be used at this path. This happens whenthe site administrator has locked access to this section using<location allowOverride="false"> from an inherited configurationfile.

Source Error:

Line 22: </providers>
Line 23: </membership>
Line 24: <trust level="Full"/>
Line 25: </system.web>
Line 26: </location>

Source File: d:\hosting\javagrinder\web.config Line: 24

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||

Java,

Let me know how this goes... I am trying to run the reportviewer control from their servers and it gives me permssion errors. I have heard that the reportviewer control requires full trust. Please let me know if they allow you to use full trust.

Thanks!

|||

Oh and by the way Java, I had asked Godaddy before about the trust level and all they kept saying was that I could create folders myself. I don't think they really had a clue what I was talking about... Good luck and let me know how it goes!! I might have to end up swiching host since I need this reportviewer control to work..

Thanks!

|||They say on shared hosting the trust level is set to medium. You have to have a dedicated hosting account to increase it to full.|||Ok, so after talking to godaddy support (useless) I started searching around on google again. I don't know where I got this web.config, but it works for me. Maybe it will work for report viewer.

-Jon

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"
<location allowOverride="true"
<system.web>
<!--
<trace enabled="true" requestLimit="50" localOnly="false"/>
-->
<compilation debug="true"/>
<!-- roleManager enabled="true"/ -->
<customErrors mode="Off"/>
<authentication mode="Forms"/>
<membership
<providers
<remove name="AspNetSqlMembershipProvider" /
<add name="AspNetSqlMembershipProvider"

type="System.Web.Security.SqlMembershipProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a"

connectionStringName="MyLocalSQLServer"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

applicationName="/"

requiresUniqueEmail="false"

passwordFormat="Hashed"

maxInvalidPasswordAttempts="5"

minRequiredPasswordLength="7"

minRequiredNonalphanumericCharacters="1"

passwordAttemptWindow="10"

passwordStrengthRegularExpression="" /
</providers
</membership
<profile
<providers
<remove name="AspNetSqlProfileProvider" /
<add name="AspNetSqlProfileProvider"

connectionStringName="MyLocalSQLServer"

applicationName="/"

type="System.Web.Profile.SqlProfileProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" /
</providers>

</profile
<roleManager enabled="true">

<providers
<remove name="AspNetSqlRoleProvider" /
<add name="AspNetSqlRoleProvider"

connectionStringName="MyLocalSQLServer"

applicationName="/"

type="System.Web.Security.SqlRoleProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" /
</providers
</roleManager>
<!--
<membership defaultProvider="MySqlMembershipProvider" >
<providers>
<clear/>
<add name="MySqlMembershipProvider"
connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
-->
</system.web>
</location>
<connectionStrings>
<add name="MyLocalSQLServer"
connectionString="Data Source=whsql-v01.prod.mesa1.secureserver.net;Initial Catalog=DB_nnnnnn;User ID=userid;Password=password;" />
</connectionStrings
</configuration>|||

Didn't work for me :( I still get this error...

Required permissions cannot be acquired.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Security.Policy.PolicyException: Required permissions cannot be acquired.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[PolicyException: Required permissions cannot be acquired.] System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Boolean checkExecutionPermission) +2737861 System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Int32& securitySpecialFlags, Boolean checkExecutionPermission) +57[FileLoadException: Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417)] System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +211 System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +141 System.Reflection.Assembly.Load(String assemblyString) +25 System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +32[ConfigurationErrorsException: Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417)] System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +596 System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +3479081 System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +46 System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +177 System.Web.Compilation.WebDirectoryBatchCompiler..ctor(VirtualDirectory vdir) +267 System.Web.Compilation.BuildManager.BatchCompileWebDirectoryInternal(VirtualDirectory vdir, Boolean ignoreErrors) +36 System.Web.Compilation.BuildManager.BatchCompileWebDirectory(VirtualDirectory vdir, VirtualPath virtualDir, Boolean ignoreErrors) +429 System.Web.Compilation.BuildManager.CompileWebFile(VirtualPath virtualPath) +73 System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) +580 System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) +93 System.Web.Compilation.BuildManager.GetVirtualPathObjectFactory(VirtualPath virtualPath, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +111 System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +54 System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +31 System.Web.UI.PageHandlerFactory.System.Web.IHttpHandlerFactory2.GetHandler(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +40 System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, VirtualPath path, String pathTranslated, Boolean useAppConfig) +139 System.Web.MapHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +120 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Help me in my problem

Hello,

please, help me in my problem, here is what i did:

i have created a SQL server database in the host control panel, and i created a connection string for it too in the control panel, also i added a user for the database named ASPNET and i got the user the read and write permissions for this database, but what happened is that when i enter the domain name in the browser this is what happens:

Login failed for user ''. The user is not associated with a trusted SQL Server connection.

please, help me in that it is my first time to upload a website

P.S. the domain iswww.shefaprivatehospital.com

do you include the server, the database, the user ID, and the password in the connectionstring?

|||

On shared server you have to use SQL server login not windows authentication, so verify your connection string and make it SQL login type connection string.

Good luck