Mysql EXPLAIN = What In MS SQL 2000
Oct 29, 2004If you have a table named Users then using mysql
you can run EXPLAIN Users; to get all the fields & types, etc.
How would you do this same thing in SQL 2000?
ADVERTISEMENT
Any Books That Explain How To Setup Windows 2003 Server + SQL Server 2000?
Nov 24, 2004My uncle runs a small networking company and has extra licenses for Windows 2003 Server as well as SQL Server 2000. Since I just graduated from college and have started working as a database programmer (for a different company) I'd like to setup a small server at home to learn more about SQL (as well as networking, but SQL is my primary concern). I know I can setup SQL Server 2000 on my main PC, but I'd still like to set everything up in a server environment.
So, what I'm wondering is if any books (or web sites?) exist that walk you through setting up Windows 2003 Server and SQL Server 2000. I've actually set these up before (it's not very complicated) but I'm not sure if I did it the "correct" way.
Ideally if any books exist on O'Reilly's Safari Bookshelf that would be even better.
Thanks in advance,
John
MS SQL 2000 And MySQL
Mar 14, 2006I have a Windows 2003 Server that currently has MySQL installed and running a compan intranet. And I need to install MS SQL 2000 to be used with a database interface. Can I have both these versions of SQL installed and running on the same server?
View 1 Replies View RelatedSQl Server 2000 Vs. MySQL 5 .0
Apr 7, 2006hi !
i am using SQL Server 2000...someone told me the new version of Mysql 5.0 is very powerfull and it is better than MS products?!!! any good resources about the benefit of using SQL server 2000 rather than open source?!!
Thanks!
Converting Mysql 2 Sql 2000
Feb 19, 2004how do i go about converting the Mysql Script below to Sql Server Script ????
CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
INSERT INTO cat VALUES (1,'Not Categorised');
ALTER TABLE cat AUTO_INCREMENT = 5;
i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5
i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');
but when i enter the data in i get the error below
An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.
please can anyone help or advice
thanks
MySQL LIMIT Equivalent In MS SQL 2000
Oct 11, 2007I am looking for the MS SQL equivalent of MySQL LIMIT.
I know that the code in MySQL is
SELECT col_name FROM tbl_name LIMIT x,y
where x = the starting row and y = the offset
E.g. SELECT movie_name FROM my_movies LIMIT 10,5
This will give me the records from 11-15
I would like to know what is the MS SQL equivalent for the above?
Replication : SQL Server 2000 X MySQL 5.0.41 (Windows)
May 23, 2007
Good day!
I am carrying through a replication of the transacional and heterogeneous type between the SQL Server 2000 and MySQL 5.0.41, through driver of ODBC of the MySQL (version 3.51.15).
Unhappyly I am not having success!
I make all the necessary configurations, but in the hour to create schema in the MySQL, only the table msrepl7 is created (but nondata) and occurs the following error: €œDate too long will be column €œtimecol€? at row 1€? and later €œInvalid cursor state€?.
The first error must to the size of the column timecol that it is created as char (16), but the value that goes to be inserted is to char (19) - > ({ts'2007-05-01 20:45: 21 '}) The table that is published has only two entire fields and the same one is not created in the MySQL, exactly with the option to initiate schema. I looked for in some sites and I found one that said to bind query Log of the MySQL and to catch the command of insert for the table msrelp7 and to insert in the hand, after making this to come back to the SQL Server and to ask for to synchronize again. Did not function, now only with the error of €œInvalid cursor state€?! To complete one another person in one another site it placed that was to exclude the table msrepl7 that everything would function, did not function!
Thanks!!
Cristiano Rodrigues
Inserting Mysql Dump File To SQL Server 2000
Apr 14, 2004Hi,
I would preciated if some could help me with this problem.
My have used Mysql database and now i have to change my database to sql server.
I have made a dump file from mysql db and i would like to insert that dumb file to sql server.
Have any idea how it is possible?
cheers,
inkku
Linked Server To MYSQL Using OLEDB Provider For MYSQL Cherry
Feb 12, 2007Good Morning
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'
Many Thanks
David Hills
Can Someone Please Explain This To Me...
Jun 26, 2007why do the following return the same datasets?
select * from myTable where myData = ''
select * from myTable where myData = ' '
in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL...
P.S. I'm using T-SQL
How Do I Run 'explain' On A Sql??
Jun 27, 2001Hi, does anyone know how to run an 'explain' on a sql query on SQL Server 2000?
Thanks.
Could Someone Please Explain The Following...
Dec 1, 2005As previous threads have indicated I've been trying to figure out what's slowing down our 3rd party ETL tool.
My research has figured out the culprit, but now I need some advice on the best way to handle it.
Here's what's going on...
SPID 1 = dynamic sql passed from Crystal
SPID 2 = ETL tool
SPID 3 = report in stored proc
Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.
Here's the scenario
SPID 1 is running (inefficient select)
SPID 2 is waiting
30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH
SPID 3 fires off
Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1
Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.
My questions:
1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update?
2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?
Thanks in advance
Explain
May 2, 2008Any one explain me about this query exactly wat it will do
backup log iiswebappauditdb with truncate_only
Explain Me
May 8, 2008set @tb1='rptmain.Month01.DBO.LCDB' in
View 1 Replies View RelatedCan U Just Explain....
Nov 3, 2007Query:
insert into aaaa values(',Â?'&',',Â?'&');
BUJJI.
Can Some Explain This To Me?
Oct 25, 2007Hello,I run the following query on a database (SQL 2005):delete from person where pers_companyid in (select pers_companyid fromcompany where comp_expiry is not null)I wanted to delete all people associated with a company which had anexpiry date. The mistake I made was that the column pers_companyiddoes not exist. It should have been comp_companyid.However, the query ran anyway and deleted all records from my persontable? If I run the subquery on its own then it doesn't run as thecolumn is missing.Shouldn't I have got an error running this query?Thanks,David
View 2 Replies View RelatedPlease Explain Transaction For Me
Sep 29, 2007 I found this on http://msdn2.microsoft.com/en-us/library/86773566.aspx which i am hoping may be able to resolve my problem but I am not sure I actually understand it. Where it shows // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction");
is the "SampleTransaction" nothing more then just a name of the transaction that is being created through this code or is it actually pointing to something?I believe that I understand the rest. Oh and in case you have a better way of doing what I am needing here is the SQL Query that is pulling info for me to display on a pageSELECT p.product_ID, p.class_ID, p.category_ID, p.product_name, p.product_desc, p.product_image, p.product_dimension, p.product_o1, p.product_o2, p.product_o3, p.product_ac, p.product_ph, p.product_photo, pcl.class_name, pca.category_nameFROM products AS p INNER JOIN productClass AS pcl ON p.class_ID = pcl.class_ID INNER JOIN productCategories AS pca ON p.category_ID = pca.category_IDBasically I am using the transaction to perform multiple updates seeing as how I will need to update multiple tables. private static void ExecuteSqlTransaction(string connectionString){ using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } }}
Please Explain This SQL Trigger
Sep 25, 2005I am new to triggers.I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @counter = 1 When does this counter change value? USE Northwind GO CREATE TRIGGER PriceCheck ON [Order Details] FOR INSERT AS DECLARE @counter int SET @counter= @@ROWCOUNT DECLARE @unitprice money DECLARE order_details_insert_cursor CURSOR FOR SELECT Unitprice FROM inserted IF @counter = 1 BEGIN IF(SELECT UnitPrice FROM inserted) > 300 -- If the price entered is greater than 300 BEGIN --print a warning PRINT 'Cannot enter price greater than 300' --Take back the command ROLLBACK TRANSACTION END END ELSE BEGIN OPEN order_details_insert_cursor FETCH NEXT FROM order_details_insert_cursor INTO @unitprice WHILE @@FETCH_STATUS = 0 BEGIN if @unitprice > 300 -- If the price entered is greater than 300 BEGIN --print a warning PRINT 'Cannot enter price greater than 300' --Take back the command ROLLBACK TRANSACTION RETURN --Exit trigger immediately END FETCH NEXT FROM order_details_insert_cursor INTO @unitprice END CLOSE order_details_insert_cursor END DEALLOCATE order_details_insert_cursor
View 3 Replies View RelatedExplain Xp_cmdshell
Feb 2, 2006I've got some questions about xp_cmdshell and was hoping someone could explain some things to me. I've written a trigger for a table that uses 'xp_cmdshell' to launch a VB.Net application. The syntax is correct and it "seems" to work but in the way I intended. When the app launches there is no GUI present but when I check the task list I see that the exe has been launched. This intrigued me so I started playing with it in Query Analyzer. When I run the commandexec master.dbo.xp_cmdshell 'C:WINDOWSsystem32otepad.exe'I seen a process for notepad show up on my processes list started by 'system' as opposed to myself and notepad still doesn't open up for me to see. I also noticed that the query executes until I kill that particular process. I'm guessing cause it's waiting on the tranaction to complete. What I want to know though is why can't I see the program when I open like this? Is it because 'system' is the user behind it or is there some extra parameter to throw in the xp_cmdshell?
View 3 Replies View RelatedEXPLAIN Function
May 19, 2008Is there some equivalent to MySQL's EXPLAIN function? (or some utility that does the same thing?)
Basically the function takes a query and will return a list of the tables in the query and the number of rows that would need to be checked. It's used to check how well the indexes are set. (If a table returns a very high number, that could be a bottleneck for the query).
Any help is appreciated. Thanks.
-D. Israel
Explain Plan
Mar 17, 2008Dear All,
what exactly is ExplainPlan and maintenance plan? and where would we use these?
i've found some artilce in the NET, but i'm not satisfied
please provide me good articles and your words for me
Vinod
Even you learn 1%, Learn it with 100% confidence.
Can Someone Explain This Behaviour?
Jul 23, 2005Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin
View 8 Replies View RelatedExplain Plan
Jul 20, 2005Hello.Does someone of you know if is there a 'explain plan' like function in SQLServer (similar to explain plan in Oracle or DB2)If so then how it works, where stores data and how it can be retrieved?Best regardsBagieta=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=dbDeveloper - Multiple databases editorhttp://prominentus.com=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
View 5 Replies View RelatedCould Someone Explain This Script To Me
Jan 29, 2008Hi,
Could someone help me understand this script:
DECLARE @TempValue tinyint
SELECT @TempValue = 1
FROM dbo.TABLE_NAME (tablockx updlock holdlock)
WHERE 1 = 2
To my understanding, it would lock all the row in the table at once.
Can Someone Explain This Error Please
Oct 9, 2007
Upon executing the following:
create function test()
returns @tmp table
(
somevalue varchar(max)
)
as
begin
select '' as ttt
into tmp
insert into @tmp(somevalue)
select ttt
from tmp
return
end
I get this error:
Msg 443, Level 16, State 15, Procedure test, Line 8
Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.
I've searched around and honestly cannot find a definitive description of what this means. Can anyone explain in simple terms for a simpleton like me?
Thanks
Jamie
Equivalent To MySQL's Password() Function? (was MySQL To SQL Server And Password())
Mar 3, 2005I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
Could Anybody Explain To Me Why Sqldatadpater Does Not Allow Subqueries?
Aug 17, 2007Dear experts,
Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS.
Well, actually i was using IN.
I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed??
Really appreciate it if some expert can tell me.
Thanks in advance
You Can't Explain This! ARRRRG! SQL Ghost!
Dec 23, 2007Hello all,
Where do I start? My son and I wrote a small ASP.net 2.0 website on XP IIS 5. Works ok with the standard login controls.
I FTP'ed the files up to a Windows server2003. I installed the SQL publishing wizard. I created a .sql file of the data base.
I ran the file in SQL2005 manager to build the data base on SQL express running on the Windows 2003 server. It installed, no errors. great!
The website would not run with Data base connection errors , like files or data base was read only, duplicate database , can not run mydatabase.create. something like that.
I created a new app pool and made sure network service was the account. Gave network server permissions to the folders, it started working great.
Now sit down. I created a few accounts using the website ok, no errors, Great! Then check the database to see if the passwords were encrypted. The new accounts were not there.
But the accounts went somewhere???? Must be two databases???? One hidden?
I detached the database I created with the .sql script file.... to see if I would get an error on the browser. nope still worked. Hmmmm other hidden database still working.... I stopped database server
in the management tool, It still worked....Ok, I'll get the house check for poltergiests.
I went to SERVICES. The service was stopped(SQLEXPRESS).. I changed auto start to manual. Browsed back to the site again, finally an error.
I restarted the service, website worked again, but the database, the only user database, is still detached. Created more user accounts with no error....
Seached the hard drives for .MDF files. Found the file I detached and also the ASPNETDB.MDF that I orginally FTP'ed to the server earlier in the APP_DATA folder of the website. I renamed the
MDF file to see if then I would get an error, yep I did. It then automatcly re-created the MDF file exactly the same size. Two files in the directory now and now a new error in the browser
about a database mismatch. Ok that makes sense. But it will not allow me to rename the new file so I can rename the original.
Question is how does SQLEXPRESS run the ASPNETDB.MDF file in the web folder which is not attached to the database engine?
Why use a SQL pub wizard if ASP.NET creates the database automaticly? Or just "Runs" it? AM I HIGH?
Thanks in advance, before I take drugs and sit in a dark closet.
-Wade
Can Anyone Explain To Me Why This Is Not A Good Idea
Jan 28, 2008I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely.
Is a view a good idea in this case? Is the Select * from View really a bad idea?
Thanks
Explain Exists() Function
Apr 8, 2008Can any one explain to me how to use the exists() function and what exactly it is used for?
View 1 Replies View RelatedCan Someone Explain The Precision Of An Integer In A Sql Db Pls
Nov 3, 2003Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???
Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.
Thanks
Can Someone Explain This Strange Value Of @@ROWCOUNT?
May 31, 2006I have noticed the following strange situation, but cannot find any explanation. The @rows variable is 1 , even though the @@ROWCOUNT value just after the select statement is 0. (I have checked this by stepping through the SP).
update Products set ParentId = @parent where ParentId = @lastParentIdset @err = @@ERRORset @rows = @@ROWCOUNT
However, if I set @rows before @err then the @rows is correctly set to 0. (as in script below).
update Products set ParentId = @parent where ParentId = @lastParentIdset @rows = @@ROWCOUNTset @err = @@ERROR
Can someone please explain why this is happenning?
Explain The Below Stored Procedure
May 7, 2008CREATE procedure r_routeGetCache1
@pLongest varchar(32),
@pLongestCls int,
@pDate datetime,
@pSrcInt varchar(7),
@pSrcIntGroup varchar(64),
@pSrcIntDom varchar(64),
@routeclass int=0 ,
@pCLI varchar(32)='',
@pOperatorGroup int = 0 ,
@isgrade int=0
as
begin
declare @pc varchar(2)
declare @dd int
declare @hh int
declare @mm int
set @pc=left(@pLongest,2)
set @dd=1+(@@datefirst+datepart(dw,@pDate)-2)%7
set @hh=(datepart(hh, @pDate)-0)
set @mm=datepart(mi, @pDate)
set nocount on
--- Azam
select top 1 @routeclass = routecls from r_interface
where [group] = @pSrcIntGroup
and state='I'
if @routeclass is null set @routeclass=0
--- End
--insert
CREATE TABLE #operator_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[cls] [varchar] (1) NOT NULL ,
[pc] [varchar] (2) NOT NULL
)
insert into #operator_selected(routecls,oprid,cls,pc)
select x.routecls,x.oprid,x.cls,x.pc
from RoutingV3..r20_route_timecls x (nolock),
RoutingV3..r_TimeCls y with (nolock),
RoutingV3..r_interface tit with (nolock),
RoutingV3..r_timecode tco with (nolock),
RoutingV3..r_daycode dco with (nolock)
where x.routecls=0
and tit.id = x.oprid and tit.state = 'I'
and y.tintid =x.oprid and y.cls=x.cls
and y.dc=dco.id and y.tc=tco.id and y.sc=0
and @dd between dco.d1 and dco.d2
and ((24+@hh+isnull(tit.prefixcls,0))%24) * 100 + @mm between tco.h1
and tco.h2-1
and x.pc=@pc
CREATE TABLE #timecls_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[pc] [varchar] (2) NOT NULL ,
[prefixcode] [varchar] (50) NOT NULL ,
cnt int,
clsA int,
clsP int,
clsO int,
clsW int,
[cls] [varchar] (1)
)
insert into
#timecls_selected(routecls,oprid,pc,prefixcode,cnt,clsA,clsP,clsO,clsW,cls)
select a.routecls, a.oprid, a.pc, max(a.prefixCode) prefixCode,
count(*) cnt,
sum(case when a.cls='A' then 1 else 0 end) clsA,
sum(case when a.cls='P' then 1 else 0 end) clsP,
sum(case when a.cls='O' then 1 else 0 end) clsO,
sum(case when a.cls='W' then 1 else 0 end) clsW, 'Z' cls
from RoutingV3..r20_route12 a(nolock), #operator_selected b
where a.routeCls=@routeclass --b.routeCls
and a.oprId=b.oprId and a.cls=b.cls
and a.pc=b.pc
and left(@pLongest,prefixLen)=a.prefixcode
--- Exclude Artificial
--and not (id<0 and parentId is not null and parentId>0)
group by a.routecls, a.oprid, a.pc
update #timecls_selected
set cls = (case when (clsW>0) and (cnt = clsW) then 'W'
when (clsO>0) and (cnt = clsO) then 'O'
when (clsP>0) and (cnt = clsP) then 'P'
when (clsA>0) and (cnt = clsA) then 'A'
else 'Z' end)
update #timecls_selected
set cls = (select max(a.cls) from RoutingV3..r20_route12 a (nolock)
where a.routecls=#timecls_selected.routecls
and a.oprid = #timecls_selected.oprId
and a.pc=#timecls_selected.pc
and a.prefixCode=#timecls_selected.prefixCode
and ((a.cls='A' and #timecls_selected.clsA<>0) or
(a.cls='P' and #timecls_selected.clsP<>0) or
(a.cls='O' and #timecls_selected.clsO<>0) or
(a.cls='W' and #timecls_selected.clsW<>0))
)
where cls = 'Z'
CREATE TABLE #route (
seqno int identity(10,10),
priority int,
[id] int,
isactive int,
reason int,
exception int,
exceptionCls int,
calcexception int,
ext int,
oprid int,
parentid int,
routecls int,
prefixcode varchar(50),
universe varchar(5),
domain varchar(5),
pdomain varchar(5),
[group] varchar(40),
interface varchar(40),
userinfo varchar(40),
hint varchar(100),
clsorg char(1),
cls char(1),
cost float,
flag int,
rating int,
access varchar(10),
redlist int ,quality float)
if @isgrade =0
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else isnull(x.exceptionCls,0) end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc and
x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.cost
end
else
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else 10 end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc
and x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.quality,x.cost
end
set nocount on
select top 5
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #topN
from #route x, RoutingV3..r_interface y with (nolock)
where x.isActive=1
and x.oprId=y.id
and @pLongest like x.prefixcode+'%'
and exceptionCls<>0
order by seqno+[calcException], x.cost
select
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #all
from #route x, RoutingV3..r_interface y with (nolock)
where x.oprId=y.id
and @pLongest like x.prefixcode+'%'
order by seqno+[calcException], x.cost
delete #all
from #topN a, #all b
where a.id = b.id
set nocount off
select * from #topN where isActive=1
union all
select * from #all where isActive=1
end