Check Validity of SQL Server Stored Procedures, Views and Functions

 
 
 

"When I set out to do this, someone reminded me of the "check syntax" feature in SQL Enterprise manager. If I could replicate this functionality, this would be ideal, as I didn’t want to actually compile the database objects – just simulate compilation. So I pulled out SQL profiler and recorded the statements that are executed when you press the "Check Syntax" button. Interestingly what happens when you press this button is the following:

Collapse Copy Code
SET NOEXEC ON
GO
CREATE PROC....etc
GO
SET NOEXEC OFF
GO
SET PARSEONLY OFF
GO

I had not come across these SET commands before. Looking them up in BOL tells me what you have already guessed:

  • SET NOEXEC "compiles each batch of Transact-SQL statements but does not execute them"
  • SET PARSEONLY "Checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement"
Advertisements

SQL Server Intellisense Refresh

 

Actually this is because Objects are cached. You can refresh cache using

Edit -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)

 

Find Views SchemaBinding

 

Other ways exist. For instance in code.

CREATE PROCEDURE [dbo].[uspViewsSchemaBound]
(@DatabaseName nvarchar(50))
AS

DECLARE

@SQL nvarchar(200)
SET @SQL = ‘SELECT * FROM ‘ + @DatabaseName + ‘.INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION LIKE ”%WITH SCHEMABINDING%”’
EXECUTE (@SQL)

 

 

ToolStripTextBox as Password

 

Can I make a ToolStripTextBox show a character if I am using it for a password ie a * instead of a character as you can in a textbox ? Yes

ToolStripTextBox1.TextBox.PasswordChar = "*"c

From http://vbcity.com/forums/topic.asp?tid=153131

 

Entity Framework – Connection Strings

Technorati Tags:

See:

http://msdn.microsoft.com/en-us/library/vstudio/cc716756(v=vs.100).aspx

Entity Framework connection string for Entity Model

If in a different project in the same solution and a folder in that project then.


res://Project.ProjectName/Folder.ModelName.csdl|
res://Project.ProjectName/Folder.ModelName.ssdl|
res://Project.ProjectName/Folder.ModelName.msl;

If you struggle then the following will work, but may not be as efficient because the application has to search through available references. This is all that is needed though.


res://*/

 

  <connectionStrings>
    <add name="MovieModel"
         connectionString="metadata=res://OneWay.EntityModel/Models.MovieModel.csdl|
                                    res://OneWay.EntityModel/Models.MovieModel.ssdl|
                                    res://OneWay.EntityModel/Models.MovieModel.msl;
                                    provider=System.Data.SqlClient;
                                    provider connection string=
                                      "data source=(local);
                                      initial catalog=DBName;
                                      integrated security=True;
                                      multipleactiveresultsets=True;
                                      App=EntityFramework;""
         providerName="System.Data.EntityClient" />

    <add name="OneWayEntitiesCF"
         connectionString="Data Source=(local);Initial Catalog=OneWay;Integrated Security=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Error 1: System.IO.FileNotFoundException: Unable to resolve assembly ‘OneWay.EntityMode’.

Solution is to correct res://[this bit]/..

Error 2: System.Data.Entity.Core.MetadataException: Unable to load the specified metadata resource.

Solution is to correct res://../[ThisBit]

Possibly you require FolderName.ModelName.

 

Other notes

If do not have Initial Catalog then will attach it to program and detach itself from SQL. Agh ! May also need User Instance = False for this.

Connection string in App.config.

Name and Model name need to match.

In edmx change Entity Set Names to have an ‘s’ on the end for each entity

Good luck !

 

Error handling Stack Frame

Technorati Tags:
 

Dim stackFrame As New Diagnostics.StackFrame(1)
Dim sProcedure As String = stackFrame.GetMethod.Name
Dim sModuleName As String = stackFrame.GetMethod.DeclaringType.FullName

Set ToolStripTextBox Focus when Opening the form

me.ActiveControl=me.ToolStripTextBox.Control
You also need to set the ToolStrip’s (not the ToolStripTextBox’s – it doesn’t have the property) TabStop property to True.
Also if you set this active control on form load, then if you hide the form you may change the focus when it becomes visible again.
Could use following

PrivateSub frm_VisibleChanged(sender AsObject, e As System.EventArgs) HandlesMe.VisibleChanged

If Me.Visible Then
           ‘ToolStrip1.TabStop = True
            ActiveControl = InputProductToolStripTextBox.Control
   End If

EndSub

Lambda Enumerable..::.First<(Of )>) Generic Method (IEnumerable<(Of )>), Func<(Of )>))

 
 
ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/fxref_system.core/html/d232a6bf-5e64-d5b8-83f5-a0cf110f4107.htm
 

Public Function IsServiceRuning(ByVal scName As String) As Boolean

Dim scServices() As ServiceController
scServices = ServiceController.GetServices
Dim sc As ServiceController = scServices.First(Function(s) s.ServiceName = scName)
If sc.Status = ServiceControllerStatus.Running Then
Return True
End If

End Function

Debug Visualizer DataTables and DataSets

 
 
Hover over variable containing dataset or datatable.
Then look for drop down with magnifying glass.
Or go to Quick watch then look for magnifying glass