|
ok, i am using following queries, all are running fine but only one is not working in sql2000(however it is working in 2005).
if object_id('dbo.t1') is not null drop table dbo.t1;
create table dbo.t1
(
id int not null identity primary key,
val varchar(500) not null
);
go
-- Create and populate an auxiliary table of numbers
IF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
SET NOCOUNT ON;
DECLARE
@max AS INT,
@rc AS INT;
SET @max = 1000000;
SET @rc = 1;
BEGIN TRAN;
INSERT INTO dbo.Nums(n) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums(n)
SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums(n)
SELECT n + @rc FROM dbo.Nums
WHERE n + @rc <= @max;
COMMIT TRAN;
-- Function that splits array elements
CREATE FUNCTION dbo.fn_split(@arr AS VARCHAR(8000), @sep AS CHAR(1))
RETURNS TABLE
AS
RETURN
SELECT
(n-1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, '')) + 1 AS pos,
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr+@sep, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@arr) + 1
AND SUBSTRING(@sep+@arr, n, 1) = @sep;
GO
--And here’s the query code:
select *
from dbo.t1
order by
(select right('000000000' + element, 10) as [text()]
from dbo.fn_split(val, '.')
order by pos
for xml path(''));
This last query is not working in sql2000, however it is running in 2005.
can someone write this query for sql2000.
select *
from dbo.t1
order by
(select right('000000000' + element, 10) as [text()]
from dbo.fn_split(val, '.')
order by pos
for xml path(''));
modified on Sunday, August 31, 2008 6:10 AM
|
|
|
|
|
If I remember correctly, support for XML was introduced in SQL Server 2005 so this functionality cannot be used in version 2000.
So I suggest that you use different approach as I mentioned in previous post. Use temporary tables for splitting and sorting and return results based on temporary table.
|
|
|
|
|
Mika, can you please write complete code or an article so that everyone can benefit from it. you can write complete stuff by replying to this question or you can write a complete article. but i suggest that you do both of the jobs 
thanks,
habib
modified on Sunday, August 31, 2008 6:44 AM
|
|
|
|
|
I'll take the article into consideration, but I don't currently have the time to write it nor the full implementation for your question. So I advice you to google for similar problems and/or ideas and to experiment on different approaches.
I'm sure that when you put your mind into this, it'll be done in no time.
Mika
|
|
|
|
|
How rude, you not only want himn to do your work for you, but to format it (an article) to your liking.
Thats rich.
|
|
|
|
|
Hello heyy, this is a great community!!
i've had this question all my life since i was learning databases lol,
once that i had my project finished, my desktop software and my database schema and all is finished .. and i want to make an installation ..
what i need to do .. to have my database installed and configured when the installation of my software finishes? ... i mean ..once that i have installed my software in another comp ... i need to install mysql and run the script to have my database created and in some cases go to control panel odbc ... and sign my database in there ... thats supposed to be part of my installation ..
a end user client ... doesnt know how to do that ...
i hope someone could help with that doubt
|
|
|
|
|
That would be a custom installer action.
MS Sql Express has a merge module that can be added to your install, and allows you to attach your programs database file on the fly.
For MySQL it would get a little more complex - you can probably unzip the server somewhere and then unzip your blank database into the right folder. Not sure about how the programmatic administration stacks up for MySQL.
You might also want to check your licensing for MySQL. IIRC the client libraries are GPL - so if you are distributing MySQL and have linked against the client libraries you'll probably either need a paid license, or GPL your program.
|
|
|
|
|
If you're building your installer from Visual Studio, you can add items to the Prerequisites window by creating a Bootstrap Manifest Package. See here[^] for a program that can create one of these, given the necessary install files. For instance, if you want to install the MySQL Community Server, you would need to specify the file mysql-essential-5.0.67-win32.msi, as well as the necessary info to check if it's already installed. Then just check off that item in your Prerequisites for your installer, and it will take care of the installation for you.
As for configuration, a custom install action is needed. What I usually do is create the install package, then write a simple console project that will run the necessary config programs without any user intervention. This console project is my custom action.
Another nice thing about the Bootstrap packages with the Visual Studio installers...if you have a URL directly to the install file (like you can do for MySQL), then you can tell your installer to download any needed prerequisites from the internet, thus keeping the size of your installer down. This is great if you need to email an installer.
Hope this helps some.
Dybs
|
|
|
|
|
thank u that was a very good explanation i have a better panorama now ... but i still have the doubt about the odbc part ... when is needed for sure ....
i dont know how the system manages the configuration or the file that is holding all the config needed to add a resource for odbc thats all ...
but u got me a great idea with the console program part thank u so much
|
|
|
|
|
Glad to be of service 
|
|
|
|
|
I have a query like
SELECT * FROM SomeTable WHERE SomeColumn In('FirstValue','SecondValue'). But the number of items in the IN is not predictable. How do I write a parameterized query then ? If I know the number of items in the IN, I know I can write
SELECT * FROM SomeTable WHERE SomeColumn In(@FirstParam,@SecondParam) . Is there anyway to do the same when the number is unknown?
|
|
|
|
|
Hi,
As far as I know you have to either declare each parameter separately or somehow change the structure. Some possibilities could be:
1.
create a function for evaluating condition. Something like
SELECT * FROM SomeTable WHERE OwnFunction(SomeTable.SomeColumn, neededParameters) = 1
This way you could use arrays for passing values to function
2.
Use temp-table and insert the values in temp table before select. Something like
CREATE TABLE #Values(value...)
INSERT INTO #Values...
SELECT * FROM SomeTable a WHERE EXISTS (SELECT 1 FROM #Values b WHERE a.SomeColumn = b.Value)
3.
Place the whole query into procedure and pass the parameters to the procedure and return a table
Hope this helps,
Mika
|
|
|
|
|
If you're creating the query in code; yes. If you're trying to write a stored procedure, function, or view; no.
|
|
|
|
|
Hi, i would need to create a software application using C#.NET. I understand there are database such as MS Access and SQL Server. But is there freeware database? No license needed. Example, PHP MyAdmin which is used for php database and its free.
Thanks
|
|
|
|
|
You can use MySQL. It's powerful.
|
|
|
|
|
Few choices (registration needed in most):
- SQL Server Express Edition for multiuser environment
- SQL Server Compact Edition for singleuser environment
- Oracle XE
- IBM DB 2 Express-C
- MySql
and there are lots more but I think these are the most useful.
Mika
|
|
|
|
|
How about SQL Server Express?
Use Live Search to find it.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
The 2 best options I know of are MySQL and PostgreSQL. A couple of differences I've noticed:
1. MySQL Community server is free, unless you redistribute your project not user the GPL license. In this case, you would need to purchase a license. However, if your project is distributed under GPL, then it's still free.
2. PostgreSQL is licensed under the BSD license, so it is completely free to use in any and all situations.
3. From personal experience, MySQL seems to be much faster than PostgreSQL, so if speed is an issue (as it is in my application), I'd go with MySQL.
Dybs
|
|
|
|
|
You can use SQLite. It is a free (public domain) database and it doesn't require an independent installation of a DB server.
It is also very simple to work with (you only need to reference its assembly) and it has reasonable free admin tools.
SQL Server Express is not really free. It has a size limit (4GB), it requires a separate installation (which makes installation more cumbersome) and it uses a separate server.
MySQL is not really free either (for commercial applications at least) and requires a separate installation of the DB server.
If you are interested - go to www.sqlite.org for more information. SQLite also has a very good .net provider (search on google).
Best of luck
Liron
|
|
|
|
|
download sql server 2005 free edition and also download the bob tubor free tutorial videos on microsoft.
nelsonpaixao@yahoo.com.br
|
|
|
|
|
I have a datasource that pulls a IP address. I need to call a function in SQL does the same as the asp class, that converts it to a non dotted format http://www.maxmind.com/app/csv[^]
which I can insert into my destination.
I tried using a lookup data flow item with SELECT [LogTracking].[dbo].[sfnConverIp] (?) but I can not map it to a column with the dotted IP.
There has to be a simple way right?
|
|
|
|
|
Hi,
I was having a discussion with someone much more knowledgeable than mysef about the data designer in VS2005 and I was just hoping to get his thought's on the topic. He was trying to tell me that it is very difficult to find the code for specific things. While I agreed with him, I noted that in almost all cases the code could be changed by using the designer itself, and that the features that were available such as click and drag databinding and the building of the dataset itself were very beneficial. Now I understand that the data designer is a shortcut to avoid writing code but at the end of the day I feel that we need to be more efficient and I know that a project that I am working on right now, I have saved about 1/2 the time by using the data designer. I would be really interested to see what people have to say on the topic.
|
|
|
|
|
Never used it, it's probably rubbish like any "shortcut to avoid writing code", pay attention to "someone much more knowledgeable than mysef".
There's no substitute for getting in there and really understanding what's going on.
|
|
|
|
|
Totally agree 
There should be a test in designers that after you can successfully write a solution using code, only then you are allowed to use graphical designer (or tool). I've seen so many total disasters after using a GUI without any knowledge what's happening behind the scenes 
|
|
|
|
|
Absolutely in agreement. If you look at how many questions are posted on CP where the real cause of the problem is lack of understanding....
Graphical designers should be banned for all non-GUI work - and before someone gets on their high horse, a well designed DAL means I can execute queries and return almost any type of result set (table, datareader, xml, collection etc) synchronously or asynchronously in about 5 lines of code.
Well, thats my opinion fo what is worth 
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
