I have a custom application that on occasion requires thousands of TSQL
files (on the file system) to be compiled to the database.
What is the quickest way to accomplish this?
We currently have a small vbs script that gets a list of all the files,
the loops around a call to "osql". each call to osql opens/closes a
connection to the destination database (currently across the network).
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>
Running SQL Server Express is there a way to debug TSQL stored procedures? I also have Visual Studio .NET 2003, can I use it to debug the TSQL stored procedures?
Thanks in advance,
Mark
Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?
View 1 Replies View RelatedWhen I try and create the following stored procedure, I get the following error message:
Any ideas as to what went wrong? Here is the stored procedure
USE [DBS07]GO/****** Object: StoredProcedure [dbo].[updateMarketName] Script Date: 09/17/2007 22:28:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[updateSubMarketName]( @inMarketId int, @inSubMarketId int, @inSubMarketDescription nvarchar(100), @inActive nvarchar(2), @inLastUpdateDate datetime, @inLastUpdateUser nvarchar(100))AS SET NOCOUNT OFF;UPDATE [SubMarket] SET [SubMarketDescription] = @inSubMarketDescription, [Active]= @inActive, [LastUpdateDate] = @inLastUpdateDate,[LastUpdateUser] = @inLastUpdateDateUserWHERE ([MarketId] = @inMarketId)AND (SubMarketId]= @inSubMarketId)
Here is my error message
Error message in Red:Msg 137, Level 15, State 2, Procedure updateSubMarketName, Line 12Must declare the scalar variable "@inLastUpdateDateUser".
To all,
When a stored proc is executed in SQL server 2000, it is holding a EXCLUSIVE [COMPILE] lock on the proc and the proc os getting recompiled every time it is executed. This is happening with most of the procs that are called from this proc. When multiple users are executing the same process they are having to wait until the other users are done compiling the procs. The lapse time is growing exponentially with multiple users.
I have looked at several places to find a solution for this. Microsoft Articles Q243586 and Q263889 have provided me with some options; but at this point, I need a miracle.
All these procs users temp tables. I have got the code changed to replace most of them with Table datatypes (on SQL2K only) . Some of them still need to use temp tables as they are cross referenced by multiple procs.
I am hopeful, there is some one out there who has dealt with this kind of situations before. Any ideas/sugessions are greatly appreciated..
Thanks
Hello!I am trying to investigate strange problem with particular storedprocedure. It runs OK for several days and suddenly we start gettingand lotof locks. The reason being [COMPILE] lock placed on this procedure. Asaresult, we have 40-50 other connections waiting, then next connectionusingthis procedure has [COMPILE] lock etc. Client is fully qualifyingstoredprocedure by database/owner name and it doesn't start with sp_. I knowthese are the reasons for [COMPILE] lock being placed. Is theresomethingelse that might trigger this lock? When troubleshooting this issue, Inoticed there was no plan for this procedure in syscacheobjects. Thestoredprocedure is very simple (I know it could be rewritten/optimized butourdeveloper wrote it):CREATE PROCEDURE [dbo].[vsp_mail_select]@user_id int,@folder_id int,@is_read bit = 1, --IF 1, pull everything, else just pull unread mail@start_index int = null, --unused for now, we return everything@total_count int = null output, -- count of all mail in specifiedfolder@unread_count int = null output -- count of unread mail in specifiedfolderASSET NOCOUNT ONselect m1.* from mail m1(nolock) where m1.user_id=@user_id andfolder_id=@folder_id and ((@is_read=0 and is_read=0) or (@is_read=1))orderby date_sent descselect @total_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and ((is_read=0 and@is_read=0)or (@is_read=1))select @unread_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and is_read=0GOI was monitoring server for a couple of day before and I am not surewhythis happens every 3-4 days only!Any help on this matter would be greately appreciated!Thanks,Igor
View 1 Replies View RelatedAll,
I have beating my head over this and have asked questions to clear this up in small bits.
In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:
--Normal ASP header
<%
myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'
'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable
Set SysMainQuery=Server.CreateObject("ADODB.Recordset" )
SysMainQuery.open "Select * from tbl_Date", strDSNPath
howmanyfields=SysMainQuery.fields.count -1
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>"
NEXT
response.write "</tr>"
' Now loop through the data
DO WHILE NOT SysMainQuery.eof
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=SysMainQuery(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue=" "
END IF
response.write "<td valign='top' NOWRAP><Font Size=2>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
SysMainQuery.movenext
'howmanyrecs=howmanyrecs+1
LOOP
response.write "</table></font><p>"
' close, destroy
SysMainQuery.close
set SysMainQuery=nothing
%>
</body></html>
The stored procedure takes the input an returns the sql statement:
Alter Procedure MainSysLogQuery
@myDates nvarchar(200) = '',
As
declare @oSql nvarchar(4000)
declare @viewtmp nvarchar(3000)
declare @querytmp nvarchar(3000)
declare @wheretmp nvarchar(3000)
declare @ordertmp nvarchar(3000)
declare @tmplen int
Set @wheretmp = ''
Set @tmplen = 0
if @myDates != ''
Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''
If len(@wheretmp)>0
Set @wheretmp = ' WHERE ' + @wheretmp
print @wheretmp
Set @viewtmp = 'SELECT *'
Set @querytmp = ' FROM tbl_All_SysData'
Set @ordertmp = ' ORDER BY LongDate DESC'
Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp
PRINT @oSQL
Exec(@oSQL)
Go
The problem is I keep getting:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure....
The Errors are on on the recordset.eof, recordset.movenext, etc....
Has anyone ever run into this?
Thanks
~Lee
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 RelatedHi,
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
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,
i just started working on a new project, a RIA with a backend on SQL 2005.
The question that I'm asking - about the databases architecture - is it possible:
a) (this is the preferred solution) to have up to 5 000 (five thousands) databases inside a SQL 2005 instance (each client with his own small db)
b) would it be better to have one large database with tables eventually having near a billion of rows ?
c) one single database with thousands of small tables.
Thank You All.
Edit :
The clients databases will be identical in structure.
The motivaion for having each client with his own database is to ease management (backups, restores, roll-backs) and tools in this direction will be created (scripts, automated procedures, scheduled management plans etc). Each client is proprietary, and to have things separated seems more legal.
The hardware is not a problem, the db server will be a blade center runing a clustered MS Windows Server.
Code Snippet
(07:51:49) him: i haven't followed sql recently but let me see (seems difficult question)
(07:52:36) him: what is the motivation to have different datbases for each client?
(07:52:46) me: (the hardware is not a problem, probably ibm blade center, about the management - tools will be created to ease)
(07:52:52) him: it is it the size/efficiency or you want to separate them
(07:53:02) me: for the back-ups and restores and roll-backs
(07:53:13) me: the db-s r identical
(07:53:30) him: identical in design or data inside them
(07:53:33) me: yes, design
(08:02:12) him: so the limitation is Databases per instance of SQL Server 32,767
(08:02:21) him: so it seems that in that respect you are going to be fine
(08:02:45) me: yes, i've seen the specs
(08:02:50) him: yep
(08:03:03) him: in terms of design
(08:03:13) him: managing 5000 databases would be a nightmare
(08:03:22) him: unless there is some automatic way to do it
(08:04:18) me: that's for shure, and that's what is intented
(08:04:29) me: automadet procedures, scripts etc
(08:05:27) him: so again what is the motivation to partition each client in different database
(08:06:05) me: there is a need to keep their data separate
(08:06:19) me: each client is proprietary
(08:06:44) me: and to have things separated is more legal
(08:07:25) him: well you try with several databases and see how it scales
(08:08:37) him: one thing that would also prevent confusing databases connections and may be helpful if you decide to change the to a single database model is to design the database as if it would be used with many clients but use it just for one
(08:09:09) him: then, if you decide to merge the databases in the future it would be relatively easy
(08:09:16) him: unless this would create too much overhead
(08:09:41) me: yes, this is allready the case
(08:09:58) me: so, i go for a test u say
(08:10:12) me: and see how it's working
(08:10:16) him: ok. then it seems that the issue is maintaince then anything else
(08:10:29) him: yeah. i don't think the sql server would be a problem
(08:10:44) him: there would be some overhead of the different databases but should be small
(08:11:12) me: :)
(08:11:24) me: k, thank you verry much
(08:12:53) him: http://www.thescripts.com/forum/thread503521.html
(08:13:09) him: i think either way has plus and minuses
(08:16:56) him: also read the forum link i sent you
(08:17:00) me: ty, i'm on it
(08:17:16) him: there are some interesting considerations i did not think of
(08:17:58) me: i just saw about caching maintainance plans, stored procs
I'm receiving these two errors again and again:
Windows cannot load extensible counter DLL MSSQL$MSFW, the first DWORD in data section is the Windows error code.
Windows cannot load extensible counter DLL MSSQL$SBSMONITORING, the first DWORD in data section is the Windows error code.
What might be generating them and how can I cure it?
The system is SBS 2003 R2 Premium.
Thanks.
I have an insert stored procedure which creates a customer. The customer has a ID which is an autonumber within the SQL server table.
I want to output this value to use this to update another table. How do I output a value of an insert query which i am not passing in as it's an autonumber ??
I am sure someone has come accross this before. Is it a trigger solution ?? Any ideas very welcome.
mark
How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())
increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.
Anyone has an idea?
mipo
I have a query that returns raw tick data from a table. Unfortunately aftereven a few days there are hundreds of thousands of rows so the followingquery is not efficient.SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? ANDRawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' ANDContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE Status= 'I' AND ContractCode = ?))The most obvious solution then is to get all tick data with status ='I'(Imported) for a contract, process it and then move it to another table forarchiving. I am faced with a problem however: After selecting all data for acontract with status='I' the application was updating these records to a newstatus of 'P' (processed). Unfortunately another application is continuningto feed in live data and so it is possible that we will inadvertantly updateunprocessed data to 'P'.Question: Is it possible to select all records with status 'I' (from abovequery) and update their status to 'P' in one sequence?I am not a programmer, but if this is possible I should be able to implementthe query.Many thanks.Steve
View 3 Replies View RelatedI have the following for sql server 2000...
Select b.courseName, a.courseId, count(a.courseId) as [count], avg(convert(INT, a.fldScore)) as [average], count(fldPass) as [passed], count(fldPass) as [failed] From tblTest a inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseName
Problem is the [passed] and [failed]
As it is, it's counting all of them.
I need to adjust it so passed will only read where fldPass = 'yes'
and fldPass = 'no' for the passed and failed.
Suggestions?
Thanks,
Zath
Can i execute a URL in tsql stored procedure.
Waht i want's is to hit a url on some event.
I know i can do it in CLR stored procedure but for that i have to deploy assembly on the server which i want's to avoid.
Is there any way i can hit a url from tsql stored procedure
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records. Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download. When I
save the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks
Format number thousands separator with point
I have this number in my database output result of query: 1013473
I need this output: 1.013.473
I would like to select a BIGINT type and get a formatted result with commas. Anyone have ideas?
declare @i bigint
set @i = 123456789
select @i
--Would like to get
123,456,789
Well I have a two tables lets say they look like as follows:
Table A
CompanyID
Location
TableB
CompanyID
CompanyName
Date
The CompanyID column has values that look like this:
AAA OR
AAA.BBB OR
AAA.BBB.CCC OR
AAA.BBB.CCC.DDD OR
AAA.BBB.CCC.DDD.EEE OR
AAA.BBB.CCC.DDD.EEE.FFF
each line representing the level of the company.
we can see that CompanyID column exists in both tables. and I would like to find out the CompanyName from table B for each companyID that exists in Table A.
but the tricky part is that for a specific CompanyID in tableA I might not have a exact match.
e.g. in A lets say I have AAA.BBB.CCC.DDD.EEE for CompanyID but in table B i might not have AAA.BBB.CCC.DDD.EEE but instead have AAA.BBB or AAA.BBB.CCC or AAA.
so I have to walk up the tree or these levels to look for a match an get the companyName. the match logic is as such.
If in table A companyID = AAA.BBB.CCC.DDD.EEE then look for same value in B if NOT FOUND then
remove the last level from the companyID value from Table A. so new value of CompanyID = AAA.BBB.CCC.DDD
Now look for this new value AAA.BBB.CCC.DDD in table B. IF NOT FOUND then
remove another level from ComapnyID in table A so new companyID = AAA.BBB.CCC and look for
AAA.BBB.CCC in table B. just going up the tree by chopping off a level till I find a match.
now the question is how to do this in TSQL.... can someone help?
So what I really want to do is to look for a match between A and B based on companyID.
I have a report that I'd like to involve in delivering tons of PDFs for each of our customers for a billing cycle. There is a table that the report reads from that gives it all its data necessary for the report (which is a customer bill) and the table also has a column that has the file name for the PDF for that particular customers bill for the report. Basically each table row represents one output report (bill) and each table row has its name nicely formated for me. Writing the report is not a problem. Figuring out how to run through thousands of rows generating a PDF for each with the file name from the table is my challenge.
I would like to create either a SP or a SSIS package (that is scheduled) that can run a report for each line of data in my table and spit out a PDF file to a UNC path. It might have to generate thousands of PDFs to a UNC path that has plenty of space. There will be another SSIS package that moves the PDFs later to their proper directory.
So, is there someone who has done this before? Any suggestions? Is there a quick path to doing this, would it take a ton of time? Any tutorials out there?
Thanks,
Keith
p.s. My preference is to do this in SP's or SSIS and if I need a .NET language to do that in VB.NET, but I'd rather avoid that if I can.
Hi guys,
I have this problem with reporting services wherein my report contains an Area Chart. My plot points is about 100,000 records. The problem is sometimes the Chart is displaying right and sometimes its not. The users need to refresh the report again for the chart to display right. This doesn't happen if my records to plot contains around 10,000 records.
Does RS have a limation in plotting records in an Area Chart? Any solutions?
How 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!
Welcome
i am want to teach sql . how to
install compile
Hi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
I have one procedure that gets executed many times per day(thousands at least). I consistently get blocking in my database, as users compile this stored procedure. How can I keep this from recompiling all the time, and bogging down my database? I tried the keep plan option in the portion of the code that uses tembdb, and that doesn't work. thanks.
View 1 Replies View Related