Thursday, March 28, 2013

[SQL Server 2008 issues] There is insufficient memory available in the buffer pool.

[SQL Server 2008 issues] There is insufficient memory available in the buffer pool.

There is insufficient memory available in the buffer pool.

Posted: 27 Mar 2013 03:09 PM PDT

Please help me.I m try to import data from csv to table using bulk insert but it gives me insufficient memory available in buffer pool error.what i have to do for resolving this issue.Regards,Arjun.

Find a word in a String

Posted: 27 Mar 2013 01:54 PM PDT

How do I find a word in string? That word stands by itselfIn this example, CD is the word, it should only return row 1 row 3 and row 4 as cd appears seperately on those rows.Table AValues(varchar)abbc cd efabcdefadb ef cdad cd eg

The delivery extension for this subscription could not be loaded.

Posted: 27 Mar 2013 12:39 AM PDT

Hi All, I have a single SSRS report which is set with a daily (weekday) subscription. Randomly I get the following error when the subscription fires -[b]The delivery extension for this subscription could not be loaded[/b]. This doesn't happen all the time, it is random, sometimes the reports sends out via email fine, then sometimes not.Does anyone have any ideas why this would be happening? Unfortunately Google isn't being much help on this one..I am running SQL Server 2008R2 SP1 Cheers, Chris.

Find out The column list which are referenced by paricular table in stored procedure using DMV

Posted: 27 Mar 2013 05:10 PM PDT

How to find out The column list which are referenced by paricular table in stored procedure using DMV

how i can save image in sql server 2008

Posted: 27 Mar 2013 06:20 PM PDT

hi every onei am using vb 6.0 and i want to save my image in sqlserver 2008 how can i save and retrieve image from sqlserver

Existing While Loop

Posted: 27 Mar 2013 05:07 AM PDT

I'm looking at some code from a project that is not performing very well, and I've noticed that they have a while loop on a couple of the data gathering procedures that do a number of retries.But theres no obvious break only a Return 0 as per below.[code="sql"]WHILE @Retry<@LoopcountBEGIN BEGIN TRY --DO STUFF RETURN 0 END TRY TRY CATCH SET @Retry=@Retry+1 END CATCHEND[/code]Surely there needs to be a BREAK rather than a RETURN or does the RETURN act like a BREAK in this instance?

how to group using cases

Posted: 27 Mar 2013 05:36 AM PDT

hi,i need to group people who weight more less than 200 ordered by age (from the oldest to the youngest), and the result need to work with bigger tables.how can i build a case?, this is the data.my major problem is that i dont know how to build a case that once we have the first group of people will jump to the next person.create table #Person ( PersonId int identity(1,1), PersonName nvarchar(100), PersonHeight int, PersonWeight int, PersonBorn datetime )insert into #Person(PersonName, PersonHeight, PersonWeight, PersonBorn)values ('Loskov', 180, 80, '1988-01-19'),('Sychev', 190, 88, '1966-05-15'),('Evseev', 174, 91, '1974-12-29'),('Maminov', 166, 70, '1981-09-05'),('Orlov', 176, 89, '1972-12-29'),thanks!

Migration

Posted: 11 Oct 2012 10:17 AM PDT

Any article/guidance/experiences on Migrating application data from Iseries to SQL Environment using SSIS as well as wherescape? thanks.

MS SQL + MS Visual Studio VS Wherescape RED

Posted: 24 Dec 2010 07:53 PM PST

Have you ever heard of or used Wherescape RED to do the data warehouse developing? The fact is it is so popular in New Zealand, and maybe already hi-jacked the whole NZ BI industry. However, after I have been forced to use this product to do the MS SQL DW and database project for months, I found it has nowhere can compete with MS SQL + MS visual studio itself. AS an ETL tool, SSIS is far more flexible and scalable. As to version control, source safe or team fundation is far more sophisticated. in terms of documentation, the BI Documenter is certainly much advanced.What I also found is that Wherescape is also not able to keep up with the SQL server new functionalities? The autogenerated sql script is good on standardized point of view but largely diminished the developer's SQL skills.Indeed, I was totally lost why we need it under Microsoft SQL evironment? Could anybody give me your thoughts to clear my doubts?

Slow Cascade Stored Procedure & Hang

Posted: 14 Mar 2013 06:11 AM PDT

Table Image : http://tinypic.com/r/1075g6v/6So I have this query which searches id by id and calculates the cost accordingly. But is very slow and I would like to understand how I could convert it into a set based operation.So depending on our condition we calculate our modeled costs differently.When user updates a driver we can run a update on the entire driver column based on where it has changed.But when it comes to calculating the modeled cost. We do it row by row as the fixed cost differs and then divide by months. I have pasted the code below. Is there still a way to this by set based operation ?First we update the drivers in the same table depending on what value has changed using an update and then update the modeled cost row by row (which is really slow)Code :SELECT @rowCounter = 1, @totalrows = @@ROWCOUNTWHILE @rowCounter <= @totalrows BEGIN SELECT @currentId = tempId FROM @temp WHERE row = @rowCounter SELECT @newModeledCost = case when not exists (select 1 from dbo.DIMSTD_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model') then ISNULL(DriverValue1,0)*ISNULL(DriverValue2,0)*ISNULL(UnitA,0)*ISNULL(UnitB,0)+ISNULL(FixedCost,0) -- normal allocation for all scenarios else (ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(NULLIF(DriverValue2,0),1))* ISNULL(UnitB,0))+ISNULL(FixedCost,0) --(ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(DriverValue2,0))*ISNULL(UnitB,0))+ISNULL(FixedCost,0) -- allocation for model scenarios end , @oldModeledCost = ISNULL(ModeledCost,0), @newOct = (ISNULL(@newModeledCost,0) * (ISNULL(Oct, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newNov = (ISNULL(@newModeledCost,0) * (ISNULL(Nov, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newDec = (ISNULL(@newModeledCost,0) * (ISNULL(Dec, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJan = (ISNULL(@newModeledCost,0) * (ISNULL(Jan, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newFeb = (ISNULL(@newModeledCost,0) * (ISNULL(Feb, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newMar = (ISNULL(@newModeledCost,0) * (ISNULL(Mar, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newApr = (ISNULL(@newModeledCost,0) * (ISNULL(Apr, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newMay = (ISNULL(@newModeledCost,0) * (ISNULL(May, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJun = (ISNULL(@newModeledCost,0) * (ISNULL(Jun, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJul = (ISNULL(@newModeledCost,0) * (ISNULL(Jul, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newAug = (ISNULL(@newModeledCost,0) * (ISNULL(Aug, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newSep = (ISNULL(@newModeledCost,0) * (ISNULL(Sep, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))) FROM dbo.TBF_BUDGETExpenses WHERE BudgetId = @currentId --and not exists (select 1 from dbo.DIMSTD_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model') UPDATE dbo.TBF_BUDGETExpenses SET ModeledCost = @newModeledCost, Oct = @newOct, Nov = @newNov, Dec = @newDec, Jan = @newJan, Feb = @newFeb, Mar = @newMar, Apr = @newApr, May = @newMay, Jun = @newJun, Jul = @newJul, Aug = @newAug, Sep = @newSep, Username = 'Cascade', lastmodified = getdate() WHERE BudgetId = @currentId AND @oldModeledCost <> 0 Print 'Record Update ' + CAST(@currentId AS VARCHAR(15)) SET @rowCounter = @rowCounter + 1END

Error in application when started by SQL server agent

Posted: 27 Mar 2013 08:06 AM PDT

I' running SQL server 2008R2. I have a C# app that accesses a database and then constructs and sends an email based on the info. Outlook is used to send the email.I created an SSIS job to run this on a schedule. The app would get an error sending the email when I started it from the 'job' tab in SQL server agent, even if I signed on as the SSIS job owner. So I created a PROXY/credentials (PROXY uses an id that has access to the OUTLOOK profile I'm using to create the email) and set the SSIS job to 'run as' the PROXY. This worked and when I start the SSIS job (from SQL Server Agent) when I am signed in as the owner of the job, the emails get sent. However, if I try to start it when I'm signed on as a different user (that does have access to the OUTLOOK profile,) the app errors. I also receive the same error if the job is started using the scheduler. The error is "Error in Outlook logon: The server is not available."First tme asking a question here so hopfully I'm m aking sense.

Execute SQL in all database

Posted: 27 Mar 2013 01:50 AM PDT

Hi,I would like do execute multiple statements in all databases.I created the following code:DECLARE @C1_NAME VARCHAR(1000), @SQL VARCHAR(1000) DECLARE C1 CURSOR FORSELECT Name FROM sys.databases WHERE substring(name,1,3) = 'MyD'ORDER BY NAMEOPEN C1FETCH NEXT FROM C1 INTO @C1_NAMEWHILE @@FETCH_STATUS = '0'BEGIN SET @SQL = 'USE ' + @C1_NAME; PRINT @SQL EXECUTE( @SQL ); SET @SQL = 'SELECT * FROM MyTable'; -- or update MyTable set field1 = 1; PRINT @SQL EXECUTE( @SQL ); FETCH NEXT FROM C1 INTO @C1_NAMEEND CLOSE C1DEALLOCATE C1GOThe problem is that it is running only on the database where he was processed this instruction...There is another way to pass all database and execute SQL commands on each base?Thanks!!Jose Anchieta Carvalho Junior

how to calculate average time in sql?

Posted: 27 Mar 2013 05:09 AM PDT

Hi i have a big problem herei have a list of person in my databaseeach person have several entry date and exit datei would like to know what is the average time those person stays if the first person stay 2 days , the second 3 days ....i would like to calculate 2+3+... and give the averagethe problem is some person have entry date but without exit dateso i need to eliminate those person i will only consider person that have an exit following and entrythanks to help please its very urgent and important

CC# in String

Posted: 27 Mar 2013 06:35 AM PDT

Hi Guys,I have a table with a column that may have Credit Card number in the string. What I need to do is find rows that might have credit card numbers... Does anyone have function (NOT using CLR) to find group of numbers in a string that might look like CC#. The string also has date in it.I was working on someting like this, but I am sure others have already worked on a function to do so...[quote]WITH Tokens AS(SELECT 'you 123456789123-9999' AS Memo UNION ALL SELECT '11/29/2012 me 99123456789-99' UNION ALL SELECT 'this is my 9912345678999-88--99' UNION ALL SELECT 'What the 11/29/2012' UNION ALL SELECT '11/29/2012 98897788778') SELECT Memo, SUBSTRING(Memo,NULLIF(PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Memo),0),20) as CCN,patindex('%[0-9][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -]%',Memo),Substring(Memo, patindex('%[0-9][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -]%',Memo), 20) as CCN2FROM TokensWHERE patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Replace(replace(Memo, '-', ''), ' ', '')) > 0[/quote]Thanks in Advance!Laura

Can I use a case in a where?

Posted: 27 Mar 2013 05:04 AM PDT

Tried this below with no luck I Wanted to use today's date if the parameter @enddate was greater than todayWHERE (KindOfDay = 'Weekday') AND (Date BETWEEN @startdate AND CASE When @enddate > getdate() THEN Getdate() ELSE (@enddate + 1))Thanks in Advance.. Again :)Joe

SQL registration in Management STudio

Posted: 25 Mar 2013 11:34 PM PDT

From time to time, I need to test a users login with SQL, so I go to a registered servers properties and test from there. Somehow I saved a users id and password. But now I cannot resave or overwrite it with the sa userid and password. I even tried deleting the registration and re-registering, but the user id keeps coming back.

Arithmetic overflow error converting varchar to data type numeric.

Posted: 27 Mar 2013 02:18 AM PDT

Here is my query,select a.row,sum(CONVERT(decimal(11,0), a.value)) as total from(SELECT adc.row,adc.col,adc.value FROM AnesthesiaDrugCells adc right join AnesthesiaDrugs ad on adc.row = ad.rowwhere adc.ProcID=11080 and ad.ProcID = 11080 AND adc.row>=2 and adc.row<=11and adc.value != '' and ad.transmissiontype != 'I' and adc.value !='-' and (ISNUMERIC(adc.value)=1)) as awhere(ISNUMERIC(a.value)=1)group by a.roworder by a.row Resulting in error message 'Arithmetic overflow error converting varchar to data type numeric.'If I just execute,(SELECT adc.row,adc.col,adc.value FROM AnesthesiaDrugCells adc right join AnesthesiaDrugs ad on adc.row = ad.rowwhere adc.ProcID=11080 and ad.ProcID = 11080 AND adc.row>=2 and adc.row<=11and adc.value != '' and ad.transmissiontype != 'I' and adc.value !='-' and (ISNUMERIC(adc.value)=1)) I am getting,row col value7 1 505 1 110 5 48 1 1508 2 506 1 1006 4 504 1 35Now I need to sum all those values for each row.My result should be likerow value4 355 16 200 so on..'m not sure where am I going wrong. Please someone help.Thank you

Updating Part of Filename

Posted: 27 Mar 2013 03:08 AM PDT

Hello Everyone,What would be the best way to go about converting a part of this Filename from upper case to lower case?FROM: [b]APR[/b]0111OFR_AAAAAAAA_01TO: Apr0111OFR_AAAAAAAA_01

running total possible??

Posted: 27 Mar 2013 01:45 AM PDT

HiI have a view which selects name and date say to make it short...each name can contain more than one recordI want to keep a running total of names by month examplename1 1/1/12name1 1/2/12name3 1/2/12name3 2/1/12name1 2/1/13The end results I want to show January(or 1) =2(2 names)February = 2Im also writing this out to SSRS so maybe a formuala ?ThanksJoe

Space between parameters in reporting services

Posted: 27 Mar 2013 03:39 AM PDT

Hi,I am stuck with an issue which relates to managing space between parameters. I have a multi-value parameter with values like 'KWR TFR' and 'KFT IUT'. I know there is data available for these values in the database but because of the space in between the parameter values, it is not detected and the report draws a blank. If there is a value like 'KIETYR', then the data for the same is displayed on the report.I have managed to use the split function for the multi-value parameter but dont know what to do with this space in the values.Does anyone have any idea ?Thanks,Paul

Count number of days that a doctor works

Posted: 27 Mar 2013 12:09 AM PDT

I need to count the number of days that a doctor works in a month. My data has the doc's name and appointment date. The number of visits per month isselect MonthName, DocName, count(*)from Appointmentsgroup by MonthName, DocNameWhat I need to do is count each appointment day as one for each doc, then sum the count to get days worked. I just don't know how to do that efficiently.

Extract between &lt;div&gt; tags

Posted: 27 Mar 2013 02:37 AM PDT

I need to extract data from a sharepoint list, where the data is written within div tags. I got this so far:[code="sql"]DECLARE @c varchar(100)SET @c = '<div>Sunshine</div>' SELECT SUBSTRING( @c, CHARINDEX('>', @c) + 1, LEN(@c) - CHARINDEX('>', @c) - CHARINDEX('<', REVERSE(@c)))[/code]But when I adapt this to my table it says:[quote]Argument data type ntext is invalid for argument 1 of len function.[/quote]How can i work around this problem?thanks in advance!

Clustering an Existing SQL 2008 R2 Server

Posted: 27 Mar 2013 03:10 AM PDT

Hi distinguished SQL peeps, I have a need to cluster two physical SQL servers to achieve some form of HA. The tricky part is that these servers are already live and there is not any alternative tin to put them onto (the HA was an afterthought, as always). I am aware that you can not make an existing SQL instance into a cluster but I was wondering about anyone's experience with this, so all comments are welcome. My idea is to create two new instances on each of the servers to facilitate a side-by-side upgrade on the same servers. This would mean that if it all goes belly up I can revert to the default instance. One new instance would be the new active node for that server and the second would be the inactive node, so they would effectively fail over onto a single server in critical failure scenario, running at a potentially reduced capacity (not an issue, as servers are highly spec'd). Both servers have 1000+db's residing on them.Has anyone ever tried this? Do you know it's not possible? If so let me know your experiences. Much obligedE

Options for deleting millions of rows from a heap?

Posted: 27 Mar 2013 01:22 AM PDT

One of the Devs I work with "inherited" a DB that started life back in the '80s with ISAM and such. It's only a couple tables, but one has ~500M rows, and weekly he needs to delete several million rows from it.This table has NO PK / Clustered Index, nor are any of the columns (or group of columns) a good choice for such (every column has duplicates) Also thankfully, there are no FKs on any of the tables.Right now, the Dev is modifying his delete routine to read in a couple thousand items from one table, delete them, then go and grab the next couple thousand, wrapping the delete in a transaction to help keep the log from ballooning out of control...Myself and another Dev have been pushing him to add an identity column as a PK / CI to the table, so that he could populate his delete table with the identity value (hopefully speeding up the process) Of course, with a couple hunded million rows, it would take a while to populate that ID column...Thanks all,Jason

Any potential issues with putting a Stored Procedure in Master?

Posted: 27 Mar 2013 01:12 AM PDT

I'm working in a rather locked down environment, and would rather avoid adding a DB if I can. I'm thinking of using the CheckDB procedure created by [url=http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/]Gianluca Sartori[/url] to run a weekly DBCC CheckDB against all DBs on the servers.Other than the normal "don't use the system DBs for user stuff" is there any potential issues?Although I am going to check, and see if I can create a DB for these sort of tools, and if so, use that instead.Thanks,Jason

Enabling Trace Flag T1118

Posted: 25 Mar 2013 04:17 PM PDT

Hi, We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said its not much help sql server 2005 aftwrwards. We are thinking about enabling it. Is there any disadvantages of enabling T1118?Thank You

Table design and query performance

Posted: 26 Mar 2013 11:36 PM PDT

Hi All,I'm working on database where a table contains an ID that is prefixed with a 2 letter identifier typei.eAB123456AB345234AB423423XY456789XY234344Ideally i would like to remove the prefix completely and create a new column for this.However I don't have the capacity to do this now as a lot of changes would be required.My Question is, when querying the data, say i wanted to only get all XY records, would it be quicker if I created a new column storing these types. (AB,XY) and then filter where column = 'XY' while retaining the prefix on the id. Or create a bit column where XY is set to 1 and filter on this?Or since the data is already setup with the prefix is it quicker just to filter where left 2 is XY?Many thanks

Inserting rows from one table to another another with PK column value incrementing

Posted: 25 Mar 2013 09:15 PM PDT

Hi,We are using Sql server 2008 R2 express. We have two tables,table1(id int,name nvarchar(50)) and table2(id int,name nvarchar(50)). For both tables id is primary key.I want to move rows from table2 to table1 with PK value incrementing i.e i want max(id)+1 of table1 for all rows copying .I tried this query[code="sql"]declare @root int select @root=max(id+1) from Bgd_common.dbo.table1 insert into Bgd_common.dbo.table1(id,Name) select @root,name from Bgd_common.dbo.table2[/code]But its giving error as ''Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'dbo.Table1'.". How to do?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog