ALTER table queries

In Oracle, alter statements with modify clause can be given for more than one query ? Is it possible in SQL Server ?

Eg :-

ALTER TABLE test1
MODIFY col1 VARCHAR2(1024)
MODIFY col2 VARCHAR2(256)
MODIFY col3 VARCHAR2(256)

Please give the equivalent for the above in SQL Server . Can all this exists in a single query in SQL Server ?MS-SQL supports the SQL-92 standard. Under the standard, you can add multiple columns in a single operation, but you can only change one existing column at a time.

It is possible to configure SQL 2000 to allow changes to multiple columns at once, but it is NOT supported at all. I would strongly advise that you break your changes down so that they meet the SQL-92 standard instead of trying to work around the standard.

-PatP|||Hi,

Thanks for your prompt reply.

Thanks,
Sam

Almost there (I think)...SQL Update problem...

I am trying to update a single field in a SQL database table. I created a SQLDataSource, configured Select and Update queries, and wrote some code in the script block to do the update after a button click. The SQLDataSource is in a contentplaceholder. What am I doing wrong in the data source, the script block, or both? Thanks so much in advance...

Here is the code for the SQLDataSource:

Dim ImageUploaded As Integer = 2

srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded

srcUpdateImageUploaded.Update()

Here is the code in the script block:

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = ?">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="EmilyTheKitty" PropertyName="Text" Type="Object" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Here is the error that I get:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 164: Dim ImageUploaded As Integer = 2
Line 165:
Line 166: srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded
Line 167: srcUpdateImageUploaded.Update()
Line 168:

Source File: C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx Line: 166

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx:166
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +104
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5614

There is no @.ImageUploaded parameter in your SQLDataSource, See the modified code below

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="ImageUploaded" PropertyName="Text" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

I appreciate the response...but it didn't work. I tried the modified code you posted. I pasted it into my page, tried it, and got the "Object reference not set to an instance of an object" again. Here is the code that I copied out of my page (it's the same code you posted)...

<asp:SqlDataSourceID="srcUpdateImageUploaded"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

ProviderName="System.Data.SqlClient"

SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"

UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">

<UpdateParameters>

<asp:ControlParameterControlID="TextBox1"Name="ImageUploaded"PropertyName="Text"Type="Int32"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"Name="UserName"PropertyName="Text"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

|||

where you are trying to update your code?? is it in the page load event ?? and also try to change asp:controlParameter into asp:FormParameter. If still not solved pls paste the whole code I will look into it

|||

Okay...I changed my mind on how I want to do this. I did away with the SQL data source connection object and I want to do this entirely with code in the script block. There is a command button that is clicked which invokes the following code. A textbox is included on the page and it is called by the code. The new error I get is this:

" Error updating table. Must declare the scalar variable "@.updatevalue". "

Here is the entirety of the code that is called:

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.updatevalue "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

|||

Okay, I fixed my own problem. I also figured out how these lines are put together so I am beyond merely cutting and pasting code in from books. Here is the correct code (corrected lines in bold, italics, and underlined):

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.ImageUploaded "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

cmd.Parameters.AddWithValue("@.username", username)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

Allowing Local and Remote connections breaks SQL queries

hello
I have a remote server in a datacentre that hosts our website
(www.sheffcare.co.uk) and we have an online backup service that tries
to backup out \inetpub\www and SQL files.
The thing is, that everytime the the backup runs, the website SQL
queries break, with the following errors:
Microsoft JET Database Engine error '800004005'
Operation must use an updateable query
/web_managers/calendarAmend.asp, line 88
Also logged in the System Even Log are:
1. Login failed for user 'administrator'
2. SSPI handshake failed with error code 0x8009030c while establishing
a connection with integrated security; the
connection has been closed
3. Login failed for user ''. The user is not associated with a trusted
SQL Server connection.
It seems the only way to get the website working again is to go into
SQL Server Suface Area Configuration application and change Remote
Connections from Local and Remote to Local only and restart the
SQLEXPRESS service.
But we need to allow Local and Remote Connections for the online
backup to work.
Could this be a problem with the code?
Thanks for any pointers.What backup program are you using?
T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
Because you said "online backup service that tries to backup out
\inetpub\www and SQL files" I get the feeling you're doing some filesystem
based backup and not a database backup. I've never done that.
If I'm correct, then a simple and fast solution is to not backup the .mdf,
.ndf & .ldf files directly and create a maintenance plan - except that
maintenance plans are 2000 and I think you have 2005.
"cw1972" <cw1972@.gmail.com> wrote in message
news:1192439902.757094.12590@.e9g2000prf.googlegroups.com...
> hello
> I have a remote server in a datacentre that hosts our website
> (www.sheffcare.co.uk) and we have an online backup service that tries
> to backup out \inetpub\www and SQL files.
> The thing is, that everytime the the backup runs, the website SQL
> queries break, with the following errors:
> Microsoft JET Database Engine error '800004005'
> Operation must use an updateable query
> /web_managers/calendarAmend.asp, line 88
>
> Also logged in the System Even Log are:
> 1. Login failed for user 'administrator'
> 2. SSPI handshake failed with error code 0x8009030c while establishing
> a connection with integrated security; the
> connection has been closed
> 3. Login failed for user ''. The user is not associated with a trusted
> SQL Server connection.
> It seems the only way to get the website working again is to go into
> SQL Server Suface Area Configuration application and change Remote
> Connections from Local and Remote to Local only and restart the
> SQLEXPRESS service.
> But we need to allow Local and Remote Connections for the online
> backup to work.
> Could this be a problem with the code?
> Thanks for any pointers.
>|||On 15 Oct, 14:31, "Jay" <s...@.nospam.org> wrote:
> What backup program are you using?
it's an online backup prodcedure provided by these people -
http://www.databarracks.com/OnlineBackup/Technology/Software/SupportedApplications/
- they say SQL server is fully supported
> T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
> Because you said "online backup service that tries to backup out
> \inetpub\www and SQL files" I get the feeling you're doing some filesystem
> based backup and not a database backup. I've never done that.
> If I'm correct, then a simple and fast solution is to not backup the .mdf,
> .ndf & .ldf files directly and create a maintenance plan - except that
> maintenance plans are 2000 and I think you have 2005.
>
I'm unsure what a maintenance plan is, I'll have to look into it - we
are using SQL Express 2005|||I am not familar with that backup program, however, I would suggest asking
their support.
As to the maintenance plans, SQL Server Express does not support maintenance
plans, or SQL agent. So, to automate anything, you have to use the Windows
scheduler and SQLCMD (see BOL).
What is it you are trying to backup? Just the database, or the database and
the website? I still have the feeling that you're trying to use one
application to back everything up. While I suppose that's possible, I don't
think it's a good idea.
Just to get yourself covered, in a query window, for each database you care
about (including master) run:
BACKUP DATABASE master TO DISK='X:\Backups\master.BAK' WITH INIT
(replacing the dbname and the path, making sure everything exists)
"cw1972" <cw1972@.gmail.com> wrote in message
news:1192458718.529399.35120@.e9g2000prf.googlegroups.com...
> On 15 Oct, 14:31, "Jay" <s...@.nospam.org> wrote:
>> What backup program are you using?
> it's an online backup prodcedure provided by these people -
> http://www.databarracks.com/OnlineBackup/Technology/Software/SupportedApplications/
> - they say SQL server is fully supported
>> T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
>> Because you said "online backup service that tries to backup out
>> \inetpub\www and SQL files" I get the feeling you're doing some
>> filesystem
>> based backup and not a database backup. I've never done that.
>> If I'm correct, then a simple and fast solution is to not backup the
>> .mdf,
>> .ndf & .ldf files directly and create a maintenance plan - except that
>> maintenance plans are 2000 and I think you have 2005.
> I'm unsure what a maintenance plan is, I'll have to look into it - we
> are using SQL Express 2005
>

All DB ops hangs in sql2000

Hi
We have a warehouse fact table with millions of rows.
When we run a report that select massive amount of data
all the queries against that table hangs.
We checked there were no outstanding transactions against that table.
Any ideas what it could be.
Tks
MangeshMangesh Deshpande wrote:
> Hi
> We have a warehouse fact table with millions of rows.
> When we run a report that select massive amount of data
> all the queries against that table hangs.
> We checked there were no outstanding transactions against that table.
> Any ideas what it could be.
> Tks
> Mangesh
Are any of the queries timing out, possibly leaving locks on the server
until the connection is cleaned up by SQL Server?
What about blocking issues? Can you use a NOLOCK hint on the "massive"
queries to prevent shared locks from being taken?
David Gugick
Imceda Software
www.imceda.com