I'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]
Hi NG,I've got an ASP Script (useing Javascript) to which i send data to by POST.This data stored in variables will be transferred to SQL stored Procedure.As shown at bottom...welches eine Variable übergebenThere is coming up an error...I don't know how to solve it,please help or give me a hint...[asp code line 665 and + from Functions.asp]var TopicName=String(Request.Form("TopicName"));var TopicContent=String(Request.Form ("TopicContent"));var strSQL="EXECUTE Topic @TopicTitle=["+TopicName+"],@TopicContent=["+TopicContent+"]";cmd.CommandText = strSQL;cmd.Execute;oDB_connect.close;[/asp code][error page]Fehlertyp:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit'0123456789012345678901234567890123456789012345678 9012345678901234567890123456789012345678901234567890123456789012345678901234567' ) ist zu lang. DieMaximallänge beträgt 128./Functions.asp, line 669[/error page][snip from Stored Procedure]CREATE PROCEDURE dbo.Topic @TopicTitle VARCHAR(20), @TopicContent VARCHAR(200)AS INSERT INTO Table.........etc[/snip from Stored Procedure]
==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 schemastatically in Mgmt Studio. However, I want to do this dynamicallyin source code.I am using JDBC if that matters.Any help appreciated.TIAaj
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.
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.
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?
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.
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
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 !)
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.
I am trying to build a stored procedure reference two diffrent tables with same name but diffrent schemas based on the login_user or who calls the procedure. I have developed the following procedure but still ot able to get it..The table test_table exists two times within the system
User1.test_Table
User2.TestTable
if i am user1 calling the procedure should reefernce user1.test_table and vice versa for user 2.
alter procedure dbo.test_proc (@pUsername varchar(150)) with Execute as caller as Begin Select System_User Select * from Test_Table End
hi, i'm trying to debug a stored procedure (in sql server 2005) with visual studio 2005, but all my tables and procedures are inside a schema and when i configure my login in sql server for being in sysadmin role, it dosn't map the schema so i need to write the schema before the tables and procedures, and i don't need it in that way... how can i do to have a login in sysadmin role and a schema maping in than login???,
I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.
I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.
I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.
My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?
Hi, I wanted to use a default for yearly instead of monthly for stored procedure, but couldn't figure out how. I usually used stored procedure @start and @end for date. i.e. this is monthly by default @start = jul 2007 and @end= aug 2007; i.e. i want yearly by default @start = jul 2007 and @end= jul 2008.
Is it possible to use a stored procedure to fill the default value of a column when i'm building the db? i mean if i can use a stored procedure for the "colum property": "default value or bnding" if yes how can i do it?
Hi,This works:CREATE PROCEDURE MyProc @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this?
Is there a way inside a stored procedure to determine if a parameter defined with a default value was explicity passed the same value as the default vs. the parameter was omitted and you just got the default value.
E.g. create procedure mysp_stuff @parm1 varchar(20)=NULL, @parm1 bit=0 as if (@parm1 is null) begin -- did we get the default or did the caller pass NULL end if (@parm2 = 0 begin -- did we get the default or did the caller pass 0 end GO
EXEC mysp_stuff go EXEC mysp_stuff @parm1=NULL, @parm2=0 go
Hello, does anyone know how to determine (within the stored procedure)whether a parameter has been passed explicitly to a stored procedure asopposed to a parameter that has taken on the default value.Many thanksPhil
Can someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname
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!
When I call this function and the database field 'Login' is null, then I get an error message "Conversion from type 'DBNull' to type 'String' is not valid." I've supplied a default value in my stored procedure and I've also provided a default value in the result value in the function. How do I get around this? Thanks lblLastUserLogin.Text = GetLastUserLogin().ToString() Private Function GetLastUserLogin() As String Dim result As String = "" Dim con As New SqlConnection("server=x.x.x.x;database=database;uid=x;password=x") Dim cmd As New SqlCommand("GetLastUserLogin", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = lblUserID.Text cmd.Parameters.Add("@ReturnVal", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output Using con con.Open() cmd.ExecuteNonQuery() result = CType(cmd.Parameters("@ReturnVal").Value, String) End Using Return resultEnd Function CREATE PROCEDURE [dbo].[GetLastUserLogin] -- OUTPUT parameter to hold the count. @UserID int, @ReturnVal smalldatetime = ' ' OUTPUT AS -- This will return the last date returned by the SELECT query. Set @ReturnVal = (SELECT MAX(Login) FROM TUserLogs WHERE UserID=@UserID)
I'm generating a list of parameters needed by stored procedures, and I'd like to know which ones have default values assigned to them. To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value, 'COLUMN_DEF' always returns as NULL, even when that column has a default value assigned to it. i.e. CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Hi everyone, I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records? Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode) My stored procedure: CREATE PROCEDURE sp_accepting_practice (@searchpostcode as nvarchar(100)) AS SELECT dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId EXECUTE sp_accepting_practice G4GO I greatly appreciate your help. Thanks in Advance Regards Shini
If a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:IF @Parameter IS NULL BEGINor IF @Parameter = '' BEGINThe only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:IF @Parameter = '99' BEGIN<Code Here>END
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?
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.
