Error In Filling A Table With A Stored Procedure

Jan 4, 2007

When I run the code below, I get
a table with "In order to evaluate an indexed property, the property
must be qualified and the arguments must be explicitly supplied by the
user." as the contents...
 

Public Function ReturnTable(ByVal strName As String, ByVal alParameters As ArrayList, ByVal strTable As String) As DataSet

        Dim sqlSP As New SqlCommand

        sqlSP.CommandTimeout = 120

        sqlSP.Connection = sqlConn

        sqlSP.CommandType = CommandType.StoredProcedure

        sqlSP.CommandText = strName



        AddParameters(sqlSP.Parameters, alParameters)



        Dim dsDataSet As New DataSet()



        sqlConn.Open()



        Dim sqlDataAdapter As New SqlDataAdapter(sqlSP)

        sqlDataAdapter.Fill(dsDataSet, strTable)

        sqlConn.Close()



        Return dsDataSet



    End Function



 AddParameters is a function which adds each of the parameters in the
array list to the sqlSP parameter collection... In SQL Profiler, the sp
is called and runs and returns results... But a dataset with one table
and the above msg is returned...
 

View 2 Replies


ADVERTISEMENT

Filling SQLDataReader With Stored Procedure Recordset

Jun 7, 2007

Hi All,
 I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read.
   Dim CurrentUser As String, Pos1 As Int16, EmployeeId As Int32
Dim cn As New System.Data.SqlClient.SqlConnection
Dim param As New System.Data.SqlClient.SqlParameter
Dim reader As System.Data.SqlClient.SqlDataReader
Dim cmd As New System.Data.SqlClient.SqlCommand

CurrentUser = CStr(User.Identity.Name)
Pos1 = InStr(CurrentUser, "") + 1
CurrentUser = Mid(CurrentUser, Pos1)
Session("User") = CurrentUser
Session("CID") = Nothing

cn.ConnectionString = "Data Source=LAPTOP-4SQLEXPRESS;Initial Catalog=SCMdb;Integrated Security=True"
cn.Open()

cmd.Connection = cn
cmd.CommandText = "CurrentUser"
cmd.CommandType = CommandType.StoredProcedure

param = cmd.CreateParameter
param.ParameterName = "@UserName"
param.SqlDbType = SqlDbType.VarChar
param.Value = CurrentUser

cmd.Parameters.Add(param)
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
EmployeeId = reader.Item("EmployeeID")
reader.Close()
 Any help would be much appricated this is driving me mad.
Thank You
Shaft

View 5 Replies View Related

Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz

Apr 24, 2008

My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.


main.ds.MailsSignature.Clear();

main.ds.MailsSignature.AcceptChanges();


string[] signFiles = Directory.GetFiles(Settings.signDirectory);


foreach (string signFile in signFiles)

{


mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();

mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.

main.ds.MailsSignature.Rows.Add(mailsSignatureRow);

}


mailsSignatureTableAdapter.Update(main.ds.MailsSignature);

But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.


ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )

AS

SET NOCOUNT OFF;

INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);



SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())

For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.

Is there any limitation in CF?

Regards,
Professor Corrie.

View 3 Replies View Related

Passing Table Name In Stored Procedure Got Error

May 13, 2008

ALTER PROCEDURE [dbo].[sp_STATEWLEVEL_DAILY]
@STATE varchar(50),@TBLNAME varchar(50)

AS
BEGIN TRANSACTION -- Start the transaction
TRUNCATE TABLE @TBLNAME;
SELECT
t1.Date_Taken as 'DATE', t1.Time as 'TIME',
t1.Main_ID as 'MAIN_ID', t1.WATER_ULEVEL as 'WATER_ULEVEL'
FROM dbo.SEL t1 INNER JOIN dbo.station_info t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.STATE=@STATE
AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time

-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT -- Success! Commit the transaction

Error said Incorrect syntax near '@TBLNAME'.

View 8 Replies View Related

Error In Stored Procedure While Working With Temp. Table

May 31, 2007

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
 
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
 
please let me know the solurion of this error. 
 

View 2 Replies View Related

#Table Not Found Error Using Stored Procedure For DataSet

Aug 29, 2007

Hi all

I have a procedure where I am inserting some elements into #Table and then finally get the datset I need.
Now when I am using this procedure as dataset to my report, it throws up the following error:





Invalid object Name "#TEMP2".

The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1

Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?

I am not sure should I posting it here or in T-SQL Forum.








View 17 Replies View Related

Creating A Table Through Stored Procedure Shows An Syntax Error

May 26, 2000

hai guys,
i have written a stored procedure which creates a table ex:
USE PUBS
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC')
DROP PROCEDURE RC_STRPROC
GO
USE PUBS
GO
CREATE PROCEDURE RC_STRPROC
(@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35))
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME')
DROP TABLE @TBLNAME
CREATE TABLE @TBLNAME
(@COLVAL1, @COLVAL2)
GO
it gives an syntax error at '@tblname'
can u guys tell me the problem

thanks
hiss

View 2 Replies View Related

Filling A Table In Sqlserver2005

Apr 9, 2008

hello everyone
i have created a table in sqlserver2005  named "Departments" - in this table different departments of a telephone ( landline ) company are to be stored,which deals with complaints registered to them by there users.
i want to know the name of these different departments which deals with complaints assigned to them like if i do have complaint from a user who has problem with his handset then that complaint will be assigned to "Maintance dept."
as i was never in indusrty , i need the help in filling the table.
just do write me name of departments and the nature of complaints wh they deal with!!!
thanks for the consideration

View 1 Replies View Related

Recordset Error While Filling The Listbox

Jun 9, 2004

Hello all
I have shifted my vb/access database to vb/mysql and i have one form in my project in which i want to display all the records in the database in the list box . But while doing this i m getting the error " variable uses an automation type not supported in visual basic " . Moreover, it was working in Vb/access .

here is the code


Dim sql As String
intCountSW_ID = 0
sql = "select SW_IDEN, SW_NAME, SW_DELETE,SW_LEFTDATE from SV_SOCIALWORKER order by SW_NAME"
If rs.State = 1 Then rs.Close


Set connString = New ADODB.Connection

connString.CursorLocation = adUseClient
connString.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=;" _
& "DATABASE=shrivatsa;" _
& "UID=root;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
connString.Open

rs.ActiveConnection = connString
rs.CursorLocation = adUseClient


rs.Open sql, connString, adOpenStatic, adLockOptimistic



intCountSW_ID = rs.RecordCount


If intCountSW_ID > 0 Then
rs.MoveFirst
While Not rs.EOF
/* here comes the error on the below code

If rs("SW_DELETE") = 0 And IsNull(rs("SW_LEFTDATE"))

*/
Then
lstCaseName.AddItem rs("SW_IDEN") & " " & rs("SW_NAME")
End If
rs.MoveNext
Wend
rs.Close
End If
mfuncFillList = intCountSW_ID

View 1 Replies View Related

ConstraintException Error While Filling Dataset?

Nov 15, 2006

Hi,

  I always get a "ConstraintException" error when trying, at beginning of application, to run following statement (within "Form1_Load" routine called by "this->Load" EventHandler):

"this->TauxTaxeTableAdapter->FillByPays(this->TauxTaxeDataSet->TauxTaxe,Cur_GrdView_SlPays);"

  Also curiously if, while application is still running, I invoke again that same statement by means of a pushbutton clickevent,  everything is running smootly without error...  Looks to me that it is bugging only when running the first time...

  Within Dataset, I tried to see what constraint could give me such trouble.  Here is the only constraint I could find:

"this->Constraints->Add((gcnew System::Data::UniqueConstraint(L"Constraint1", gcnew cli::array< System::Data::DataColumn^ >(2) {this->columnPays, this->columnProvince_Etat}, true)));" 

  I then tried to find within "TauxTaxe" table if there could be any trace of records where "Pays" and "Province_Etat" columns would show any null value as well as any duplicate key values but there wasn't any... 

  Any place I should start to look for?  BTW, I'm using a SQL Express database.

Thanks for your help,

Stéphane

Here is the detailed error log I got:

System.Data.ConstraintException: Impossible d'activer les contraintes. Une ou plusieurs lignes contiennent des valeurs qui violent les contraintes de type non null, unique ou de clé externe.
   Ã System.Data.DataSet.FailedEnableConstraints()
   Ã System.Data.DataSet.EnableConstraints()
   Ã System.Data.DataSet.set_EnforceConstraints(Boolean value)
   Ã System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   Ã System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   Ã System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   Ã System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   Ã Test_ADO.DS_TauxTaxeTableAdapters.TA_TauxTaxe.FillByPays(TauxTaxeDataTable dataTable, String Pays) dans d:projetvisual c++ 2005projets c++cli est_adods_tauxtaxe.h:ligne 1247
   Ã Test_ADO.Form1.Sel_Pays_NewSel(Object sender, EventArgs e) dans d:projetvisual c++ 2005projets c++cli est_adoform1.h:ligne 1887
   Ã System.Windows.Forms.RadioButton.OnCheckedChanged(EventArgs e)
   Ã System.Windows.Forms.RadioButton.set_Checked(Boolean value)
   Ã Test_ADO.Form1.Init_Form1() dans d:projetvisual c++ 2005projets c++cli est_adoform1.h:ligne 1480
   Ã Test_ADO.Form1.Form1_Load(Object sender, EventArgs e) dans d:projetvisual c++ 2005projets c++cli est_adoform1.h:ligne 1446
   Ã System.Windows.Forms.Form.OnLoad(EventArgs e)
   Ã System.Windows.Forms.Form.OnCreateControl()
   Ã System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   Ã System.Windows.Forms.Control.CreateControl()
   Ã System.Windows.Forms.Control.WmShowWindow(Message& m)
   Ã System.Windows.Forms.Control.WndProc(Message& m)
   Ã System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   Ã System.Windows.Forms.ContainerControl.WndProc(Message& m)
   Ã System.Windows.Forms.Form.WmShowWindow(Message& m)
   Ã System.Windows.Forms.Form.WndProc(Message& m)
   Ã System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   Ã System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   Ã System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Finally, SQL Statement for "TA_TauxTaxe::FillByPays" is the following:

SELECT        Pays, Province_Etat, Taxe1_Appl, Taxe1_Dsc, Taxe1_Taux, Taxe2_Appl, Taxe2_Dsc, Taxe2_Taux
FROM            TauxTaxe
WHERE        (Pays = @Pays)

In debug mode, I double-checked and could verify that @Pays didn't have any null value but a valid string value at time "Fill" routine was invoked.  Any clue?

View 3 Replies View Related

Filling Database Table Randomly

May 18, 2006

Hi there I need to fill database table randomly with 5 ordered numbers such as 43566 , 78578 , 92565 , .. to gain approximately 100000 row of a table . Is there a query ?

View 7 Replies View Related

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

Jan 19, 2007

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View 9 Replies View Related

Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code

Nov 6, 2007

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)


-----------------------------------------------------------------------------------------------------------------

View 4 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Using Table Name Stored In A Scalar Variable In Stored Procedure Problem

Mar 27, 2006

Hello to all!

I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:

For example:

declare @countRows int

set @countRows = (select count(*) from MyTable)

The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:

declare @countRows int

set @countRows = (select count(*) from @myTableName)

I also tried this:

declare @sqlQuery varchar(100)

set @sqlQuery = 'select count(*) from ' + @myTableName

set @countRows = exec(@sqlQuery)

But it looks like function exec() does not return any value...

Any idea how to solve this problem?

Thanx,

Ziga

View 3 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View 2 Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

How To Stored A Stored Procedure's Results Into A Table

Jul 23, 2005

Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Stored Procedure For Insert Data From One Table To Another Table

Apr 25, 2006

Hi,
     I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
 

View 1 Replies View Related

Stored Procedure Append All From Table A To Table B

Jan 29, 2013

I've never used a stored procedure before - let alone created one. how to append records from table A to table B.

View 1 Replies View Related

Stored Procedure And Error

Oct 17, 2006

Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks

View 5 Replies View Related

I Have An Error In My Stored Procedure

Jul 29, 2007

hi,
i want to take the first n values from my Categorii table, here is my stored procedure:
SELECT ROW_NUMBER() OVER (ORDER BY CategoryID) AS RowNumber, CategoryID, Name, Description FROM Categorii
WHERE DepartamentID = @DepartamentID AND RowNumber <= 5
i get the error: invalid column name RowNumber
why? what should i do? if i execute the procedure without the AND RowNumber <= 5 i get the RowNumber values 1 to 9 (that means it works)...but what should i do to retrive only the first n?
thank you

View 2 Replies View Related

Error In Sql Stored Procedure

Aug 17, 2007

I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks. ALTER procedure [dbo].[USP_Account_Search_Mod]
@ClientCode VARCHAR(7) = ''
,@DebtorName VARCHAR(25) = '',@DebtorNumber INT = 0
,@AccountNumber VARCHAR(30) = ''
,@ReferenceNumber VARCHAR(30) = '',@Tier INT = 0
,@Status VARCHAR(5) = ''
,@UserID INT
,@Month DateTime = NULL
,@FromDate DateTime = NULL
,@ToDate DateTime = NULL,@OriginalMin decimal = 0
,@OriginalMax decimal = 0,@CurrentMin decimal = 0
,@CurrentMax decimal =0
,@lstAmountSelect VARCHAR(3),@IsActive bit = 1
 
 
ASDECLARE
@SQLTier1Select VARCHAR(2000)
,@SQLTier2Select VARCHAR(2000)
,@Criteria VARCHAR(2000)
,@SQL VARCHAR(8000)
,@CRI1 VARCHAR(100)
,@CRI2 VARCHAR(100)
,@CRI3 VARCHAR(100)
,@CRI4 VARCHAR(100)
,@CRI5 VARCHAR(100)
,@CRI6 VARCHAR(200)
,@CRI7 VARCHAR(500)
,@CRI8 VARCHAR(500)
,@CRI9 VARCHAR(500)
SELECT @CRI1 = ''
,@CRI2 = ''
,@CRI3 = ''
,@CRI4 = ''
,@CRI5 = ''
,@CRI6 = ''
,@CRI7 = ''
,@CRI8=''
,@CRI9=''
,@Criteria = ''
SELECT @DebtorName = REPLACE(@DebtorName,'''','''''');
Print @DebtorName
if(SELECT UserTypeID FROM dbo.tbl_Security_Users Where UserID = @UserID) = 3 AND @ClientCode = ''
                return (-1)IF LEN(@DebtorName) > 0
SET @CRI1 = ' AND Name like ' + '''%' + @DebtorName + '%'''IF @DebtorNumber > 0
SET @CRI2 = ' AND Number = ' + CAST(@DebtorNumber AS VARCHAR(7))IF LEN(@AccountNumber) > 1
SET @CRI3 = ' AND AccountNumber like ' + '''%' + @AccountNumber + '%'''IF LEN(@ReferenceNumber) > 0
SET @CRI4 = ' AND Account like ' + '''%' + @ReferenceNumber + '%'''IF LEN(@ClientCode) > 1
SET @CRI5 = ' AND Customer = ' + '''' + @ClientCode + ''''
SET @Status = RTRIM(@Status)
IF ((@Status Not IN ('ALL','ALA','ALI')) AND (LEN(@Status)>1))
BEGIN
 
IF(@Status = 'PAID')
SET @CRI6 = ''
IF(@Status = 'CANC')
SET @CRI6 = ' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'
 
END
--PRINt @CRI6IF LEN(CONVERT(CHAR(8), @Month, 112)) > 0
BEGIN
 
IF(LEN(CONVERT(CHAR(8), @FromDate, 112)) > 0 AND LEN(CONVERT(CHAR(8), @ToDate, 112)) > 0 )
BEGIN
SET @CRI7 = ' AND Received BETWEEN ' + '''' + CONVERT(CHAR(8), @FromDate, 112)+ '''' + ' AND ' + '''' + CONVERT(CHAR(8), @ToDate, 112) +''''END
ELSEBEGIN SET @CRI7 = ' AND DATEPART(mm, Received) = DATEPART(mm, ' + '''' + CONVERT(CHAR(8), @Month, 112) + '''' + ') AND DATEPART(yy, Received) = DATEPART(yy, ' + '''' + CONVERT(CHAR(8), @Month, 112) + ''''
 
END END
IF @lstAmountSelect='ALL'
SET @CRI8=''
else IF @lstAmountSelect = 'DR'
BEGIN
SET @CRI8=' AND OriginalBalance >= '+ convert(Varchar,@OriginalMin) + 'AND OriginalBalance<=' + convert(Varchar,@OriginalMax)+' AND CurrentBalance >= '+ convert(Varchar,@CurrentMin) + 'AND CurrentBalance<=' +convert(Varchar,@CurrentMax)
END
ELSE IF @lstAmountSelect = 'OLC'
BEGIN
SET @CRI8=' AND OriginalBalance < CurrentBalance '
END
ELSE IF @lstAmountSelect = 'OGC'
BEGIN
SET @CRI8=' AND OriginalBalance > CurrentBalance '
END
ELSE IF @lstAmountSelect = 'OEC'
BEGIN
SET @CRI8=' AND OriginalBalance = CurrentBalance '
END
 SELECT @Criteria = @CRI1 + @CRI2 + @CRI3 + @CRI4 + @CRI5 + @CRI6 + @CRI7 + @CRI8
 
--PRINT @Criteria
--PRINT @CRI7
if @Status = 'ALL' OR @Status = 'ALA' OR @Status = 'ALI' --All Period
BEGIN
if(@Status = 'ALL') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'
 
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'
END
if(@Status = 'ALA') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'
 
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'
END
if(@Status = 'ALI') --All Inactive
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'
 
SELECT @SQLTier2Select = 'SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'
ENDEND
ELSE IF @Status = 'PAID'
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'END
ELSE
BEGINSELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria END
 SELECT @SQL = CASE @Tier
WHEN 0 THEN @SQLTier1Select + ' UNION ' + @SQLTier2Select + 'ORDER BY NAME ASC' WHEN 1 THEN @SQLTier1Select + 'ORDER BY NAME ASC'
WHEN 2 THEN @SQLTier2Select + 'ORDER BY NAME ASC 'END
PRINT @SQL
--SELECT @SQL
EXEC (@SQL)

View 5 Replies View Related

Error In Stored Procedure

Sep 6, 2007

When I'm trying to execute my stored procedure I'm getting the following code  Line 35: Incorrect syntax near '@SQL'.
Here is my procedure. Could someone tell me what mistake I'm doing.Alter procedure [dbo].[USP_SearchUsersCustomers_New]
@UserID INT
,@RepName VARCHAR(50)
,@dlStatus VARCHAR(5) = ''
as
Declare
@Criteria VARCHAR(500)
,@SQL VARCHAR(8000)
 
SELECT @Criteria = ''SET NOCOUNT ON
if (@dlStatus <>'ALL' AND (LEN(@dlStatus)>1))
BEGIN
if(@dlStatus='ALA')
SET @Criteria='AND dbo.tbl_Security_Users.IsActive=1'
else
SET @Criteria='AND dbo.tbl_Security_Users.IsActive=0'
END
--If the user is an Admin, select from all users.
if(dbo.UDF_GetUsersRole(@UserID) = 1)
BEGIN@SQL = 'SELECT U.UserID
--,U.RoleID
,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR
INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleIDWhere UR.UserID = U.UserID), 'Unassigned') as 'RoleName'
,U.UserName
,U.Name
,U.Email
,U.IsActive
,U.Phone
FROM dbo.tbl_Security_Users U
--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID
WHERE U.NAME LIKE @RepName AND U.UserTypeID < 3'+ @Criteria
 
END

View 6 Replies View Related

Error Im My SQL Stored Procedure

Jan 29, 2008

Hi,I want to use a variable to put a value in a table but it doesn't seems to works. How can i do that? I have bolded and underlined the text that i think is not correct.What syntax can i use to make it work?Thanks----------------------------------------------------------------------dbo._UpdateImage(@ID int,@ImageID int)
ASBegin
Declare @PhotosThumb nvarchar(50)Declare @Photos nvarchar(50)
SET @PhotosThumb = 'PhotosThumb' + convert(nvarchar, @ImageID)SET @Photos = 'Photos' + convert(nvarchar, @ImageID)
SET NOCOUNT ON
IF @ImageID = 1
UPDATE PhotosSET @PhotosThumb = 'Logo_thumb.gif',@Photos = 'Logo320x240.gif'WHERE ID = @ID
ELSE
UPDATE PhotosSET @PhotosThumb = NULL,@Photos = NULLWHERE ID = @ID
SET NOCOUNT OFFEND

View 6 Replies View Related

Error SQL Stored Procedure

Feb 12, 2008

Yo people, got a little problem with this stored procedure, i go to save it and it kicks out these errors:
Incorrect syntax near the keyword 'Drop'.Incorrect syntax near 'Go'.Incorrect syntax near 'Go'.'CREATE/ALTER PROCEDURE' must be the first statement in the query batch
I dont no about this sort of stuff so a good break down of what wrong would be good, below is the whole procedure.
CREATE PROCEDURE dbo.SQLDataSource1
Drop Table PersonGo
 Create Table Person
(PersonID Int Identity,
PersonEmail Varchar(255),PersonName Varchar(255),
PersonSex Char(1),PersonDOB DateTime,
PersonImage Image,PersonImageType Varchar(255)
)
 
 
 
 Drop Proc sp_person_isp
Go
 Create Proc sp_person_isp
@PersonEmail Varchar(255),@PersonName Varchar(255),
@PersonSex Char(1),@PersonDOB DateTime,
@PersonImage Image,
@PersonImageType Varchar(255)
As
BeginInsert into Person
(PersonEmail, PersonName, PersonSex,
PersonDOB, PersonImage, PersonImageType)
Values
(@PersonEmail, @PersonName, @PersonSex,
@PersonDOB, @PersonImage, @PersonImageType)
End
 
Go
 

View 4 Replies View Related

Error With Stored Procedure

Nov 4, 2003

I have the following code in my code behind page:

Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim CM As New SqlCommand("spCCF_CrossTab", CN)
CM.CommandType = CommandType.StoredProcedure
CM.Parameters.Add(New SqlParameter("@LocationID", "CCFIF"))
CM.Parameters.Add(New SqlParameter("@BeginDate", dtbStart.Text))
CM.Parameters.Add(New SqlParameter("@EndDate", dtbEnd.Text))
CN.Open()
DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
dgReport.DataSource = DR
dgReport.DataBind()

A SQL exception is thrown: Incorrect syntax near the keyword 'END'

But I turned on tracing in Enterprise Manager, the following request is sent to SQL:
exec spCCF_CrossTab @LocationID = N'CCFIF', @BeginDate = N'11/3/2003', @EndDate = N'11/4/2003'
In query analyzer the above line executes without error and returns the expected information.

My stored procedure is:

CREATE PROCEDURE spCCF_CrossTab
@LocationID varchar(10),
@BeginDate varchar(10),
@EndDate varchar(10)
AS

declare @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100), @where varchar(1000)

select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action'
select @sumfunc= 'COUNT(ActionID)'
select @pivot='UserName'
select @table= 'UserInfo'
select @where='(dbo.UserInfo.LocationID = ''' + @LocationID + ''' and dbo.UserInfo.Inactive<>1 )'


DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null and ' + @where)

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=(CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END)
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
GO



I've been banging my head on this for quite some time now, any insight someone might have as to the problem would be greatly appreciated! Thanks!

View 19 Replies View Related

Stored Procedure Error -- Please Help

May 27, 2004

I am trying to get a returned value from the stored procedure below
CREATE PROC insert_and_return_id
(
@parameter1 varchar,
@parameter2 varchar

)
AS
DECLARE @newID int
SELECT @newID = 0
INSERT INTO tbltest (field1, field2)
VALUES (@parameter1, @parameter2)
IF(@@ROWCOUNT > 0)
BEGIN
SELECT @newID = @@IDENTITY
END
RETURN @newID
GO
___________________________
My asp Code looks like this
___________________________
Function InserTest(value1, value2)
Dim objConn, objRs, objCmd
' Create a connection to the database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "DSN=" & CONNECTION_STRING
' Create the query command
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "insert_and_return_id"
objCmd.CommandType = adCmdStoredProc
' Create the parameter for output and returned valueand populate it
objCmd.Parameters.Append objCmd.CreateParameter("parameter1", adVarChar, adParamInput, 255, value1)
objCmd.Parameters.Append objCmd.CreateParameter("parameter2", adVarChar, adParamInput, 255, value2)
objCmd.Parameters.Append objCmd.CreateParameter("newID", adInteger, adParamReturnValue, 4)

objCmd.Execute objCmd0
response.write objCmd.Parameters("newID")
'objCmd.Close
End Function

And I get the following ASP Error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function insert_and_return_id has too many arguments specified.
/netwasp/tester.asp, line 62

I only just started to use sp's hence it might be something really simple, Can anyone help, cheers?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved