Sproc Create View And Parameter Problem
Aug 21, 2006
In the below sproc I want to create a view based on the incoming parameter, which is a date passed on from VB in the format 01-01-2006.
Yet getting that to work, seems quite a challenge.
I've been dabbling a bit with the sproc and QA, passing the date parameter to it in varous forms, using both single, double and triple quotationmarks and changing the parameter type to datetime.
I either wind up with a syntax error " Incorrect syntax near '01' " or " Unclosed quotation mark before the character string ".
Anyone have a guess at how to fix this issue?
Cheers, Trinsan
REATE PROCEDURE KONTROL_DRdobbeltrapportering
@rapdato as varchar
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[KONTROL_vw_DRdobbeltrappbasis]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [KONTROL_vw_DRdobbeltrappbasis]
EXEC('
CREATE VIEW KONTROL_vw_DRdobbeltrappbasis
AS
SELECT Gramex_DW.dbo.Programhoved.[Rettet af DR],
Gramex_DW.dbo.Programlinie.Stationskode,
Gramex_DW.dbo.Programlinie.Udsendelsesdato,
Gramex_DW.dbo.Programlinie.[Udsendelses starttid],
Gramex_DW.dbo.Programlinie.Rapporteringsdato, Gramex_DW.dbo.Programlinie.Mediekode,
Gramex_DW.dbo.Programlinie.MUSANummer, Replace(Left(convert(varchar, [Gramex-ID]),11),''.'','''') AS GramexID,
Gramex_DW.dbo.Programlinie.Side, Gramex_DW.dbo.Programlinie.Tracknummer,
Gramex_DW.dbo.Programlinie.ISRC, Gramex_DW.dbo.Programlinie.Producentlandekode,
Gramex_DW.dbo.Programlinie.Spilletid, Gramex_DW.dbo.Programlinie.Minuttakst,
Gramex_DW.dbo.Programlinie.[Afregnet Station],
Gramex_DW.dbo.Programlinie.[Afregnet den],
Gramex_DW.dbo.Programlinie.[Afregnings ID], Gramex_DW.dbo.Programlinie.[Ur-opførelse],
Gramex_DW.dbo.Programlinie.Talestof, Gramex_DW.dbo.Programlinie.[Betalt af DR],
Gramex_DW.dbo.Programlinie.Kending, Gramex_DW.dbo.Programlinie.[Oprettelses ID],
Gramex_DW.dbo.Programlinie.[Oprettet den], Gramex_DW.dbo.Programlinie.[Oprettet af],
Gramex_DW.dbo.Programlinie.[Ændret den], Gramex_DW.dbo.Programlinie.[Ændret af],
Gramex_DW.dbo.Programlinie.[Eventuel delskæring], Gramex_DW.dbo.Programlinie.Plademærkenavn,
Gramex_DW.dbo.Programlinie.Katalogbetegnelse, Gramex_DW.dbo.Programlinie.Tracktitel,
Gramex_DW.dbo.Programlinie.Selskabsnummer, Gramex_DW.dbo.Programlinie.Indspilningsår,
Gramex_DW.dbo.Programlinie.Fonogramtitel, Gramex_DW.dbo.Programlinie.Plademærkenummer,
Gramex_DW.dbo.Programlinie.[Antal opførelser], Gramex_DW.dbo.Programlinie.[Rec nummer],
Gramex_DW.dbo.Programlinie.Indslagsnummer, Gramex_DW.dbo.Programlinie.[Afspillet gange],
Gramex_DW.dbo.Programlinie.[Afspillet minutter], Gramex_DW.dbo.Programlinie.Spilletidsløbenummer,
Gramex_DW.dbo.Programlinie.Pointtildelt, Gramex_DW.dbo.Programlinie.[Rettet af Gramex],
Gramex_DW.dbo.Programlinie.[Pgmkorlinie løbenummer], Gramex_DW.dbo.Programlinie.[Tape Kilde],
Gramex_DW.dbo.Programlinie.[Hoved artist], Gramex_DW.dbo.Programhoved.Produktionsnummer
FROM Gramex_DW.dbo.Programlinie
INNER JOIN Gramex_DW.dbo.Programhoved
ON (Gramex_DW.dbo.Programlinie.Stationskode = Gramex_DW.dbo.Programhoved.Stationskode)
AND (Gramex_DW.dbo.Programlinie.Udsendelsesdato = Gramex_DW.dbo.Programhoved.Udsendelsesdato)
AND (Gramex_DW.dbo.Programlinie.[Udsendelses starttid] = Gramex_DW.dbo.Programhoved.[Udesendelses starttid])
WHERE Gramex_DW.dbo.Programlinie.Rapporteringsdato >= ' + @rapdato + '
AND (Gramex_DW.dbo.Programlinie.Mediekode <> 1) AND
(Gramex_DW.dbo.Programlinie.Spilletid >= ''00:01:00'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2'') AND
(Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 ZULU'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 CHARLIE''')
GO
View 2 Replies
ADVERTISEMENT
Dec 16, 2003
I have a SPROC that uses various VIEWs (passed into the SPROC from an ASP.NET dropdownlist), but I'm getting "Invalid Syntax near 'View name' " errors from the browser (IE) when I execute the dropdownlist.
It appears to me that it should work. The VIEWs run fine by themselves.
When I run the SPROC in Query Analyzer, I get the following error
Server: Msg 201, Level 16, State 4, Procedure Company_List_by_Filter, Line 0
Procedure 'Company_List_by_Filter' expects parameter '@Filter', which was not supplied.
I completely removed the control and rebuilt it (Using VisualStudio.NET 2003), but still have the same error.
VS builds the code without error.
Here's the code for the event handler:
private void Select_Filter_SelectedIndexChanged(object sender, System.EventArgs e)
{
SqlConnection myConnection = new SqlConnection("server=AMD;database=imSMART;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("Company_List_by_Filter", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar, 150));
myCommand.SelectCommand.Parameters["@Filter"].Value = Select_Filter.SelectedValue;
//Arbitrary table name Company_List
DataSet ds = new DataSet();
myCommand.Fill(ds, "Company_List");
Select_Company.DataSource=ds.Tables["Company_List"].DefaultView;
Select_Company.DataBind();
}
Somehow the @Filter variable is not properly getting passed by the SPROC to the VIEW.
Anyone have any ideas why this is happening?
Thanks,
Paul
View 1 Replies
View Related
Jun 15, 2001
Hi guys,
I am trying to create a view through SP
as follows, but I am getting as error as Invalid syntax near view.
Please let me know where I am doing wrong.
Thanks,Rau
CREATE PROCEDURE PurgeReport
@intJob as int
AS
if @intjob=1
begin
CREATE VIEW Purge AS SELECT Btch_id AS Batch_ID, DLN, Process,End_DLN, Job,
Tcode
FROM Batch_HDR
end
View 2 Replies
View Related
May 9, 2005
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
View 1 Replies
View Related
Aug 24, 2006
This code works EXACTLY as it should on a development server that has a Framework 1.1 hotfix (operating in the 'localhost' webserver environment) but not on a production server (in a live www. web app) that does NOT have the 1.1 hotfix. Is there a code problem (if so, why should it work in the localhost) or is the hotfix on the development server needed on the production server.
(in a button sub that does an update)...Dim currentUser As New AccBusiness.User( _ CType(Context.User, SitePrincipal)) Dim DDName As String = currentUser.DDName Dim NosData As String Dim AccountDetails As New SecureNums(txtDDPhone.Text, txtDDAddress.Text) NosData = AccountDetails.EncryptedData() AccountDetails.UpdateAcctDetails()----------------------(business object for the update)... Public Function UpdateAcctDetails() As Boolean Dim DataUserAcctNo As New Data.SecureCard(myModuleSettings.ConnectionString) Return DataUserAcctNo.UpdateAcctDetails( _ myDDName, _ myNosData) End Function--------------------------------(data object)...Public Function UpdateAcctDetails(ByVal DDName As String, _ ByVal NosData As String) As Boolean Dim rowsAffected As Integer Dim parameters As SqlParameter() = { _ New SqlParameter("@DDName", SqlDbType.VarChar, 50), _ New SqlParameter("@NosData", SqlDbType.VarChar, 512)} parameters(0).Value = DDName parameters(1).Value = NosData RunProcedure("sp_Accounts_UpdateAccount", parameters, rowsAffected) Return CBool(rowsAffected = 1) End Function------------------------------and the sproc:ALTER PROCEDURE sp_Accounts_UpdateAccount@DDNameVarChar(50),@NosDataVarChar(512)AsUPDATE Accounts_UsersSet NosData = @NosDataWHERE DDName = @DDName------------------------------------------------------------
It all works on the development server but on the production server it gives me an error :SProc sp_Accounts_UpdateAccount expects parameter "@DDName" which was not supplied.
Can anyone suggest why this works in one environment and not the other?
Thx for looking/Reid C.
View 2 Replies
View Related
Dec 11, 2006
I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction
(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)
AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)
RETURN
View 15 Replies
View Related
Jan 16, 2004
I need to use a parameter in a stored procedure that is optional and if it is optional I need all records returned, even if there is an entry in the field that the parameter is appllied to. Is this possible? I know I can assign a default value. but the value in the field could be one of many choices. I want to be able to specify a choice and have only those records returned or leave it blank and return all records.
Thanks for any help,
View 4 Replies
View Related
Sep 15, 2005
Hi,
I've tried that but it does not work:
SELECT Field FROM Table WHERE Field LIKE @parameter
thanks
View 4 Replies
View Related
Mar 19, 2007
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.
When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects.
However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.
Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ?
I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.
Any help and/or suggestions are appreciated !!!
Thanks.
View 8 Replies
View Related
Oct 2, 2006
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition
View 5 Replies
View Related
Feb 5, 2004
Hello,
I am having trouble with a SPROC and I was wondering if anyone has had similar problems. I have a web form where users enter in a large amount of text, the web app saves that data via a SPROC into a varchar(8000) field in a SQL table. However, any amount of text beyond 255 gets cut off. I have double checked and tested my code and I dont seem to find the problem. Below is the beginning of my SPROC and as you can see, I have the correct data type and length. I have also verified the data type and length on the table and it is set at varchar(8000). Any ideas?
SPROC CODE
-----------------------------------------------
CREATE PROCEDURE Add_ProductsReview
(
@Pros varchar(100) = null,
@Cons varchar(100) = null,
@Title varchar(100),
@Body varchar(8000) -- This is the parameter with problems
)
AS......
Thanks!
View 5 Replies
View Related
Mar 6, 2015
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
View 1 Replies
View Related
Apr 7, 2008
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
View 12 Replies
View Related
May 16, 2008
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:
StudentInfo
StudentID StudentName DeptID
101 John 10
102 Alex 10
103 Beth 20
ClassInfo
ClassID DeptID
901 10
902 10
225 20
I want to create a Sproc which will retreive the student's classes in DeptID 10
Following is the Sproc and cursor:
use master
go
Create PROCEDURE [dbo].[getEnclishClasses]
@StudentID int
AS
Declare @printInsertStatement nvarchar(100)
ECLARE NewRowID int
Declare classCursor CURSOR FOR
SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID)
DECLARE @ClassID INT
DECLARE @DeptID INT
OPEN classCursor
FETCH NEXT FROM classCURSOR INTO
@ClassID, @DeptID
WHILE (@@FETCH_STATUs=0)
BEGIN
PRINT 'SET @newID = Scope_Identity()'
SET @printInsertStatement=
(Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values('
+CONVERT(NVARCHAR (10), @ClassID) + ','
+CONVERT(NVARCHAR (2), @DeptID)+')'
FROM [myTest].dbo.StudentInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID))
PRINT @printInsertStatement
END
CLOSE classCursor
DEALLOCATE classCursor
EXEC getEnclishClasses 101
Here is what I try to get (text with actual data from the table):
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10)
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)
Here is what I had got (returning multiple lines, more than number of records I have):
Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
View 8 Replies
View Related
Nov 15, 2007
Why don't you folks (SQL SERVER Management Studio Team or SQL SERVER Database Engine Team) provide an option by using which I would be in a position to know what all the other objects are depended on a selected object and vice versa. That means:
When I select a Table within the SQL SERVER Management Studio if there is an option for understanding "Which all the objects (tables or views) it is depended on (I mean Foreign key and etc... kind of relations) and also Which all the objects (Tables, Views, Stored Procedures and etc...) are using this table", It would be of great help for all developers.
I can be contacted over kotis@microsoft.com
I BLOG @ http://blogs.msdn.com/kreddy
View 3 Replies
View Related
Aug 9, 2006
Could someone please tell me why the following SELECT statement...
SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...gives me 9 rows back, but embedding the exact same statement in the following sproc...
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...and passing the exact same parameter values to with the following execute statement...
USE [Geomusdb]
GO
DECLARE @return_value int
EXEC @return_value = [petrander].[DynamicQuery]
@museum = 1,
@collection = 1,
@binomen = N'le',
@locality = N'Fakse'
SELECT 'Return Value' = @return_value
GO
gives me 0 rows!? What is different!?
Any help is greatly appreciated...
View 4 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
Apr 12, 2006
Hi,
I have currently a problem with setting up the permissions for some developers. My configuration looks like this.
DB A is the productive database.
DB B is a kind of "development" database.
Now we have a couple of users call them BOB, DAVID, ...
who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.
Therefor I added them to the db role db_owner for db b.
For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message
'Msg 262, Level 14, State 1, Procedure Test, Line 3
CREATE VIEW permission denied in database 'b'.'
I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.
What's my mistake?
Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.
Regards,
Stefan
View 8 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Jul 20, 2005
Hi everybody,this view runs just fine but when I try to save it I have an error message:ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect ofsyntax error. Where is the problem? I use questionmark because I needunnamed parameter veiw.select ProductID, NameOfProduct from dbo.Products where ProductID < ?Thank you in advance,Danilo Popoviæ, B. Sc.Trgostil d.d. - ICToplièka 1649240 Donja StubicaCroatia
View 3 Replies
View Related
Nov 12, 2007
Hi,
I create a view in SQL Server Enterprise Manager - View Design, something like this:
Select customer, name from scheme.custm Where customer=?
There's a SQL Syntax error message - "Parameters cannot be used in this query type."
When I run a SQL query in SQL Query Analyzer as:
Create VIEW view_customer As Select customer,name from scheme.custm Where customer=?
There's an error as:
"COUNT field incorrect or syntax error."
What's the problem?
Thank.
Yabing
View 1 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Jul 20, 2005
Hi Everyone,Is there an easy way that I could pass a filter (variable) unto a viewvia stored procedures? I would appreciate your suggestions.Benito
View 3 Replies
View Related
Jan 22, 2006
Hello.
After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.
One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.
So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.
Any help would be appreciated!
Shirley
View 1 Replies
View Related
Jul 20, 2005
Hi,I have a View that groups and sums up totals.This View is then nested within in another View and used (it needs tobe done like this). What i need to do is to be able to vary therecords in the nested query by specifying dates. These would somehowneed to be passed to the nested query.I've looked into stored procedures/functions but am still stumped onhow to do this.Can anyone help?
View 2 Replies
View Related
Jan 8, 2001
When I use following command it works fine.
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
but if I use following command with condition it gives me an error.
if not exists(select * from sysobjects where name ='ikw_custom' and type='V')
begin
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
end
help will be appriciated.
Thanks in advance
-Balbir
View 1 Replies
View Related
Apr 15, 2006
I am learning how to work with MSSQL
here is what I like to do
I have a table with URLs like this "www.urls.com" I need to create a view that will insert this "http://" before "www.urls.com" can some please let me know what I need to do
Thank you
View 14 Replies
View Related
Sep 25, 2005
Hello All;
I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.
Can anyone tell me if I can create an SQL View from VBA?
I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.
I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.
As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.
Does any of this make sense, and am I on the right track??
View 2 Replies
View Related
Mar 18, 2006
hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))
if i want to create a view
how ?
thank you!
View 5 Replies
View Related
Jan 26, 2006
Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Use Northwind
Go
DROP VIEW Spain_Orders
Go
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = 'Spain'
this is what I get.
server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog.
albanie
View 20 Replies
View Related
Nov 29, 2007
I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'.
How can I do this ?
View 13 Replies
View Related
Jul 23, 2005
Hi Gurus,I'm a beginner. Would you please tell me if it's possible to create a viewhaving a calcuated column based on the condition of the column on the sqltable.create view vwImaging ASselectEmpID, LastName, FirstName, EmpTag = 'Act' ifFROM tblPerPayI have a table EMP:SSN (char 9)A view giving a formatted SSN (XXX-XX-XXXX,) a--Message posted via http://www.sqlmonster.com
View 3 Replies
View Related
