|
Sorry for the late answer, but was not in town for a day. The thing is that my friend doesn't have an instance of sql 2005 installed on the system. plus, i think the connection string would change (sql 2005 express accepts only files to be accessed as far as i remember; i've worked only a little with 2005 because i had some conflicts on my pc so i had to reinstall all the system and...).
Thanks a lot. If professional doesn't solve it, i'm gonna take all the database to 2005.
Thanks again,
Catalin David.
|
|
|
|
|
I've read through a few articles on the issues that a machine running XP will have when accessing a server on a 2k or 2k3 machine, but have yet to find the solution to my problem. Here goes...
The issue is that when someone running a machine with XP on it, tries to access a Crystal Report,(which queries a db hosted via sql server on a machine with win2k), they often run into access issues. The access (in the crystal report) is set to use integrated security so the users are not required to use a un/pw. This happens after new installations (almost every time) and occurs randomly throughout the day. Currently, when the user encounters this error, we are just having them log off / log on and this takes care of the problem. So again to sum it all up, users are being asked for their credentials (although access is set to integrated security) when trying to access a crystal report.
Nathan Lindley
|
|
|
|
|
hi all....
plz let me know how to retrieve the column name of a given table.....without content(data),just only column names
not like select * from sometable and sp_help sometable
Plz help me,,thanqs in advance![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
declare @tblname varchar(100)
set @tblname = 'mytable'
select * from syscolumns where id = (select id from sysobjects where name = @tblname)
Regards
KP
|
|
|
|
|
actually i want to bind the column names with a grid on the webpage...here i don't want any content...it giving some information along with the columns...anyway thanqs for ur kind reply MR krish
|
|
|
|
|
while retrieving data to grid use select query which returns no rows
like SELECT * FROM tblA WHERE 1 = 2
this should work (I've not tried this.)
Regards
KP
|
|
|
|
|
Hi
I have to import flat files in a sql server database which are of the format:
Date: 2 apr 07
Input file: input1.test
Output file: input2.test
table1_column1 table1_column2 ....
123 456
table2_column1 table2_column2.......
26678 23334
the file is tab delimited. i have to read the date from the header and read only the first table.
is it possible to do it with IS?
I have a parsing routine in c# that extracts the required data but it is very slow. Iam looking for some efficient solutions
|
|
|
|
|
Hi i will give month and year...,
based on that want to find number of days in a month and number of sundays in that month...,
Its possible in sqlquery???,
If its possible plz send me the query how to find the number of days and number of sundays in a given month.....,
Magi
|
|
|
|
|
query for getting number of sundays in a month.
declare @mth varchar(4), @yr varchar(4), @dt datetime
set @mth = '4'
set @yr = '2007'
set @dt = convert(datetime, '01-' + @mth + '-' + @yr, 103)
-- to get first sunday of the month
select cast(case when ((7 - datepart(dw, @dt)) + 2) = 8 then 1 else ((7 - datepart(dw, @dt)) + 2) end as varchar(5)) + '-' + @mth + '-' + @yr
-- to get number of sundays
select case when (21 + (case when ((7 - datepart(dw, @dt)) + 2) = 8 then 1 else ((7 - datepart(dw, @dt)) + 2) end)) between 24 and 28 then 4 else 5 end
Regards
KP
|
|
|
|
|
Can u provide me with an example of a date routine that will return the begin and ending period for a week number. For example, if week 14 for 2007 was provided, the routine would return starting date 08/04/2007 and ending week 14/04/2007.
Input Provided - week number (1 - 53), and Year(cccc)
Output - week begin date and week end date
Revathi Raj
|
|
|
|
|
check this out. hope the smae you are looking for
declare @wk int
set @wk = 15
select dateadd(dd, @wk * 7 - datepart(dw, '01-jan-2006'), '01-Jan-2006')
this gives begining date of week. Adding 7 days returns end date
Regards
KP
|
|
|
|
|
Hi all,,
Plz let me know the query to retrieve all the databases names present in sqlserver..!or give me some urls to releted topics
|
|
|
|
|
sp_databases
or
SELECT * FROM sysdatabases
Regards
KP
|
|
|
|
|
hello krish....plz let me know the sql query for retrieving the user tables present in the selected database
thanqs in advance..
|
|
|
|
|
sysobjects table holds all objects created in the database
SELECT * FROM sysobjects WHERE xtype = 'U'
xtype column holds object type like U-Table / V-View / P-Storedprocedure /...
Regards
KP
|
|
|
|
|
Thanqs Krish...this code sippet is quied for my requirement
|
|
|
|
|
I have a big problem here.
I have a query that reads (suedo):
select [column],
(select [a row] from [joined tables] where [condition]) as column2,
(select [a row] from [joined tables] where [condition]) as column3,
....
(select [a row] from [joined tables] where [condition]) as columnN
from [main table]
This is what I meant by crosstab query.
MORE DETAILS: [a row] contains basic arithmetic computations, [joined tables] join 5 tables (one of them has nearly a million records) and [condition] inludes several LIKE statements (that might be contributing to the problem??? - but I don't think this is the MAIN reason)
Problem is it gets too slow when the number of columns ([columnN]) increases.
What do you suggest?
Should I not do it as a crosstab, and run multiple smaller queries and put them in to one big table programmatically? Then I think, the sql server has to be smarter than me... Or is there something I'm missing?
PS: the SQL database is a MS SQL and running on windows server 2003.
Any help, hints, suggestions, questions are greatly appreciated.
|
|
|
|
|
OK - this is seriously in need of rewriting. The data that you are pulling back seems to be far too complicated. Like conditions are generally not very performant. Run this query and view it's execution plan, then run the indexing wizard on it.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
The query is (probably) looping thru every (select [a row]...) for EVERY row in [main table]. This means repeating each of the joins, then scrubbing each result set for each where [condition], for every single row in [main table].
Are you sure you can't pull the joins out into the main loop? If possible, I think that would dramatically speed things up.
|
|
|
|
|
Basically my output of the query should return something like this:<br />
(The numbers are average of some tests that were ran on differnt model and year of cars. In order to calculate these averages I need the joins both places.)<br />
<br />
______________|2000|2001|....|2006<br />
Avalon________|__12|__12|....|__11<br />
Camry_________|11.5|11.5|....|__10<br />
Celica________|__10|__10|....|__10<br />
Corolla_______|___9|___9|....|___7<br />
Highlander____|____|____|....|__10<br />
Land Cruiser__|__15|__14|....|__14<br />
<br />
It used to be faster when I had this query in 1 dimentional form:<br />
Avalon |2000| 12<br />
Avalon |2001| 12<br />
...<br />
Avalon |2006| 11<br />
Camry |2000|11.5<br />
...<br />
<br />
When I had the query result in 1D format I just changed to 2 dimentional format programatically (using 2D array and binary search). I figured it would be faster if I do the whole thing in one step within the sql query.<br />
<br />
Maybe I should just stick with the old version. I mean how do you guys make crosstab queries in MS SQL?<br />
|
|
|
|
|
Could I maybe see a little of your table structure and data? Not enough to give away any trade secrets...I just can't figure why this won't yield to a GROUP BY. Actually, you've piqued my puzzle-brain, and I won't be able to rest until I conclude with you "Yep; can't do it."
|
|
|
|
|
The structure itselft is not really complex.
A. Cars: Make, Model, CarID, Country, BodyType, etc
B. Tests: TestID, CardID, YearRan, etc
C. EngineTests: EngineTestID, TestID, etc
D. TestOutcomes: OutcomeID, EngineTestID, TestID, MyNumericValue, etc
A - B - C - D
"-" indicates one-to-many relationship
I need averages for selected cars and selected test years. Not a rocket science. It's fairly large database. You can think of the scale as current car manufacturers data.
As I said, it runs smooth if I do the select statement as 1 dimentional (by doing GROUP BY and turning it into 2D programmatically)
And I haven't been resting for about a week now.
|
|
|
|
|
This has the look of something that seriously needs to be converted into a data warehouse. Now that you are trying n-dimensional transformations, you really need to look at what SQL Server BI can do for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Is it smarter than the normal one? And does it cost more?
If it does I'll stick with my dumb one and break the query into smaller pieces and build the 2 dimentional table withing the program. Takes 30 seconds.
Thank you for all your help!
LESSON LEARNED: NO CROSSTAB QUERIES ON HUGE Databases!!!
Is that right?
|
|
|
|
|
If you've got SQL Server 2005, it's already present in there. It's also very powerful, and designed to solve issues like this.
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
