Saturday, April 13, 2013

[SQL Server 2008 issues] Visual Studio 2008 SSRS - issue need help please urgent

[SQL Server 2008 issues] Visual Studio 2008 SSRS - issue need help please urgent

Visual Studio 2008 SSRS - issue need help please urgent

Posted: 12 Apr 2013 04:17 PM PDT

Dear Friends,I have designed a report in SSRS Visual Studio-but when i try to render it in Visual Studio or pload it on SSRS web get the following error-"The value of the Width property for the report 'body' is "466.61111in", which is out of range. It must be between 0in and 455in. (rsOutOfRangeSize)"any help will be appreciated please on this.ThanksDhananjay

CTE Vs temp table

Posted: 24 Jan 2013 02:50 PM PST

In my procedure i need to store temporally around 400 rows, 50 columns data. At current i have stored in a temp table. I used this temp table to select different columns 6 times in the procedure. And i am suffering performance issue with this procedure.So i want convert it from temp table to CTE, will it improve my performance ? Or if you have any other suggestion, please let me know.

Building a SQL Server test lab

Posted: 12 Apr 2013 06:45 AM PDT

Hi,I would like to set up a test lab where I can mimic SQL Server environments in a basic way. For example:1. A SQL cluster2. Mirrored databases3. SSRS/SSIS/SSASI think my first set of questions is about whether this can be done effectively through virtual machines, or whether I need some hardware as well. And also, what software is needed to make a passably realistic SQL cluster. Ideally I would do this at home to have it completely separate from my work environment.I don't have much networking experience or experience with current Windows servers (2008 etc.), so any advice on what general kinds of things I should study or practice would be very helpful. To take just one example, I don't know if I need to work on setting up my own Active Directory domain, domain controllers, etc.I'm curious about how people who frequent SQL Server Central go about this. I've done some initial Googling and found these pages which suggest ways to get MS software. https://www.dreamspark.com/Institution/Subscription.aspxhttp://blog.gurock.com/articles/taking-advantage-of-microsofts-empower-for-isvs-program/I realize the scope of this sounds ambitious, perhaps too much so, but at the most basic level I am trying to learn more about how to set up these things on my own instead of relying on other people all the time. I don't mean to attempt being an expert in everything, just competent in knowing the major parts involved and how to get them to work together.Thanks for any help or advice.- webrunner

Dynamic SQL in stored procedure doesn't return result set; when run in SSMS I get results

Posted: 12 Apr 2013 05:56 AM PDT

I have a stored procedure that builds a dynamic sql and executes it. It gives no error messages, but it doesn't return results, just a message that the command completed successfully. However, when I run the sp code manually in SSMS I get the results I'm looking for. Why would this be? The sp takes one nvarchar(max) parameter, @CLM. ANSI_NULLS, QUOTED_IDENTIFIER, and NOCOUNT are ON. In the sp there is one temp table #tmpAffPeople. Its columns are EmpLogin nvarchar(8), Matter nvarchar(15), NameandTitle nvarchar(max), TotalHours decimal(6,2), ClientMatter varchar(max). The text of the sp is as follows: [quote]DECLARE @SQLString AS nvarchar(max) SET @SQLString = ';WITH Emps (EmpLogin, AttorneyAndTitle) AS (SELECT EmpLogin, CASE WHEN EmpTermDate IS NULL OR EmpTermDate = ' + '''' + '''' + ' THEN LastName + ' + '''' + ', ' + '''' + ' + FirstName + ' + ''''+ ' [' + '''' + ' + Title + ' + '''' + '] ' + '''' + 'ELSE LastName + ' + '''' + ', ' + '''' + ' + FirstName + ' + '''' + ' [' + '''' + ' + Title +' + '''' +'] (inactive) ' + '''' + ' END as FullName FROM [LitigationHold].[dbo].[Employees] where JobCode IN (' + '''' + '100' + '''' + ',' + '''' + '300' + '''' + ',' + '''' + '400' + '''' + ',' + '''' + '500' + '''' + ',' + '''' + '700' + '''' + ',' + '''' + '800' + '''' + ',' + '''' + '1801' + '''' + ')) INSERT INTO #tmpAffPeople SELECT t.[EmpLogin],t.Matter, e.AttorneyAndTitle, SUM([Workhours]) AS TotalHours, c.ClientName + ' + '''' + '/' + '''' + ' + em.MatterDesc FROM TimeCard t INNER JOIN Emps e ON e.EmpLogin = t.EmpLogin INNER JOIN EliteMatter em ON t.Matter = em.MatterName INNER JOIN EliteClient c ON c.ClientNum = em.ClientNum WHERE t.Matter IN (' + @CLM + ') GROUP BY t.EmpLogin, c.ClientName, em.MatterDesc, t.Matter, e.AttorneyAndTitle ORDER BY TotalHours'[/quote]After this runs, in the sp I execute the @SQLString by calling execute with @SQLString right after it between parentheses. I then drop the table. I've done this kind of thing before and never had a problem. What am I missing? I'm using SQL Server 2008R2.

Virtualized SQL Server

Posted: 12 Apr 2013 05:50 AM PDT

Hello,I know that is a subject very discussed on the internet, but i need to ask here to have sure. Even because the papers i've found is a little old and i imagine that scenario could be change since then. The subject is: the use of the SQL Server (2012) on virtual machines. Lets assume we have a good hardware (storage, SAN, etc). Is there any kind of performance issue when using sql server on a virtualized machine? What is the most suitable virtualizer software for a production scenario?Regards,Rafael - Brazil

schema bind view

Posted: 12 Apr 2013 03:37 AM PDT

We need to change a view that is using schema binding. In the view we used a UDF.But when alter it, it complains that the function is not schema bind, we have to schema bind the function , then the view will work.But the UDF actullly is for a Split comma delimted strings, not actually bound to any tables.So should we schema bind the function so that the View can be altered,And we have two other sproc using the function, could they be affected if we change the function to schema binded?any other recommendations,

error string to datetime - where to set localisation for speific database ?

Posted: 12 Apr 2013 04:40 AM PDT

Hi,we have the following problem in our app (Sage OfficeLine):when changing a record, the app tries to write a record into an archive-tab which runs into an error since several days.After debugging and running the ms-profiler, we got this result:for writing the record the app calls 'exec sp_executesql N'INSERT INTO ...' with filling some datetime-fields by a string with an init-value (should be 30. dec 1899);this runs into failure, because '1899-12-30 00:00:00:000' is (now ?) thought as yyyy-dd-mm ...but some weeks ago all went ok.When installing a clear new demo-database by the app (same server, same user, same app - diff db),the same app calls the sp_ with 'Dez 30 1899 12:00:00:000AM' and runs well (so here it's clear where the month is set) !So - what is wrong on my productive database ?(search for "1899" in the attachments)

Partitioning Tables - Querying Question.

Posted: 10 Apr 2013 07:23 PM PDT

Hello,I hope someone can answer a question I was asked but was not sure about. If you partition a table by year, but the query does not use year, but some other criteria such as namej for example, does the engine still scan through all the partitions like if would if the table was just standard?Is it worth partitioning a 2-3 GB table? Would it be a better idea to review the indexes instead if performance is believed to be a little slow?Thanks in advance.Regards,D.

URGENT: Maintenance plans running as me. I dont want that to happen.

Posted: 11 Apr 2013 07:24 PM PDT

Hello,I have set up many maintenance plans in the past, but I have found that in one environment, the plans are logging in under my username, I have set SA as the job owner. In one plan, only the DBCC runs as me and the others are ok. But on another SQL machine, all the transaction log backups are logging in as me, again the job owner is SA and the 'run as' has sql agent selected.The problem is, I will leaving soon and my account will be deleted, I dont want the jobs to fail after I have gone. How can I resolve this?Thank you,Regards,D.

SSIS list file names in directory

Posted: 09 Apr 2013 04:12 AM PDT

Hello All,I am trying to create an SSIS package that will look at a directory, get a list of all the file names in the directory, and enter them into a table. Any advice to accomplish this?Thanks!Robert

If statement in combination with case statement

Posted: 12 Apr 2013 01:02 AM PDT

Hi,I have 3 categories ie Voice, Data, SMS.I have a list of case statements that I currently run based on these categories.SUM(CASE WHEN categ = 'Voice'AND rev <> 'ICT'THEN Durtn_Seconds_Rtd_Evnts ELSE 0 END)/60 AS voice_tot_usg,SUM(CASE WHEN categ = 'Data'AND rev <> 'ICT'THEN volume_kb ELSE 0 END)/1024 AS data_tot_usg,SUM(CASE WHEN categ = 'SMS'AND rev <> 'ICT'THEN count_events ELSE 0 END)AS sms_tot_usg,from table AProblem is Voice has 20 case statements, Data has 15 and SMS has 10. For example SUM(CASE WHEN categ = 'Voice'AND rev = 'Roam'THEN Durtn_Seconds_Rtd_Evnts ELSE 0 END)/60 AS voice_Roam_usg etcWhat I would like to do is in my table specify the categ as a variable and then only have the relevant case statements runso something likeIf categ = 'Voice'THEN(Perform all 20 Voice case statements and ignore the Data and SMS ones)The idea is that I have a massive fact table so if I can pass the Categ = voice in the where clause I can reduce the base and then insert the results of the voice case statements while ignoring the Data and SMS.I will then pass Data in the where clause, which will reduce the fact table and then only run the data case statements etc.Any ideas? I cant seem to get if statements to work in sql...Thanks a million

Really difficult to troubleshoot this replication problem

Posted: 12 Apr 2013 01:17 AM PDT

I have a transactional replication publication with about 30 tables. Most of these tables are being replicated as expected. When I run a trace I can see their sp_MSupd_dboXXXXX procedures running, I can verify that the data is in sync.However, for one table, this is not happening. When I looked in the database the table was being replicated to, it did not even have its own sp_MSins, upd, and del procedures. When I run sp_browsereplcmds and provide its publication_database_id and article_id, I can see that the command is waiting to be run.For example, it might say {CALL sp_MSins_dboTableName (fields here)}. It will sit there for a while, then disappear.My first assumption was that it wasn't being applied because the procedure didn't exist. So I wrote my own insert proc to confirm this (@c1 datatype, @c2 datatype etc...) and tested it to be sure it worked.But even then, when I do an insert, the command sits in the repl_commands table for a while, then disappears, and no row ever hits my table.I'm getting pretty lost here. Can anyone help? Thanks![b]EDIT [/b]I should add some more detail:The distribution DB lives on the subscriber. There are several publications from the same database on the publisher to the same database lon the subscriber (three to be exact, two work fine, one does not).On two of the publications, a tracer token is processed within about 4 seconds. On the one I'm having trouble with, the tracer token hangs in limbo indefinitely.

Query to get system databases

Posted: 22 Mar 2013 10:29 PM PDT

Hi,I want a query to get list of databases except system databases from sys.databases.I don't know how to differentiate system databases from user databases.Is there any way to do it?

Text analysis

Posted: 12 Apr 2013 12:20 AM PDT

I built a "noise word" table, and parsed a free text field from customer care exchanges, so now i have a word with a primary key from the customer care table...about half a million records, so the most rudimentary thing to do with it was to count the frequencies, which was illuminating, but now, what is the next step to understand what is going on with those exchanges?For example, I can link back to the 3500 customer care records that mention Ambulance and learn the zip codes, ages and genders of the requestors, or the 6500 records that mention Membership cards, but I know zero about text analysis or its methodology, so whats to read about it, and what are my next steps to learn from that data store?One idea is that this probably lends itself to a cube because the text, by dint of being parsed, is multidimensional, that is, some words are positive, some are negative and some neutral (i filtered everything but nouns...if that was an error then i could rerun the parsing to include verbs and pronons too, but as it is i think i have a pretty rich field to investigate becase the pk links the words to their originating records, and the frequency liinks them to their repetitions, if you see what i meanthanks a lotdrew

FISQL: DB-LIBRARY error: Data conversion resulted in overflow

Posted: 11 Apr 2013 11:45 PM PDT

I use fisql to query my SQL SERVER with this script[code="sql"]SELECT TOP 10 SUBSTRING(qt.text,1,100), qs.total_elapsed_time/1000000 Elapsed_time , qs.total_physical_reads Disk_readsFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtORDER BY execution_count DESCGO[/code] but i received this error DB-LIBRARY error: Data conversion resulted in overflowOperating-system error: SuccessIf i Remove "SUBSTRING(qt.text,1,100)" it's ok :/Someone can help me please ?

SQL Server Errorlog

Posted: 11 Apr 2013 08:35 PM PDT

Good Day.We are using SQL Server 2008R2 and it ROCKS1!!! I would like to know how one can change the order of the history in the SQL Server Errorlog as I want the oldest entries at the top . Any ideas ? Regards.Lian
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog