|
Much better. Works as advertised.
Thanks,
-Brian
|
|
|
|
|
Made a few changes working on what you originally supplied and have something that works. If the parameter is null then I have '1=1' added to it evaluates true for each row checked. If it isn't null then it performs the requested check.
Thanks again for the idea.
-Brian
declare @state char(2)
select @state = 'TN'
declare @au_lname varchar(40)
select @au_lname = 'G'
select * from dbo.authors
where ((@state IS NULL AND 1 = 1) OR (state = @state))
and ((@au_lname IS NULL AND 1 = 1) OR (au_lname LIKE @au_lname + '%'))
order by au_lname
|
|
|
|
|
I have a DTS that takes Visual FoxPro files (.dbf) and reads them to then transform the data and load it into SQL Server.
it runs quite well when executed manually. Fails every time when trying to
run it as a scheduled job via asp.net.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
i have done a test as below:
If I add other tasks such as ActiveX tasks to delete some specific data from the tables in SQL Server,the "delete" task canbe executed quite well,but still failed when transforming the data.i think this test means the problem maybe from foxpro.
Is there any one who can help me? Thank you in advance!![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
I have a problem here while excecute a oledb datareader...
<br />
<br />
...<br />
<br />
rdr.close();
<br />
cmd.CommandText = "SELECT MID FROM MandantMC";<br />
<br />
rdr = cmd.ExecuteReader();<br />
<br />
if(rdr.HasRows)<br />
{<br />
strDir = rdr.GetString(0);
...
rdr has rows but responsed with this message:
System.InvalidOperationException: No data for row/column
I don't understand this. The SQL Query analyzer give me correct results, but not this app. Before I started the Excecution of this datareader, I have closed one before ( you can see the first code line ). First request works. After closing it, the second ( the new ExcecuteReader ) fails. Maybe "cmd" make problems ?
|
|
|
|
|
After the ExecuteReader() do a rdr.Read().. it will fix your problem. First record does not get read automatically.
|
|
|
|
|
Hi, I have a Decimal(9,2) type, and I want to evaluate the fractional component of this. What I actually want to know is if there is a sql function to perform this. If I had 13.45, is there a function to return .45?
|
|
|
|
|
The following should get you what you want:
SELECT (MyDecimalColumn - CAST(MyDecimalColumn AS INT)) AS MyFractionalComponent
FROM ....
Do you want to know more?
|
|
|
|
|
I know that ADO doesnt like binary blobs of paradox tables.
i wonder if anyone knows a way to work around this
i was thinking about using Borland C++ to build a library (dll ) and then use it in C#.. but it is a lito more than i can handle rightnow..
any suggestion would be great..thank you
Elapid For The Win
|
|
|
|
|
How can i update my DataSourc(SQL SERVER) from an DataSet
Thank you
|
|
|
|
|
|
Hello,
I have three tables: Distributors, Business, Users. The Distributors table only has three fields: ID_distributor, an ID_Business, and an ID_repr (like the boss of that distributor). The Business table has information about the business (Name, Address and so on) and the User table about people accesing the web site (login name, password, and the ID_business they belong to.)
So, I want to insert a new Distributor from a web page. I need to first add a new Business record, and then (in any order) the Distributor record and the User record. (EDIT: Obviously, I want to store the first record's ID in the other two tables, to keep the relation. @@IDENTITY would help here, wouldn't it?))
And finally, I need it to return all the three IDs (distributor, business and user).
I've never written a stored procedure by hand (only designed them with Access) so I have no idea of the syntax. There are many more fields, but I'd like just to see the general structure of that stored procedure and how to use output parameters and so on.
Thanks in advance,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Believe it or not, it is basically similar to writing a VB function with some VB and SQL code inside.
You declare the procedure name and the parameters to be passed (or returned as output), and write your code inside. Below is the basic structure of it:
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} [VARYING] [= default] [OUTPUT]
]
[,...n]
AS
(code starts here)
Here is a link that has some good examples of stored procedure. I believe you can start writing your own after reading it.
Creating Stored Procedure[^]
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thanks! I'll take a look at it! With a few examples, what I want to do may not be that hard.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I have the following section of code
str_SQL = "(SELECT Description.DescKey " _
& "FROM (Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE (Category.CategoryDescription)='" & GetCurrCategory & "')"
'TO REMOVE
CurrentDb.QueryDefs.Delete "qdf_q"
'Create sub query
Dim qdf_Query As QueryDef
Set qdf_Query = CurrentDb.CreateQueryDef("qdf_q", str_SQL)
DoCmd.OpenQuery ("qdf_q")
'Build base action
str_SQL = "DELETE FROM Description " _
& "WHERE DescKey " _
& "IN (qdf_q)"
'Execute Query
CurrentDb.Execute (str_SQL)
The queries with code format removed are:
SELECT Description.DescKey
FROM (Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey)
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
WHERE (Category.CategoryDescription = GetCurrCategory())
DELETE FROM Description
WHERE DescKey
IN (qdf_q)
Now, the problem I have is, the first query generates the correct table, which contains all the items I want to remove. But when I try and run the second query, It displays an error saying "Too few parameters, expected 1"
I have all the single parameter covered already, so why is it doing this?
Cheers
Cata
(Sorry, SQL n00b  )
|
|
|
|
|
Hi Cata. You're creating the select query as a temporary QueryDef object and referencing its name in your delete statement. When executing the delete statement with CurrentDb.Execute(str_SQL), the name [qdf_q] is not recognized (its scope is as a variable in your procedure); thus, Access wants to treat it as a parameterized name (in the scope of the SQL), and without explicitly assigning a parameter object with that name to the QueryDef, you're getting the "Too few parameters" error.
I would think a way to avoid this is to build in the Select statement as part of the str_SQL string (not a QueryDef) then execute str_SQL with the subquery. The resulting SQL would look something like this:
DELETE FROM Description
WHERE DescKey
IN
(
SELECT Description.DescKey
FROM (Category INNER JOIN SubCategory
ON Category.CategoryKey = SubCategory.CategoryKey)
INNER JOIN Description
ON SubCategory.SubCategoryKey = Description.SubCategoryKey
WHERE (Category.CategoryDescription = GetCurrCategory())
)Does that make sense? The variable str_SQL would hold the complete text as above, then would be executed without creating a QueryDef object.
|
|
|
|
|
The qdf_Query is the procedural object, qdf_q is an actual database query that exists within the access DB. I can see it in the query viewer.
The qdf_q query is fully populated as well, so there is no reason why it should not work.
When I tried to integrate the sub query by using SQL, it threw and INNER JOIN Syntax error. The current solution displays "Too Few Parameters, Expected 1". But when i experimented with using a table, it worked fine.
This is confusing me. A table works, but a query that produces a far simpler table, requires additional input? Why is this?
Cheers
Cata
|
|
|
|
|
Well, the only other thing I can think of is that you appear to be using a custom function in your select statement - GetCurrCategory. If this is evaluating to a name that is not recognized in your where clause, you could get the parameter error you mention.
|
|
|
|
|
But the select statement works perfectly. There is a populated query table.
What's more infuriating is that, it works with a standard table.
I know queries require parameteres, but the ones in the sub query are already filled in.
|
|
|
|
|
I've got a problem executing DTS package from ASP.Net.
I use this code:
Dim oPkg As DTS.Package
oPkg = CreateObject("DTS.Package")
oPkg.LoadFromSQLServer("WIN2000", "UID", "password",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , ,
"Import_Sales_History")
oPkg.Execute()
oPkg.UnInitialize()
oPkg = Nothing
I used this code before, and it always worked. Now it doesn't. The problem
is that I'm not receiving any error messages, so I cannot find out what's
wrong. When I step through the code I don't see anything unusual. But in
fact it doesn't do anything, no data has been imported. When I execute the
package manually - everything is OK.
|
|
|
|
|
|
Thank you for your reply,minhpc_bk.
i have tried your suggestion ,and now i can execute the pkg quite well. while transforming data from sql server to sql server,however,when i try to execute the transformation between sql server and foxpro ,it always fails.The error handle shows it can successfully execute both ActiveX Task and Dynamic Task, but fail with transformation task.i don't know why.
|
|
|
|
|
|
Thank you so much for your help, minhpc_bk.
it is the permission problem.i now can execute my pkg with sql authentication,however,it is said that with sql authentication is not recommended in most of articles.can you recommend me some information about the permission of sql and files? any will be appriciated! thank you!
|
|
|
|
|
|
Hi Steven,
That's my pleasure. Over the past one year and half I have learnt a lot from CP, and now it's time to return something to CP. Trying to answer questions in the CP forums, in my opinion, is a good way to do that.
Very nice to know you, Steven.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
