Changing columns name in a temp table

Dear All,

I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.

trying to use:

exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'

I get:

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @.objname is ambiguous or the claimed @.objtype (COLUMN) is wrong.

There is no mistype, the table name and column name are correct.

Can temp table's column names not be altered?

If yes, how?

Thanks in advance!Hi gorgenyi

Let's cover the obvious stuff first of all to get an idea of what you are doing and why.
1) Why not define your temp table with the correct column names in the first place?
2) Why not alias your column names in the result set you create from the temp table?|||ok, here it goes.

I created a report with a stored procedure which displays resource allocation to projects in the BD pipeline week by week for the next 10 weeks ahead.

the end reult looks like this:

Project name, w1, w2, w3, w4, w5, w6 etc...
Prj A 1 2 2 1
Prj B 1 2 2 2.5 2 2
Prj C .5 1 1 1.5 1
...

I'm using w1, w2, w3 for the weeks ahead during the stored procedure for multiple joins, however I'd like to change them to the always relevant week numbers programatically in the follwoing format '2007-17', '2007-18', '2007-19' ... etc at the end so I can publish these as the datagrid's header.

Did I explain clearly? What would you recomend? Thanks for your help on this.|||You are making the all too common mistake of trying to use SQL and/or Query Analyzer as a reporting environment. It is not designed for this.
What you are trying to do is modify the way the data is displayed, and this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
Dynamic column names are BAD, because then whatever reporting tool you use will never know what output schema to expect.|||this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.Somehow you conspired to miss with all those I think. I suspect this is just some .NET front end.

You can (I think) dynamically set up your column headers in a datagrid but I am afraid I cannot remember how.

blindman is right though - this is best handled in your front end rather than SQL.|||...or .Net or ASP.|||It just seemed very simple, and must say I do not have experience of the reporting tools. Here is what I did:

temp table to get next 10 weeks ids
temp table to get active BD items and the 10 columns with 0 values
then a double loop where I update each cell with a resource number to the appropriate BD item and the appropriate week number.

Dispay the data in datagrid, with headings for the weeks as Week 1, Week 2, etc. while adding the sums up / week in ASP.NET and do various other things like colouring background etc.

But I'd like to get the actually internal week reference displayed on the grid, which of course chnage weekly, so want to do it dynamicaly. But yes, point taken on ASP.NET won't know what col name they will be.

And yes, this is ASP.NET front end.|||Some ideas from the web. Maybe consider posting in an ASP.NET site if you can't follow them. You defo do not want to do this at the database.
http://www.thescripts.com/forum/thread320779.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_20976549.html

Changing column data type constraint

I am trying to change the data type of two columns in a SQL database.The columns were created using the“smallint” data type.Since they are used to identify document and document sections rather than mathematical functions, I think they should have been constrained to a text data type such as nvarchar.When I try to concatenate with a query the result is a mathematical addition of the numbers, butI am trying to combine the two numbers as a string with a "-" between them.

I have not had any success in changing the data type of the two columns.Apparently, the original database was set up for full text search and won’t let me change the column data type.I keep getting this error message:

'Full Documents' table

- Unable to modify table.

Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.

The statement has been terminated.

I ran a query to increase the timeout period (which succeeded in increasing the timeout but still got the same error message when trying to change the column data type). My research suggests that this is really a matter of the column data constraints related to the full text search issue.

Any suggestions on how to change the data type of these columns?

Since this is a general SQL question, I'm moving it to a more general forum where you'll get a better answer.

Mike

Changing Collations...

I have looked thru the forum but have a couple of questions:
1) data base was created with case insensitive collation
2) all the tables were then create (72 tables) and by default got the CI collation on all columns
3) lots of data was added 2GB
4) discovered mistake and altered the database to have case sensitive collation
5).... how to change all the collations for all the columns without doing them all manually
can i backup the database and change some settings and restore it?
export all the data, drop and recreate tables and import data?
??Why would you want case sensitive?

99.999% of every database I work with use the default out of the box collation.

It's only when I deal with 3rd party vendors that I have to mess with a different one.

This makes life painful at times.|||and the database has to be case sensitive because the customer wants to be able to use lower and upper case codes in some of the files.|||Case insensetive doesn't mean you can't have mixed case, it just means that 'a' = 'A'. That would be false in Case Insesitive.

I would leave it alone.|||fwiw i've used binary sort order (case-sensitive) for vldb implementations that use integer keys for joins and gotten tested 30% gains in join performance.|||The apps being used for the database is an OLD cobol based programs. all thru the programs we have tests for lower and upper case values that must NOT equate 'a' as equal to 'A'. so that is why i MUST change the collation. it is not an option to change anything else about the database/tables.
anyways, i was hoping that someone here might know of a better way or have a tool that does it.|||I know you can not do a restore...

And you should probably create an instance on the box that is set up the way you want it.

Script out the database, create it in that instance, and bcp everything out and back in. Use Native format

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2|||The general sequence is this:

1) Research and find all columns that are defined as char, varchar, text or ntext.
2) Find all indexes that include any of these columns
3) Drop all indexes found in step 2
4) Issue the "alter table alter column" command to change the collation of each char column.
5) drop the index you missed in step 3 (always happens to me, anyway)
6) Finish altering tables
7) recreate all indexes that have been dropped.

Before you go through this, you may want to get a script of all of the indexes, in case you miss one here or there. A backup of the whole database is a handy thing, as well. Good luck.

Changing Collation on a Cluster

Hi,
I am sure that someone has managed to do this. We have a 64_bit Clustered
SQL2005 SP2 build 3175 server that we created as Latin1_General_Bin. We want
to change it to the North American default but keep getting errors. The
latest was: - SQL Server Setup is unable to run on this node as it is
unavailable due to a SCC failure or other reason
SQL Server Setup is unable to run. Either the owning node or all nodes that
are currently part of the virtual server are not available. Review any SCC
failures and validate that all nodes are online.
Can someone help before we uninstall? Will we have problems if we uninstall?
Thanks
Chris
Ureka. We did it. We found that the registry key value as to where the
install came from was different on the two nodes and we were using the sa
password for the adminpassword instead of the Windows account we installed
with.
The build still shows 3200, where we had got to, even though BOL seems to
think that this may not be right. It mentioned about having to re-apply the
service packs and hotfix builds.
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uWLbw$EOIHA.2376@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am sure that someone has managed to do this. We have a 64_bit Clustered
> SQL2005 SP2 build 3175 server that we created as Latin1_General_Bin. We
> want to change it to the North American default but keep getting errors.
> The latest was: - SQL Server Setup is unable to run on this node as it is
> unavailable due to a SCC failure or other reason
> SQL Server Setup is unable to run. Either the owning node or all nodes
> that are currently part of the virtual server are not available. Review
> any SCC failures and validate that all nodes are online.
> Can someone help before we uninstall? Will we have problems if we
> uninstall?
> Thanks
> Chris
>
|||BOL is correct.
The binaries on all the nodes are build 3200, but likely the procedures on
Master (and possibly the resource database) are still at RTM. Reapply all
service packs and hotfix/cumulative updates to guarantee binaries and data
elements are all in sync.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0QWCrFOIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Ureka. We did it. We found that the registry key value as to where the
> install came from was different on the two nodes and we were using the sa
> password for the adminpassword instead of the Windows account we installed
> with.
> The build still shows 3200, where we had got to, even though BOL seems to
> think that this may not be right. It mentioned about having to re-apply
> the service packs and hotfix builds.
> Chris
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:uWLbw$EOIHA.2376@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks Geoff.
I checked the summary.txt says 1399.06. That's what made me suspicious.
Looks like MS need to make a change somewhere otherwise people will not
re-run the updates.
Chris
PS. Hope this is handled better in SQL2008.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e96bTBOOIHA.2208@.TK2MSFTNGP06.phx.gbl...
> BOL is correct.
> The binaries on all the nodes are build 3200, but likely the procedures on
> Master (and possibly the resource database) are still at RTM. Reapply all
> service packs and hotfix/cumulative updates to guarantee binaries and data
> elements are all in sync.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0QWCrFOIHA.4272@.TK2MSFTNGP06.phx.gbl...
>

Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.

> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:

>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.

> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated wit
h
> numerous rows. This table should have been created with one primary and o
ne
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:

>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and on
e
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes to table not reflected in report

Hi!

I usa a SQL server for my windows application project. I have created a Crystal report, using .NET dataset connections. I made changes to one of the tables used in the report, and I updated the dataset. I then opened the report and used the "Verify database" feature, but the changes are not reflected. This worked before in the same report with changes to the same table. It seems like it does not work in any of my reports (using different datasets).

I have spent a good hour searching knowledge bases and forums, but found nothing. Anyone here that can help me? (I'm a bit pressed for time, close to deadline and all)

Thanks in advanceHave you checked EnableSaveDataWithReport and made sure its set off (either thru the menu option) or thru your code? Hope this helps|||Also Open the report and Do verify database|||Hi!

Thanks for the answers. The problem is solved for the moment. It seems that the "Verify database" functionality always works after I have run the applikation, a build is not enough. But If I start the app and then try to do the changes it seems to work....

So I consider it a bug that its not enough to changes and save the dependant DS... but as long as I get it to work I keep on. =)

Changes to cloned database are affecting the original

I am trying to use SQL Express to support unit testing of functionality which requires database access.

I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes.

Here's what I do today:

Before Test:

The original database is copied to a unique location in the temp directory

A unique database name is generated (GUID-based)

I connect to the new database file with:

Server=.\SQLEXPRESS;AttachDbFilename="Path\To\Temp\File";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true

During the test all database access is routed to the temporary database GuidDBName

After Test:

Close all active database connections and clear the connection pool

Connect to the master database and detach the database:

Server=.\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName'

Delete the temporary file

When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran.

Can anyone explain why this is happening and/or provide a solution?

Short version - I have no idea what's happening here, I can't reproduce this behavior based on the information you've provided.

Long version - Your connection strings have duplicate information in them and I'm guessing that part of the problem is that you're connecting to different databases that you think you are. I also have question about you're original database; you don't mention how your are opening the original database to discover that it has been modified. I'm guessing you're using Managment Studio, but I'll bet you're looking at the parent instance SQLEXPRESS and not the User Instance you're creating with the connection string you've provided. A User Instance is a completely separate instance of SQL Server.

The duplication you have is that Database= and Initial Catalog= are fundamentally the same thing. (Check out this BOL topic for the gorey details on connection string keywords in SNAC.) On top of that, you don't really need to use either of them when you use AttachDbFilename in combination with User Instance=True since SQL Express will automatically named the database with the file path.

I wrote a little test routine to try and cause this, but I couldn't get it to fail. Here is what I did if you want to compare it to your code solution:

static void Main(string[] args)

{

// Create a new copy of the database

string path1 = "C:\\Users\\mikewa\\Documents\\RANUTest\\Source\\foo.mdf";

string path2 = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo.mdf";

string path2log = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo_log.ldf";

try

{

File.Delete(path2);

File.Delete(path2log);

File.Copy(path1, path2);

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

// Attach the temp database to the User Instance

string connectionString = "Server=.\\SQLEXPRESS;AttachDbFilename=" + path2 + ";Integrated Security=True;User Instance=True";

using (SqlConnection cnn = new SqlConnection(connectionString))

{

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "CREATE TABLE dbo.tblFoo (myField int NULL)";

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cmd.ExecuteNonQuery();

cnn.Close();

}

SqlConnection.ClearAllPools();

// Now kill the temp database in SQL

string masterConnectionString = "Server=.\\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=True";

using (SqlConnection cnn = new SqlConnection(masterConnectionString))

{

SqlCommand cmd = new SqlCommand("sp_detach_db '" + path2 + "'");

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cnn.Open();

cmd.ExecuteNonQuery();

}

}

As you can see, I've stripped the extra keywords out of my connection string and just used the complete file path, represented by the variable 'path2' as the file name. This automatic naming happens automatically (hence the name) as part of attaching the file to a User Instance. I use the same variable later to detach the file. After I run this, I'm able to examine my source file and see that the modification does not exist (i.e. no table named tblFoo).

Check around to ensure that you don't have multiple copies of your original database laying around. If you're looking at the "original file" in Management Studio while connected to the parent instance, it's possible that you're actually looking at a different file than the one you're copying.

Mike

Changed sa password, db maint plan not working

Our in-house developer changed the "sa" password on the Sql 2000 server and now the database maintenance plan that I created for backups to disk isn't working. The day it stopped I received the following errors in the log:

"2003-02-13 09:20:41.37 spid58 Error: 15457, Severity: 0, State: 1

2003-02-13 09:20:41.37 spid58 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..

2003-02-13 09:20:41.48 spid58 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.

2003-02-13 15:54:25.59 spid53 Error: 15457, Severity: 0, State: 1

2003-02-13 15:54:25.59 spid53 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..:"I don't think that changing the sa password has got anything to do with the DBMaintenance plan unless you are using DTS for running the DB Backup etc etc.

changed password by accident. what now?

Hello,
I've changed password to the sql login wwNet we created for our intranet
application.
I expected our intranet site will fail, but it is working ok. I started a
trace on a server and I see call were made under login name wwNet.
Now I am confuse. Did this wwNet controled intranet login at all?
Or will the password change take effect only after server reboot?
Please help!
thank you
JanOn Wed, 12 Jul 2006 09:01:02 -0700, Jan wrote:

>Hello,
>I've changed password to the sql login wwNet we created for our intranet
>application.
>I expected our intranet site will fail, but it is working ok. I started a
>trace on a server and I see call were made under login name wwNet.
>Now I am confuse. Did this wwNet controled intranet login at all?
>Or will the password change take effect only after server reboot?
>Please help!
Hi Jan,
AFAIK, the passwoord change takes effect immediately for new login
attempts, but existing connections remain open.
Hugo Kornelis, SQL Server MVP

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

Change the size of database

We have created a database with size 500MB and find that
the size of it should be much larger (2GB) according to
the developer.
We would like to know is there any way to extend the size
of the database ? OR We have to backup the existing one,
delete it, create a new one, load the data ?
Thanks
You can change the size of the database files in Enterprise Manager, or you
can use the ALTER DATABASE command:
ALTER DATABASE databasename MODIFY FILE (NAME=logical_file_name,
SIZE=2000MB)
[See BOL for more info]
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
|||You can increate the size of database files using ALTER DATABASE ... MODIFY
FILE. You can find syntax details on the Books Online. Below is an example
copied from the Book Online <tsqlref.chm::/ts_aa-az_4e5h.htm>.
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
You can also do this from Enterprise Manager. Consider also allowing file
growth as a safety net.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
|||Paul wrote:
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
Of course you can increase database size. The size of database is made
up of a sum of the data files and log files used. So you need to
determine how many data files you have and what size you'd like to
increase them to. Same for the log files. In each case, you have the
option and should make use of the AutoGrow option which can increase
data and log file sizes by a percentage or a fixed amount when space
runs low. It's best to manange file sizes yourself and increase
off-hours, but using AutoGrow will prevent the database from crashing if
it runs out of disk space. Still keep an eye on the file sizes over
time. Increasing the file sizes now can limit external fragmentation in
the files. If you have time to take the SQL Server offline, I would
defrag the drives and then start up SQL Server and increase the file
sizes.
You can use the ALTER DATABASE command, but it's easier to use SQL
Enterprise Manager for this type of task.
David Gugick
Imceda Software
www.imceda.com
|||In this way, when I execute the ALTER TABLE command, does
it only change the size of data file or the size of the
database (Data File and Log File) ?
ALTER DATABASE databasename MODIFY FILE
(NAME=logical_file_name,
SIZE=2000MB)
[vbcol=seagreen]
>--Original Message--
>Paul wrote:
size[vbcol=seagreen]
one,
>Of course you can increase database size. The size of
database is made
>up of a sum of the data files and log files used. So you
need to
>determine how many data files you have and what size
you'd like to
>increase them to. Same for the log files. In each case,
you have the
>option and should make use of the AutoGrow option which
can increase
>data and log file sizes by a percentage or a fixed amount
when space
>runs low. It's best to manange file sizes yourself and
increase
>off-hours, but using AutoGrow will prevent the database
from crashing if
>it runs out of disk space. Still keep an eye on the file
sizes over
>time. Increasing the file sizes now can limit external
fragmentation in
>the files. If you have time to take the SQL Server
offline, I would
>defrag the drives and then start up SQL Server and
increase the file
>sizes.
>You can use the ALTER DATABASE command, but it's easier
to use SQL
>Enterprise Manager for this type of task.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
It will change the size of the specified file. Each physical file has a
unique logical name within a database. Specify the logical name in the
ALTER DATABASE command. You can use sp_help_db to list the database file
names. Example below:
--alter data file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase',
SIZE=2000MB)
--alter log file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase_Log',
SIZE=200MB)
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL
|||> You can use sp_help_db to list the database file names
Oops. I meant sp_helpdb. And of course with the database parameter:
EXEC sp_helpdb 'MyDatabase'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:es66IOeJFHA.732@.TK2MSFTNGP12.phx.gbl...
> It will change the size of the specified file. Each physical file has a
> unique logical name within a database. Specify the logical name in the
> ALTER DATABASE command. You can use sp_help_db to list the database file
> names. Example below:
> --alter data file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase',
> SIZE=2000MB)
> --alter log file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase_Log',
> SIZE=200MB)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <anonymous@.discussions.microsoft.com> wrote in message
> news:6f6401c525de$a967b280$a601280a@.phx.gbl...
>
|||I think someone else already pointed out that SQL Databases are composed of
2 or more files: at least one (or more) database file(s) and a log file.
You specify the file you want to change the size of in the statement below
by specifying its name in place of "logical_file_name". If you specify the
database file name it will change the size of the database file; if you
specify the log file name it will change the size of the log file.
There are some restrictions on changing database sizes, particularly on
shrinking them. See the ALTER DATABASE and BACKUP LOG (WITH TRUNCATE_ONLY)
commands in BOL for specifics.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL

Change the size of database

We have created a database with size 500MB and find that
the size of it should be much larger (2GB) according to
the developer.
We would like to know is there any way to extend the size
of the database ? OR We have to backup the existing one,
delete it, create a new one, load the data ?
ThanksYou can change the size of the database files in Enterprise Manager, or you
can use the ALTER DATABASE command:
ALTER DATABASE databasename MODIFY FILE (NAME=logical_file_name,
SIZE=2000MB)
[See BOL for more info]
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks|||You can increate the size of database files using ALTER DATABASE ... MODIFY
FILE. You can find syntax details on the Books Online. Below is an example
copied from the Book Online <tsqlref.chm::/ts_aa-az_4e5h.htm>.
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
You can also do this from Enterprise Manager. Consider also allowing file
growth as a safety net.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks|||Paul wrote:
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
Of course you can increase database size. The size of database is made
up of a sum of the data files and log files used. So you need to
determine how many data files you have and what size you'd like to
increase them to. Same for the log files. In each case, you have the
option and should make use of the AutoGrow option which can increase
data and log file sizes by a percentage or a fixed amount when space
runs low. It's best to manange file sizes yourself and increase
off-hours, but using AutoGrow will prevent the database from crashing if
it runs out of disk space. Still keep an eye on the file sizes over
time. Increasing the file sizes now can limit external fragmentation in
the files. If you have time to take the SQL Server offline, I would
defrag the drives and then start up SQL Server and increase the file
sizes.
You can use the ALTER DATABASE command, but it's easier to use SQL
Enterprise Manager for this type of task.
David Gugick
Imceda Software
www.imceda.com|||In this way, when I execute the ALTER TABLE command, does
it only change the size of data file or the size of the
database (Data File and Log File) ?
ALTER DATABASE databasename MODIFY FILE
(NAME=logical_file_name,
SIZE=2000MB)
>--Original Message--
>Paul wrote:
>> We have created a database with size 500MB and find that
>> the size of it should be much larger (2GB) according to
>> the developer.
>> We would like to know is there any way to extend the
size
>> of the database ? OR We have to backup the existing
one,
>> delete it, create a new one, load the data ?
>> Thanks
>Of course you can increase database size. The size of
database is made
>up of a sum of the data files and log files used. So you
need to
>determine how many data files you have and what size
you'd like to
>increase them to. Same for the log files. In each case,
you have the
>option and should make use of the AutoGrow option which
can increase
>data and log file sizes by a percentage or a fixed amount
when space
>runs low. It's best to manange file sizes yourself and
increase
>off-hours, but using AutoGrow will prevent the database
from crashing if
>it runs out of disk space. Still keep an eye on the file
sizes over
>time. Increasing the file sizes now can limit external
fragmentation in
>the files. If you have time to take the SQL Server
offline, I would
>defrag the drives and then start up SQL Server and
increase the file
>sizes.
>You can use the ALTER DATABASE command, but it's easier
to use SQL
>Enterprise Manager for this type of task.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
It will change the size of the specified file. Each physical file has a
unique logical name within a database. Specify the logical name in the
ALTER DATABASE command. You can use sp_help_db to list the database file
names. Example below:
--alter data file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase',
SIZE=2000MB)
--alter log file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase_Log',
SIZE=200MB)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
>>--Original Message--
>>Paul wrote:
>> We have created a database with size 500MB and find that
>> the size of it should be much larger (2GB) according to
>> the developer.
>> We would like to know is there any way to extend the
> size
>> of the database ? OR We have to backup the existing
> one,
>> delete it, create a new one, load the data ?
>> Thanks
>>Of course you can increase database size. The size of
> database is made
>>up of a sum of the data files and log files used. So you
> need to
>>determine how many data files you have and what size
> you'd like to
>>increase them to. Same for the log files. In each case,
> you have the
>>option and should make use of the AutoGrow option which
> can increase
>>data and log file sizes by a percentage or a fixed amount
> when space
>>runs low. It's best to manange file sizes yourself and
> increase
>>off-hours, but using AutoGrow will prevent the database
> from crashing if
>>it runs out of disk space. Still keep an eye on the file
> sizes over
>>time. Increasing the file sizes now can limit external
> fragmentation in
>>the files. If you have time to take the SQL Server
> offline, I would
>>defrag the drives and then start up SQL Server and
> increase the file
>>sizes.
>>You can use the ALTER DATABASE command, but it's easier
> to use SQL
>>Enterprise Manager for this type of task.
>>
>>--
>>David Gugick
>>Imceda Software
>>www.imceda.com
>>.|||> You can use sp_help_db to list the database file names
Oops. I meant sp_helpdb. And of course with the database parameter:
EXEC sp_helpdb 'MyDatabase'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:es66IOeJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> In this way, when I execute the ALTER TABLE command, does
>> it only change the size of data file or the size of the
>> database (Data File and Log File) ?
> It will change the size of the specified file. Each physical file has a
> unique logical name within a database. Specify the logical name in the
> ALTER DATABASE command. You can use sp_help_db to list the database file
> names. Example below:
> --alter data file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase',
> SIZE=2000MB)
> --alter log file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase_Log',
> SIZE=200MB)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <anonymous@.discussions.microsoft.com> wrote in message
> news:6f6401c525de$a967b280$a601280a@.phx.gbl...
>> In this way, when I execute the ALTER TABLE command, does
>> it only change the size of data file or the size of the
>> database (Data File and Log File) ?
>> ALTER DATABASE databasename MODIFY FILE
>> (NAME=logical_file_name,
>> SIZE=2000MB)
>>--Original Message--
>>Paul wrote:
>> We have created a database with size 500MB and find that
>> the size of it should be much larger (2GB) according to
>> the developer.
>> We would like to know is there any way to extend the
>> size
>> of the database ? OR We have to backup the existing
>> one,
>> delete it, create a new one, load the data ?
>> Thanks
>>Of course you can increase database size. The size of
>> database is made
>>up of a sum of the data files and log files used. So you
>> need to
>>determine how many data files you have and what size
>> you'd like to
>>increase them to. Same for the log files. In each case,
>> you have the
>>option and should make use of the AutoGrow option which
>> can increase
>>data and log file sizes by a percentage or a fixed amount
>> when space
>>runs low. It's best to manange file sizes yourself and
>> increase
>>off-hours, but using AutoGrow will prevent the database
>> from crashing if
>>it runs out of disk space. Still keep an eye on the file
>> sizes over
>>time. Increasing the file sizes now can limit external
>> fragmentation in
>>the files. If you have time to take the SQL Server
>> offline, I would
>>defrag the drives and then start up SQL Server and
>> increase the file
>>sizes.
>>You can use the ALTER DATABASE command, but it's easier
>> to use SQL
>>Enterprise Manager for this type of task.
>>
>>--
>>David Gugick
>>Imceda Software
>>www.imceda.com
>>.
>|||I think someone else already pointed out that SQL Databases are composed of
2 or more files: at least one (or more) database file(s) and a log file.
You specify the file you want to change the size of in the statement below
by specifying its name in place of "logical_file_name". If you specify the
database file name it will change the size of the database file; if you
specify the log file name it will change the size of the log file.
There are some restrictions on changing database sizes, particularly on
shrinking them. See the ALTER DATABASE and BACKUP LOG (WITH TRUNCATE_ONLY)
commands in BOL for specifics.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
>>--Original Message--
>>Paul wrote:
>> We have created a database with size 500MB and find that
>> the size of it should be much larger (2GB) according to
>> the developer.
>> We would like to know is there any way to extend the
> size
>> of the database ? OR We have to backup the existing
> one,
>> delete it, create a new one, load the data ?
>> Thanks
>>Of course you can increase database size. The size of
> database is made
>>up of a sum of the data files and log files used. So you
> need to
>>determine how many data files you have and what size
> you'd like to
>>increase them to. Same for the log files. In each case,
> you have the
>>option and should make use of the AutoGrow option which
> can increase
>>data and log file sizes by a percentage or a fixed amount
> when space
>>runs low. It's best to manange file sizes yourself and
> increase
>>off-hours, but using AutoGrow will prevent the database
> from crashing if
>>it runs out of disk space. Still keep an eye on the file
> sizes over
>>time. Increasing the file sizes now can limit external
> fragmentation in
>>the files. If you have time to take the SQL Server
> offline, I would
>>defrag the drives and then start up SQL Server and
> increase the file
>>sizes.
>>You can use the ALTER DATABASE command, but it's easier
> to use SQL
>>Enterprise Manager for this type of task.
>>
>>--
>>David Gugick
>>Imceda Software
>>www.imceda.com
>>.

Change the size of database

We have created a database with size 500MB and find that
the size of it should be much larger (2GB) according to
the developer.
We would like to know is there any way to extend the size
of the database ? OR We have to backup the existing one,
delete it, create a new one, load the data ?
ThanksYou can change the size of the database files in Enterprise Manager, or you
can use the ALTER DATABASE command:
ALTER DATABASE databasename MODIFY FILE (NAME=logical_file_name,
SIZE=2000MB)
[See BOL for more info]
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks|||You can increate the size of database files using ALTER DATABASE ... MODIFY
FILE. You can find syntax details on the Books Online. Below is an example
copied from the Book Online <tsqlref.chm::/ts_aa-az_4e5h.htm>.
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
You can also do this from Enterprise Manager. Consider also allowing file
growth as a safety net.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:5a7601c525d6$3b6bb270$a501280a@.phx.gbl...
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks|||Paul wrote:
> We have created a database with size 500MB and find that
> the size of it should be much larger (2GB) according to
> the developer.
> We would like to know is there any way to extend the size
> of the database ? OR We have to backup the existing one,
> delete it, create a new one, load the data ?
> Thanks
Of course you can increase database size. The size of database is made
up of a sum of the data files and log files used. So you need to
determine how many data files you have and what size you'd like to
increase them to. Same for the log files. In each case, you have the
option and should make use of the AutoGrow option which can increase
data and log file sizes by a percentage or a fixed amount when space
runs low. It's best to manange file sizes yourself and increase
off-hours, but using AutoGrow will prevent the database from crashing if
it runs out of disk space. Still keep an eye on the file sizes over
time. Increasing the file sizes now can limit external fragmentation in
the files. If you have time to take the SQL Server offline, I would
defrag the drives and then start up SQL Server and increase the file
sizes.
You can use the ALTER DATABASE command, but it's easier to use SQL
Enterprise Manager for this type of task.
David Gugick
Imceda Software
www.imceda.com|||In this way, when I execute the ALTER TABLE command, does
it only change the size of data file or the size of the
database (Data File and Log File) ?
ALTER DATABASE databasename MODIFY FILE
(NAME=logical_file_name,
SIZE=2000MB)

>--Original Message--
>Paul wrote:
size[vbcol=seagreen]
one,[vbcol=seagreen]
>Of course you can increase database size. The size of
database is made
>up of a sum of the data files and log files used. So you
need to
>determine how many data files you have and what size
you'd like to
>increase them to. Same for the log files. In each case,
you have the
>option and should make use of the AutoGrow option which
can increase
>data and log file sizes by a percentage or a fixed amount
when space
>runs low. It's best to manange file sizes yourself and
increase
>off-hours, but using AutoGrow will prevent the database
from crashing if
>it runs out of disk space. Still keep an eye on the file
sizes over
>time. Increasing the file sizes now can limit external
fragmentation in
>the files. If you have time to take the SQL Server
offline, I would
>defrag the drives and then start up SQL Server and
increase the file
>sizes.
>You can use the ALTER DATABASE command, but it's easier
to use SQL
>Enterprise Manager for this type of task.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
It will change the size of the specified file. Each physical file has a
unique logical name within a database. Specify the logical name in the
ALTER DATABASE command. You can use sp_help_db to list the database file
names. Example below:
--alter data file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase',
SIZE=2000MB)
--alter log file
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME='MyDatabase_Log',
SIZE=200MB)
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
>
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL|||> You can use sp_help_db to list the database file names
Oops. I meant sp_helpdb. And of course with the database parameter:
EXEC sp_helpdb 'MyDatabase'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:es66IOeJFHA.732@.TK2MSFTNGP12.phx.gbl...
> It will change the size of the specified file. Each physical file has a
> unique logical name within a database. Specify the logical name in the
> ALTER DATABASE command. You can use sp_help_db to list the database file
> names. Example below:
> --alter data file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase',
> SIZE=2000MB)
> --alter log file
> ALTER DATABASE MyDatabase
> MODIFY FILE
> (NAME='MyDatabase_Log',
> SIZE=200MB)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <anonymous@.discussions.microsoft.com> wrote in message
> news:6f6401c525de$a967b280$a601280a@.phx.gbl...
>|||I think someone else already pointed out that SQL Databases are composed of
2 or more files: at least one (or more) database file(s) and a log file.
You specify the file you want to change the size of in the statement below
by specifying its name in place of "logical_file_name". If you specify the
database file name it will change the size of the database file; if you
specify the log file name it will change the size of the log file.
There are some restrictions on changing database sizes, particularly on
shrinking them. See the ALTER DATABASE and BACKUP LOG (WITH TRUNCATE_ONLY)
commands in BOL for specifics.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:6f6401c525de$a967b280$a601280a@.phx.gbl...[vbcol=seagreen]
> In this way, when I execute the ALTER TABLE command, does
> it only change the size of data file or the size of the
> database (Data File and Log File) ?
> ALTER DATABASE databasename MODIFY FILE
> (NAME=logical_file_name,
> SIZE=2000MB)
>
> size
> one,
> database is made
> need to
> you'd like to
> you have the
> can increase
> when space
> increase
> from crashing if
> sizes over
> fragmentation in
> offline, I would
> increase the file
> to use SQL

Change the InsertCommand of a datasource before insert

Hi,

I am trying to set the InsertCommand of a SqlDataSource prior to a new record being created in DetailsView. It has to be changed because certain fields are not being used in one scenerio and it is causing Null's to be written to the database.

Here is what I have tried:

I set the Insert command on page Load:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

}

But I don't want to insert the "extraValue if a certain condition is true so I want to change the insert command to SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

I tried to do that in the following:

protectedvoid DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

if (e.Exception !=null)

{

ErrorMessageLabel.Text =

"An error occured while entering this record. Please verify you have entered data in the correct format.";

e.ExceptionHandled =

true;

Response.Write(e.Exception);

}

GridView1.DataBind();

GridView1.DataBind();

}

But it doesn't seem to change,

Any Help would be appreciated,

Doug

Excuse me, maybe I missed it, but where did you change the insert command of SqlDataSource3? I saw you assign the same string to the insert command of SqlDataSource3 twice:

The first time:

protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

}

And the second time:

protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

|||

Sorry I made the same reply by mistake

|||

Some logic for this:

On you page load event:

IF condition1 is true THEN

SqlDataSource3.SelectCommand=" SELECT name,realName,type,extraValue FROM table"

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)"

ELSE

SqlDataSource3.SelectCommand ="SELECT name,realName,type FROM table"

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

END IF

}

|||

Sorry, the second should be:

protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

That is actually what I tried. But it doesn't seem to change..

|||

Thanks for taking a look,

Unfortunately the condition doesn't change on page load, it changes when a radio button is clicked within this page:

protectedvoid Type_Changed(object sender,EventArgs e)

{

string aha = ((RadioButtonList)DetailsView1.FindControl("type")).SelectedItem.Value;if (aha =="Mortgage")

{

DetailsView1.Fields[6].Visible =

true;

DetailsView1.Fields[7].Visible =

true;

DetailsView1.Fields[8].Visible =

true;

}

else

{

DetailsView1.Fields[6].Visible =

false;

DetailsView1.Fields[7].Visible =

false;

DetailsView1.Fields[8].Visible =

false;

}

}

I tried to change the Sql Insert command here but it doesn't seem to work.

Thanks again,

Doug

Change the displayed name in the Series Group for a chart

Hi,
I created a chart in sql reporting services. I added a series group to it.
Now what i want to do is to dispaly different series names when the chart is
viewed . For example, in my database, I have Region code as numbers. I have
used this Region Code as the Series group. What i want to do is, ...
if (RegionCode ==01
{
//the series group should display North
}
Similarly for other region codes. Is it possible? & how?
Thanks
pmudYes this is possible through the Label expression of a chart series
grouping.
* right-click on the chart and open the chart properties dialog
* go to the Data tab and edit the series grouping
* the grouping dialog has a grouping expression and a label expression
below. If no label expression is set, it will take the group expression
value as label by default.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:13D0A129-DEAA-4C05-9736-04E3104A9935@.microsoft.com...
> Hi,
> I created a chart in sql reporting services. I added a series group to it.
> Now what i want to do is to dispaly different series names when the chart
> is
> viewed . For example, in my database, I have Region code as numbers. I
> have
> used this Region Code as the Series group. What i want to do is, ...
> if (RegionCode ==01
> {
> //the series group should display North
> }
> Similarly for other region codes. Is it possible? & how?
> Thanks
> pmud

Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.
>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

Change Table from Namespace

I have a table like this : dbo.Table, and I recently create a namespace, and that table belongs there. So I want the table to be created on the new namespace, Namespace.Table, which is not big deal because I can script the table as a Create, and changes the [dbo].[table] to [Namespace].[Table]. But I need to import all the rows already on the old table. How can I achieve that?
Thanks

YOu can simply do a

INSERT INTO SchemaName.NewTable
SELECT * FROM SchemaOld.OldTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sweet! Thanks a lot for yur help!!