|
Simple answer - NO
unless as mentioned by someone else you are creating an MS access project to hook up to a sql server, if so its just a SQL server stored proc
But if its a normal access database you should be able to write your own scalar functions (single return value), but you can not write functions that will return a recordset. unless the consumer of the recordset is another vba function...
hope that makes sense, I've not had my coffee yet!
Mr ra ra
|
|
|
|
|
Hi
I am using following stored procedure
-----------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;
Select dbo.cp_product.product_type, dbo.cp_genmask.genmask_code
from dbo.cp_genmask inner join dbo.cp_product
on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type
where dbo.cp_product.product_id=@product_id
END
--------------------------------
when I execute the above code using
exec GetUserControlTemplateCode 1,'x'
I get correct values for x i.e. output parameter.
My code behind to call stored procedure is as follows
---------------------------------
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sConnectionString2"].ConnectionString);
SqlCommand Com = con.CreateCommand();
Com.CommandType = CommandType.StoredProcedure;
Com.CommandText = "GetUserControlTemplateCode";
//Create parameter object to provide input
SqlParameter parInput = Com.Parameters.Add("@product_id", SqlDbType.Int);
parInput.Direction = ParameterDirection.Input;
parInput.Value = nProductID;
// Create parameter to hold output
SqlParameter parOutput = Com.Parameters.Add("@genmask_code", SqlDbType.NVarChar,300);
parOutput.Direction = ParameterDirection.Output;
//Open the connection
con.Open();
//Execute command
try
{
Com.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
string sTemplateCode = Convert.ToString(parOutput.Value);
sTemplateCode = sTemplateCode.Replace("ProductID", nProductID.ToString());
Control myControl = ParseControl(sTemplateCode);
PlaceHolderForProducts.Controls.Add(myControl);
con.Close();
}
-----------------------------------
when I execute the code, i do not get any exception, but the value of output parameter is null.
Can someone please help me to understand whats missing here?
Thanks.
|
|
|
|
|
I did not see the statement setting value for @genmask_code so infact, it have no return value. Also I think exec GetUserControlTemplateCode 1,'x' will not work because of lacking OUTPUT modifier. It should be:
<br />
exec GetUserControlTemplateCode 1, @x OUTPUT<br />
print @x<br />
|
|
|
|
|
|
Colin Angus Mackay wrote: NOTE: You should only do this if you expect the SELECT statement to retrieve only one row.
You won't get an error if it does retrieve more than one row, but if it does, you'll simply get the value from the last row returned by the query. Unless you put an ORDER BY clause in, however, which row that is will be unpredictable.
If no rows are returned, the value of the variable will be unchanged.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for your replies,
I changed my stored procedure as follows to assign the value to output variable:
ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;
Select dbo.cp_product.product_type, @genmask_code=dbo.cp_genmask.genmask_code
from dbo.cp_genmask inner join dbo.cp_product
on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type
where dbo.cp_product.product_id=@product_id
END
when I execute this I get following error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
where should I do data assignment operation?
|
|
|
|
|
I changed the code as follows
ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;
Declare @product_type int
Select b.product_type, @genmask_code=dbo.cp_genmask.genmask_code
from dbo.cp_genmask as a inner join dbo.cp_product as b
on a.genmask_product_type=b.product_type
where b.product_id=@product_id
END
I executed this code using using
exec GetUserControlTemplateCode 1, @genmask_code output
print @genmask_code
It gives me message as command executed successfully, but I can not see any data in output window. Again when I call this procedure from code behind code it shows null value...
Please suggest where I am wrong.
|
|
|
|
|
My evaluation copy ran-out and I uninstalled the previous and
installed another evaluation edition. The installation was
successful. I created a SSIS package importing a table from Oracle
10G XE to the SQL 2005 on my machine. I saved the package on
the server. The SSIS service is running but when I try to refresh
the node, I get this message. The help link leads to no where(Hello, Microsoft!):
--------------------------------------
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request.
(Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Login timeout expired
An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server
does not allow remote connections.
TCP Provider: No connection could be made because the target
machine actively refused it. (Microsoft SQL Native Client)
------------------------------
Login timeout expired
An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server
does not allow remote connections.
TCP Provider: No connection could be made because the target
machine actively refused it. (Microsoft SQL Native Client)
------------------------------
My efforts so far:
:-DI have verified the items in the Surface area screen, everythign
looks good.
SSIS service has started.
:-DBoth the Network Server TCP/IP dynmaic port and the SQL
Client TCP/IP dynamic port are the same.
:-DI have no problem logging in to any of the services.
 But I get this message.
I tried to create a package from Visual Studio, but that also did
not succeed.
 In my previous set up, I created perhaps 1/ 2 dozen different
packages.
I am puzzzled.
I appreciate if some one could throw some light.
|
|
|
|
|
One of my statements is wrong.
I could create a package in Visual Studio, but I could not access this in SSIS.
|
|
|
|
|
mysorian wrote: My evaluation copy ran-out and I uninstalled the previous and installed another evaluation edition
Do you want to read that again and guess what a probably cause of the problem is? Do you think it's entirely possible that the new installation knows that you already had an evaluation edition installed and it expired?
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
The evaluation edition was completely removed as any new install flags any remnants that needs removing. I was instructed during new install to move my 'Data' to another location which I did. The packages although saved to 'Sql Server' had created 'dtsx' files as well.
However, from VS 2005 I can create a package, but cannot see, or access it in the SQL 2005 Management Studio. I wish error messages could have been more friendly [see my comments in the article on SQLCMD].
I am technical writer and I routinely, meticulously document what I do. Here are my recent articles.
Thnaks for your fast response.
Moving Data from SQL Server 2000 to SQL Server 2005
Querying Databases in SQL Server 2005
SQL2000 Data Transfer is a snap with SQL 2005
On using the SQLCMD in SQL 2005 Server
Generating and Hosting a SQL Server Reporting Services Report Using SQL Server 2005 Business Intelligence Development Studio
These and other articles may be found at:http://www.angelfire.com/vt2/hodentek/[^]
|
|
|
|
|
What is the Differance between '@variable' and '@@variable' in SQL syntax?
Thanks
Sampath
|
|
|
|
|
@variable are local variables
@@WHATEVER are system variables
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi
My table has one column "vat_percentage" as numeric(2,2) data type.
when I try to insert values using
INSERT INTO [dbo].[cp_vat]
([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to])
values
('test',10.2,05/2005/05,05/05/2007)
I get an error as
Arithmetic overflow error converting numeric to data type numeric.
can someone pls help me?
|
|
|
|
|
That will be your dates. Best practice is to use ISO format dates, yyyymmdd, as these are not sensitive to the selected locale, so you would write your statement as:
INSERT INTO [dbo].[cp_vat]
([vat_text],[vat_percentage],[vat_valid_from],[vat_valid_to])
values
('test',10.2,'20050505','20070505')Note that date literals are entered as strings, not numbers - if you pass a number, it will be treated as the binary representation and you'll get the wrong answer.
If you're passing these dates in from application code, you should use parameterised queries and the appropriate Parameters collection.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mik, I used the string given by u, but still the error persists 
Arithmetic overflow error converting numeric to data type numeric.
|
|
|
|
|
What Mik said about date literals, is right.
The precision is the total number of digits in the number, and the scale is the number of digits to right of the decimal point in the number. see here[^]
You defined the field vat_percentage as numeric (2,2), means numeric field with precision 2 and scale 2. Now in this scenario the problem is with the numeric value that you given, which is 10.2, even if you enter 1 you will get the same error message.
With your defined precision and scale you can only enter values like this: 0.23. If you entered .23987, it will round to .24
Or Alternatively change the field definition to numeric(4,2)
that is total number of digits will be 4. Among them 2 digits will be at right of the decimal point.
Hope this would solve your problem.
Regards
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
|
Check out your decimal separator. Try to use ',' instead of '.'
|
|
|
|
|
Hi all,
i'm trying to build a SQL statement,
commStr = "insert into mytable values(default,?number,?text,?date) " +<br />
"select * from mytable " +<br />
"where not exists(select * from mytable where int_field = '" + <br />
System.Convert.ToInt32(TextBox1.Text) + "')";<br />
and I keep getting the following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> MySql.Data.MySqlClient.MySqlException: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from mytable where not exists(select * from mytable where int_field = '' at line 1<br />
at MySql.Data.MySqlClient.PacketReader.CheckForError()<br />
at MySql.Data.MySqlClient.PacketReader.ReadHeader()<br />
at MySql.Data.MySqlClient.PacketReader.OpenPacket()<br />
at MySql.Data.MySqlClient.NativeDriver.Prepare(String sql, String[] parmNames)<br />
at MySql.Data.MySqlClient.MySqlCommand.Prepare()<br />
at Service.InsertDB(String insStr, String connStr, Int32 inInt, String inText, String inDate)
the SQL string that is being passed looks like
insert into mytable values(default,?number,?text,?date) select * from mytable where not exists(select * from mytable where int_field = '20')<br />
What I'm trying to do is check that the entry is not already present in the database before i insert it.
Some help I'm going mad with this.
Thanks in advance.
|
|
|
|
|
From this page[^] it looks like you should simply say INSERT IGNORE INTO mytable .... I don't know if that applies to earlier versions of MySQL.
If you don't state which database you're using we tend to assume SQL Server 2000.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
The syntax is INSERT INTO table1 (field1,field2,field3) SELECT x,y,z FROM table2 WHERE fieldwhatever = 'whatever'
You need the same # of fields int he insert as what you select.
I don't know what the question marks in your sql are about, but my guess is that they are causing problems.
You shouldn't use the word values, but have the field names in the insert into.
Also, is it really your intention to insert duplicate rows into the table? Why do you have an exists clause, and you are selecting from the same table?
insert into mytable (default,number,text,date) select default,number,text,date from mytable where int_field = '20'
If you post your actual code...it would be easier to help you.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
i'm not trying to insert duplicate records i am 'trying' to do the opposite.
The '?' act sort of like tags so that the actual values can be put in later. The problem is definately not here as this works fine until i use the exist condition. I'm trying to use the exist to check for records as to not add duplicates.
I have solved the problem in the web page that I'm using but was just trying to solve the problem at the database level thus making it more reliable.
As the error message is quite obviously pointing to the sql statement, and the fact that a simple insert statement is working completely okay, then I am taking for granted that the problem occurs when i add the 'where not exists' condition. I was therefore just trying to get help with writing these kinds of sql statement. 
|
|
|
|
|
richiemac wrote: insert into mytable values(default,?number,?text,?date) select * from mytable where not exists(select * from mytable where int_field = '20')
You are inserting into mytable, and yet you are selecting from my table also. This is perfectly legal, however...it will insert duplicates into the table - if that is what you want.
Select * from mytable where not exists(select * from mytable where int_field = '20')
This will return NO records if there is a value of '20' in int_field for ANY record in mytable.
I don't fully grasp what you are trying to do, but look at this example of using exists in an insert into:
Three tables named (tblPeople,tblYoungPeople,tblDeadPeople)
All three have fields (PeopleID,LastName,FirstName,Age)
To fill the tblYoungPeople table with all people under 40 who are not dead, you would do this:
insert into tblYoungPeople (firstname,lastname,age)
select firstname,lastname,age from tblPeople p
where p.age < 40 AND not EXISTS(SELECT * from tbldeadpeople dp WHERE dp.peopleid = p.peopleid)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I thought a bit more about what you're trying to do. You want to insert a record into the table if, and only if, there are no records that have int_field = '20'.
Something you could do is like this:
insert into mytable (default,number,text,date)
select 'xxx' as default,123 as number,'yyy' as text,'2006/07/14' as date)
from mytable where not exists(select * from mytable where int_field = '20') LIMIT 1
The LIMIT 1 assures that only 1 record will be inserted (because you are selecting only 1 record). The values are aliased in the select statement, because you don't want to pull values from mytable.
Using my previous example, this worked:
INSERT INTO tblpeople (firstname,lastname,age)
SELECT 'John' as firsname, 'Williams2' as lastname, 20 as age
FROM tblPeople p
WHERE not EXISTS(SELECT * from tblpeople p2
WHERE p2.firstname = 'John' and p2.lastname='Williams2' and p2.age = 20) LIMIT 1
I hope this helps.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
