In my SQL Server 2005, we stored multiple application tables in a single database. All of them is using the dbo schema in the past.
Now, we would like to change this practice and adopt a better one. However, we got some problems in the configuration.
If I want to create such a database, should I first create multiple logins and multiple users for all the application. The mulitple logins are used in the connectionstring of my web. While each login can only have 1 user in a database, I need to create a user for each login as well. (Am I correct in doing so? It seems a bit messy.)
The other problem is what should the database role of all that users? If I don't grant them the db_owner. They can't create the required tables. If I grant it, it voliate the intension of separating different application...
I wanted to know how to set the schema for a stored procedure/function. I have some tables, T-SQL stored procedures and fuctions under schema MYSOFT and I call them as follows
MYSOFT.LOOKUP_REC
I have now written some c# SP's and Functions and want to add them to the MYSOFT Schema, how would I do this. I have tried setting the Assemly Owner value to MYSOFT in the project properties but it doesnt seem to work. I can see all my other functions & SP's having the schema name as their post fix in braces
FIND_NAMES (MYSOFT)
but not my c# ones.
When I try to access my C# Function with the schema name MYSOFT I get an error message
'Cannot find either column 'MYSOFT' or the user-defined function or aggregate 'MYSOFT.FIND_NAMES' or the name is ambiguous'
Is there a way to specify a given Schema as the currently active one for a given database session ? i.e. im looking for somthing like:
Use Schema=<name>
Execute Queries / Stored Procs, etc
.....
I want all the Queries and Stored Procs to execute on the Schema set initially.
I can't use the Default_Schema on the User to do this due to certain restrictions in the way we create users and Schemas.
I know that i can always qualify the objects in the Queries with a Schema Name, but i have a requirement where the Queries need to be generic in nature and need to run on one of many identical schemas in the Database. Is this Possible in Sql Server?
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!
Hi - can anyone recommend tutorials/guides for beginners? The Microsoft documentation seems very poor to me especially in comparison to Oracle Express. I am really struggling to find a good intro/tutorial. Many thanks db
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 am not new to the SQL Server world, but am somewhat new to the SQL Server 2005 world. I am just getting into the reporting tool. By just messing around for about an hour or so, I was able to get what I wanted somewhat. Basically, I'd like to get up to speed on the reporting side of things fairly quickly. So I am looking at some books, and was wondering if anyone had any suggestions on any.
Here is a few that caught my eye:
-MS SQL Server 2005 Reporting Services by Brian Larson -MS SQL Server 2005 Reporting Services for Dummies -Professional SQL Server 2005 Reporting Services by Paul Turley -The Rational Guide to SQL Server Reporting Services
I am thinking the 1st two would probably be my best bet, but wanted to get you folks suggestions.
Let me know as soon as you can as I am planning on going to the book store tonight or this weekend.
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data. Does anyone know how to do that? Many thanks for replying.
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master] go create database [test] go
USE [test] GO
CREATE ROLE [ReadOnlyRole] GO
USE [test] GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole] GO
Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.
whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
Hi, does somebody recognize the problem with my code? -Thanks! Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: This is an unexpected token. The expected token is 'NAME'. Line 58, position 52.Source Error: <sessionState mode="SQLServer"stateConnectionString="Jensen"sqlConnectionString= "datasource=Database;userid=username;password=pass"cookieless="false"timeout="60"/>
Always looking for people who are strong with microsoft technology for new oppurtunities in the Seattle area. If you are good, we can help you find your ideal next position.
I am looking for strong SQL developers and/or DBA's for some really sweet companies here in Seattle. Anyone interested that is good and wants to hear about new oppurtunities? I would love to help you out.
In the First DELETE and bcp I was getting the thread being launched by xp_cmdshell was being blocked by the parent thread...
put in WAITFOR...sometime it worked...started with an empty table..it worked....left the 28k rows, blocked...
Now, put SELECT COUNT(*)...works each and every g-d damn time...
HUH?
Now I get to the bcp out..
added the same code WAITFOR/SELECT *...
blocks each and ever g-d damn time....
I'm very reticent to COMMIT and start another tranny block...
Anyone have any ideas?
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[wrk_DataHold] GO
CREATE TABLE wrk_DataHold(Col1 varchar(8000)) GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[wrk_OldNew] GO
CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255)) GO
INSERT INTO wrk_OldNew(Old,New) SELECT 'SEVERAL EE~S', '' GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_ModifyRows] GO CREATE PROC usp_ModifyRows @Path sysname , @FName sysname AS
SET NOCOUNT ON
BEGIN TRAN
DECLARE @cmd varchar(8000), @Servername sysname, @rc int, @error int, @rowcount int , @Old varchar(255), @New varchar(255), @x int
CREATE TABLE #bcpLog(Col1 varchar(8000))
SET @rc = 0
DELETE FROM wrk_DataHold
SELECT @error = @@error, @rowcount = @@ROWCOUNT IF @error <> 0 BEGIN SET @rc = -1 GOTO usp_ModifyRows_Error END
SELECT @x=COUNT(*) FROM wrk_DataHold WAITFOR DELAY '000:00:10'
SET @cmd = 'bcp wrk_DataHold in ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c' INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew
OPEN OldNew
FETCH NEXT FROM OldNew INTO @Old, @New
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE wrk_DataHold SET Col1 = REPLACE(Col1,@Old,@New) WHERE Col1 LIKE '%'+@Old+'%'
SELECT @error = @@error, @rowcount = @@ROWCOUNT IF @error <> 0 BEGIN SET @rc = -1 GOTO usp_ModifyRows_Error END
INSERT INTO #bcpLog(Col1) SELECT 'REPLACE "'+ RTRIM(@Old) + '" With "' + RTRIM(@New)+ '"' UNION ALL SELECT '('+CONVERT(varchar(25),@rowcount)+' row(s) affected)'
FETCH NEXT FROM OldNew INTO @Old, @New END
CLOSE OldNew DEALLOCATE OldNew
SELECT @x=COUNT(*) FROM wrk_DataHold WAITFOR DELAY '000:00:10'
I have a SQL Server (Express) database that I use in a Visual Studio (Visual Basic) 2005 application.
The application is of the Decision Support type, that routine deletes the contents of some of the database tables and then re-populates them with new data. There is no reason for keeping the "old" contents and no reason to restore the old content.
(It does this using "BULK INSERT" statements, as in:
"BULK INSERT SharePrices FROM "C:SharePrice.txt")
Because of all the data replacing, the log file gets huge quickly. I don't need the log file at all.
(I suspect that the logging is also taking up time unncessarily.)
Is there any way I can set the database not to have a log file at all, or to have a small log file?
(I've tried deleting the log file from SQL Server Management SQL Express, but get a message that there has to be a log file.)
When I restrict the log file size, my application returns error messages that the log file is too small, and the application can't do what it needs to do.
I have already made sure that in the database that the recovery mode is set to "Simple" and that "AutoShrink" is set to true.
I've tried manual "Shrinks". These work. However, as soon as the application accesses the database, the log file gets huge again.
I realise I may have to do (unnecessary) backups before/after my bulk insert statements. If so, I'd appreciate some help on how to do this from within a Visual Basic 2005 application. (I'm actually using the Data Access block from the Enterprise Library for my data access.)
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE"; execute SQL statement "select * from table A where tableA.field B = "ABCDE"; For each record from the result of the above SQL statement, "select * from tableB where tableB.field C=tableA.fieldX" if the record can be found in tableB, "update tableB set tableB.fieldD=something"; if the record cannot be found in tableB, "insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
here is my problem. I have to rebuild database after crash. there is no backup. So I did a bcp to get data from. But I do not have the original database so my question is how to get the full schema off the data base, tables,colomns,stored procedures etc...
Hi. I would like to retrieve the table names of a database, the column names nad its contraints of each table in a database.. How can this be achieved???
We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.
Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).
Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?
We have a RS 2000 server with a over a hundred reports, and about half as many weekly and monthly subscriptions. In reply to my request to upgrade to RS2005, the boss asked me today for a compete list of the reports, who subscribes to them, and their delivery frequency.
He was not interested in paying for a VB or C# development effort with the SOAP API that it would require to obtain a simple list from a SQL server database, since he already has SQL programmers on staff.
So how can I get this list? Anyone know of any demo code out there?
I have a table called tblsample, where i have information stored row wise. Ther four quarter information is stored for many years. I want those information column wise for a given year.
say
select col1, col2 from tblsample where rqtr=1 and ryear = 2000 select col1, col2 from tblsample where rqtr=2 and ryear = 2000 select col1, col2 from tblsample where rqtr=3 and ryear = 2000 select col1, col2 from tblsample where rqtr=4 and ryear = 2000
hi! I'm still fairly wet behind the ears when it comes to databases, especially sql server 2000. But I just got a copy from my University and I really want to spend some time messing with it. The first thing I want to try is making a shopping cart that stores the items in the database rather than in the session. I'm using asp.net (VB) and I had previously made a simple shopping cart for a SMALL site that stored all the information in a dataset stored in the session... but I've been told that isn't a good idea and reccomended I store the data in the sql database. so that's what I want to do!
I'm looking for resources that can help me get this started. These are the first questions I have about this approach, and any advice from you experts would be most appreciated!
1) do I store the data in a temporary table or do I use the regular table and make temporary rows? if the former how do I transfer the rows from the temp table to the order table? if the latter how do I change it from temporary to peromanent? in either case, how do I eliminate rows if the user abandons their cart?
2) how do I first initialize an order and keep track of the orderID for each user and ensure that no one is assigned a duplicate orderID?
those are my biggest confusions about this approach. I've looked for books on this but most are either too much about sql syntax and big-time server management, or they are too general and not specific to sql server. I've tried looking online, but I can't find much information on doing this using asp.net 2.0. thanks again for your help!
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
I'm new to SSIS (and quite new to SQL Server). I have a process which I'd like to automize via SSIS - just don't know how and couldn't figure it out yet by playing around with the program. Shouldn't be too difficult though.
First of all, that's the process as I do it now:
1) Load several flatfile sources (dumps of SQL tables) into an SQL database. 2) Add identifier rows (to some tables), set the primary and foreign keys so the database is "recreated" and I can work on it. 3) Do several simple transformations, aggregations and selects across tables and finally write a new table containing information for reporting stuff.
I succeded in loading flatfiles within the data flow view, doing some transformations and saving the output to a flatfile. What I didn't find out: how can I "recreate" the database enabling me to perform "SELECT/FROM/WHERE" statements across tables? Will I have to write the imported files to tables within a db (how?) or can I avoid this step?
A little guide (newbie friendly) would be great help!
I created a report which has duplicates on one field. I want the report to display the duplicates, but cannot figure out how. Anywhere I have found where there is a "Hide Duplicates" option I have it turned off (in matrix properties), but the report matrix still hides the duplicates. I don't understand why this would happen since the other fields on the same record are not duplicates. I tried grouping but that was no help.
The query in the dataset DOES return the duplicates as expected.
