Hi All,
I require to compare two database structures.This is what I went for:
I generated scripts (right click on the database --> All Tasks--> Generate Scripts) for all tables in the two given databases and compared them.
The problem is that the order of the tables in the generated scripts are different. Can I control this order by any possible means?
Thanks in advance.
Hi all,What I am trying to do is generate a stored procedure that is desiredto output XML in this type of format<Parent Device><Device><Device ID>1</DeviceID><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister></Device></Parent Device>The area of concern is the child register, the XML being generateddisregards the Device the ChildRegister belongs to and always places itas elements of the last device.<Parent Device><Device><Device ID>1</DeviceID></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device></Parent Device>I am trying to produce XML like the first one I described and have yetto discover a way of associating the ChildRegister with the parentDevice in XML. I am not sure if it is a limitation of SQL Server, or ifmy implementation is incorrect. If anyone could post hints orsolutions, I would greatly appreciate it.A shortened version of the stored procedure is belowCheers :)AlvinSELECT1AS TAG,NULL AS PARENT,NULL AS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element]FROM udetails INNER JOINDetail ON udetails.ID = Detail.IDWHERE (uDetails.JobID = @ID)UNION ALLSELECT2 AS TAG,1 AS PARENT,TempTable.DeviceIDAS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element][color=blue]>From #Temp as TempTable INNER JOIN[/color]device ON TempTable.DeviceID = device.DeviceIDUNION ALLSELECT3 AS TAG,2 AS PARENT,NULL AS [Device!2!DeviceID!element],RegisterID AS [ChildRegister!3!RegisterID!element]FROM #Temp t INNER JOINregister ON t.DeviceID =register.DeviceIDFOR XML EXPLICIT
View 1 Replies View RelatedI want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
The problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is:
"The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. "
The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that.
Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online?
Brian
[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/
(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)
But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#
Does anyone know how to do this?
Cheers,
Matt.
Hi,
is there a way in SQL Server 7, to use some sql to extract the entire database schema?
I know that with the Enterprise manager there is All Tasks- Generate SQL Scripts.
But I need to connect to the database and use some sql to do this.
Any suggestions?
Thankyou
RH
I have installed SQL Server 2012 Express edition SP1 in my system. I have created a database in the instance. Now when I try to generate scripts of tables using Right click on database > Click on Task > Click on Generate Scripts, it is showing Error in Action 'Getting the list of objects from <database>'.
View 4 Replies View RelatedHello,
there are a tools, or a "ways" to generating "database creation scripts" from a MDF files?
Thank you for help.
Bye
Toni
I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
Using the scripting wizard in SQL Server 2005 database engine, I have been able to script all my DDL out to a flat file which is great; however, when I scripts for instance all views I would like to have the script in alphabetical order by view name, is there a value I can set to accomplish this?
Thanks
Hi!
After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65".
I checked that by executing sp_dbcmptlevel <database_name>.
I can change it to "70" but my question is how it's going to affect the application and do I have to change it?
Thank you
Lena
Database level password security
View 2 Replies View RelatedI have many tables and in those i require to change some data. Sayfrom ARCA to ARCAEX. I am sure that the string is unique in the sensethere will be no ARCAABC. So what do i do change by not manuallyneeding to search in each table and the whole database and still canbe sure that the changes have taken place. Please helpRegards,Rajesh
View 2 Replies View RelatedHi,
Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.
Kind regards,
Jan.
Hi,
How do I lock entire database? I want an exclusive lock on the db by
a user
who is the dbo of that database only (not sa
user).
The
scenario is we have a web application and each week we need to do data
uploads (with etl). During this upload, the users accessing the website
should not be able to read data. This is why I want database lock.
Now
the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So
will db locking help in this case as the website can also read the data
being a abc user?
Thanks,
Tanmaya
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
Hi,i created at general database level a login 'Network service' (windows server 2003).At level of 'mydb' database, i created an user 'aspnet' and i associated it to the login 'Network service'. That user 'aspnet' receives the necessary schemas (db_read /db_write) rrquired by the application.Now, i removed the login 'Network Service' at general database level (not the user 'aspnet') but the application still runs properly.My question is: is it allowed to remove a defined login at general database level (in my case the login 'Network Service')?ThanksJean
View 5 Replies View RelatedCan someone help me with the following problem:
I'm a SQL server DBA and we sell intern SQL-Server resources to the application teams based on needed storage and number of users.
I have several servers shared by different application teams.
Now I'm looking for a method to limit the number of users working together on a specified Database. Because when a project manager wants resources for 100 users I want to be sure that only 100 users can connect to that particular database at the same time.
I know I can limit the number of connections on server level but I need this on Db level too.
Can someone help me?
Dear Freinds,
we are having an requirement to log the unauthorized backend update on database.Is there any other way ,apart from going and creating the triggers at each table to log the backend update.
Note :Is Database base level trigger is possible on SQL Server 2000?
Looking for your reply.
With Cheers :beer: ,
Sathesh.M
Hello
We moved SQL Server 2000 Databases to SQL Server 2005 Instance. If we change the Database compatibility level from 80 to 90, shall Replication will Fail? or any consequencies will arise? please advice. Thanks,
Testing team is performing some activities on MSSQL - 2008 R2 database ..and after completion of the test, again team wants to restore the original database ( in the same state as before starting of the testing). Currently team is taking the back-up before starting testing and restoring the backup after the testing .. Since the database size is huge, this restoration is taking more time Is there any better way of taking the database to the previous state in lesser time.
View 3 Replies View RelatedThe sp_spaceused proc seems to have been removed in 7.0?
Dbcc sqlperf also seems to have been removed?
Surely there must be a counterpart - which is?
I like to have a script which shows the space usage
especially on a table basis for a periodic report.
In May of this year I graduated from Penn State with a BS in IST (Information Sciences and Technology). Right after graduation I got a database programming job with a company that uses Delphi 6 and MS SQL Server 2000.
I've been working with this company for six months now but I'm still not very good with SQL. I can do basic SQL queries and table joins (as well as use datetime functions and cursors), but I'd say I'm only at an intermediate level (at best).
So... I'm looking to learn more about SQL. I'm guessing a good SQL reference book would help, but I'd really prefer a good book that actually teaches you and guides you along. The only problem is that I don't want a basic/beginner level SQL book since I already know all of the basics.
Can anybody recommend anything for me?
Thanks!
I m using sql server 2005
i have got one request ,to apply page level locking on database
can nyone how it is done
i can do that for a single script and for session(transaction isolation level)
but dont know about database level locking scheme
thanks in advance
In May of this year I graduated from Penn State with a BS in IST(Information Sciences and Technology). Right after graduation I got adatabase programming job with a company that uses Delphi 6 and MS SQLServer 2000.I've been working with this company for six months now but I'm stillnot very good with SQL. I can do basic SQL queries and table joins (aswell as use datetime functions and cursors), but I'd say I'm only at anintermediate level (at best).So... I'm looking to learn more about SQL. I'm guessing a good SQLreference book would help, but I'd really prefer a good book thatactually teaches you and guides you along. The only problem is that Idon't want a basic/beginner level SQL book since I already know all ofthe basics.Can anybody recommend anything for me?Thanks!
View 2 Replies View RelatedWhen my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
View 3 Replies View RelatedHello,
Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well?
Thanks.
I have following table structure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FAS_LEDGER](
[TID] [INT] IDENTITY(1,1) NOT NULL,
[TDATE] [DATETIME] NOT NULL,
[code].....
in this table I have 1571182 rows
the problem is someone did some changes in this LEDGER table
I can insert new rows in this table but when I try to fetch latest rows on the basis of following query then I did not get the latest row.
means
following query gives all the rows of this table
SELECT * FROM dbo.FAS_LEDGER ORDER BY TID DESC
and when I try to filter Master_code = '02-07-01-008-0001' and apply oder by TDATE I do not get latest rows
SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'
ORDER BY TDATE DESC
HiI need to set security for row level but not based on Database user'slogin. It should be based on the user table login. For the particularuser I need to allow only the particular records to access insert,update delete and select.Let me explain clearlyFor example think we are using asp/asp.net websiteEg:www.test.comSo take this is our website and if you try this URL then you will get awindow for Login name and password.For example the Login name is windows user name (Here windows usermeans server windows user and not client) and windows password. So ifyou have login user id you can able to login in our site and we haveanother check. We have our own usertable this table consist all theuser login names and user rights. We will check the windows user whologin in our site has rights in the usertable I mean he is present inthe usertable if he is not present then we will display a message youhave no rights to access this site.If he has login id in our usertable then he allowed viewing ourpages. Still if he has the login id we will check the user who loginhas how much right to access to each page and the records of each tableits all depend on the user rights.So, here I need the row level security. For each and every table weneed to check the corresponding user and executing the record producelot of business logic problem for us.So after the user login we need automatically to set row levelsecurity for all the tables. Based on the user who login.So from there if we try select * from <tablename> then we can only ableto get the allowed records to select, insert, update, delete.Please can some one help how to solve this?Note:For some help you can refer the below URL (See in that they only givenabout the row level and column level security for each database usersnot for our required concept)http://www.microsoft.com/technet/pr...5/multisec.mspxThanks in advanceRams
View 1 Replies View RelatedWe have a reasonably large (several TB) database that was recently migrated from 2008 to a new box running 2014. Before giving it back to the users we forgot to change the compatibility level of the DB to bring it up to date with it's new environment.
We want to do some testing with backup compression so want to change the compatibility level, but we are unsure whether making the change on such a large database would cause slowness or downtime for our users.
Does the process of changing the compatibility level simply allow options that are not available in the older version or does it make structural changes to the database that would cause the users to notice slowness or downtime?
My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.
ALTER DATABASE R3P
SET PARTNER resume
the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.