I have written a utility that checks our stored procedures for
potentially incorrect code (eg = null as opposed to is null, unused
variables/parameters etc). It also ensures that parameters/variables
etc match our naming conventions and data types/sizes are the sames as
the corresponding type in the database (if appropriate)
Are there any other utilities that do this sort of thing?
Hello, everyone:
I have 40 stored procedures in a database. A table X is called by some procedures. How to check which procedure call table X, and how many procedures call table X? Thanks
ZYT
Any code out there to check to see if stored procedures, views, etc. are invalidated (as with Oracle that shows procedure as being INVALID) due to dependent object changes ?
View 9 Replies View RelatedHi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
View 2 Replies View RelatedComiling a stored procedure produces the message
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect
This is produced in Query Analyser and OSQL
Normal syntax error gives errors of the Form 'Msg 1234 line no. '
Introducing syntax errors to get a normal message still gives the Count field incorrect Message. There is no user COUNT field
Any ideas
E
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine
My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:
Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.
In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option.
My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure?
Here's a code snippet stripped down to the bare minimum to generate the error:
CREATE PROCEDURE [My_FullTextSearch]
@Term VarChar(1000)
AS
BEGIN
SET NOCOUNT ON;
IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1
BEGIN
Select [DocumentID]
from [DocumentText]
where [Term] like '%' + LTRIM(@Term) + '%'
END
ELSE
BEGIN
Select [key] from FREETEXTTABLE(Documents, Contents, @Term)
END
END
I have large stored procedures in SQL Server 2005 that often get updated. Sometimes it is very difficult to recognize how one change could impact the overall results. I would like to have some sample input that I could test during changes and see it compare the data results before & after my changes. This would help me quickly identify if the results are fine to pass through.
So basically I would like an easy way to compare the results of 2 stored procedures. Any suggestions or utilites that would help me do this?
I have large stored procedures in SQL Server 2005 that often get updated. Sometimes it is very difficult to recognize how one change could impact the overall results. I would like to have some sample input that I could test during changes and see it compare the data results before & after my changes. This would help me quickly identify if the results are fine to pass through.
So basically I would like an easy way to compare the results of 2 stored procedures. Any suggestions or utilites that would help me do this?
Hi all,
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?
Thanks =)
I set up a test database to try and understand table partitioning. Since deleting the partition schemas, functions and all relevant info on the database properties page, I am getting the following errors during the weekly database integrity check:
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:223) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Can anyone suggest why this might be happening?
Thanks
Hi experts,
i am asked to check data driven errors while importing data from an excel workbook to a sql server table.
what are the data driven errors expected and how to introduce that in this sql code.
SELECT a.* FROM OPENROWSET(BULK 'Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database = C:DataExTestVista_Logo_Product_List_20080204.xls',
'SELECT * FROM [RAW Data$]'
, FORMATFILE = 'D:format.fmt') as a
Thanks in advance.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedI can't get the download to work...i've tried regular sql server express 2005, the express for sp2 and the express with advanced services. I keep getting this message after the native client and support files are installed.."This application has failed to start because XOLEHLP.dll was not found. Re-installing the application may fix this problem."Then right after that message is says... "Failed to load SQLSpars.dll"
View 1 Replies View Relatedhow to check the errors when a database is configured in Always on in sql 2012?
View 1 Replies View RelatedI am not a DBA but have responsibility for a particular MSSQL 2008 R2 file server running a particular application.how to solve a database consistency check problem.The database fails dbcc checkdb with multiple 8903 errors. Unfortunately this was not discovered until well after any good backups were deleted. The good news is that the DB otherwise seems fine. We have experienced zero problems with the DB or the applications. Running the checkdb with the "repair_allow_data_loss" option does not fix the problem.
However, I would still like to fix the problem. Using a popular SQL recovery product I am able to recover the database.The original, vendor designed and supplied DB, has 2 file groups, and three files (MDF, NDF, LDF). The output of the recovery process produces 1 file group and 2 files (MDF and LDF). Vendor says they cannot support me since the recovered DB is 'non-standard' according to their design.
I am able to set up a new, blank version of the vendors database on another dev system with the proper file and filegroup structure. How can I get the data moved/copied from the recovered (MDF/LDF) database into the dev database (MDF, NDF, LDF). I've tried the import/export function but it fails (I can rerun and give details if necessary).
I am using execute sql task and my sql statement contains
EXEC test ?
procedure test has 2 parameters. I added parameters using parametermapping tab. When I run i get an error message
Multiple-step OLE DB operation generated errors. Check each OLE DB status value
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Hi all,
We have Windows 2003 64 sp2 Xeon, 2005 EE SP2 64 bit...
Trying to do conversion from DTS sql 2000..One package use load from excel to sql..So I tried to create same thing by myself.. Hell, so many issues.. So I used wizard, package created, I changed Run64bit to False, tried to run package, once - completed in debug mode.. Now it's time to create deployment utility and deploy package..During execution of manifest file got error:
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
In BIDS, open up solution and tried to rerun package again - no way,".. cannot acquire connection from connection manager blah blah blah.."
Even tried to fire package without debugging, it fires 32 bid execution utility, so no question about 64 bit mode.. package failed..
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The
external request has completed.
Start Time: 2007-11-09 09:41:25
End Time: 2007-11-09 09:41:25
End Log
Error: 2007-11-09 09:41:25.95
Code: 0xC0202009
Source: Package_name loader Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error
s. Check each OLE DB status value, if available. No work was done.".
End Error
Log:
Name: OnError
Source Name: Data Flow Task
Source GUID: {2A373E56-8AAF-40E9-B9EF-4B2BB40175F0}
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
. The AcquireConnection method call to the connection manager "SourceConnection
Excel" failed with error code 0xC0202009. There may be error messages posted be
fore this with more information on why the AcquireConnection method call failed.
Any help..?
Hi,
I am using ATL COM library application. It is using sql data base for fetching the records. Some times, i get the following error. could you please let me know, why this happens? This is not reproduceble every time.
(Error! hr=80040e21, hrDesc=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work
Here is the code to connect to database which i am using.
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
dbinit.AddProperty(DBPROP_INIT_CATALOG, (const char *)bDatabase);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (const char *)bServer);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_TIMEOUT, (short)150);
hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
if (FatalError(hr, "db.Open", buf))
{
*iErrorCode = hr;
return S_OK;
}
Any help, appreciated.
Thanks,
Satish
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
Hi all,
I am going to upgrade a SQL server 6.5 box to SQL7.0. The 16 bit-client utilities are a prerequisite because there is a 16-bit application and requires some of the database libraries. My question is how does SQL7.0 deal with 16-bit application? In my case, could I upgrade the SQL6.5 box to SQL7.0?
I appreciate any input and help ahead,
Stella Liu
Does anyone have any palm utilities, or reference docs, urls - I am administering SQL7 and would like info on Palm-3, using as a references. I have ModileDB installed, with Oracle Recovery references, but could not find SQL.
thanks,
Jason
I am looking for good utilities that document MS SQL table schema, indexes, layouts etc. I am currently looking at SQL auditor, but this product does not give me table schema or any kind of device revisions. I recently was given several SQL servers that have not been documented in any way.
thanks
ps SQL auditor gets a B+ in my book. Let me know what you think
HI
Would like to know whether SQL server has the utilities do the following and the command to activiate the followings:
- limit the no. of login (ie to set the number of failed_login_attempts)
- password_reuse_max (eg password A can only be reuse 3 times)
tia
MK
Hi,
I heard from a guy that the client utilities (Enterprice Manager and Query Analyser) that are on the Sql Server 2000 CD can be fetched separatly from Microsoft and that they can be deployed with MSDE to a potential customer.
Is that correct?
And if it is, where can I obtain those client apps?
TIA
Ohlywohly
Hi everyone,
Can anyone tell me the basic difference between Client Network Utility and Server Network Utility and also when to use what..?????
Does anyone know how to remove SQL utilities off the server manually.
When I run the setup.exe from the SQL CD to remove SQL from server that option button is
shaded, I try reinstalling SQL utilities and then I try removing and still it won`t let me?
Help!!!
We are looking for utilities to help us manage DTS packages. Specifically to help us track changes in the packages in a fairly fine-grained fashion; several layers deeper than the version listing under package info, for example. Think of it as an object-oriented diff utility if you will, something that can report on changes in a package very specifically; for example, we need to know that step 4 of a package is a SQL widget and the text has changed between versions, and what the changes are. Converting the package to a VB project & versioning the source text doesn't work, for several reasons too complicated to state here.
Is there aything like this available? We would rather buy than build, but I haven't seen anything like this yet. If anyone has experience with such a utility I would appreciate knowing about this. Thanks.
Jim Rivera
Software Engineer
Grange Insurance Graoup
Seattle, WA
Folks,I work on a system which is growing rapidly, with the number oftransactions we process growing on a daily basis. While this is goodnews or the business, maintenance is starting to become an issue as thedatabase is the backend for a website which cannot be down for alengthy period of time.While I do defrag the indexes, periodically the indexes do need to berebuilt. When this happens, the process locks pages and transactionsstart getting bounced out.Are their any third party utilities which will rebuild an indexwithout this locking occuring? Any help in pointing me in the rightdirection would be appreciated.
View 1 Replies View RelatedHow do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!