When a DBA creates Windows Logins in SQL Server, the format of these logins is:
[Domain or Server Name]\[Windows Username]
[Domain] can also be the local Server name if the user is a local Windows User.
In the case where the Server name changes and the Windows Login names remain the same in SQL Server then the login will not be usable. To this end, these login names have to be renamed.
The syntax for renaming Windows Logins in SQL Server is:
ALTER LOGIN "[Domain or Server Name]\[Windows Username]"
WITH NAME="[New Domain or New Server Name]\[Windows Username]"
*Note: You have to use the double quotes.
If you want to rename a SQL Server login (SQL Server Authentication) you can use one of the following syntaxes:
Syntax 1
----------
ALTER LOGIN "[SQL Server Login Name]"
WITH NAME="[New SQL Server Login Name]"
Syntax 2
---------
ALTER LOGIN [SQL Server Login Name]
WITH NAME=[New SQL Server Login Name]
As you may noticed from the above two syntaxes, you can either select to use or not double quotes.
Read more on this article...
Friday, May 29, 2009
Wednesday, May 27, 2009
Screencast: How to Create an Entity Data Model
Welcome to my first screencast dedicate to SQL Server 2008 Programmability!
This screencast features the ADO .NET Entity Framework; a new feature of Microsoft .NET Framework 3.5 providing the developer with fast, robust and scalable data access. SQL Server 2008 fully supports the ADO .NET Entity Framework, allowing developers to effortlessly create entity data models in Visual Studio 2008 applications.
This screencast demonstrates how the developer can create an entity data model in Visual Studio 2008 and mapping it to a SQL Server 2008 Database:
Read more on this article...
This screencast features the ADO .NET Entity Framework; a new feature of Microsoft .NET Framework 3.5 providing the developer with fast, robust and scalable data access. SQL Server 2008 fully supports the ADO .NET Entity Framework, allowing developers to effortlessly create entity data models in Visual Studio 2008 applications.
This screencast demonstrates how the developer can create an entity data model in Visual Studio 2008 and mapping it to a SQL Server 2008 Database:
Read more on this article...
Screencast Series Announcement
We all know the saying: “A picture is worth a thousand words”.
Well, what about a video? A screencast maybe? :)
Today I am initiating a series of screencasts dedicated to SQL Server Development and Administration - mainly for SQL Server 2008.
Blogging is great of course and I will sure continue doing it as there are so many great SQL Server features out there for discussing about :)
Though, a screencast comes to fill possible knowledge gaps as it demonstrates the actual way (or at least, one of the many ways) of doing things, i.e. SQL Server Development and Administration.
So, in parallel to my blog posts, I will also be publishing screencasts which I make in order to demonstrate detailed steps for administering and developing SQL Server.
Make sure you check back my blog soon!
Read more on this article...
Well, what about a video? A screencast maybe? :)
Today I am initiating a series of screencasts dedicated to SQL Server Development and Administration - mainly for SQL Server 2008.
Blogging is great of course and I will sure continue doing it as there are so many great SQL Server features out there for discussing about :)
Though, a screencast comes to fill possible knowledge gaps as it demonstrates the actual way (or at least, one of the many ways) of doing things, i.e. SQL Server Development and Administration.
So, in parallel to my blog posts, I will also be publishing screencasts which I make in order to demonstrate detailed steps for administering and developing SQL Server.
Make sure you check back my blog soon!
Read more on this article...
Sunday, May 24, 2009
Installing 32-bit SQL Server 2005 Reporting Services on a 64-bit machine/Windows OS
Is it possible to install 32-bit SQL Server 2005 Reporting Services on a 64-bit Computer/Windows OS while keeping the 64-bit version of the rest of SQL Server features?
The answer is yes!
Well, here's the story:
I recently installed SQL Server 2005 on a 64-bit Windows Server 2003 OS.
This was done within the context of setting up a test environment for an ASP .NET application. As the OS was a 64-bit architecture I installed the 64-bit version of SQL Server as well.
Though, the ASP .NET application was compiled on .NET Framework 1.1 and IIS 6.0 was setup to use this version of the .NET Framework. To this end, when the installation of the reporting services started, I got the message that it was not possible to install the 64-bit version of the SQL Server 2005 Reporting Services because of using .NET Framework 1.1 for IIS 6.0.
Though, SQL Server did not dissapoint me! :) With some simple steps, it was very easy to install the 32-bit version of the Reporting Services while keeping installed the 64-bit versions of the rest of SQL Server features (i.e. Database Engine, Analysis Services, etc).
The following MSDN Link provides excellent instructions on how to perform this!
http://msdn.microsoft.com/en-us/library/ms143293(SQL.90).aspx
Remark: The procedure is almost identical for SQL Server 2008 with the difference that when you are going to install Reporting Services, before starting the installation process, from within SQL Server 2008 Installation Center, click on "Options" and select the "x86" Processor Type", then click on "Installation" and after selecting "New SQL Server stand-alone installation or add features to an existing installation" install only Reporting Services.
Read more on this article...
The answer is yes!
Well, here's the story:
I recently installed SQL Server 2005 on a 64-bit Windows Server 2003 OS.
This was done within the context of setting up a test environment for an ASP .NET application. As the OS was a 64-bit architecture I installed the 64-bit version of SQL Server as well.
Though, the ASP .NET application was compiled on .NET Framework 1.1 and IIS 6.0 was setup to use this version of the .NET Framework. To this end, when the installation of the reporting services started, I got the message that it was not possible to install the 64-bit version of the SQL Server 2005 Reporting Services because of using .NET Framework 1.1 for IIS 6.0.
Though, SQL Server did not dissapoint me! :) With some simple steps, it was very easy to install the 32-bit version of the Reporting Services while keeping installed the 64-bit versions of the rest of SQL Server features (i.e. Database Engine, Analysis Services, etc).
The following MSDN Link provides excellent instructions on how to perform this!
http://msdn.microsoft.com/en-us/library/ms143293(SQL.90).aspx
Remark: The procedure is almost identical for SQL Server 2008 with the difference that when you are going to install Reporting Services, before starting the installation process, from within SQL Server 2008 Installation Center, click on "Options" and select the "x86" Processor Type", then click on "Installation" and after selecting "New SQL Server stand-alone installation or add features to an existing installation" install only Reporting Services.
Read more on this article...
Monday, May 18, 2009
The Entity Framework - Part 2 - Inheritance
As promised on my previous Entity Framework-related post I will try through a series of posts which will contain examples, to talk about the ADO. NET Entity Framework and all the cool features it provides the developer with.
This post discusses about one of those features; Inheritance.
Inheritance allows creating entities having as base type other entities, thus allowing constructing even more sophisticated data models with ease.
The introductory post provided information on how you can create an entity data model by connecting to a SQL Server 2008 database.
For better explaining inheritance in this post, I edited the authors table on the pubs database and added another column named type. Then I updated the records and populated this new columnn with data as on the below screenshot:
So, you can see some authors having values (SF or CM) in the type column. This column presents the type of publications some authors create. SF stands for Science Fiction and CM for something else (I forgot what to be honest :). Authors having no type (that is, NULL) means that they create publications not belonging to a specific genre/type all the time.
Let's start!
First you have to create a Windows Forms C# Application, then right click on the project and add as a new item an ADO .NET Entity Data Model. After taking care of the database connection for this model (see introductory post for more information), the next step is to add the authors table in the entity data model designer:
So on the above screenshot you can see that the authors entity has been created and mapped to the corresponding table on the pubs database. In order to use inheritance, you have to create a new entity (or more than one) that have as a base type the original entity; in this case the authors entity. To this end, I have created the entity AuthorsWithType:
Now you can see that on the entity data model designer workspace there are two entities; the base entity (authors) and the inherited entity (AuthorsWithType):
At this point, you have to cut-paste the connecting propery (in this case type) from the base entity, that is authors, to the inherited entity - AuthorsWithType. Also, the authors entity has to be set as abstract (entity properties) as it won't be used for data manipulation because we are going to use the AuthorsWithType for this purpose.
The next screenshot displays the mapping editor tool and shows how the type column in the authors table has to be mapped to the type scalar property (string) in the AuthorsWithTypes entity. Also at this point, you also have to add a condition as shown below:
The purpose of the inherited entity is to list only the authors having a specific type/genre of publications and that is why we added the above condition.
You then have to save the data model and build the solution.
Then we start the process of adding a new datasource to our project of the type object:
We then bind our datasource to the AuthorsWithType object:
We drag-and-drop our new datasource on our application's main form and we get the following datagrid:
We then enable the Save icon (for being able to add some code later for saving any changes we perform on the datagrid contents during runtime):
Then we add the following code:
As the code comments say, we have to: (i) create a pubsEntities object (this is the entity data model connection string), (ii) instantiate the object, (iii) navigate through the entity data model to the authors data of type AuthorsWithType (this is a way of using inheritance via source code), and (iv) add a single line of code for allowing when clicking on the Save button to post any changes performed on the data during runtime (before that you have to double-click on the Save button during design-mode in order to generate the handling method called: authorsWithTypeBindingNavigatorSaveItem_Click).
At this point I would like to comment a little bit Line 3. Line 3 uses the OfType keyword for declaring the datasource. When using the OfType keyword, you are able to browse to the inherited entities in the entity data model via their base type entities. In this example, you browse the AuthorsWithType via its base type, that is authors.
When running the program, you will get the form with only displaying authors that have a type (SF or CM):
Now, let's change something on the datagrid contents. So let's change the au_lname of the first record from White to White-TEST and click on the save button:
Finally close and re-run the application; well, that's it, you can see that the above modification was saved on the database:
The command model.SaveChanges() posted the changes first through AuthorsWithType entity, then authors and finally on the authors table on the pubs database. A simple command did all these! :)
I hope you found this post useful. The Next post in the Entity Framework series will be dedicated to Entity Splitting.
Until next time!
Read more on this article...
This post discusses about one of those features; Inheritance.
Inheritance allows creating entities having as base type other entities, thus allowing constructing even more sophisticated data models with ease.
The introductory post provided information on how you can create an entity data model by connecting to a SQL Server 2008 database.
For better explaining inheritance in this post, I edited the authors table on the pubs database and added another column named type. Then I updated the records and populated this new columnn with data as on the below screenshot:
So, you can see some authors having values (SF or CM) in the type column. This column presents the type of publications some authors create. SF stands for Science Fiction and CM for something else (I forgot what to be honest :). Authors having no type (that is, NULL) means that they create publications not belonging to a specific genre/type all the time.Let's start!
First you have to create a Windows Forms C# Application, then right click on the project and add as a new item an ADO .NET Entity Data Model. After taking care of the database connection for this model (see introductory post for more information), the next step is to add the authors table in the entity data model designer:
So on the above screenshot you can see that the authors entity has been created and mapped to the corresponding table on the pubs database. In order to use inheritance, you have to create a new entity (or more than one) that have as a base type the original entity; in this case the authors entity. To this end, I have created the entity AuthorsWithType:
Now you can see that on the entity data model designer workspace there are two entities; the base entity (authors) and the inherited entity (AuthorsWithType):
At this point, you have to cut-paste the connecting propery (in this case type) from the base entity, that is authors, to the inherited entity - AuthorsWithType. Also, the authors entity has to be set as abstract (entity properties) as it won't be used for data manipulation because we are going to use the AuthorsWithType for this purpose.The next screenshot displays the mapping editor tool and shows how the type column in the authors table has to be mapped to the type scalar property (string) in the AuthorsWithTypes entity. Also at this point, you also have to add a condition as shown below:
The purpose of the inherited entity is to list only the authors having a specific type/genre of publications and that is why we added the above condition.You then have to save the data model and build the solution.
Then we start the process of adding a new datasource to our project of the type object:
We then bind our datasource to the AuthorsWithType object:
We drag-and-drop our new datasource on our application's main form and we get the following datagrid:
We then enable the Save icon (for being able to add some code later for saving any changes we perform on the datagrid contents during runtime):
Then we add the following code:
As the code comments say, we have to: (i) create a pubsEntities object (this is the entity data model connection string), (ii) instantiate the object, (iii) navigate through the entity data model to the authors data of type AuthorsWithType (this is a way of using inheritance via source code), and (iv) add a single line of code for allowing when clicking on the Save button to post any changes performed on the data during runtime (before that you have to double-click on the Save button during design-mode in order to generate the handling method called: authorsWithTypeBindingNavigatorSaveItem_Click).
At this point I would like to comment a little bit Line 3. Line 3 uses the OfType keyword for declaring the datasource. When using the OfType keyword, you are able to browse to the inherited entities in the entity data model via their base type entities. In this example, you browse the AuthorsWithType via its base type, that is authors.
When running the program, you will get the form with only displaying authors that have a type (SF or CM):
Now, let's change something on the datagrid contents. So let's change the au_lname of the first record from White to White-TEST and click on the save button:
Finally close and re-run the application; well, that's it, you can see that the above modification was saved on the database:
The command model.SaveChanges() posted the changes first through AuthorsWithType entity, then authors and finally on the authors table on the pubs database. A simple command did all these! :)I hope you found this post useful. The Next post in the Entity Framework series will be dedicated to Entity Splitting.
Until next time!
Read more on this article...
Sunday, May 10, 2009
Sample Databases for SQL Server
DBAs and Database Developers always need to test new database-related work before migrating it to Production Environments.
To this end, Test/Development Environments are usually set up in order to allow for testing new administration and programmability features of DBMSs (in our case SQL Server :)
Though, by only having such an environment set up is not enough. DBAs as well as Database Developers need sample data in order to manipulate it with various SQL Server operations and record the "responses" of the underlying SQL Server objects (i.e. databases, logins, etc.).
For every version of SQL Server, Microsoft releases sample databases (and many other samples such as code examples, etc.) which allow the user setting up Testing Environments of SQL Server along with sample data, thus being able to easily test new functionality.
It is not random that many SQL Server examples found on blogs, forums and websites, are referring well-known sample databases such as: AventureWorks, Northwind, pubs, etc (well, my posts on Entity Framework use the "pubs" database :)
So, by using the links provided below, you can find SQL Server sample databases (as well as other sample stuff) for all the SQL Server versions:
SQL Server 2000
------------------
You can download the Northwind and pubs databases from the following link.
SQL Server 2005 and 2008
----------------------------
You can find community projects and samples for SQL Server 2005 or later on Microsoft CodePlex, on the following link: http://www.codeplex.com/SqlServerSamples
The sample databases link is the following: http://www.codeplex.com/MSFTDBProdSamples/
Well, that's it, I guess that now you can continue experimenting with the new features of the latest SQL Server release with using the latest sample data as well! :)
Read more on this article...
To this end, Test/Development Environments are usually set up in order to allow for testing new administration and programmability features of DBMSs (in our case SQL Server :)
Though, by only having such an environment set up is not enough. DBAs as well as Database Developers need sample data in order to manipulate it with various SQL Server operations and record the "responses" of the underlying SQL Server objects (i.e. databases, logins, etc.).
For every version of SQL Server, Microsoft releases sample databases (and many other samples such as code examples, etc.) which allow the user setting up Testing Environments of SQL Server along with sample data, thus being able to easily test new functionality.
It is not random that many SQL Server examples found on blogs, forums and websites, are referring well-known sample databases such as: AventureWorks, Northwind, pubs, etc (well, my posts on Entity Framework use the "pubs" database :)
So, by using the links provided below, you can find SQL Server sample databases (as well as other sample stuff) for all the SQL Server versions:
SQL Server 2000
------------------
You can download the Northwind and pubs databases from the following link.
SQL Server 2005 and 2008
----------------------------
You can find community projects and samples for SQL Server 2005 or later on Microsoft CodePlex, on the following link: http://www.codeplex.com/SqlServerSamples
The sample databases link is the following: http://www.codeplex.com/MSFTDBProdSamples/
Well, that's it, I guess that now you can continue experimenting with the new features of the latest SQL Server release with using the latest sample data as well! :)
Read more on this article...
Saturday, May 2, 2009
Upcoming CDNUG Event, May 13th: Microsoft SharePoint Feature Areas
At Cyprus .NET User Group (CDNUG), we focus on Microsoft .NET and SQL Server technologies (for more information please read about CDNUG).
We organize regular monthly events on the above technologies which, of course, are free to the public.
This month’s event (May, 2009) is dedicated to Microsoft SharePoint Technologies.
See below for event-specific details:
--------------------------------------------------------------------
Topic: Microsoft SharePoint Feature Areas
When: Wednesday, May 13th, 2009
Time: 19:00
Where: Microsoft Office (11 Limassol Avenue, Nicosia)
Speaker: Evangelos Hadjichristodoulou (Services Consultant, Microsoft Cyprus)
Host: Cyprus .NET User Group
Registration: Please signup on CDNUG Yahoo polls.
For more details check our website at www.cdnug.net
--------------------------------------------------------------------
If it happens and you are in Cyprus in May, you are more than welcome to attend our event!!!
Read more on this article...Friday, May 1, 2009
tempdb growth
As its names implies, the tempdb database contains temporary data that are created during SQL Server operations.
Such data may include: temporary user objects (i.e. temporary tables, cursors), row versions (i.e. those that come up from online index operations), and other internal objects that are created by SQL Server Database Engine.
The tempdb is global on a SQL Server Instance, that is available to all the users/databases on a SQL Server Instance, so in the cases where an instance might contain a large number of databases resulting to a large number of operations which use temporary data, this might have as an effect the tempdb size to increase rapidly.
So, how can you shrink the tempdb database and limit its size?
Well, there is more than one option for doing this.
The following KB article describes three different methods for shrinking tempdb.
In summary, these are:
Method 1
----------
Altering the tempdb file size with the "ALTER DATABASE [tableName] MODIFY FILE" command
Method 2
----------
Using the "dbcc shrinkdatabase" command
Method 3
----------
Using the "dbcc shrinkfile" command
Read more on this article...
Such data may include: temporary user objects (i.e. temporary tables, cursors), row versions (i.e. those that come up from online index operations), and other internal objects that are created by SQL Server Database Engine.
The tempdb is global on a SQL Server Instance, that is available to all the users/databases on a SQL Server Instance, so in the cases where an instance might contain a large number of databases resulting to a large number of operations which use temporary data, this might have as an effect the tempdb size to increase rapidly.
So, how can you shrink the tempdb database and limit its size?
Well, there is more than one option for doing this.
The following KB article describes three different methods for shrinking tempdb.
In summary, these are:
Method 1
----------
Altering the tempdb file size with the "ALTER DATABASE [tableName] MODIFY FILE" command
Method 2
----------
Using the "dbcc shrinkdatabase" command
Method 3
----------
Using the "dbcc shrinkfile" command
Read more on this article...
Subscribe to:
Posts (Atom)
Posts
