Hi All,
In SSRS 2005, how to fix the size (length) of the parameter since it is getting extended by length when the characters (data) is more.
Is there any way to fix the size and on click of the parameter then it should show the full value. Like usual drop down box.
Please let me know if you have any idea on this.
Thanks,
Senthil
Hey all --
Does anyone know if a sprocs parameter has a size limit? For example if you're passing in a XML document to a sproc - could that call fail based upon the size of the XML document? Consider memory a non-issue.
Hello,
I want to write a function that takes varying number of parameters.I mean:
Fonk(4,5,7)
Fonk(2)
Fonk(6,6,6,8)
There is a way to do this in T-SQL?
Thanks in advance...
Hi,
In my report,I have multivalue parameter for this parameter the Available values are from the Dataset.Suppose the dataset contains Names like this:
'AL - Alabama Center (Tuscaloosa)',
'AL - Regional Control Center (Birmingham)',
'AR - Arkansas & Rock)',
'Arizona Department of Health'
But my problem is the Multivalue parameter TextBox is of fixed size.Now i want the size of the text box to the size of the name which is of length long in the Name.
Whether it is possible or not.
How to achieve this.
I have created a SSIS package which runs every hour from my Machine. First part of my Package runs a Execute SQl Task and result set (single row, single column) is a comma separated list of Unique Record Ids which have been updated in last hour. (i.e. FJ1,FJ2,FJ3) I capture this result in a variable and when I User script taks to display the value of this property, it appears as it should be.I pass this Variable to Execute SQL Task on Different ODBC Server using a Stored Procedure.My SQL Statement is as Following
call DeleteRowsById(?)
And then I have assigned my Variable (as SQL_Longvarchar) to Parameter 1 in Parameter mapping Section. (ParameterSize is -1) but it doesnt delete the records from the table. (I ran the SP manually in Workbench and it works as expected) Then i had an Idea and
as part of my SP I started making entries in a Temp table just to see whats coming up as variable. Insert in the table shows only first Charecter from the variable and which is why its not deleting any records.I can not find why my variable is being truncated.
Is there any possibility to have the  Defaultvalue of  a Parameter in Bold fonts and rest of it as Default parametervalue=Defaultvalue ,"Bold","Default".So for example in drop down of parameter if the C is the current value it would look like this:
ParameteValue:
A
B
C
D
Hello, all
We are trying to work out a memory problem associated with a SQLCLR procedure we have developed.
This procedure will run properly on a SQLExpress box, but fails with an Insufficient Memory error (701) when run on our 32-bit enterprise edition server on W2K3 Enterprise Server. The server has 3.25 GB of RAM, and does *not* have the /3GB switch enabled; AWE is *not* enabled on SQL Server.
I initially suspected the problem was due to the idiosyncracies of how the BPool is allocated on SqlExpress and its 1GB buffer pool limitation, giving a 1GB MemToLeave region for SQLCLR allocations -- substantially larger than the default available on the server with it's default 256MB allocation (+128MB for thread stacks). That seemed to explain why a memory problem might not manifest itself on the client, but would on the server. I then altered the server to include an explicit "-g" parameter at startup, reserving 512MB for MemToLeave, but the query still fails with the same memory errors. I've tried values as high as 1GB (-g1024), but none have worked Our DBA has installed a recent hotfix associated with certain memory errors, to no avail.
It appears that the server will max out the CLR allocations at 102MB, regardless of the presence of the -g parameter. Could it be that the MemToLeave region really is that severely fragmented? There are still log messages indicating failures to reserve virtual memory...
One thing I notice is that the buffer's VM Reserved value never seems to change regardless of the value of the "-g" option. I would expect to see it decrease by the amount specified as reserved, eg -g 768 should leave, for example, something like 1.2GB VM Reserved for buffer pool allocation, but it doesn't. That tells me I'm not really establishing a larger MemToLeave area. Is this expectation incorrect?
Bottom line, we don't know why this procedure is running on the server, but not in SqlExpress. It is a procedure that connects to a remote Oracle database and collects about 14,000 records, but dies after sending about 7,000 or so records when run on the server. Again, the same query running against the same code on a SqlExpress box works perfectly.
Any thoughts or suggestions welcomed and greatly appreciated.
David
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
Hi, I'm specify the size of the interger dataypes in my code behind files; e.g.
commad.Parameters.Add("@SomeID", SqlDbType.Int, 4)
I know it may seem silly, but I have to ask:
Will specifying ths size (i.e. 4) eventually stop the auto-icnrement of the underlying primary key SomeID in the SQL Server 2005 database?
What restriction, if any, does this place if the next SomeID value is 10000, which is 5 digits? or 999999, which is 6 digits?
Thank you
I have an expression based project connection manager in an existing package and updating Packet Size parameter.
After update and clicking on OK, Packet Size value reverts back to 0 (which is old value). This connection manager is used by lot of Dataflow tasks in my package and recreating the connection manager and updating the reference is not a viable option.
Keep getting this error when positioning to the last page of a report.
Using Server 2003...SqlRpt Svcs 2000 sp2
Detail error msg:
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Anyone have any suggestions? Any way to find out what collection is blowing?...or where parameter name: index comes from?
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
View 6 Replies View RelatedI have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies View RelatedI want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter.
ExecuteNonQuery statement is called using SqlHelper.
Error : String[18]: the Size property has an invalid size of 0
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
View 4 Replies View RelatedThe Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.
View 3 Replies View RelatedHi,
An MSSQL DB running SAP indicates a smaller DB size (MMC & SAP) than the actual physical size. The difference is about 8 GB.
A lot of records were deleted before this. Did they remain in the DB as NULL values or something ?
Does anyone know what the reason for this could be ? And how to clean this up ?
Thanks in advance,
Paul
Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
Here are my scenarios:
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
It is possible to find table size and in that table each row size.
View 4 Replies View RelatedHello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.Â
How do I know , when this parameter is coming in the preview ?
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
Hi,
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
Thanks.
Kong
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,
Any help on this greatly appreciated...
ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
Many thanks
Hi,
I am using
exec sp_helpdb
go
dbcc sqlperf(logspace) for
getting database size and log size. Is this gives the correct
database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.
Thanks in Advance.
Seenu. S
I have a SQL Server database that is showing 2853.44 mb in size but when Iexport the data into MS Access the size is less than 1 mb. Can anyone tellme how to reduce the size of my SQL Server database so that it's less than15 mb?Thanks in advance! Rob
View 5 Replies View RelatedWhy is it that lately our full backup (.bak) file size is more than 2x the size of the actual database file size (.mdb)?
View 9 Replies View Relatedmy dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies View Related