|
Guys,
I have a problem with a stored proc in SQL Server 2000. I'm logged in as 'sa' so have complete access to the DB. I have a table defined as follows:
CREATE TABLE Conduit (
Index_i int IDENTITY (1,1) NOT NULL Primary Key,
Name_vc nvarchar(125) UNIQUE NOT NULL,
StartDate_dt datetime
)
GO
I then have 2 stored procs that access it:
CREATE PROCEDURE ReadConduitNames
AS BEGIN
SET NOCOUNT ON
SELECT Name_vc FROM Conduit
END
GO
and
CREATE PROCEDURE ReadConduit
{
@Name_vc nvarchar(125)
}
AS BEGIN
SET NOCOUNT ON
SELECT * FROM Conduit WHERE Name_vc = @Name_vc
END
GO
The first stored proc runs fine, but the second one gives the following error:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
I've tried various forums and msdn, but can't find an answer as to why the second stored proc won't load.
Any thoughts?
Thanks in advance,
John.
|
|
|
|
|
replace
jgrogan wrote: CREATE PROCEDURE ReadConduit
{
@Name_vc nvarchar(125)
}
with
CREATE PROCEDURE ReadConduit<br />
(<br />
@Name_vc nvarchar(125)<br />
)
for more info check this google result[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Many thanks - that worked a treat!
|
|
|
|
|
No problem
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Rename you to Eagle Eyes - well done
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah - I was staring at it for 2 minutes before I realised where the difference was
|
|
|
|
|
Hi again and sorry for the delay. Thank you very much for your answer and here is the code I am using
RSClientPrint.MarginLeft = 0;
RSClientPrint.MarginTop = 0;
RSClientPrint.MarginRight = 0;
RSClientPrint.MarginBottom = 0;
RSClientPrint.Culture = 2058;
RSClientPrint.UICulture = 2058;
RSClientPrint.PageWidth = 85.2;
RSClientPrint.PageHeight = 279.4;
RSClientPrint.Print('http://ServerName\SQLinstance/ReportServer', '/FolderName/Impresion_Factura/Factura&CODIGO_SERIE='+serie+'&ID_SUCURSAL='+sucursal+'&CAJERO='+cajero+'&NIT='+nit+'&SUBTOTAL='+subtotal+'&DESCUENTOS='+descuentos+'&CODIGO_CAJERO='+codigo_cajero, 'Factura');
I really appreciate your help. You guys always get me out of trouble!!!
|
|
|
|
|
And you question is?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi, sorry about not posting the question again. My problem is that when I preview the report, there is no problem with the first page, but if it is a long report, and I try to view the second page, it shows an error.
In the report server, all pages can be viewed ok. So this happens only when previewing and printing the report.
Thnk you very much for your help.
|
|
|
|
|
Try to fix it by adding code in report event (something like Initialize) which calls report data from database.
What error message do you get?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
hi
how to insert multiple records in the table using procedure but records will be dynamic
means I know Howmany records will be inserted but not fix depend on input provided by user ?
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
on your stored procedure use input parameters, something like this:
CREATE PROCEDURE procedureName @maxInt int<br />
AS<br />
--insert values via looping based on maxInt parameter
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I can provide maxInt(mean howmany record) but how can I insert multiple values which is in table in .net server side code.
means how can I pick up all those records in procedures.
what varaiable i can use like-array
so that i can insert all records using loop.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Try using XML to pass multiple records to your stored proc
create proc xyx (@recs xml)
then unpack the xml to insert the records.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
here it is
CREATE PROCEDURE ProcedureName @maxInt int AS<br />
<br />
declare @i as int<br />
set @i=0<br />
set @maxInt=@maxint <br />
<br />
while @i < @maxInt <br />
begin<br />
set @i = @i + 1<br />
insert into myTable values (@i) --Do your inser which according to your needs<br />
end
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
modified on Friday, May 16, 2008 3:42 AM
|
|
|
|
|
now how to get multiple records for procedure from asp.net.
and how to pick up by procedure.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Now I need you to tell me how to write method for executing a procedure and returnd a dataset
The stored procedure (statement outlined at the bottom of the word document) will give you a list of orders (first table) and order items (second table) that contain mail lists.
The first table will contain the order info itself. This is where you will get the additional fields for the “Master Mail list file” such as order number, name of person who placed the order etc. The second table will contain the specific item info (such as item name/description).
You will use the Order number (the actual column name is SOPNumbe) to reference the path where the order files are stored.
An order folder will have one or more package folders.
Add a method to DataObjects.FeedsDO.vb to execute the stored procedure and return a DataSet.
So once you have your dataset.
the below one is the SQl statement so I also need Code for writing a procedure which gives me two records
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ClientCode varchar(5), @ClientID int
SELECT @ClientCode = 'cmc'
SELECT TOP 1 @ClientID = ClientID FROM ODClient WHERE Code = @ClientCode AND ParentID IS NULL
DECLARE @Orders TABLE (OrderID bigint)
--Set the id of the mdb output data feed for reference in next statement
DECLARE @DataFeedID int
SELECT @DataFeedID = 5
INSERT INTO @orders
SELECT o.OrderID FROM ODOrder o
OUTER APPLY (SELECT TOP 1 oh1.CreateDate, oh1.OrderID, oh1.StatusID FROM ODOrderHistory oh1 WHERE oh1.OrderID = o.OrderID AND oh1.StatusID = 22) oh
WHERE o.ClientID IN (SELECT ChildID FROM dbo.funGetClientChildren(@ClientID))
AND o.OrderID IN
(SELECT OrderID FROM ODOrderShipment os
INNER JOIN ODOrderItem oi ON oi.OrderShipmentID = os.OrderShipmentID
WHERE oi.HasMailingList = 1)
AND
(oh.StatusID = 22 AND oh.CreateDate >= (SELECT MAX(CreateDate) FROM ODDataFeedLog WHERE DataFeedID = @DataFeedID AND DataFeedLogEventTypeID=3) OR NOT EXISTS (SELECT 1 FROM ODDataFeedLog WHERE DataFeedID = @DataFeedID))
SELECT o.OrderID, o.SOPNumbe, su.LoginID, c.FirstName, c.LastName,
StatusDate = (SELECT TOP 1 CreateDate FROM ODOrderHistory oh WHERE oh.OrderID = o.OrderID ORDER BY CreateDate DESC)
FROM ODOrder o
INNER JOIN ODSysUser su ON su.SysUserID = o.SysUserID
INNER JOIN ODContact c ON c.ContactID = su.ContactID
WHERE o.OrderID IN (SELECT OrderID FROM @Orders)
SELECT * FROM ODOrderShipment os
INNER JOIN ODOrderItem oi ON oi.OrderShipmentID = os.OrderShipmentID
INNER JOIN ODPackage p ON p.PackageID = oi.PackageID
WHERE oi.HasMailingList = 1
AND os.OrderID IN (SELECT OrderID FROM @Orders)
|
|
|
|
|
Sorry, but this is obviously a homework question. Have a try and post your code if it doesn't work and someone will probably help you. Nobody will do your homework for you.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Friends,
I have a data like in a name field like below
Martins
Martin's
how can I get both record when some one passes in search input string only Martins
i.e if I pass Martins It should return both Martins and Martin's
it is a specific case, think in general where uer will pass Martins not Martin.
johns not john only
can i remove ( ' ) it from field in where condition if yes then how?
If you need further explination please do not hesitate to ask.
modified on Thursday, May 15, 2008 8:25 AM
|
|
|
|
|
select [name] from myTable where [name] like '%Martin%'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Use like operator like this:
Select * from tablename where name like 'martin%'
Piyush Vardhan Singh
p_vardhan14@rediffmail.com
http://holyschoolofvaranasi.blogspot.com
http://holytravelsofvaranasi.blogspot.com
|
|
|
|
|
select * from myTable<br />
where soundex(ColValue) = soundex('john''s')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
select organisationname from organisation
where soundex(organisationname) = soundex('martins')
I used your query , my question was if Input martins it also return record including martin and martin's.
so its not returnt record which have martin's in it.
|
|
|
|
|
I have tested by giving input string 'martins' and 'martin''s' and query returns two rows with values martin and martin's (those values are stored on database).
select organisationname from organisation<br />
where soundex(organisationname) = soundex('martins')
and the next query gives me same result
select organisationname from organisation<br />
where soundex(organisationname) = soundex('martin''s')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Dear all,
I need one help for installing sql server 2000 reporting services.
Iam getting the following error while installing Sql server 2000 Reporting service software sp2.
The Upgrade patch cannot be installed by the windows installer service because the program to be upgraded may be missing, or the upgrade patch may update a different version of the program. verify that the program to be upgraded exists on your computer and that u have the correct upgrade patch.
I have downloaded reporting service from Microsoft site.
I have the following with these
--Visual studio 2005
--Sql server 2000 with SP4
Please help me on this.
How to install sql server 2000 reporting service.
Regard's
Veeresh
i want to join this group
|
|
|
|