Is there anyway to drag the fields on to a report and have it create labels
for those fields without having to create each of them individually and
without using the table or matrix?
DavidYou mean in a list control? No, this is not supported. With the nature of
lists, it would be pretty tough to tell where you wanted the labels anyway.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"CapitalEMR" <CapHS@.hvif.com> wrote in message
news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Is there anyway to drag the fields on to a report and have it create
> labels
> for those fields without having to create each of them individually and
> without using the table or matrix?
> David
>|||No - not on a list control. Just on the report page.
Drag and drop field and label - just like VB6 used to do. I would love to
worry about where I wanted the labels instead of creating them by hand and
then placing them.
"Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
news:umOy5HjVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> You mean in a list control? No, this is not supported. With the nature of
> lists, it would be pretty tough to tell where you wanted the labels
> anyway.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "CapitalEMR" <CapHS@.hvif.com> wrote in message
> news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
>> Is there anyway to drag the fields on to a report and have it create
>> labels
>> for those fields without having to create each of them individually and
>> without using the table or matrix?
>> David
>>
>
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Thursday, March 29, 2012
dropping functions
Bonjour,
I'm as green as can be...and I created a function but could not drop it when
things went south....
create or replace function available(client_id, property_id)
return number
is
ret number := 0;
begin
begin
select nvl2(c.client_id,1,0)
into ret
from client c, property p
where p.property_id = property
and p.asking_price <= c.max_rent(+)
and c.client_id (+) = client;
exception
when no_data_found then
ret := 0;
end;
return ret;
end;
/
:eek:Did you try:
DROP FUNCTION AVAILABLE;
:rolleyes:
I'm as green as can be...and I created a function but could not drop it when
things went south....
create or replace function available(client_id, property_id)
return number
is
ret number := 0;
begin
begin
select nvl2(c.client_id,1,0)
into ret
from client c, property p
where p.property_id = property
and p.asking_price <= c.max_rent(+)
and c.client_id (+) = client;
exception
when no_data_found then
ret := 0;
end;
return ret;
end;
/
:eek:Did you try:
DROP FUNCTION AVAILABLE;
:rolleyes:
DSN connection w/o password
hi all
i want to connect to SQL Server using DSN.
I create a System DSN, say 'DSN_PWD', provide SQL server authentication in that step and test the connection which works fine.
Now, currently i am using a code by my seniors which is as below
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN_PWD","username","password"
objConn.Close
Set objConn =Nothing
what i am concerned is that this will be saved as ASP file, i don't want any login informatoin to be saved in a physical file openly.
i have tried this but it gives error
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DSN=DSN_PWD;"
objConn.Open
objConn.Close
Set objConn =Nothing
Error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
is there any effective way?
Thanks all
Yogesh JangamYou can hardcode the username and the password within the DSN itself using the ODBC Connection Manager. Is that what you want?
-PatP|||my primary concern being security, i don't want any SQL login information to be saved in any ASP files.
I am creating DSN with SQL Authentication with Login ID/PWD entered by user
i want a method to use this DSN to create an ADODB.Connection w/o manually again typing username and password in objConn.open method
i hope i am clear ;-).......
i have looked for connection code, but most of them have login info in the Open method.
Thanks
Yogesh Jangam|||The easiest way to do this is if your IIS service runs as an NT account. Then just use Windows Authentication for your ODBC connection and you are "good to go" without any fuss at all.
If you must run your IIS Service as LocalSystem, then you need to butcher a DSN to force static SQL authentication. That would be a last resort.
-PatPsql
i want to connect to SQL Server using DSN.
I create a System DSN, say 'DSN_PWD', provide SQL server authentication in that step and test the connection which works fine.
Now, currently i am using a code by my seniors which is as below
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN_PWD","username","password"
objConn.Close
Set objConn =Nothing
what i am concerned is that this will be saved as ASP file, i don't want any login informatoin to be saved in a physical file openly.
i have tried this but it gives error
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DSN=DSN_PWD;"
objConn.Open
objConn.Close
Set objConn =Nothing
Error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
is there any effective way?
Thanks all
Yogesh JangamYou can hardcode the username and the password within the DSN itself using the ODBC Connection Manager. Is that what you want?
-PatP|||my primary concern being security, i don't want any SQL login information to be saved in any ASP files.
I am creating DSN with SQL Authentication with Login ID/PWD entered by user
i want a method to use this DSN to create an ADODB.Connection w/o manually again typing username and password in objConn.open method
i hope i am clear ;-).......
i have looked for connection code, but most of them have login info in the Open method.
Thanks
Yogesh Jangam|||The easiest way to do this is if your IIS service runs as an NT account. Then just use Windows Authentication for your ODBC connection and you are "good to go" without any fuss at all.
If you must run your IIS Service as LocalSystem, then you need to butcher a DSN to force static SQL authentication. That would be a last resort.
-PatPsql
DSN Connection to SQL Server
I am using the code below to create a DSN connection if it does not exist. Is there a way to set the DSN to use NT Authentication?
lngResult = SQLConfigDataSource(0, _
ODBC_ADD_SYS_DSN, _
"SQL Server", _
"DSN=" & JDS_DSN_name & Chr(0) & _
"Server=" & JDS_Server_name & Chr(0) & _
"Database=RMAData" & Chr(0) & _
"UseProcForPrepare=Yes" & Chr(0) & _
"Description=RMA Database" & Chr(0) & Chr(0))Figured it out|||Try specifying "Trusted_Connection=No" attribute along with the other attributes specified in SQLConfigDataSource().
lngResult = SQLConfigDataSource(0, _
ODBC_ADD_SYS_DSN, _
"SQL Server", _
"DSN=" & JDS_DSN_name & Chr(0) & _
"Server=" & JDS_Server_name & Chr(0) & _
"Database=RMAData" & Chr(0) & _
"UseProcForPrepare=Yes" & Chr(0) & _
"Description=RMA Database" & Chr(0) & Chr(0))Figured it out|||Try specifying "Trusted_Connection=No" attribute along with the other attributes specified in SQLConfigDataSource().
Tuesday, March 27, 2012
Dropping a uniwue clustered index HELP
-- CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
drop index TRt_lu_Trans_Subtype.IX_TRt_lu_Trans_Subtype
is taking forever, Why?
This is really urgentDropping the index reorganizes all data in table. If you have lots of data,
expect a wait (this is normal)
Further, the Table is Exclusively locked during this time frame so not to be
done during production hours.
Greg Jackson
PDX, Oregon|||Hi,
Please do this operation while there is no access to the table
TRt_lu_Trans_Subtype. If you try to drop this index while some one is
accessing
the table will cause Locks. You can view the blovks using sp_who command.
See the the Blocked column in the output.
Thanks
Hari
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6057E1B7-89F4-4CEC-825D-04DB32A268A5@.microsoft.com...
> -- CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> drop index TRt_lu_Trans_Subtype.IX_TRt_lu_Trans_Subtype
> is taking forever, Why?
> This is really urgent
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
drop index TRt_lu_Trans_Subtype.IX_TRt_lu_Trans_Subtype
is taking forever, Why?
This is really urgentDropping the index reorganizes all data in table. If you have lots of data,
expect a wait (this is normal)
Further, the Table is Exclusively locked during this time frame so not to be
done during production hours.
Greg Jackson
PDX, Oregon|||Hi,
Please do this operation while there is no access to the table
TRt_lu_Trans_Subtype. If you try to drop this index while some one is
accessing
the table will cause Locks. You can view the blovks using sp_who command.
See the the Blocked column in the output.
Thanks
Hari
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6057E1B7-89F4-4CEC-825D-04DB32A268A5@.microsoft.com...
> -- CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> drop index TRt_lu_Trans_Subtype.IX_TRt_lu_Trans_Subtype
> is taking forever, Why?
> This is really urgent
Thursday, March 22, 2012
Dropdown parameter in Report Builder
Hi, Is it possible to create a dropdown parameter in Report Builder?
The result I want to get is (for example):
The parameter is "Project". Next to the parameter should be a dropdown box from where I can select a project (for example: project1, project2, ...). Once the parameter is selected, and I click "view report", the report should only display data from the selected project.
The only other options available in the Filter dialog screen are:
"Field" is in list
"Field" contains
"Field" equals
But none of these methods of creating a parameter generate a dropdownbox from where items can be selected, equal to those in the database.
So ... is it even possible to create a dropdown parameter with values out of the db? Or are there any workarounds?
You could create a second dataset for the paramaters. Then in that data set something like this to call the data from the db:
Select project_name
from project_name_table
Group by project_name
Then set your paramater equal to the new dataset.
|||rs12345 wrote:
You could create a second dataset for the paramaters. Then in that data set something like this to call the data from the db:
Select project_name
from project_name_table
Group by project_nameThen set your paramater equal to the new dataset.
You are talking about reports generated through a Report Server Project. But I created a model (from a Report Model Project) and then I want to generate a report on that model through the Report Builder! There it is not possible to create datasets.
Aren't there any MS people on here who can confirm if it is possible or not? (Possible to work with a parameter displayed as a dropdownbox containing values from the database)
|||To create a filter condition based on a report parameter, open the Filter dialog, add a filter condition based on the appropriate field, then click on the field name (not the operator) and choose "Prompt" from the menu that appears.
Note that you will only get a dropdown when running the report if you see a dropdown in the Filter dialog as well. The presence of the dropdown in both cases is determined by the Entity.InstanceSelection or Attribute.ValueSelection property in the report model (whichever applies).
Hope that helps!
|||Thanks!Changing those properties did the trick!
drop. temp. table proc.
CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
AS
DECLARE @.STATEMENT VARCHAR(8000)
SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
BEGIN
EXEC(@.STATEMENT)
END
SELECT *
INTO #AA
FROM a_table
DT '#AA'
SELECT * FROM #AA--the table #AA is still existing.
How can I change the procedure to enable dropping.I think that this line is where your proc is going wrong:
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
The object name in tempdbs sysobjects table will be
#AA_________________somethinghere
Therefore, you'd have to change your = to LIKE, something like this:
@.TEMP_TABLE_NAME + '___%'
To check for an object's existence, I always try to retrieve the object's ID
using OBJECT_ID('objectname') function. If a non-null value is returned,
delete the object.
IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
I wouldn't normally recommend dynamic SQL due to the risk of a SQL injection
attack, but if it's only for your own use?
Dan.
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
> AS
> DECLARE @.STATEMENT VARCHAR(8000)
> SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME=
@.TEMP_TABLE_NAME)
> BEGIN
> EXEC(@.STATEMENT)
> END
> SELECT *
> INTO #AA
> FROM a_table
> DT '#AA'
> SELECT * FROM #AA--the table #AA is still existing.
> How can I change the procedure to enable dropping.
>|||On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>The object name in tempdbs sysobjects table will be
>#AA_________________somethinghere
>Therefore, you'd have to change your = to LIKE, something like this:
>@.TEMP_TABLE_NAME + '___%'
Hi Daniel,
I think that you meant to write
LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
or
LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
The _ character in a LIKE pattern will match any single character.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you.
"Daniel Doyle" wrote:
> I think that this line is where your proc is going wrong:
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME
)
> The object name in tempdbs sysobjects table will be
> #AA_________________somethinghere
> Therefore, you'd have to change your = to LIKE, something like this:
> @.TEMP_TABLE_NAME + '___%'
> To check for an object's existence, I always try to retrieve the object's
ID
> using OBJECT_ID('objectname') function. If a non-null value is returned,
> delete the object.
> IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
> I wouldn't normally recommend dynamic SQL due to the risk of a SQL injecti
on
> attack, but if it's only for your own use?
> Dan.
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> @.TEMP_TABLE_NAME)
>
>|||Yes, of course you are correct Hugo, it slippled my mind that _ is a
wildcard character.
Thanks. Dan.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vbt1g1tbdoovaq2tt15vahkm5d0qhevn4n@.
4ax.com...
> On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>
> Hi Daniel,
> I think that you meant to write
> LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
> or
> LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
> The _ character in a LIKE pattern will match any single character.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
AS
DECLARE @.STATEMENT VARCHAR(8000)
SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
BEGIN
EXEC(@.STATEMENT)
END
SELECT *
INTO #AA
FROM a_table
DT '#AA'
SELECT * FROM #AA--the table #AA is still existing.
How can I change the procedure to enable dropping.I think that this line is where your proc is going wrong:
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
The object name in tempdbs sysobjects table will be
#AA_________________somethinghere
Therefore, you'd have to change your = to LIKE, something like this:
@.TEMP_TABLE_NAME + '___%'
To check for an object's existence, I always try to retrieve the object's ID
using OBJECT_ID('objectname') function. If a non-null value is returned,
delete the object.
IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
I wouldn't normally recommend dynamic SQL due to the risk of a SQL injection
attack, but if it's only for your own use?
Dan.
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
> AS
> DECLARE @.STATEMENT VARCHAR(8000)
> SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME=
@.TEMP_TABLE_NAME)
> BEGIN
> EXEC(@.STATEMENT)
> END
> SELECT *
> INTO #AA
> FROM a_table
> DT '#AA'
> SELECT * FROM #AA--the table #AA is still existing.
> How can I change the procedure to enable dropping.
>|||On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>The object name in tempdbs sysobjects table will be
>#AA_________________somethinghere
>Therefore, you'd have to change your = to LIKE, something like this:
>@.TEMP_TABLE_NAME + '___%'
Hi Daniel,
I think that you meant to write
LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
or
LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
The _ character in a LIKE pattern will match any single character.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you.
"Daniel Doyle" wrote:
> I think that this line is where your proc is going wrong:
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME
)
> The object name in tempdbs sysobjects table will be
> #AA_________________somethinghere
> Therefore, you'd have to change your = to LIKE, something like this:
> @.TEMP_TABLE_NAME + '___%'
> To check for an object's existence, I always try to retrieve the object's
ID
> using OBJECT_ID('objectname') function. If a non-null value is returned,
> delete the object.
> IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
> I wouldn't normally recommend dynamic SQL due to the risk of a SQL injecti
on
> attack, but if it's only for your own use?
> Dan.
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> @.TEMP_TABLE_NAME)
>
>|||Yes, of course you are correct Hugo, it slippled my mind that _ is a
wildcard character.
Thanks. Dan.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vbt1g1tbdoovaq2tt15vahkm5d0qhevn4n@.
4ax.com...
> On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>
> Hi Daniel,
> I think that you meant to write
> LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
> or
> LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
> The _ character in a LIKE pattern will match any single character.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
drop user
Hi,
I have a user in my SQL server 2005 database sys.sysusers table with following values.
I am unable to delete this user and unable to create a user with this same user name.
Please tell some one what is status=16 and issqluser=0
status 16
ame \CMSXXCMSTESTER
roles NULL
altuid 5
hasdbaccess 0
islogin 1
isntname 0
isntgroup 0
isntuser 0
issqluser 0
isaliased 1
issqlrole 0
isapprole 0
When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.
And i also tried sp_change_users_login 'report' but I can't see the user in question.
Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.
I want to drop this user some how..
Thanks
Hello,
It seems that the account is aliased. execute sp_dropalias.
Hope that helps.
Cheers
Rob
|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.|||
Hi
COuld you please post the results of the sp_helpuser command.
regards
Jag
drop user
Hi,
I have a user in my SQL server 2005 database sys.sysusers table with following values.
I am unable to delete this user and unable to create a user with this same user name.
Please tell some one what is status=16 and issqluser=0
status 16
ame \CMSXXCMSTESTER
roles NULL
altuid 5
hasdbaccess 0
islogin 1
isntname 0
isntgroup 0
isntuser 0
issqluser 0
isaliased 1
issqlrole 0
isapprole 0
When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.
And i also tried sp_change_users_login 'report' but I can't see the user in question.
Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.
I want to drop this user some how..
Thanks
Hello,
It seems that the account is aliased. execute sp_dropalias.
Hope that helps.
Cheers
Rob
|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.|||
Hi
COuld you please post the results of the sp_helpuser command.
regards
Jag
drop user
Hi,
I have a user in my SQL server 2005 database sys.sysusers table with following values.
I am unable to delete this user and unable to create a user with this same user name.
Please tell some one what is status=16 and issqluser=0
status 16
ame \CMSXXCMSTESTER
roles NULL
altuid 5
hasdbaccess 0
islogin 1
isntname 0
isntgroup 0
isntuser 0
issqluser 0
isaliased 1
issqlrole 0
isapprole 0
When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.
And i also tried sp_change_users_login 'report' but I can't see the user in question.
Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.
I want to drop this user some how..
Thanks
Hello,
It seems that the account is aliased. execute sp_dropalias.
Hope that helps.
Cheers
Rob
|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.|||Hi
COuld you please post the results of the sp_helpuser command.
regards
Jag
sqlWednesday, March 21, 2012
Drop The Create Table
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Drop The Create Table
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
Drop The Create Table
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Drop temporary tables whilst connected ?
Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.
Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)
However, what I was wondering is :
Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.
Example
SQL Statement with several parts, each uses a series of temporary
tables to create a result set. At the end of a section, these work
tables are no longer needed, so drop table commands are used. The
final result set brings back the combined results from each section
and then drops those at the end.
TIA
RyanRyan (ryanofford@.hotmail.com) writes:
> Just a quicky about temporarary tables. If using QA, when you create a
> temporary table, it gets dropped if you close the query. Otherwise you
> need to state 'DROP TABLE myTable' so that you can re-run the query
> without the table being there.
> Sometimes, you can have quite lengthy SQL statements (in a series)
> with various drop table sections throughout the query. Ideally you
> would put these all at the end, but sometimes you will need to drop
> some part way through (for ease of reading and max temp tables etc...)
> However, what I was wondering is :
> Is there any way to quickly drop the temporary tables for the current
> connection without specifying all of the tables individually ? When
> testing/checking, you have to work your way through and run each drop
> table section individually. This can be time consuming, so being
> naturally lazy, is there a quick way of doing this ? When working
> through the SQL, it's possible to do this quite a lot.
No, there is no "DROP TABLE #%".
You could write a cursor over tempdb..sysobjects which finds the tables,
but then you would have to mask out the part which is tacked on to the
table name. Kind of messy.
On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.
If using a stored procedure is problematic for some reason, a RAISERROR
with level 21 is a brutal way if getting rid of the temp tables - in
fact, this kills your connection. Only do this, if you are your own DBA,
because it may ping an alert for an operator on a big server.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> On the other hand, why not pack everything in a stored procedure? A temp
> created in a scope is dropped when that scope exits. Thus, with a stored
> procedure, this is a non-problem.
Most of this type of query will be put into a stored procedure once
finished, but we most often need to work through it in stages to check
that we have the maths correct at each stage before we progress this
further into an SP. We do a lot of manipulating financials so need to
check our maths throughout. We have a lot of reports based on our
figures and each needs to use the same logic but slightly different
groups of answers which needs checking.
In a lot of cases the SQL can be over a thousand lines long, so we
tend to break it down as much as possible in order to keep it simple.
Hence grouping the drop table statements so we can work with it.
Thanks
Ryan
Drop table's primary key with knowing the constraint's name
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name =
(SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name =
(SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Drop table's primary key with knowing the constraint's name
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name = (SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name = (SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql
drop table not supported
I create an Execute SQL Task and add a connection and the sql
DROP TABLE Table1
Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
construt or statement is not supported."
Baffling ...John,
I think you are asking about SSIS related queries, and this group is for
Reporting Services.
Amarnath, MCTS
"John Grandy" wrote:
> I create an Execute SQL Task and add a connection and the sql
> DROP TABLE Table1
> Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
> construt or statement is not supported."
> Baffling ...
>
>
DROP TABLE Table1
Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
construt or statement is not supported."
Baffling ...John,
I think you are asking about SSIS related queries, and this group is for
Reporting Services.
Amarnath, MCTS
"John Grandy" wrote:
> I create an Execute SQL Task and add a connection and the sql
> DROP TABLE Table1
> Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
> construt or statement is not supported."
> Baffling ...
>
>
Monday, March 19, 2012
Drop Stored Procedure causing Dropped Tables
Hey guys, has anyone ever seen this happen:
Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.
Three tables referenced by that stored proc have been dropped and re-created with the same table structure.
I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?You will have to check the options you picked for the Transfer task in your DTS package. Did you ask it to move dependent objects also? For more help on the DTS package/tasks, please post in the SQL Server Integration Services forum.|||It was selected for dependent objects, but those tables are not dependent on the stored proc. As far as I know, the DTS method I used was the same as using DROP PROCEDURE, since it failed after the drop.|||I don't know how the DTS task determines dependencies. If it uses say sp_depends SP then you can check by running the SP for your SP to see the dependencies. Note, that this SP only gives immediate dependencies. If this doesn't help find how DTS determines dependencies then ask in the SSIS forum or run your package and trace the calls to SQL Server.
Sunday, March 11, 2012
Drop large transaction log file and create new
Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.
It's very urgent because we need to clean up space on NT server tonight.
Thanks,
AttilaOriginally posted by horvata
Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.
It's very urgent because we need to clean up space on NT server tonight.
Thanks,
Attila
See: http://dbforums.com/t546372.html|||Thanks a lot for your help.|||Originally posted by DBA
See: http://dbforums.com/t546372.html
Whou much time you spend to make you database full backup ??
Wich type of backup do you do ?
If you log is no longer used (because you data is static) you can use the command bellow
sp_detach_db <dbname>
GO
CREATE DATABASE <dbname>
ON PRIMARY (FILENAME = '<path>.dbname.extension')
FOR ATTACH
GO
Ps: Make 2 full backups of you database before do this. On filename choose the path of you datafile <only>, forget you logfile.
Jorge|||Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.
Thanks,
Attila|||Originally posted by horvata
Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.
Thanks,
Attila
Hi Attila, I discover another way to do this.
Ps:Allways execute a full backup before.
First execute
EXEC sp_detach_db 'database_name', 'true'
Rename your physical log file on Operation system
After this execute the following command.
EXEC sp_attach_single_file_db @.dbname = 'database_name',
@.physname = 'path\database_name.extension'
This command works. I haver already done this.
Jorge Demattos
Bank of America.
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.
It's very urgent because we need to clean up space on NT server tonight.
Thanks,
AttilaOriginally posted by horvata
Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.
It's very urgent because we need to clean up space on NT server tonight.
Thanks,
Attila
See: http://dbforums.com/t546372.html|||Thanks a lot for your help.|||Originally posted by DBA
See: http://dbforums.com/t546372.html
Whou much time you spend to make you database full backup ??
Wich type of backup do you do ?
If you log is no longer used (because you data is static) you can use the command bellow
sp_detach_db <dbname>
GO
CREATE DATABASE <dbname>
ON PRIMARY (FILENAME = '<path>.dbname.extension')
FOR ATTACH
GO
Ps: Make 2 full backups of you database before do this. On filename choose the path of you datafile <only>, forget you logfile.
Jorge|||Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.
Thanks,
Attila|||Originally posted by horvata
Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.
Thanks,
Attila
Hi Attila, I discover another way to do this.
Ps:Allways execute a full backup before.
First execute
EXEC sp_detach_db 'database_name', 'true'
Rename your physical log file on Operation system
After this execute the following command.
EXEC sp_attach_single_file_db @.dbname = 'database_name',
@.physname = 'path\database_name.extension'
This command works. I haver already done this.
Jorge Demattos
Bank of America.
DROP IDENTITY from tables
Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
John
John,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
John
"David Gugick" wrote:
> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
John
John,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
John
"David Gugick" wrote:
> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Subscribe to:
Posts (Atom)