==SQL Server 2005 SP2==
Is it possible to set the default schema in code?
I know that, for a particular DB user, I can set the default schema
statically in Mgmt Studio. However, I want to do this dynamically
in source code.
I am using JDBC if that matters.
Any help appreciated.
TIA
aj
I Would like to create a schema sample and make it as default schema instead of dbo.
If a user logs in and creates a table like create table t1 (no int ) .
it would be assigned to sample schema and displayed as sample.t1 not dbo.t1.
How to set the user created schema as primary schema.
We have a new development SQL2005 server.
To implement secutiry on this new server we decided to grant rights to NT-groups.
We defined an NT-group Programmersmembers and gave this group the necessary rights.
The problem now is when a member of this NT-group creates a table (this group is dbo in the development database) everything works fine as expected.
But when a member of this group tries to make a new table through 'Microsoft SQL server management studio' they receive the error that there is no default schema available.
But it seems to be impossible to map a default schema to an NT-Group.
=> Does this means we have no other way than to give each individual programmer the necessary rights and assign them to a default schema.
It is no option to force our programmers to create each table through a script on the development database.
Or is there another option ?
Hi,
Re: sql server 2005
I recently migrated a database from one server to another. In the process of doing this, I renamed one of the sql server 2000 migrated logins to a new name, using "alter login with name" and "alter user with name"
Now, I'm having problems with permissions. This user cannot execute sp_send_dbmail, even though it has explicit execute permissions on this stored proc in the msdb database.
What I noticed about this user is that is carried over a default schema from the old 2000 server. I'm beginning to think that this has something to do with the lost permissions. So I tried to drop the default schema. No dice. Next, I set the default schema to dbo. That still didn't work.
Can someone please tell me how to drop a default schema for a user? I've tried everything I can think of, from removing the schema name from the properties windows, to "alter user". Nothing has worked.
Thanks
During deployment of my database project I receive the following error:
"The default schema does not exist."
What could be causing this error?
I am confused.
I added my NT account to the sql server logins for my sql server (2005), then I added a corresponding user account to my database. I then set my default schema. I connect to the database, and the default schema seems to be set to dbo.
Can anyone thing of a reason why this might be happening? Is there some sort of override if I have additional privledges on the server?
I appreciate any thoughts...
-Mike Graham
Hi,
I have SQL Server Management Studio and SQL Server 2000.
What I want to know is:
Is there any way to prevent displaying default schema or owner, when viwing the "tables list" in the object browser?
eg. Instead of
dbo.MyTable1
dbo.MyTable2
....
I want to view as
MyTable1
MyTable2
Thanks
Srinika
We're using Windows Authentication with our SQL Server. We've added a domain group to the SQL Server groups and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enable only for user entities. We cannot add a default schema to a group.
View 89 Replies View RelatedI'm migrating a dotnetnuke website from SQL Server 2000 to SQL Server 2005 and have run into a problem with one of the stored procedures.
The database objects seem to have upgraded successfully to use the db schema identifer from the dbowner identifier. However I am having a problem with a particular stored procedure trying to execute another stored procedure.
When the following procedure is called, it seems that the db engine has forgotten the schema context and therefore can't find the called procedure. Has anyone come across this before and is there a workaround other than modifing every SP that uses EXEC?
ALTER PROCEDURE [myschema].[dnn_Forum_StatisticsGet]
(
@ModuleID int,
@UpdateWindow int = 12,
@TabId int
)
...
BEGIN
EXEC dnn_Forum_AA_StatisticsSiteUpdate 0, 0, @ModuleID, @TabId
END
...
Does anyone know how to tell sp_rename to look in a schema other than the default.
The code below reproduces the problem.
-- WORKS IN default schema
--
create table dbo.TestDF1(
dfField intconstraint DF1 default 0
)
go
sp_rename 'DF1', 'DF2', 'OBJECT'
go
Select name
From
sys.default_constraints
where
object_name(parent_object_id) = 'TestDF1'
go
drop table dbo.TestDF1
go
-- DOESN'T WORK IN added schema
--
create schema TestSchema
go
create table TestSchema.TestDF2(
dfField intconstraint DF3 default 0
)
go
sp_rename 'DF3', 'DF4', 'OBJECT'
go
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 315
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
---------------------------
drop table TestSchema.TestDF2
go
drop schema TestSchema
go
Hi
For MSSQL 2005 server, when we create new user using stored procedure sp_grantdbaccess, it creates schema with name given to user.
While when we create new user with query " Create login identfied by'password'. It attach user to default schema at the server.
My question is can we have a default schema assigned to the user, while user is created with sp_grantdbaccess?
Is there any way to do so?
Please reply as early as possible.
I have a SQL Server project in Visual Studio 2005 which deploys an assembly to SQL Server 2005 containing various stored procedures user defined functions. Is there any way to tell Visual Studio to drop/create the stored procedures in a schema other than dbo?
ie: User.ChangePassword instead of dbo.ChangePassword.
Hi.
I am trying to get the schema in which the trigger is created within the CLR code.
1)create a new schema MySchema.
2) created a table MySchema.MyTable
3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.
Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.
So how do dynamically get the trigger's schema name ?
Thanks for your help.
Nach
i have database and set default table schema to "ray" and me must input ruy.TABLE-NAME for retrive data !!! , i need set Default Schema to current user for call database as just database name(for my program) , how changed it ? (i change default schema for current user by alter command but not worked !)
View 1 Replies View RelatedDear All,
I am using SQLServer 2005, I have setup a login user "User1" and next I setup database user using the same username and login name. After that I create a new schema "mySchema" and make "User1" as the owner of the schema. To "User1" I assigned the default schema to "mySchema", so far its working fine. But when I open the user's property window (dialogbox) the default schema always gets reset to "dbo".
What could be the problem here? Please help me if there is any solution to get the right schema which I assigned to the user.
Thanks and regards,
Hi Smart Folks,
My SQL Server-based app will require generating a few tables at run-time, and, possibly, a new small database. Again, this is at run-time. Any advice on this? Should I build the functionality from the app, having my code generate all of the commands on the db to create what I need, or should I rely on SQL Server functionality (Scripts? SPs?) to do so. I am sure there is trade-off in one appraoch versus the other - I could use a tour of the trade-offs.
Thanks all!
DF
I've been reading some forums and I'm not able to get this to work. Basically I'm using an Oracle DB source and trying to import data into SQL server 2005. I guess the best connections to use are OLE DB.
Here are my current connections:
Source: Native OLE DBOracle Provide for OLE DB
Destination: Native OLE DBSQL Native Client
I'm running SP2 of SQL 2005.
Now, the issue is I'm not getting the code page stuff correct. In the data flow I'm just doing a OLE DB Source to SQl Server Destination. I'm not doing any transformations (I'm hoping to avoid doing that).
I'm getting an error:
[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
I'm looking at how to set the DefaultCodePage property in various forums, but nowhere does it say exactly how to do this. I've tried changing the "Extended Properties" and added AlwaysUseDefaultCodePage=TRUE in there, but that doesn't work. I've tried changing the Locale Identifier to 1252 that doesn't work. I've tried a combination of the two, doesn't work. Can somebody tell me two things:
1. what's the best/fastest/industry used method to get data from Oracle into SQL server via SSIS (please include the type of connections).
2. How the hell do you set the DefaultCodePage property. I would love a screenshot on this one as well.
Thanks,
Phil
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedHello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies View RelatedI am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
@pvColumnName VARCHAR(100) = Default,
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
Any words of wisdom gratefully recieved,
Howard Stiles
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
View 2 Replies View RelatedHi:
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8462</Code>
<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>
</Error>
Why am i gettting this error after the conversation.
Thanks,
Pramod
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
SQL 7.0 SP3 replicating a database from a publisher to a SQL 7.0 SP 3 subscriber.
Is there a way to change publisher schema without stopping the replication? Example Add a 'bit' column to a table with default of '0'.