I have a scenario where I have to keep track of users last three passwords,
if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your last three passwords.."
Basically I can do : select top 3 passwords from MyTable order by createdDate desc
But how to do comparision ?
I was googling for if anything like array exist in sql server 2000?
I am monitoring my replication process and I have a problem with the loop detection, I can see origined transacctions in the server, executed in the same for the replication.
The subscriptions configuration is sp_helpsubscription
I have to automate a process that assigns sales leads to sales people.For example:Every day we buy a list of sales leads, it ranges in size from 50 -100 records.We have a team of sales people that also can range from 5 - 8 people.I need to take the new records and divide them evenly among the salespeople.If i get 50 records, and have 5 sales people, then each sales persongets 10 leads.--So, im guessing that I may need to have a nested loop inside this. Ihave tried it several different ways, but cant seem to get it quiteright.DECLARE @TotalRecordCount int, @TotalSalesPeopleCount int,@AmountForEach int, @LooperSalesPeoplerecords int,@LooperNewSalesLeadsRecords int, @SalesPersonID intSELECT @TotalSalesPeopleCount = COUNT(UserId)FROM SalesPeopleWHERE Active = 1--SELECT @TotalRecordCount = COUNT(*)FROM NewSalesLeads--SELECT @AmountForEach = (@TotalRecordCount/@TotalSalesPeopleCount)--SELECT @LooperSalesPeoplerecords = 1SELECT @LooperNewSalesLeadsRecords = 1--WHILE @LooperSalesPeoplerecords <= @TotalSalesPeopleCountBEGINWHILE @LooperNewSalesLeadsRecords <= @TotalRecordCountBEGINSELECT @SalesPersonID = (SELECT UserIDFROM SalesPeopleWHERE UniqueId = @LooperSalesPeoplerecords)SELECT @LooperSalesPeoplerecords =(@LooperSalesPeoplerecords + 1)UPDATE SalesLeadsSET SalesPerson_ID = @SalesPersonIDWHERE UNIQUEID = @LooperSalesPeoplerecordsSELECT @LooperSalesPeoplerecords =(@LooperSalesPeoplerecords + 1)ENDEND----Table structuresCREATE TABLE [dbo].[SalesPeople] ([SalesPerson_ID] [int] NOT NULL ,[FirstName] [varchar](20)NOT NULL) ON [PRIMARY]--INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,'Chris')------------------------------------------------CREATE TABLE [dbo].[SalesLeads] ([SalesLeadID] [int]NOT NULL ,[SalesPerson_ID] [int]NOT NULL) ON [PRIMARY]--INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)------------------------------------------------So in this case, all 3 salespeople should receive 2 salesleads each.I dummied this down quite a bit. It actually ends up being more like15 sales people, and about 400,000 sales leads. But it should work onany level.Thanks for any help you might shed on this.
I just upgraded a large SQL 2000 database server to SQL 2005, and I have 2 databases that won't attach to the 2005 Server. They are both very small, about 90MB in size, and when I attach them to 2005, the process alternates between running and spinloop status. It can sit forever, and will never complete. I restored backups before the upgrade to a SQL 2000 Server, and reran DBCC CheckDB on both, and got no consistency errors, Updated Stats and indexes, then detached and moved the data files to 2005, and same thing.
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
On a couple of occassions I have had MS SQL Server 6.5 with SP3 go into a loop writing the following message to the errorlog.
Error 17824 Severity 10, State 0 Unable to write on ListenOn Connection '.pipesqlquery', loginname 'sa'
By the time I have caught it, it has written about 1/2 gig worth of messages to the errorlog.
Both times it has happened I had been in SQL Enterprise Manager and the application stops responding and I am forced to terminate it. I believe this is the point as which the error starts..
I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following: WebpageId WebpageAddress Handler 1 /company/about ~/about.aspx 2 /blog ~/blog.aspx As you can guess, one of my queries will be a SELECT command where WebpageAddress = @address. The hiccup I have is with a friendly URL such as the following: /blog/2005/10/6 The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.) What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this: ~/blog.aspx?parameters=2005/10/6 While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server? My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
I will fetch records from table 1 based on tretmentid. get the count of records run the while loop inside loop I will compare the column values with the same table(table 1) for different tretmentid. if any one row matches will come out from loop and send status .
Dear experts,Again, sorry to bother you again with such a seemingly dumb question,but I'm having some really mysterious results here.ie.Create procedure the_testAsBeginSelect CustomerID
I am trying to write an SSIS package that will look though all the databases on a server (in this case 2k server) and get the list of tables in each database
I have created a Execute SQL Task with select name from master..sysdatabases where name <> 'Tempdb'
This is connected to a Foreach Loop that uses Foreach ADO Enumerator on the first table from the var generated in step 1 (List of databases)
Within the Foreach loop there is a dataflow that has a a sql command
select @@servername SQLInstanceName ,db_name() DatabaseName , name TableName --, getdate() RecordDate from sysobjects where type = 'u'
This should return all the tables in a database however the databases is not changing it just lists all the tables in the master database times the number of databases on the server
Can anyone point me in the correct direction for this one
I have a list of servers on a table, I take this list and pass to a for each loop container and that will grab some system information from each server that we give for our internal auditors.
The issue that I'm currently having with this package is that some times, 1 or 2 servers from the list are down and/or extremelly busy and it times out, either case causes the whole package to fail.
What I'm trying to do is, test the connection to the server prior passing the server name to the For Each Loop Container and log that information somewhere (Maybe a boolean field in a table), so then I check after the package finish and validate that the server was actually up or down.
I'm writing a query to loop through each instance on a server so I can get each instances relevant information. I can't seem to get each instances sql service and sql agent service, either one or the other.
DECLARE @InstanceNames varchar(50), @SQL varchar(2000) DECLARE @GetInstances TABLE (Value varchar(100), InstanceNames varchar(50), Data varchar(100), ID int IDENTITY(1,1)) DECLARE @Serverinfo TABLE (ServerName varchar(50), InstanceName varchar(50), ProductVersion varchar(20), ProductLevel char(3)
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1 Tableid, Processigndate Amount remainingCollectonCount Frequency 1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int while (select count(*) from @MyTempTable) > 0 begin select top 1 @FieldVal = FieldVal from @MyTempTable -- process @FieldVal then delete the row delete top 1 from @MyTempTable end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
I have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?
I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting.. How to access the values in row by row?
This is the code for calculating a formula field in Crystal Reports. I want to implement the same in Sql Server Reporting Services.. But it doesn't have the feature of For Loop.... The strings started with @ are formula fields.... Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR; for YR := 1 to {@CalcFiscalAge} step 1 do ( IF YR = {@CalcLifeCode} +1 then locFactor1 := 1; exit for; IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN HALF_YEAR := 2 ELSE HALF_YEAR := 1 ; LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4); MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4); IF MACR >= LINEAR then DEP := MACR ELSE DEP := LINEAR; locFactor1 := locFactor1 + DEP ; REM := 1 - locFactor1; locFactor := locFactor1; );
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101) then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
Hi All, I would like to know, how the datetime will be stored in the sqlserver datetime column. Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy. while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in yyyy/mm/dd format. So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database? Thanks in Advance. Regards, Dhanasekaran. G
I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
