Hi all
Does somebody know how to get the code of a stored procedure?
I would like to know the name of the view in the data dictionary
which holds the code of the functions or stored procedures ...
Thanks for any help
Best regards
Frank Uray
Hi ,all here,
Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?
I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.
Will anyone gives me advices for that? Really need help.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
All --
Please help.
I am using managed code in SQL Server 2005 and have a question about stored procedure name qualification.
With a VS.NET 2005 Pro Database project, when I use >Build, >DeploySolution, it works great but my stored procedures are named with qualification using my domainusername.ProcName convention, something like this...
XYZ_TECHKamoskiM.GetData
...when I want them to be named with the dbo.ProcName convention, something like this...
dbo.GetData
...and I cannot see where this can be changed.
Do you happen to know?
Please advise.
(BTW, I have tried going to this setting >Project, >Properties, >Database, >AssemblyOwner, and putting in the value "dbo" but that does not do what I want.)
(BTW, as a workaround, I have simply scripted the procedures, so that I can drop them and then add them back with the right names-- but, that is a bit tedious and wasted effort, IMHO.)
Thank you.
-- Mark Kamoski
I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.
Anyone have a solution?
Thanks in Advance.
oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent) 'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies View RelatedHello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
How to pass a XML data parameter to an SQL 2005 Stored Procedure
I hope to insert a xml data into an typed xml column in SQL 2005.
1. I can run the Code 1 correctly.
2. I hope that I can pass a XML data parameter to an SQL 2005 Stored Procedure, So create the Code 2. but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: Only non-document nodes can be inserted. Found "xs:string ?".
3. I create the Code 3, but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: ',' or ')' expected
4. I create the Code 4, but I get the error below:XQuery: SQL type 'xml' is not supported in XQuery.
//--------------------------Code 1-------------------------------------create procedure Hellocw_InsertBookmark40@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert <x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 1-------------------------------------
//--------------------------Code 2-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 2-------------------------------------
//--------------------------Code 3-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert cast(sql:variable("@Insertxml") as xml)as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 3-------------------------------------
//--------------------------Code 4-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml xmlasupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId
//--------------------------Code 4-------------------------------------
Hi
Can anyone point me in right direction as to how to prepare a DATA DICTIONARY for a
given databse ??.
And what is the usual format of preparing a data dictionary...Any help in this regard is
greatly appreciated.
Thanks
VENU
Hi
can anyone forward me the format to prepare a DATA DICTIONARY For a GIVEN DATABASE. It's Kind off very urgent
Thanks VENU
We have several SQL 6.5 data bases and a few Access Data Bases. We would like to build a data dictionary. Has anyone seen any 3rd party products which might help, or any ideas of how to manipulate the existing objects to do so?
View 1 Replies View RelatedHi
I need to document all the tables & the fields of it & the data type , the PK & FK keys & all that
Can anyone suggest the fastest method to get all the data , or some link on how the format should be
Please help
Is there any tool for data dictionary? Basically there are no
descriptions/comments to many of the columns (about 99%) in the database. Datawarehouse team does not know exactly what those columns are, where its used, how its used. No comments have been defined in the system tables. We would like to have a tool where SME's/Bussiness analysts enter the description for the columns in the production database. We have SQL Server databases, many oracle dbs, few sybase databases, ACCESS. Sometimes we might have to search for strings in the descriptions entered.
Hello,I am working on a project to reverse engineer requirements for adatabase. The database is sitting on a MS SQL Server. How would Iget a listing of all tables and columns within each table to helpcreate a data dictionary.I have developed a script in Oracle to do this, but I am just notfamiliar with MS SQL Server synthax.Thanks,Hitesh
View 6 Replies View RelatedI have the following stored procedure in sql server 2000:
(@nsn varchar 15 int,
@item_nam varchar 255 ouput
)
AS
declare @stockquantity int
select @stockquantity = stockquantity
from inventory
where stockquantity = 0
select @item_nam = item_nam
from inventory where nsn = @nsn
delete from inventory
where nsn = @nsn
and stockquantity = 0
GO
what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equal to zero. Right now it returns the item_nam even if the record wsa not deleted.
ANy help is much appreciated.
I don't know if this is possible. However, what i am attempting to do isusing C#'s window forms. I open up an excell sheet stored in my windowsform. The excel sheet stores names of the stored procedures in thatdatabase. I want to know if it's possible to click on that storedprocedure to open up a link to display the code of that stored procedure ofcourse in a read only mode.any suggestions....
View 2 Replies View Relatedhi all,i need to convert these simple PHP code into stored procedure :<?php$result=mssql_query( "SELECT whid, whcode FROM warehouse" );while( $wh = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT plid, nopl FROM packlist WHERE whid ='" . $wh->whid . "'";while( $pl = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT qty FROM packlistnmat WHERE plid ='" . $pl->plid . "'";while( $pln = mssql_fetch_object( $result ) ){echo "Stock from " . $wh->whcode . " AND Packing List number " .$pl->plid . " = " . $pln->qty;}}}?>my focus is in nested query, then i can call each field from the query(SELECT whid, whcode...) in sub query.thanks,aCe
View 4 Replies View Relatedhelp BUG in my sql code
i can not see this employee 111111 whan i run this code
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
TNX
Code Block
------------------------
-- create mod cycle shift pattern
------------------------
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
-- select * from @shifts_pattern
--- end shift pattern
--------------------------------
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
SELECT 222222,2,'29/01/2008','30/01/2008' UNION ALL
SELECT 333333,3 ,'27/01/2008','30/01/2008' UNION ALL
SELECT 444444,5 ,'26/01/2008','30/01/2008'
--------------------------------
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
--SET
----@StartDate = StartDate
---- last day of next month
--SET
--@EndDate = EndDate
--@StartDate = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1 )
---- last day of next month
--SET
--@EndDate = DATEADD(m ,3,GETDATE()-DAY (GETDATE()) + 1)- 1
--set
--@current = @StartDate
----
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
I need to have my middle tier code get the T-SQL code for a stored procedure. So, I need to create a string something like:
string tsqlCode = @"CREATE PROCEDURE "NSInsertGenerator"
@GeneratorName NVARCHAR(255),
@SystemName NVARCHAR(255),
@ThreadPoolSize INT,
@ActivationState TINYINT
AS
INSERT INTO "NSGenerators"
( GeneratorName, SystemName, ThreadPoolSize, ActivationState)
VALUES(@GeneratorName, @SystemName, @ThreadPoolSize, @ActivationState)
GO"
This string will be analyzed by the middle tier code so that the list of parameters for this procedure will be built dynamically depending on the current version of this stored procedure.
We are using SQL Server 2000 SP4.
Could someone recommend me how I can get T-SQL code of a stored procedure, please?
Thanks a lot!
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I need to build a data dictionary for a SQL 7 DB that already exists that has over 16000 columns. What I really need is to add a comment to the table and column names.We use ERWin and have looked at the Microsoft Repository
but I would like a quick interface to create the descriptions which neither one of these have. I scripted out all the table and column objects into tables and built a simple Access Form to handle the updates. Now I need to
commite these updates to the Microsoft Repository. Has anyone seen or tried something like this?
THanks
I am adding tables to a DB. I want to put table and column descriptions in as I go.
1. Is there a "Description" property for the table? I see in the column properties list a "description".
2. Once I have my descriptions all in, indexes set etc, is there a system stored procedure that I can run that will generate the data dictionay? (With a table description.)
Thanks
Pls check my code for the stored procedure which i created for the companydetails including companyid P.K. Not Null int(4),companyname Not Null varchar (20),address varchar(30) where companyid is the primary key and it should be autogenerate.I also want that it should check if the name exits or not.It should also check that the field is entered and not kept null.If it's null then should return the error message.I want to write the queries select,insert,update and delete in the single stored procedure.How can i differ all the query individually in a stored procedure.The select and insert query are done on the button click event whereas the update,delete queries are performed in the gridview link event. Pls help me and modify my code accordingly with ur suggestions who know
the stored procedure very well.First read what i want then give reply.waiting for the reply and with corrections.The coding is perfomed in sql server 2005 and asp.net with C# 2005, 1 ALTER PROCEDURE CompanyStoredProcedure2 @uspcompanyid int,3 @uspcompanyname varchar(20),4 @uspaddress1 varchar(30), 5 @frmErrorMessage as varchar(256) OUTPUT,6 @RETURNVALUE as int OUTPUT,7 @RETURNID as int OUTPUT8 AS9 declare
10 @companyid int,11 @companyname varchar(20),12 @address1 varchar(30) 13 14 BEGIN15 16 begin17 Select @RETURNVALUE = -918 RETURN -919 end20 21 begin22 Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
23 return -924 end25 26 27 28 begin 29 --validation...
30 if (@uspcompanyname is Null or @uspcompanyname = '')31 begin32 Select @RETURNVALUE = -933 select @frmErrorMessage = 'Company Name is empty'
34 return -935 end 36
37 if exists (select companyid from companymaster 38 where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))39 begin40 select @companyid = companyid from companymaster 41 where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )42 end43 else 44 45 select @companyname= cast (@uspcompanynameas varchar(20))46 select @address1= cast(@uspaddress1 as varchar(30))47 select @companyid = isnull(max(companyid),0) + 1 from companymaster48 49 IF exists(SELECT * from companymaster where companyname=@companyname)50 begin51 Select @frmErrorMessage = 'Record With Company Name ' 52 + @companyname + ' is Already Exisiting For The Company Name ' 53 return -954 end 55 56 -- the following codes inserts
57 begin transaction58 INSERT INTO companymaster59 ( companyname, address1)60 VALUES (@companyname,@address1)61 commit transaction62 63 select @RETURNVALUE = 064 select @RETURNID = @companyid65 66 end67 68 69 -- the following codes edit/updates
70 begin71 UPDATE companymaster 72 SET companyname=@companyname,73 address1=@address1 74 WHERE companyid =cast(@uspcompanyid as int)75 76 select @RETURNVALUE = 077 select @RETURNID = cast(@uspcompanyid as int)78 end79 -- the following codes delete
80 begin81 DELETE companymaster WHERE (companyid = @companyid)82 end 83 84 END 85
Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies View RelatedHi,
I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.
It seems I must use switch clrafe for my executable to deploy to sql.
But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!
But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!
Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?
Hi,
I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.
I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)
I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.
Here is my SP
ALTER PROCEDURE uspSelectBarItemID2
(
@BarTabID INT,
@DrinkID INT,
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID
FROM [Bar Items]
WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.
What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.
Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.
Cheers.
I have a data dictionay table with these columns
field_name
field_type
field_size
nulls
#1
#2
#3
column1
char
20
y
1
0
1
column2
char
30
y
1
1
1
column3
int
5
n
0
1
1
How to create a table using the content of the dd table above as the structure of my new table?
cb
Can someone show me how to use SqlDataSource to call stored procedure in a c# class (a support class for aspx) and not in aspx or code-behind?
View 20 Replies View Relatedhi all
I have to execute the stored procedure from code file
string constr = ConfigurationSettings.AppSettings["ConnectionString"];SqlConnection con = new SqlConnection(constr);con.Open();SqlCommand cmd = new SqlCommand("GetTax",con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter paramFrom = new SqlParameter("@from", SqlDbType.VarChar, 50);paramFrom.Value = "JFK";SqlParameter paramTo = new SqlParameter("@To", SqlDbType.VarChar,50);paramTo.Value = "HOU";SqlParameter paramAirline = new SqlParameter("@Airline", SqlDbType.VarChar,50);paramAirline.Value = "US";SqlParameter rpTax = new SqlParameter("@Tax",SqlDbType.Int);rpTax.Direction = ParameterDirection.Output;cmd.Parameters.Add(rpTax);
insted of this way can i execute stored procedure any other way
like
exec MystoredProc "param1"."param2","param3"
i appreciate u r help
Hi all, I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code: // Define data objects SqlConnection conn; SqlCommand comm; // Initialize connection string connectionString = ConfigurationManager.ConnectionStrings[ "pay"].ConnectionString; // Initialize connection conn = new SqlConnection(connectionString); // Create command comm = new SqlCommand("UpdatePaymentDetails", conn); //comm.CommandType = CommandType.StoredProcedure; // Add command parameters comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int); comm.Parameters["PaymentID"].Value = paymentID; comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewPayment"].Value = newPayment; comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewInvoice"].Value = newInvoice; comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money); comm.Parameters["NewAmount"].Value = newAmount; comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewMargin"].Value = newMargin; comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money); comm.Parameters["NewProfit"].Value = newProfit; comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime); comm.Parameters["NewEditDate"].Value = newEditDate; comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewQStatus"].Value = newQStatus; comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewStatus"].Value = newStatus; // Enclose database code in Try-Catch-Finally try { conn.Open(); comm.ExecuteNonQuery(); }
View 7 Replies View RelatedHi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code?
---------------------
Here is part of the cursor I have created.
IF @@FETCH_STATUS = 0
BEGIN
IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1
IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1
IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1
FETCH curs_Briefs INTO @txtPrintSuit
END
------------
How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?
THanks in advance
How to Decrypt the SQL Encrypted Stored procedure in ASP.NET (i.e.vb code)Pls let me know ASAP.Thanks.
View 1 Replies View Related