Can I do an "alter table add column", with an existing named default?

Hello folks,
Shouldn't I be able to do this? I'm not able to get the syntax to work
Alter table TableName add ColumnName tinyint not null default
DefaultZero
This works:
Alter table TableName add ColumnName tinyint not null default (0)
...but I need to immediately drop the default programatically, and for
that I need the default name, so I'd like to name it myself.
I tried this:
EXEC sp_unbindefault 'TableName .ColumnName'
but got the error "Cannot unbind from 'TableName .ColumnName'. Use
ALTER TABLE DROP CONSTRAINT.
I guess I could query the system tables to figure out the name, but
would prefer not to if I can avoid it.
thanks for any ideas!
SylviaHere's an example with the proper syntax:
ALTER TABLE TableName
ADD ColumnName tinyint NOT NULL
CONSTRAINT DF_TableName_ColumnName DEFAULT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <Puget4753@.yahoo.com> wrote in message
news:1116288687.747349.22520@.g47g2000cwa.googlegroups.com...
> Hello folks,
> Shouldn't I be able to do this? I'm not able to get the syntax to work
> Alter table TableName add ColumnName tinyint not null default
> DefaultZero
> This works:
> Alter table TableName add ColumnName tinyint not null default (0)
> ...but I need to immediately drop the default programatically, and for
> that I need the default name, so I'd like to name it myself.
> I tried this:
> EXEC sp_unbindefault 'TableName .ColumnName'
> but got the error "Cannot unbind from 'TableName .ColumnName'. Use
> ALTER TABLE DROP CONSTRAINT.
> I guess I could query the system tables to figure out the name, but
> would prefer not to if I can avoid it.
> thanks for any ideas!
> Sylvia
>|||thanks - this works perfectly!

Can anyone plss help me with this sql syntax plss...

Hi.. I want to join this query
[CODE]select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & "select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0[/CODE]
in this query
[CODE]select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_ID,iCalls_Status.Status_Label from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID[/CODE]
The Place where i need to Join is after
[CODE]iCalls_Status.Status_ID,iCalls_Status.Status_Label [/CODE]
and before
[CODE]((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )[/CODE]
I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) (as i need to bind Countrec to a repeater list ) like
[CODE]select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec[/CODE] . I HAVE TO BIND THIS "Countrec" in the repeater list.The Final Query would be something like this
[CODE]select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_ID,iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID[/CODE]
but this syntax is not correct..Please can U get me the Correct Syntax.

Moving to Transact-SQL, as it seems the ideal place to post this question.

Thanks,

John

|||

You need to understand a thing or two about formatting your code. People will take time out of thier busy lives to extend help for free without anticiapting anything, but you need to follow certain etiquettes, like posting your question clearly, formatting the code so that it is easy to read and interpret. Ok, enough of that. Now lets look into the problem.

Code Snippet

--select 1

select

Count(*)

from

iCalls_Events

where Call_ID = " & Session("Call_ID") & "

--select 2

select

Count(*)

from

iCalls_Events

where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0

--select 3

select

iCalls_Calls.Call_ID

, iCalls_Calls.Requestor

, Type

, Scope

, iCalls_Calls.Status_ID

, iCalls_Status.Status_ID

, iCalls_Status.Status_Label

from

iCalls_Calls inner join iCalls_Status

on iCalls_Calls.Status_ID=iCalls_Status.Status_ID

inner join iCalls_Users

on iCalls_Calls.Requestor=iCalls_Users.User_ID)

left outer join iCalls_Messages

on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID

where Requestor='" & Session("User_ID") & "'

AND iCalls_Calls.Status_ID <> 6

order by iCalls_Calls.Call_ID

You want to get the count of select 1 / select 2 and capture the value of this in select 3 along with other columns right, if I understand your question correctly. Then this should work for you. test it out for yourself.

select

iCalls_Calls.Call_ID

, iCalls_Calls.Requestor

, Type

, Scope

, iCalls_Calls.Status_ID

, iCalls_Status.Status_ID

, iCalls_Status.Status_Label

, ( select Count(*) from iCalls_Events where Call_ID = " & Session ("Call_ID") & ") /

( select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0)

from

iCalls_Calls inner join iCalls_Status

on iCalls_Calls.Status_ID=iCalls_Status.Status_ID

inner join iCalls_Users

on iCalls_Calls.Requestor=iCalls_Users.User_ID)

left outer join iCalls_Messages

on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID

where Requestor='" & Session("User_ID") & "'

AND iCalls_Calls.Status_ID <> 6

order by iCalls_Calls.Call_ID

Also you should be able to do a small simple test before doing this a big query. Its easy when you break down the task into smaller ones.

create table test_a (col1a int, col2a int)

create table test_b (col1b int, col2b int)

create table test_c (col1c int, col2c int)

insert test_a select 1, 2

insert test_a select 1, 2

insert test_b select 2, 3

insert test_c select 3, 4

select

col1c

, col2c

, ((select count(1) from test_a) / (select count(1) from test_b)) as rec_count

from test_c

Can a view have parameters?

For years I would have said, no it can't. But in the course of studying for
my cert exam, I checked out the following syntax:
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
What is (column [ ,...n ] ) if not parameters, and how are they to be used?
I've never seen an example, and there wasn't one in the BOL article. I tried
creating a vew with a parameter, but got syntax errors.Doh! It one to many columns for the view.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:E4F3733F-720F-47C6-8C19-C99DAA45FA8A@.microsoft.com...
> For years I would have said, no it can't. But in the course of studying
> for
> my cert exam, I checked out the following syntax:
> CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
> [ WITH <view_attribute> [ ,...n ] ]
> AS select_statement [ ; ]
> [ WITH CHECK OPTION ]
> What is (column [ ,...n ] ) if not parameters, and how are they to be
> used?
> I've never seen an example, and there wasn't one in the BOL article. I
> tried
> creating a vew with a parameter, but got syntax errors.
>|||Never mind, I just figured it out. The column list is just another way to
set up names for the columns in the view.
CREATE VIEW Test (Vendor, Company)
AS SELECT VendID, VendName FROM Vendor
is the same as
CREATE VIEW Test
AS SELECT VendID AS Vendor, VendName AS Company FROM Vendor
which is information that I will probably never need in the real world.
"Bev Kaufman" wrote:
> For years I would have said, no it can't. But in the course of studying for
> my cert exam, I checked out the following syntax:
> CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
> [ WITH <view_attribute> [ ,...n ] ]
> AS select_statement [ ; ]
> [ WITH CHECK OPTION ]
> What is (column [ ,...n ] ) if not parameters, and how are they to be used?
> I've never seen an example, and there wasn't one in the BOL article. I tried
> creating a vew with a parameter, but got syntax errors.
>|||A parameterized view is called a "user defined inline table function".
Insert / update / delete over UDFs requires coding relevant triggers.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:E4F3733F-720F-47C6-8C19-C99DAA45FA8A@.microsoft.com...
> For years I would have said, no it can't. But in the course of studying
> for
> my cert exam, I checked out the following syntax:
> CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
> [ WITH <view_attribute> [ ,...n ] ]
> AS select_statement [ ; ]
> [ WITH CHECK OPTION ]
> What is (column [ ,...n ] ) if not parameters, and how are they to be
> used?
> I've never seen an example, and there wasn't one in the BOL article. I
> tried
> creating a vew with a parameter, but got syntax errors.
>

can {oj be used in query? gives syntax error

Hi all
i am using a query
SELECT DISTINCT lcactivityT.activitycategory_id, code, sort_order, description, lccategoryT.code_alias, lccategoryT.description_alias FROM
{oj ActivityCategory AS categoryT INNER JOIN LicensedClientActivities AS lcactivityT ON lcactivityT.activitycategory_id = categoryT.activitycategory_id LEFT OUTER JOIN LicensedClientCategories AS lccategoryT ON categoryT.activitycategory_id = lccategoryT.activitycategory_id AND lccategoryT.licensedclient_id = '1'}
WHERE lcactivityT.licensedclient_id = '1'

This works fine in mssql query analyser but when i use it in code, using mssql jdbc driver, i am getting following error

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error a
t token ON , line 0 offset 84.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
......

i removed the white spaces and i also have the correct driver, what can i do?

Thanku{oj ... what does it exactly mean ?|||as far as i know {oj is a way to tell JDBC driver that we are using OUTER JOIN in the following, we can remove it if we want to but, we have whole lot of queries using it, previously we were using different server and driver, and now different server and driver and so throwing me the error. Any way to get rid of that|||{oj is an ODBC escape sequence to support outer joins (either left, right or full). Perhaps this driver has more difficulty understanding what to do with it, so removing it
might help. As for the outer/inner joins: placing parenthesis around the search conditions may help as well.|||hi all

Thanx for the reply, the problem got solved i just downloaded new microsoft mssql jdbc driver, and it recognized the syntax and its working fine now