altering a column of a table

Hello,
I have an internet site, that supports ms-sql server 2000.
The hosting company for some reason have problems on there wizard of
creating columns on db,
and doesn't support the auto-increment.
How can I do alter to a column, with an sql command, to an auto-increment
one ?
Need sample code please.
Thanks
I suggest that ALL changes on a live system should be made through SQL
scripts rather than using Enterprise Manager / Wizards. That way you can
more reliably reproduce and test your installation process.
IDENTITY is the correct name for the "auto-incrementing" column property in
SQL Server.
You can ADD an IDENTITY column to a table using an ALTER TABLE statement:
ALTER TABLE YourTable ADD col INTEGER IDENTITY
You cannot add the IDENTITY property to an existing column. Enterprise
Manager achieves this by creating a new table with IDENTITY, repopulating it
with the old data and then dropping the old table. That's something you may
want to avoid doing on a production system. If you do want to use that
approach then use Enterprise Manager to change the column on a development
copy of your data and select the Save Change Script option to save the
commands to a file. That way you can see exactly what the steps are.
David Portas
SQL Server MVP
|||Hi Eitan,
you cannot alter columns to auto-increment. You can only add columns that
auto-increment.
try dropping the column and re-creating it. hopefully, you dont need the
existing values in that column.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Eitan" <no_spam_please@.nospam_please.com> wrote in message
news:#KVZ#BX8EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have an internet site, that supports ms-sql server 2000.
> The hosting company for some reason have problems on there wizard of
> creating columns on db,
> and doesn't support the auto-increment.
> How can I do alter to a column, with an sql command, to an auto-increment
> one ?
> Need sample code please.
> Thanks
>
sql

altering a column of a table

Hello,
I have an internet site, that supports ms-sql server 2000.
The hosting company for some reason have problems on there wizard of
creating columns on db,
and doesn't support the auto-increment.
How can I do alter to a column, with an sql command, to an auto-increment
one ?
Need sample code please.
Thanks I suggest that ALL changes on a live system should be made through SQL
scripts rather than using Enterprise Manager / Wizards. That way you can
more reliably reproduce and test your installation process.
IDENTITY is the correct name for the "auto-incrementing" column property in
SQL Server.
You can ADD an IDENTITY column to a table using an ALTER TABLE statement:
ALTER TABLE YourTable ADD col INTEGER IDENTITY
You cannot add the IDENTITY property to an existing column. Enterprise
Manager achieves this by creating a new table with IDENTITY, repopulating it
with the old data and then dropping the old table. That's something you may
want to avoid doing on a production system. If you do want to use that
approach then use Enterprise Manager to change the column on a development
copy of your data and select the Save Change Script option to save the
commands to a file. That way you can see exactly what the steps are.
David Portas
SQL Server MVP
--|||Hi Eitan,
you cannot alter columns to auto-increment. You can only add columns that
auto-increment.
try dropping the column and re-creating it. hopefully, you dont need the
existing values in that column.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Eitan" <no_spam_please@.nospam_please.com> wrote in message
news:#KVZ#BX8EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have an internet site, that supports ms-sql server 2000.
> The hosting company for some reason have problems on there wizard of
> creating columns on db,
> and doesn't support the auto-increment.
> How can I do alter to a column, with an sql command, to an auto-increment
> one ?
> Need sample code please.
> Thanks
>

Alphanumeric Paging on GridView?

Hello,

I have a SQL database with about 300 company names and corresponding phone numbers. I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.

I know there are some examples on codeproject.com, but they are a bit over my head... besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do. Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?

Any idea?

Thanks,

-Derek

The basic sql statement looks like this:

SELECT *FROM CompanyTableWHERE CompanyNameLIKE'A%';

The percentage is the sql wildcard character that matches any number of characters when combined with the LIKE operator

Since you need to not hard code the letter you are searching for, you'll want to use a parameterized sql statement and you would put the wildcard into the parameters value leaving you with a sql statement like this:

SELECT *FROM CompanyTableWHERE CompanyNameLIKE @.P1;

|||

Hey Thanks!

That worked like a charm! I can now filter it based on what linkbutton is pressed. I'm still a bit fuzzy on the parametrized statement though... I understand it and all, but where and how would I set the parameter to each letter? On the OnClick of each linkbutton? If so, I would be still be writing out all 26 OnClick events?

On a side note, I cant believe how simple this filtering thing really is. I think the fellas over at codeproject are really over complicating it ;) Thanks again for your response,

-Derek

|||

add a usercontrol to encapsulate the Alphabet linkbuttons

PartialClass AlphabetBarInherits System.Web.UI.UserControlPublic Event Click(ByVal valueAs String)Protected Sub Page_Init(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Init'dynamically create a series of linkbuttonsFor keycodeAs Integer = 65To 90'one for each letter in the alphabetDim lnkAs New LinkButton lnk.Text = Chr(keycode) lnk.CommandArgument = Chr(keycode)AddHandler lnk.Click,AddressOf onClick'have them all use the same event handlerMe.Controls.Add(lnk)Me.Controls.Add(New LiteralControl(" "))'space them outNext End Sub Private Sub onClick(ByVal senderAs Object,ByVal eAs EventArgs)Dim lnkAs LinkButton =DirectCast(sender, LinkButton)'raise a single event 'using the clicked links commandargument as our events argumentRaiseEvent Click(lnk.CommandArgument)End SubEnd Class

Now drag that control onto your page and add a handler for its new Click event

Protected Sub AlphabetBar1_Click(ByVal valueAs String)Handles AlphabetBar1.Click'value is the alphabet letter that was clicked on in the usercontrol 'now we can build our filterDim filterParameterAs String = value &"%"'... '...End Sub

Allowing secure connections to SQL Server 2000 through a firewall

Hello,

My question is about allowing and securing connections to SQL Server 2000 over the internet. The company that I work for has an application server that several of our clients connect to via the internet using secure .NET remoting. Basically, the clients have a desktop application that they run that creates a remoting connection to our server software and we handle the server/database part. Anyway, one of our clients now wants to use Crystal Reports to run ad hoc queries on their data that is hosted on our SQL 2000 database server behind our firewall. Obviously, opening up a port in our firewall and allowing someone to run ad hoc queries on the database makes us all more than a little nervous about security.

Has anyone else here had to deal with this sort of situation before? We'd like to set up a secure, encrypted connection for this one client, but still keep it locked down for everyone else. Is it as simple as enabling encryption and generating SSL certificates for the client machine and our server? I've only been able to find a few resources that help with bits and pieces of the problem, never anything tackling the issue as a whole. If anyone has any thoughts, experiences, links, etc. to share it would be greatly appreciated. We are a small company and no one here has experience with this sort of thing.

Cheers!
Justin

Hi Lovero,

You could setup your SQL Server to doesn't use default port (1433). You will use custom port (10030) for example or another.

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Yes, we certainly plan to change the default port on SQL Server in addition to any other steps we take. Actually, since there will be port forwarding from the firewall, I'm not sure that it's a required step, but probably a good idea. I'm much less sure about how to set up the whole security/encryption framework.|||

Yes, It is good idea :)

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

ALL STORED PROCS RETURNING NOTHING BUT NULLS IN OUTPUT PARAMETERS

I think my hosting company changed something on SQL Server, but I don't
know what. Suddenly, all OUTPUT parameters from ANY SPROC return NULL.
The funny thing is, the values are in the variables within the SPROCS
(see the PRINT results on the SPROC example below) The behavior is the
same in Query Analyzer as in ADO.Net.
Any help would be GREATLY appreciated.
Thanks,
Brad
Here is some detail:
Stored Proc Definition
=====================================
ALTER PROC DBO.USP_GET_POOL_INFO
@.POOLID INT
,@.PRIVATE CHAR(1) OUTPUT
,@.PLAYERLIMIT NVARCHAR(33) OUTPUT
,@.PLAYERCOUNT INT OUTPUT
AS
SELECT
@.PRIVATE = private
,@.PLAYERLIMIT = CASE WHEN p.player_limit = 0 OR p.player_limit IS
NULL THEN 'No Limit' ELSE CAST(p.player_limit AS NVARCHAR(33)) END
,@.PLAYERCOUNT = ISNULL(pc.player_count,'')
from
tPools p
LEFT OUTER JOIN
(
SELECT
POOLID,
COUNT(*) player_count
FROM
tProfilePool
GROUP BY
POOLID
) pc
ON
p.pool_id = pc.poolid
where
pool_id = @.POOLID
PRINT @.PRIVATE
PRINT @.PLAYERLIMIT
PRINT @.PLAYERCOUNT
========================================
====
Stored Proc Execution:
========================================
====
DECLARE @.PLAYERLIMIT NVARCHAR(33)
DECLARE @.PLAYERCOUNT INT
DECLARE @.PRIVATE CHAR(1)
EXEC DBO.USP_GET_POOL_INFO 2, @.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
SELeCT @.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
Result:
NULL NULL NULL
Messages Result (from the PRINT command):
Y
No Limit
1
========================================
Raw TSQL Approach:
========================================
====
DECLARE @.PRIVATE CHAR(1)
DECLARE @.PLAYERLIMIT VARCHAR(33)
DECLARE @.PLAYERCOUNT INT
SELECT
@.PRIVATE = private
,@.PLAYERLIMIT = CASE WHEN p.player_limit = 0 OR p.player_limit IS
NULL THEN 'No Limit' ELSE CAST(p.player_limit AS NVARCHAR(33)) END
,@.PLAYERCOUNT = ISNULL(pc.player_count,'')
from
tPools p
LEFT OUTER JOIN
(
SELECT
POOLID,
COUNT(*) player_count
FROM
tProfilePool
GROUP BY
POOLID
) pc
ON
p.pool_id = pc.poolid
where
pool_id = 2
SELECT
@.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
========================================
Execution Result:
Y No Limit 1You need to specify OUTPUT in the EXEC
EXEC DBO.USP_GET_POOL_INFO 2, @.PRIVATE OUTPUT , @.PLAYERLIMIT OUTPUT ,
@.PLAYERCOUNT OUTPUT