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 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.
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? Please suggest, any pointer........ Thanks
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)
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 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 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.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
I used to loop through recordsets with ease in old classic .asp pages. Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below. I would love to learn how to do it to better develop my skills. SELECT * FROM zz_2007_Booth_Visitors WHERE COALESCE ([Product Interest - Other Actuator], [Product Interest - Chillers], [Product Interest - Other Chiller], [Product Interest - Electronic Products], [Product Interest - Other network interfaces], [Product Interest - Fittings], [Product Interest - High Vacuum], [Product Interest - Other high vacuum actuators], [Product Interest - Pick& Place and Transfer], [Product Interest - Teflon Products], [Product Interest - Training], [Product Interest - Valves& Manifolds], [Product Interest - Actuators]) Is Not Null Order BY [Contact Name]
IF [Product Interest - Actuators] IS NOT NULL THEN UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Actuator] IS NOT NULL THEN UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Chillers] IS NOT NULL THEN UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
Dear All. Have a nice day. I have db table, I need to update all fields of table. Please can you write code," for loop " how can update all fields of my table by loop. Thanks. Zahyea.
Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId If @ProdSize = 'XL' BEGIN UPDATE _ProductBatches SET XL = '0' WHERE ProductId = @ProdId END DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here. Thank you, Alec
