I have a host of server logins, NT and SQL access level on my server. I'd
like to iterate through the databases and find users who have no logins to
non-system databases. Using master.dbo.sysdatabases, I can retrieve a list
of all databases on the server, but not the type (System or User), unless a
SID of 0x01 is an appropriate measure of a system database. After obtaining
a list of databases, how can I iterate through them with T-SQL code?Using cursors.
Example:
use northwind
go
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
print @.db
end
close databases_cursor
deallocate databases_cursor
go
"Kevin Bowker" wrote:
> I have a host of server logins, NT and SQL access level on my server. I'd
> like to iterate through the databases and find users who have no logins to
> non-system databases. Using master.dbo.sysdatabases, I can retrieve a lis
t
> of all databases on the server, but not the type (System or User), unless
a
> SID of 0x01 is an appropriate measure of a system database. After obtaini
ng
> a list of databases, how can I iterate through them with T-SQL code?
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
choosing the right datatype
It was hard to tell if this was the correct forum, but here goes:
My browser-driven Intranet App has 3 fields where users can type in a very
long description. It has a size of 5000, and is a varchar. People will
typically type in alphanumeric characters in it. (For the web-savvy, the
users types into a Textarea).
A co-worker tells me I should change the datatype from varchar to text. I
had it at varchar specifically because I understood that varchar will only
take up what disc space that it needs to. I asked him for an explanation of
why to go to text. He replied:
"There is an 8k limitation on a row when you use standard datatypes. You
have 3 columns with 5000 characters which pretty much doubles the amount of
allowed storage in a row. I'm surprised you haven't run into any problems.
Text datatypes are stored separately and allow up to 2GB."
Is this correct? Under my described scenario, which would be best?I would stick with Varchar. Varchar can hold up to 8,000 characters.
You're not storing more than 8,000 characters, so I don't see the point of
switching to a Text field. Also, if you use a text field, unless you
specify that you're storing the data "in row", it will only store a pointer
to the separate page or pages to access your string. You also can't
directly reference a text column in a WHERE clause. Unless you're storing
unusually large amount of characters (defined as 8,001+ characters) per row
in this column there's no real point to going with text.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
> of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
> of
> allowed storage in a row. I'm surprised you haven't run into any
> problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||He is right, but the limit for a row is 8060 and not 8k.
Example:
use northwind
go
create table t (
colA varchar(8000),
colB varchar(8000),
colC varchar(8000)
)
go
insert into t values(replicate('a', 8000), replicate('b', 8000),
replicate('c', 8000))
go
drop table t
go
Result:
Server: Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 24015 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
AMB
"middletree" wrote:
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||It is, generally, correct. However, there are perfomance and usage penaltie
s
for using Text datatype, that make it worthwhile t oavoid them if possible..
.
What are the three fields used for? An Alternative which MAY be worth
investigating, is putting these three fields in another table...
Assuming your existing Table has a PK called PKID,
Create Table Comments
(PKID Integer Not Null
WhichField TinyInt Not Null,
Description VarChar(5000),
CONSTRAINT Comment_PK PRIMARY KEY (PKId, WhichField)
)
-- (The WHichField column identifies which one of the web page's Description
fields this is for... 1,2, or 3)
"middletree" wrote:
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||Hey, 'tree. If it matters, "text" columns can be a pain to deal with in DW,
depending on what you're trying to do of course. The default recordset
options don't always work, you've got to watch your field order, etc.
I know it's not strictly database relevant, but I know you use DW, so it
might be relevant for you.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
of
> allowed storage in a row. I'm surprised you haven't run into any
problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang out
at the DW forum to ask and answer questions about code, not DW. And to
discuss various theological and political topics ;)
Hope you haven't left the DW forums for good.
"CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> Hey, 'tree. If it matters, "text" columns can be a pain to deal with in
DW,
> depending on what you're trying to do of course. The default recordset
> options don't always work, you've got to watch your field order, etc.
> I know it's not strictly database relevant, but I know you use DW, so it
> might be relevant for you.
> "middletree" <middletree@.htomail.com> wrote in message
> news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
very
I
only
> of
You
> of
> problems.
>|||For a while, yes. Probably not for good though.
I'd elaborate, but the "on-topic" rules here seem quite a bit stricter.
"middletree" <middletree@.htomail.com> wrote in message
news:%23cpX5hZKFHA.572@.tk2msftngp13.phx.gbl...
> Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang
out
> at the DW forum to ask and answer questions about code, not DW. And to
> discuss various theological and political topics ;)
> Hope you haven't left the DW forums for good.
>
> "CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
> news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> DW,
> very
the
text.
> I
> only
explanation
> You
amount
>
My browser-driven Intranet App has 3 fields where users can type in a very
long description. It has a size of 5000, and is a varchar. People will
typically type in alphanumeric characters in it. (For the web-savvy, the
users types into a Textarea).
A co-worker tells me I should change the datatype from varchar to text. I
had it at varchar specifically because I understood that varchar will only
take up what disc space that it needs to. I asked him for an explanation of
why to go to text. He replied:
"There is an 8k limitation on a row when you use standard datatypes. You
have 3 columns with 5000 characters which pretty much doubles the amount of
allowed storage in a row. I'm surprised you haven't run into any problems.
Text datatypes are stored separately and allow up to 2GB."
Is this correct? Under my described scenario, which would be best?I would stick with Varchar. Varchar can hold up to 8,000 characters.
You're not storing more than 8,000 characters, so I don't see the point of
switching to a Text field. Also, if you use a text field, unless you
specify that you're storing the data "in row", it will only store a pointer
to the separate page or pages to access your string. You also can't
directly reference a text column in a WHERE clause. Unless you're storing
unusually large amount of characters (defined as 8,001+ characters) per row
in this column there's no real point to going with text.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
> of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
> of
> allowed storage in a row. I'm surprised you haven't run into any
> problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||He is right, but the limit for a row is 8060 and not 8k.
Example:
use northwind
go
create table t (
colA varchar(8000),
colB varchar(8000),
colC varchar(8000)
)
go
insert into t values(replicate('a', 8000), replicate('b', 8000),
replicate('c', 8000))
go
drop table t
go
Result:
Server: Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 24015 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
AMB
"middletree" wrote:
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||It is, generally, correct. However, there are perfomance and usage penaltie
s
for using Text datatype, that make it worthwhile t oavoid them if possible..
.
What are the three fields used for? An Alternative which MAY be worth
investigating, is putting these three fields in another table...
Assuming your existing Table has a PK called PKID,
Create Table Comments
(PKID Integer Not Null
WhichField TinyInt Not Null,
Description VarChar(5000),
CONSTRAINT Comment_PK PRIMARY KEY (PKId, WhichField)
)
-- (The WHichField column identifies which one of the web page's Description
fields this is for... 1,2, or 3)
"middletree" wrote:
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||Hey, 'tree. If it matters, "text" columns can be a pain to deal with in DW,
depending on what you're trying to do of course. The default recordset
options don't always work, you've got to watch your field order, etc.
I know it's not strictly database relevant, but I know you use DW, so it
might be relevant for you.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
of
> allowed storage in a row. I'm surprised you haven't run into any
problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang out
at the DW forum to ask and answer questions about code, not DW. And to
discuss various theological and political topics ;)
Hope you haven't left the DW forums for good.
"CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> Hey, 'tree. If it matters, "text" columns can be a pain to deal with in
DW,
> depending on what you're trying to do of course. The default recordset
> options don't always work, you've got to watch your field order, etc.
> I know it's not strictly database relevant, but I know you use DW, so it
> might be relevant for you.
> "middletree" <middletree@.htomail.com> wrote in message
> news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
very
I
only
> of
You
> of
> problems.
>|||For a while, yes. Probably not for good though.
I'd elaborate, but the "on-topic" rules here seem quite a bit stricter.
"middletree" <middletree@.htomail.com> wrote in message
news:%23cpX5hZKFHA.572@.tk2msftngp13.phx.gbl...
> Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang
out
> at the DW forum to ask and answer questions about code, not DW. And to
> discuss various theological and political topics ;)
> Hope you haven't left the DW forums for good.
>
> "CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
> news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> DW,
> very
the
text.
> I
> only
explanation
> You
amount
>
Thursday, March 8, 2012
Chinese Characters in SQL 2000
Hi All
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
JigneshAll you should need to do is use Unicode character types (NCHAR, NVARCHAR, and NTEXT) and SQL Server should handle the Chinese characters gracefully. Getting SQL Server to provide Chinese error messages is a bit more complicted, but let's deal with one problem at a time!
-PatP|||I have declared the table column as nvarchar. I was searching on google and found out that the only thing I need to do is declare them as either nvarchar or nchar etc..
I guess it does not work in my case. I am sure I am doing something wrong, but not sure what.|||I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.
But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ??.|||Something between your keyboard and the web page control is only passing 8 bit values. Since you can successfully enter Unicode (the Chinese characters) via SQL Enterprise Mangler, I think we can assume everything is Ok as far as your OS, which leads me to think the problem is either in your browser or beyond (probably the web page).
-PatP|||I guess you are right. The problem seems to be in the web page. I just created a sample ASP.net webpage, which have one text box and a button.
When user clicks chinese characters in textbox with help of language bar and press enter, the data goes to database. But the data are converted to ????|||I was doing some more trials and found out that only time when I can enter proper data in SQL is when I go to enterprise manager and type in the data in the cell myself ( just like Excel).
But when I run the following query
Insert into TestTable(Comments, Language) values('彰','chinese') even on the enterprise manager or query analyzer, the chinese characters are converted to ???|||Just for the jolly factor, let's cut SQL Server out of the problem altogether for a moment. try to construct a "hello world" sort of web page with just a text control and a button. Have it simply allow the user to enter text into a control, then create a popup that contains that text when the user presses the button. This will allow you to get the web page working first, then we can concentrate on getting the data back and forth from the database.
-PatP|||Hi Pat
I tried exactly what you said. The code is below:
Private Sub btnTemp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemp.Click
ShowMessage(txtComment.Text)
End Sub
Private Sub ShowMessage(ByVal message As String)
Dim scriptString As String = "<script language=JavaScript>"
scriptString += "alert('" + message + "');"
scriptString += "</script>"
Page.RegisterStartupScript("ShowMessage", scriptString)
End Sub
The code works absolutely fine. I enter chinese characters in textbox and onclick of a button show a messagebox with javascript, which shows the chinese text perfectly fine.|||Good! Now, can you modify that same sample script so that a new button simply sticks the Textbox contents into a column in the database? If you can do that, then modify the existing button to pull the data from the column and display that data from the column.
-PatP|||I guess thats when it fails. Below is my code:
**********************The same button click event ***************
Dim sqlConnection1 As SqlConnection = New SqlConnection("Data Source=(local); Initial Catalog = ChineseTest; UID=jop; PWD=jop2279")
Dim cmd As SqlCommand = sqlConnection1.CreateCommand
sqlConnection1.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "Insert into TestTable (Comments, Lang)values('" + txtComment.Text + "','Chinese')"
cmd.ExecuteNonQuery()
sqlConnection1.Close()
************************************************** ***********************
But when I go to the database, I can only see >???|||Try running the statement in Query Analyzer like this
Insert into TestTable(Comments, Language) values(N'彰','chinese')
and see if that works.
How are you constructing your INSERT statement in your asp.net code?
Is it something like
string cmd="Insert into your_table(Comments) values('"+someTextBox.Text"')";
// Execute cmd
?
Edit: I see from your post that that is the case. You should really consider using a parameterized query for this. It ought to solve your unicode issue AND help protect you against SQL injection attacks.|||Just for the jolly factor, I'd apply the latest MDAC to both the web server, and the client PC (this is just "iron underware" for the development environment, it is not needed in production).
-PatP|||I changed the SQL statement as per ded's instruction to:
Insert into TestTable(Comments, Language) values(N'彰','chinese')
and it start working fine. I can run the same query from ASP.net webpage and it works fine too..
Thanks for your support guys. Without you, it would have been impossible for me to figure out this thing.
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
JigneshAll you should need to do is use Unicode character types (NCHAR, NVARCHAR, and NTEXT) and SQL Server should handle the Chinese characters gracefully. Getting SQL Server to provide Chinese error messages is a bit more complicted, but let's deal with one problem at a time!
-PatP|||I have declared the table column as nvarchar. I was searching on google and found out that the only thing I need to do is declare them as either nvarchar or nchar etc..
I guess it does not work in my case. I am sure I am doing something wrong, but not sure what.|||I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.
But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ??.|||Something between your keyboard and the web page control is only passing 8 bit values. Since you can successfully enter Unicode (the Chinese characters) via SQL Enterprise Mangler, I think we can assume everything is Ok as far as your OS, which leads me to think the problem is either in your browser or beyond (probably the web page).
-PatP|||I guess you are right. The problem seems to be in the web page. I just created a sample ASP.net webpage, which have one text box and a button.
When user clicks chinese characters in textbox with help of language bar and press enter, the data goes to database. But the data are converted to ????|||I was doing some more trials and found out that only time when I can enter proper data in SQL is when I go to enterprise manager and type in the data in the cell myself ( just like Excel).
But when I run the following query
Insert into TestTable(Comments, Language) values('彰','chinese') even on the enterprise manager or query analyzer, the chinese characters are converted to ???|||Just for the jolly factor, let's cut SQL Server out of the problem altogether for a moment. try to construct a "hello world" sort of web page with just a text control and a button. Have it simply allow the user to enter text into a control, then create a popup that contains that text when the user presses the button. This will allow you to get the web page working first, then we can concentrate on getting the data back and forth from the database.
-PatP|||Hi Pat
I tried exactly what you said. The code is below:
Private Sub btnTemp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemp.Click
ShowMessage(txtComment.Text)
End Sub
Private Sub ShowMessage(ByVal message As String)
Dim scriptString As String = "<script language=JavaScript>"
scriptString += "alert('" + message + "');"
scriptString += "</script>"
Page.RegisterStartupScript("ShowMessage", scriptString)
End Sub
The code works absolutely fine. I enter chinese characters in textbox and onclick of a button show a messagebox with javascript, which shows the chinese text perfectly fine.|||Good! Now, can you modify that same sample script so that a new button simply sticks the Textbox contents into a column in the database? If you can do that, then modify the existing button to pull the data from the column and display that data from the column.
-PatP|||I guess thats when it fails. Below is my code:
**********************The same button click event ***************
Dim sqlConnection1 As SqlConnection = New SqlConnection("Data Source=(local); Initial Catalog = ChineseTest; UID=jop; PWD=jop2279")
Dim cmd As SqlCommand = sqlConnection1.CreateCommand
sqlConnection1.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "Insert into TestTable (Comments, Lang)values('" + txtComment.Text + "','Chinese')"
cmd.ExecuteNonQuery()
sqlConnection1.Close()
************************************************** ***********************
But when I go to the database, I can only see >???|||Try running the statement in Query Analyzer like this
Insert into TestTable(Comments, Language) values(N'彰','chinese')
and see if that works.
How are you constructing your INSERT statement in your asp.net code?
Is it something like
string cmd="Insert into your_table(Comments) values('"+someTextBox.Text"')";
// Execute cmd
?
Edit: I see from your post that that is the case. You should really consider using a parameterized query for this. It ought to solve your unicode issue AND help protect you against SQL injection attacks.|||Just for the jolly factor, I'd apply the latest MDAC to both the web server, and the client PC (this is just "iron underware" for the development environment, it is not needed in production).
-PatP|||I changed the SQL statement as per ded's instruction to:
Insert into TestTable(Comments, Language) values(N'彰','chinese')
and it start working fine. I can run the same query from ASP.net webpage and it works fine too..
Thanks for your support guys. Without you, it would have been impossible for me to figure out this thing.
Chinese characters in SQL 2000
Hi All
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
JigneshI have already declared the column as nvarchar.
I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.
But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ?.
Originally posted by patel_o
Hi All
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
Jignesh
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
JigneshI have already declared the column as nvarchar.
I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.
But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ?.
Quote:
Hi All
I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.
I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this
THanks
Jignesh
Saturday, February 25, 2012
checklist
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
HOW TO: Move Databases Between Computers That Are Running
SQL Server
http://support.microsoft.com/?id=314546
-Sue
On Mon, 28 Mar 2005 17:33:04 -0800, Anonymous
<Anonymous@.discussions.microsoft.com> wrote:
>Just got a new server and I was wondering if there is a checklist available
>somewhere in order to transfer all databases, jobs, users, and replication
>information to my server?
>Thanks
|||Have a look here: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:1C271C97-16DA-4B01-9044-B0DE5874A397@.microsoft.com...
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
HOW TO: Move Databases Between Computers That Are Running
SQL Server
http://support.microsoft.com/?id=314546
-Sue
On Mon, 28 Mar 2005 17:33:04 -0800, Anonymous
<Anonymous@.discussions.microsoft.com> wrote:
>Just got a new server and I was wondering if there is a checklist available
>somewhere in order to transfer all databases, jobs, users, and replication
>information to my server?
>Thanks
|||Have a look here: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:1C271C97-16DA-4B01-9044-B0DE5874A397@.microsoft.com...
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
checklist
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
ThanksHOW TO: Move Databases Between Computers That Are Running
SQL Server
http://support.microsoft.com/?id=314546
-Sue
On Mon, 28 Mar 2005 17:33:04 -0800, Anonymous
<Anonymous@.discussions.microsoft.com> wrote:
>Just got a new server and I was wondering if there is a checklist available
>somewhere in order to transfer all databases, jobs, users, and replication
>information to my server?
>Thanks|||Have a look here: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:1C271C97-16DA-4B01-9044-B0DE5874A397@.microsoft.com...
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
ThanksHOW TO: Move Databases Between Computers That Are Running
SQL Server
http://support.microsoft.com/?id=314546
-Sue
On Mon, 28 Mar 2005 17:33:04 -0800, Anonymous
<Anonymous@.discussions.microsoft.com> wrote:
>Just got a new server and I was wondering if there is a checklist available
>somewhere in order to transfer all databases, jobs, users, and replication
>information to my server?
>Thanks|||Have a look here: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:1C271C97-16DA-4B01-9044-B0DE5874A397@.microsoft.com...
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
checklist
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
ThanksHOW TO: Move Databases Between Computers That Are Running
SQL Server
http://support.microsoft.com/?id=314546
-Sue
On Mon, 28 Mar 2005 17:33:04 -0800, Anonymous
<Anonymous@.discussions.microsoft.com> wrote:
>Just got a new server and I was wondering if there is a checklist available
>somewhere in order to transfer all databases, jobs, users, and replication
>information to my server?
>Thanks|||Have a look here: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:1C271C97-16DA-4B01-9044-B0DE5874A397@.microsoft.com...
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
ThanksHOW TO: Move Databases Between Computers That Are Running
SQL Server
http://support.microsoft.com/?id=314546
-Sue
On Mon, 28 Mar 2005 17:33:04 -0800, Anonymous
<Anonymous@.discussions.microsoft.com> wrote:
>Just got a new server and I was wondering if there is a checklist available
>somewhere in order to transfer all databases, jobs, users, and replication
>information to my server?
>Thanks|||Have a look here: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:1C271C97-16DA-4B01-9044-B0DE5874A397@.microsoft.com...
Just got a new server and I was wondering if there is a checklist available
somewhere in order to transfer all databases, jobs, users, and replication
information to my server?
Thanks
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Friday, February 24, 2012
Checking The Users Server Role
I would like to determine if a particular user has sysadmin server
role. Is there a way to do this via the connection string? Currently
our code checks if a login is valid using SQLDriverConnect, however we
need to be certain that the user can login and modify the schema.
role. Is there a way to do this via the connection string? Currently
our code checks if a login is valid using SQLDriverConnect, however we
need to be certain that the user can login and modify the schema.
Is it possible to fetch a user's server role to determine if it has a
sysadmin server role?Look for the IS_SRVROLEMEMBER function in Books Online.
Razvan|||Thanks.
Checking Out Data to Client Users
I'm trying to resolve an issue that I've run into in my current system.
I have about 10 clients accessing a SQL Server several times per minute (every 10-20 seconds). To have an individual find the next record, I follow the following process:
1. Select the value of the next record in the database to be checked out.
2. Update the record to show that it is checked out to the user.
3. Select the data in the record to display to the user.
3. Update the record to show any changes and to check the record back in after the user edits it.
My issue is that clients can execute at the same time. Right now, with just SQL statements, two clients can get the same value in step #1. That makes them select the same record for editing. Can I use T-SQL to prevent this from happening? If I use a transaction, will the SQL Server 2005 queue up the transactions, or could I still get the same problem of opening up the same record?
Thanks!
Drew
Hi,
this depends on your update clause for setting the "inUseFlag". If you code it the following way there will be no concurrency with any user:
UPDATE SOMETABLE T
SET Checkout = 'CheckOutorWhatever'
OUTPUT INSERTED.IDColumn
FROM SomeTable T
WHERE EXISTS
(
SELECT * FROM SomeTable TSub
WHere T.IDColumn = TSub.IDColumn
AND Checkout IS NULL
)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Thanks! I didn't know about the OUTPUT statement and couldn't find anything through Google.
Sunday, February 12, 2012
CheckBoxes and SQL Server
Hello All,
I am tring to find a better why to do this. I store data in the SQL Server about whether a checkbox was checked or not and the users can come back to the page and alter it as needed. This is the code for Inserting the data. It uses Stored Procedures and I will cut out all unessary code.
myCommand.Parameter.Add("@.CheckBox", myConnection);
That Records it and I use this to retrieve it.
while (myDataReader.Read())
{
string strChecked = myDataReader["Test"];
if ( strChecked == "1" )
{
cbxPhysicalExam.Checked = true;
}
else
{
cbxPhysicalExam.Checked = false;
}
}
This work but I would much rather do this...
while (myDataReader.Read())
{
cbxPhysicalExam.Checked = myDataReader["Test"].ToString();
}
Does anyone know how to do this?
Much Thanks,
RogerDepends on the type you're using in the database. Typical, for a boolean value (that's the case for checkboxes) you use the SQL Server type 'bit'. This returns a 0 or 1 value which can be casted to a boolean value.
Hope this helps?|||OK thats what I use... but how do I code it on the return... It will not compile like this...
cbxExample.Checked = myDataReader["Example"];
I get this on attempt to compile...
Cannot implicitly convert type 'object' to 'bool'
OK... maybe I need to Explicitly convert. I do not know how convert 1 - true and 0 - false|||Yes I see, maybe I was not clear enough in my answer. The bit type in the SQL Server is far more efficient to work with when doing queries etc. How to convert 1 to true and 0 to false? You may use something like this:
cbxExample.Checked = myDataReader["Example"].ToString() == "1";
which will do the job.|||I tried that and it didn't work... Here is all the code
SQL SERVER Table - TEST
Columns - ID int - Test bit
Store Proc For Select
CREATE PROCEDURE dbo.sp_Test
AS
Select * From Test
GO
Store Proc For Insert
CREATE PROCEDURE dbo.sp_TestIns (
@.Testbit )
AS
Insert Into Test (
Test
)
Values (
@.Test
)
GO
Code Behind Page
void SaveClicked()
{
SqlCommand myCommand = new SqlCommand("sp_TestIns", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@.Test", cbxText.Checked);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();{
}void Fill()
{
SqlCommand myCommand = new SqlCommand("sp_Test", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlDataReader myDataReader;
myDataReader = myCommand.ExecuteReader();
while (myDataReader.Read())
{
cbxPhysicalExam.Checked = myDataReader["Test"].ToString() == "1";
}
myDataReader.Close();
myConnection.Close();
}
THis doesn't work... what am I doing wrong?|||I figured it out... Thank you for your help...
cbxTest.Checked = (bool)myDataReader["Test"];
Thanks Again...
Roger
Subscribe to:
Comments (Atom)