Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

Sometimes you need to refresh the local cache in SQL Server when writting code to remove that annoying red squiggly line.

Edit -> Intellisense - Refresh Local Cache
OR
Ctrl-Shift-R

Enjoy
Dave



The SSRS Reports website security can sometimes be troublesome when first configured and trying to connect to it from a remote machine. The RSReportServer.config file has a AuthenticationType section that required attention if you are prompted for authentication when accessing the site remotely. By default you may get all 3 types, however if you are just using regular Windows authentication you should only have RSWindowsNTLM in the list. Removing the other 2 will allow you into the site, provided you have access to the database.

 

      <AuthenticationTypes>
             <RSWindowsNegotiate />
             <RSWindowsKerberos />
             <RSWindowsNTLM />
      </AuthenticationTypes>


Replace Diameter with your own search.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Diameter%'
ORDER BY schema_name, table_name;

List ALL fields in a Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
ORDER BY schema_name, table_name;


Hope this helps.



For SQL 2005, delete the file:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

For SQL 2008, the file location, format and name changed:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

How to clear the list:

  • Shut down all instances of SSMS
  • Delete/Rename the file
  • Open SSMS


If you are working with SQL Stored Procedures and Functions, it's simple to add the db_owner role to your own login. However, when you want to release your database to a production environment you really want to take away the db_owner role as it has the ability to add, edit and remove database objects in a production environment. The proper way of assigning permissions is to give the user\group in question db_reader and db_writer privilages ONLY and remove the db_owner role. You then need to GRANT the user\group EXECUTE permission on all the Stored Procedures.

To do this you may need to run the following script in order to generate the script so this may be done quickly rather than applying the permission one by one. Once run, simply copy the results to a new window and Execute all lines.

DECLARE @User varchar(50)
SELECT @User = '[domain\userid]'

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0
ORDER BY name

OR if you want to GRANT to PUBLIC

DECLARE @User varchar(50)
SELECT @User = 'PUBLIC'

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0
ORDER BY name

This link had more details regarding the objects you might need to query.
http://msdn.microsoft.com/en-us/library/ms190324.aspx



Installing Microsoft SQL Server 2008 Management Studio Express


Microsoft SQL server 2008 Management Studio Express offers management tools for Microsoft SQL Server 2008 Express, Express with Advanced Services and Compact editions. If your database is hosted on an SQL 2005 Express server, install Microsoft SQL Server Management Studio Express. SQL server 2008 Management Studio Express runs on Windows 7, Windows Vista service pack 1 and Windows XP service packs 2 and 3, Windows server 2003 service pack 2, Windows server 2008 and Windows Server 2008 R2.

This article includes instructions for installing all of the necessary prerequisites. Each installation will be different depending upon the operating system, patch level and previous software installations on the computer. You must log in to the computer with administrative rights to install software.

Install Windows Installer

  1. Download the appropriate installation file for your version of Microsoft Windows from the Microsoft Download Center.
  2. http://www.microsoft.com/download/en/details.aspx?id=8483 is the 4.5 Installer for SQL 2008 Studio!
  3. Open the Windows installer file.
  4. Click “Next” when presented with the “Software Update Installation Wizard.”
  5. Select the option beside “I agree” to answer the licensing agreement query.
  6. Click the “Next” button.
  7. Press “Finish.”

Install the .NET Framework

  1. Download the current version of the .NET Framework for your operating system from the Microsoft Download Center.
  2. Open the .NET Framework installation file.
  3. Select the option button for “I have read and accept the terms of license agreement” to agree to the licensing terms.
  4. Press “Install.”
  5. Click “Exit” when the message that the setup has completed appears.

Install Windows PowerShell

  1. Download Windows PowerShell for Windows XP or for Windows 2003 Server from Microsoft's download center.
  2. Execute the Windows PowerShell setup file.
  3. Click “Next” in the Software Update Installation” window.
  4. Select “I Agree” to accept the licensing agreement. Click “Next.”
  5. Press “Finish” when the installation has completed successfully.

Install Microsoft SQL Server 2008 Management Studio

  1. Download Microsoft SQL Server 2008 Management Studio Express from the Microsoft Download Center.
  2. Double-click the Microsoft SQL Server 2008 Management Studio Express executable file.
  3. Choose “Installation” in the SQL Server Installation Center window.
  4. Click on the link to “New SQL Server stand-alone installation or add features to an existing installation.”
  5. Check for warnings and errors in the “Setup Support Rules” pane.
  6. Resolve any failures before clicking the “OK” button.
  7. Click “Install.”
  8. Check for warnings and errors in the “Setup Support Rules” pane.
  9. Resolve any failures before clicking the “Next” button.
  10. Select “Perform a new installation of SQL Server 2008.” Click “Next” and “Next.”
  11. Check the box beside “I accept the license terms.” Click “Next.”
  12. Check the box beside “Management Tools – Basic” beneath the shared features.
  13. Click “Next” and “Next.”
  14. Accept the defaults in the “Error and Usage Reporting” screen and click “Next.”
  15. Select the “Next” button and click “Install.”
  16. Click “Next” and “Close” when you see the message that the setup process is complete.

Microsoft SQL 2008 Server Management Studio Express simplifies management of SQL 2008 Server Express and Compact editions. Authorized users and administrators can run scripts, create tables, assign rights, schedule backups, replicate, shrink and restore databases from the Object Explorer window. While a long process, installation of the prerequisites and the software will save you time and effort when administering your SQL databases.


Copyright from Maria Montoya’s suite 101 site: Installing Microsoft SQL Server 2008 Management Studio Express | Suite101.com



If you want to copy the contents of a Results window from inside Microsoft SQL Server Management Studio AND include the Column Headers then you need to set the following via Tools -> Options.

Query Results -> SQL Server -> Results To Grid -> Check the box next to "Include column headers when copying or saving the results".

Note that you will need to close your current session and open a new one before this change takes place.



This is how to list all tables in a SQL Server Database with one statement, or two.

--Tables by Rows Descending Order
SELECT sysobjects.Name,
       sysindexes.Rows
FROM   sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = 'U' AND sysindexes.IndId < 2
ORDER BY sysindexes.Rows DESC

--Tables by Table Names
SELECT sysobjects.Name,
       sysindexes.Rows
FROM   sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = 'U'AND sysindexes.IndId < 2
ORDER BY sysobjects.Name



The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. 

Calendar

<<  April 2014  >>
MoTuWeThFrSaSu
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

View posts in large calendar

Sign in