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 RepliesRunning 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
I have a custom application that on occasion requires thousands of TSQLfiles (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 aconnection to the destination database (currently across the network).
View 5 Replies View RelatedHow do you find stale stored procedures ?
In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.
"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.
Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.
So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?
And could this be done in SS2K8 ?
Can anyone tell me the correct syntax for passing a Table Name to a Stored Procedure that uses the Table Name to do an INSERT into that Table ?
Below is my poor effort at the SQL.
Thanks.
CREATE PROCEDURE InsertOrderLine
@OrderNo INT,
@ProductID VARCHAR(15),
@UnitCost MONEY = 0,
@Quantity FLOAT = 0,
@Discount MONEY = 0,
@Tax MONEY = 0,
@TABLENAME VARCHAR(200)
AS
DECLARE @SQL VARCHAR(1000)
SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo,
ProductID, UnitCost, Quantity, Discount, Tax) VALUES
(@OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity +
',' + @Discount + ',' + @Tax)'
GO
Sorry if I haven't choose appropriate forum for this question.
I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s).
How can I do that or anything that can return parameter names and types?
It's windows application.
Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.
View 5 Replies View RelatedI would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
Does anyone know any good MS SQL or TSQL tutorials? I know the basics of SQL, such as joins, functions such as COUNT, SUM etc so I do not need one of them. Problem is I am using MS SQL at a new role in work and finding it difficult. I write the statements how I would in oracle of mysql but find that they refuse to work, and I can not find/use the MS SQL Help to find what I want.
Cheers, Mike
I am a newbie to tsql. I am trying to find if a job has failed, if so I only want to pickup the lastest job. I want to see name,message and run date. I know I need to find the max run_date and run_time for each job_id. I know that I need to join the sysjobhistory table and the sysjobs tables. I also know that I need status not equal to one. I have tried thousands of different things and nothing seems to work. If I ever get this to work then I only want to look at jobs in the last 24 hours. I am not going to include any of my job since I have tried so many things and I have been all over the map.
Is there a way i can create a query that gives me a list of all the table names in the database?
View 2 Replies View RelatedI'm fairly new to sql.I've got a database that all I want to do is view some records fromsome tables. But the thing is, I do not know the table names ortheir relationships.I've got access to the database thru query analyzer. Can someoneassist me in gettign the correct statements that will allow me to findout what tables exist in the database.
View 4 Replies View RelatedI'm new here so bare with me for alittle bit.
When I go to a website how to I find out the table names in the data?
I mean how can I do "select * from ___" if I don't know the table name.
Thank you for any help,
S_R_S
Hi,
is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable...
The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID...
Thanks,
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns.
Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values.
Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values.
I would appreciate If anybody gives me hint about achieving desired result
Hi there,
Could any one suggest a query which yields the list of tables and columns used in a stored procedures. I know sp_depends has similar functionality, but would like to know the T-SQL code for that..
Madhu
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
I want to be able to have an authorized person set or change the default values of a table column in a SQL Database. I have a stored procedure that sets the default which works fine: ALTER PROCEDURE [dbo].[addMyConst]ASBEGINALTER TABLE [dbo].[tbl1]ADD DEFAULT 70 FOR [Auto_ourlim]END(I still need to put parameters in so that I can run the stored procedure from a form, but for now....)
To change it I know that I have to drop the constraint first like this:
ALTER PROCEDURE [dbo].[dropmyValue]ASALTER TABLE [dbo].[tbl1] DROP CONSTRAINT [Auto_ourlim]
The problem is that when I execute the procedure I get the error that "Auto_ourlim" is not a constraint so it does not drop the Default Value. When I go over to SQL Server Management Studio Express I can see why:
If I open up the table and open up the Constraints the constraint is "DF__tbl1__Auto_ourli__5FB337D6". I could change the DROP CONSTRAINT to this, and that works, but it changes every time I add the new DEFAULT VALUE. I don't know how to get around it. Is there a way to put wildcards around "Auto_our" in DROP CONSTRAINT [Auto_ourlim}? Any suggestions would be welcome...even if there's a totally different way to do it.
What I'm trying to ultimately accomplish is this: Column1 (AutoLimits) would be user insert to the database and then in the database it would MINUS Column2 (Auto_ourlim - set with the default value) = Column3 (Difference - a computed field in the database)
Steve
How can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]
View 3 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 Relatedknow a way to find all stored procedures that use declared or temp tables, i.e
Declare @temptable TABLE as....
Create table #temptable
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Hi guys,
In the query below, I need to insert a new Column that shows the last Document date.
In example,
Document Date Last Date
2006/01/15 2007/11/30
2007/03/25 2007/11/30
2007/11/30 2007/11/30
I need to show the last (max) date for each row.
Thanks in advance for any help,
Aldo.
Code Block
SELECT
Stock.VALUEDATE AS 'Document Date'
FROM
Stock As Stock
INNER JOIN ACCOUNTS AS Accounts
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN EXTRANOTES AS ExtraNotes01 ON Accounts.ACCOUNTKEY = ExtraNotes01.KEF AND ExtraNotes01.NOTEID IN ('17')
LEFT OUTER JOIN EXTRANOTES AS ExtraNotes02 ON Accounts.ACCOUNTKEY = ExtraNotes02.KEF AND ExtraNotes02.NOTEID IN ('18') ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '0' AND '379' AND
Accounts.SORTGROUP Not Between '100' AND '150'
AND Stock.DOCUMENTID In ('1', '4', '5', '11', '78', '95')
AND Stock.STATUS Not In ('0', '2')
AND Accounts.SORTGROUP Between '0' AND '379'
AND Accounts.ACCOUNTKEY Like '313%'
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
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
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,
How to find if a column exists in Database Table ?
thanx in advance
We have a little app to help us move applications from developent --> stage --> production. It uploads the files, runs the new stored procedures etc.
One of the things it does is create new columns in existing tables. In EM you can create a new column in any position, but ALTER TABLE seems to only add columns to the end of the table. Is there a way in TSQL to create columns in arbitrary positions in the table? Or do I have to recreate the whole table?
I am looking for ways to add new column to a table with records inside. If I add the column using this statement:ALTER TABLE Table1 ADD NewColumn1 decimal(18, 2) NULLThe column would appear at the end.
Is there a way to set where the column should be placed? (Excluding dropping all columns and add the columns in sequence again). I know this might not be very important, I am interested in knowing how Enterprise Manager done this, since you can move a column up and down to change their sequence eventhough there're records inside.