SQL 2000 Query - Database Discussion Boards

brucedkyle wrote:
There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.

[homer simpson voice]Oohhh...shiney objects in my reports...[/homer simpson voice]

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Hi,
I m having three store procedures
1. Test_1
2. Test_2
3. Test_3

in Test_1 i get output of one date
in Test_2 i user Test_1 to get output of one week
in Test_3 i want output of 7 weeks so for that when i use Test_2 in Test_3 i got error
"Insert exec statement cannot be nested"

please help me out of this.

Thank you.

I looked in my crystal ball, stirred the tea leaves, and tried the medicine man lodge and I was still unable to see your query. Do you think your could be so kind as to post it here so we might be able to fathom better your problem. I can not express how happy that would make all of us.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Jerry Hammond wrote:
Do you think your could be so kind as to post it here

Apparently not - I wonder if it is embarassment or he considers his code too proprietry to post, or maybe he worked it out himself.

Never underestimate the power of human stupidity
RAH

No, his lawyer is preparing an NDA document for you to sign. OMG | :OMG:

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Luc Pattyn wrote:
NDA

The National Association of Dyslexics? rofl...

(I know what it really is btw...)

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

_Damian S_ wrote:
National Association of Dyslexics

No. It is a pledge that I will Never Disrespect Alcohol.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

I have a table with these fields:
Season      Day         DateStart         DateEnd         WeekNumber         MonthNumber         MonthName         Year
S               Sunday      2/1/2009         2/1/2009         1                        1                        February            2009
F               Saturday   1/31/2009         1/31/2009      53                     12                        January            2009
.
.
.

This is a daily fiscal calendar. I need to get the season, Day the season started, Date of the season start, Date of the season end, week number of season start, month name of season start, and the year of season start. Because this is daily calendar I am thinking I can get the MIN(DateStart), and WeekNumber grouped by WeekNumber and year.
Season   Date_Start Week_Number Month_Number      Month                  Year
S     2001-01-28     1     1                 February        2001
S     2002-01-27      1     1                 February        2002
S     2003-01-26      1     1                 February        2003
S     2004-02-01      1     1                 February        2004
S     2005-01-30      1     1                 February        2005
S     2006-01-29      1     1                 February        2006
S     2007-01-28      1     1                 February        2007
S     2008-01-27      1     1                 February        2008
S     2009-02-01      1     1                 February        2009

Now I know the start date. I can also get the End date of each season by getting the MAX(DateEnd), and WeekNumber grouped by WeekNumber and Year.

S     2001-07-28 00:00:00     26     6     July     2001
S     2002-07-27 00:00:00     26     6     July     2002
S     2003-07-26 00:00:00     26     6     July     2003
F     2004-07-31 00:00:00     26     6     July     2004
S     2005-07-30 00:00:00     26     6     July     2005
S     2006-07-29 00:00:00     26     6     July     2006
S     2007-07-28 00:00:00     26     6     July     2007
S     2008-07-26 00:00:00     26     6     July     2008
S     2009-08-01 00:00:00     26     6     July     2009

Now I know both start dates of each season, and end dates of each season. For example, in 2001 Spring season starts on first line of first table, and ends on first line of second table. How can I put it together into one table? Basically I need to match them by row number but I don't know how to do so in SQL 2000? This is the final result set structure I need but the data is not correct:
Season   Date_Start                     Date_End                        Week_Num Month_Num         Month_Name         Year
S     2001-01-28 00:00:00     2001-07-28 00:00:00     1     1     February     2001
S     2001-01-28 00:00:00     2002-07-27 00:00:00     1     1     February     2001
S     2001-01-28 00:00:00     2003-07-26 00:00:00     1     1     February     2001
S     2001-01-28 00:00:00     2004-07-31 00:00:00     1     1     February     2001

CodingYoshi

Artificial Intelligence is no match for Human Stupidity.

Hi,

Looking at your data, it seems that you have a single entry per year in both the min and max result sets. If so, you can use derived tables, something like:

Select ...
From
( 
  --your first (min) query 
) as SeasonStart 
Inner Join
( 
  --your second (max) query 
) as SeasonEnd
on SeasonStart.Year = SeasonEnd.Year

Hope that helps.

Regards,

Syed Mehroz Alam
My Blog | My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

No, that won't work because some fall seasons start in one year but end in a different year so the join won't work. Any other ideas?

CodingYoshi

Artificial Intelligence is no match for Human Stupidity.

I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run.

Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id.

It didn't even make a difference when I set the proc to run WITH RECOMPILE.

Can someone give me a clue of what is going on here? I'm lost.

What are you using to filter the user permissions, I hope you have a table that links the GL accounts to a userid/group.

When the admin is used there is no filter so instant response, if you filter the results SQL has to do a scan of the table to locate the GL accounts, put and index on the linking fields (userid, accountid etc) in the various tables. There will almost certainly be indexes on the primary tables (userid on user table, accountid on GL table) but the linking table may not have any indexes.

Never underestimate the power of human stupidity
RAH

Actually, whether the user is admin or not, the security mechanism is the same. They have permissions to access certain branches, and those branches have access to GL accounts.

I checked the indexes and the estimated execution plans and didn't see anything strange. After some fighting with the system, it appears that all I had to do was declare a variable inside the stored procedure, set it to the value of one of the parameters and then use it in my select clause. For whatever reason, this fixed the issue (and so far, it hasn't popped up again). I'm guessing that it somehow managed to cache an execution plan that was inappropriate for the data it had.

It's still very strange.

Without seeing the code for the stored proc it's hard to know what the problem is.

Checking the indexes is good advice. If that doesn't work and you're still confused, you could try looking at the execution plan for the stored proc to see what it's actually doing. The SQL Server Query Analyzer lets you do this. Once you can see what the query is doing, you should have a better idea of how to fix it.

Maybe This[^] article can help.

Wout Louwers

I am a newer to database of SQL.

I want to create a website which mainly contains a table, wich may contains 20,000 items.

I want to know that whether due to the large table, then perhaps the speed of the website will be very slow for the user to request some
information from this table? if so, if I divide this table into several sub tables, then it would become faster?

secondly, between the following two techniques, which one should be better choice for my applicaiton (I am using ASP.net):

1: using the new ADO.NET data source controls, which are tightly integrated with the GridViewand DetailsView controls in ASP.net.

2: involves using the SqlDataAdapter class in conjunction with the DataTable,DataView, andDataSet classes.

any expert can give me one hint?

thank you!

20,000 items in a table is not terribly large for a database engine, so you should be fine with response time. The main thing to remember is that a good design will give the best performance. You should research topics like "database normalization and design". I would only split the data into sub tables where it makes sense. For example you may have a customer table which contains the customer ID and address and an Orders table which refers to the customer number; this way you don't store the customer address for each order created. (This is part of good database design)

Relative to the two techniques you mention,
1: using the new ADO.NET data source controls, which are tightly integrated with the GridViewand DetailsView controls in ASP.net.
2: involves using the SqlDataAdapter class in conjunction with the DataTable,DataView, andDataSet classes.

Look at the following article by Microsoft:
http://msdn.microsoft.com/en-us/library/aa581778.aspx[^]

Look around the MSDN site for all kinds of helpful "How To" articles and videos. Thumbs Up | :thumbsup:

I think Davids answer is excellent, except 20,000 rows in a SQL database is miniscule, we have systems with 20 million rows. The trick is not returning all 20k rows to the user, this is the UI design, only show them the minimum number of rows to meet their needs.

Check out tuning and index design, get to know your database.

As for using the MS tightly integrated adapters and strongly typed datasets I loathe these constructions, the developer (new) has no real understanding of what is happenning. A large number of post on these forums are cries for help from new devs who cannot customise their data because they are using a wizarded widget they learnt in the tutorials. By all means use the tutorials but remember they are only the FIRST step, you are going to need a deeper knowledge to become proficient.

Never underestimate the power of human stupidity
RAH

Hi Guys ,

Is there any script that i can run on the database server so that i can retreive all the Computers(IP addresses) that tried connect to the database?

Please advice.

Many thanks,

Regards,
kibrom

I think you can't retrieve client IP's trying to connect to your DB.You could query net_address from sys.sysprocesses which is unique identifier for the network adapter on the workstation of each user ,but how to refer it to user IP I don't know. Confused | :confused:

Hi all,

how to check the existance of user name from Sql server with case sensitivity.

You're not the first to ask such question[^] ...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

I would like to change my default port 1433 for remote connection to some another port? What port number can give as another port? Is there any rule? How to change the default port?

Iam using sql server 2008.

Any Ideas and Guidences?

Thanks

From SQL Configuration Manager, Just need to change the tcp/ip port number as anyone new number.
Let it be useful to someone like me.

I Want to display more than one rows data in just one row.

My Data is:

Code Name
50141 abc
50141 pqr

I want Following Data in result.

Code name
50141 abc,pqr

Please Give me a solution.

Thanks & Regards,
Fazal