I have a select statement that is being processed through oSql on Sql Server 2000. There are 2 fields in the select statement that are defined in the dateabase as nvarchar(1). When I perform my select statement, they show up in the output as 4 char fields. See dataset below for example.
How do I get rid of the extra spaces in the output? I have tried using ltrim(rtrim(fielde)) to no avail. Fieldg (the S) is a nullable field and is being processed using an isnull(filedg, ' ').
hi please help me,i have a table queried using this sql, select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150% this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks
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
I created a report with same parameters. Two of them a based on a query. The first query contains a simple select * from ... the result will be stored in the parameter @param1. The second parameter contains select * from ... where ID = @param1. So far... If a pass the reportparameters of the report i will get a valuelist for the first query, but nothing for the second one.
How can I manage that the second parameters also gets a valuelist?
A while back I wrote a .NET assembly with a COM wrapper to provide an interface between Report Services and legacy MS Access applications. Basically, it returns the byte arrays from the .render method (output format emf) to be written into an Access image control.
This worked flawlessly on any 8.5"x11" RS report we threw at it on the first two Report Services I tried it against. When we set up a third Report Service at another facility, the same reports were coming out with the physical dimension metrics on the emf as10"x13", so the Access version of the report is clipped significantly on the edges.
I trapped the output into a System.Drawing.Image object in the .NET assembly and put a watch on it. The two servers that work as I expected show HorizontalResolution and VerticalResolution as 96.0946, while the one that doesn't has these values as 81.2799. The PhysicalDimension property shows 21542.3672 x 27939.4316 on the working servers and 25531.25 x 33031.25 Otherwise, the properties of the images seem to be identical. The PhysicalDimension values I see are completely consistent with the size units of 0.01 mm, as the the emf documentation indicates. The resolution of 96 is consistent with the "Windows default" values of 96 dpi. The ratio of the resolutions is correct for an 85% scaling factor for the "misbehaving" Report Service.
I have not been able to find any reason for the differences between the Report Services, and I have not been able to figure out how to control it. I tried setting DpiX and DpiY as 96 in the DeviceInfo for the render call with no change in the behavior of either server.
Can anybody enlighten me on what is happening and what I need to do to get consistent behavior between the servers? Or why the DpiX and DpiY settings for the emf rendering extension don't seem to work?
I have a set of data that I have sorted by a particular field. I want to output each row that is different from the next row.
In my data sample below, I want to capture the first instance of the ObjectID and all other instances where the Status changes (highlighted in yellow). The data is sorted by ObjectID and then Modified_Date.
AUDIT_ID OBJECTID MODIFIED_DATE STATUS
2753029 124248079 4/26/2007 11:30 Active
2753035 124248079 4/26/2007 11:30 Active
2753076 124248079 4/26/2007 11:33 Active
2754823 124248079 4/26/2007 14:31 Not Ready
2794102 124248079 4/27/2007 7:29 Not Ready
2817836 124248079 4/27/2007 17:06 Active
2817859 124248079 4/27/2007 17:09 Active
2818286 124248079 4/27/2007 18:00 Hold
2818473 124248079 4/27/2007 18:38 Active
2818483 124248079 4/27/2007 18:44 Active
2989403 124248079 5/1/2007 7:14 Active
3022521 124248079 5/1/2007 7:19 Active
3042121 124248079 5/1/2007 10:45 Complete
3082880 124248079 5/2/2007 7:07 Complete
I've been search books, help files and websited trying to see if this can be done is SSIS. Any ideas?
After reading Dan Guzman's blog entry (http://weblogs.sqlteam.com/dang/archive/2008/02/21/Dont-Bloat-Proc-Cache-with-Parameters.aspx) I started modifying some of my code to try it out and ran into a stumbling block. What size would you specific for a varchar(MAX) field? Since a varchar max field can hold up to 2 billion chracters I really don't think I need to specify 2 billion as the size. Anyone have any ideas?
I have MSDE installed on my computer and I'm using Web Data Administrator to manage my databses. The problem is that whenever I add a column with a length of more than 8000, I get the following error:
Length must be between 0 and 8000
If I create the column programmatically then i get this error:
The following error occured while executing the query: Server: Msg 131, Level 15, State 2, Line 2 The size (8005) given to the column 'Article' exceeds the maximum allowed for any data type (8000).
I need several columns that can hold around 32,000 characters. What's the deal? Is this a limit with MSDE, or am I missing something?
I have set up transaction replication between two databases. Data from a table in the first database is replicated to the same table in another database.
The table at the publisher already has some data in it. The table at the subscriber is empty. When the replication is synchronizing, I get the following errors in the replication monitor: *The process could not bulk copy into table "dbo"."virtualdatalocations_waitingqueues". (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037 *Field size too large
The table looks like this: CREATE TABLE virtualdatalocations_waitingqueues ( dataid int , personid int , queueid int , CONSTRAINT FK_vw_dataid FOREIGN KEY(dataid) REFERENCES datalocations(id) ON DELETE CASCADE , CONSTRAINT FK_vw_personid FOREIGN KEY(personid) REFERENCES persons(id), CONSTRAINT FK_vw_queueid FOREIGN KEY(queueid)REFERENCES waitingqueues(id) );
It used to run fine in the past. I couldn't find any help on google or on forums.
Hi, I have asked this question on 3 forums now and never get an answer, I don't know what is so hard about this question but I will try it here. I am using SHA512 in C# to convert a password and its salt to hashed. I need to store the password hash and the salt hash in the database in two fields. I was told to use binary field to store the hash data and that the output of SHA512 would ALWAYS be the same no matter how long the password is. I modified this hash example to use only SHA512 and to work with byte array instead of plain text. All I need to know now is what size I need to make my binary field to hold this password that is hashed. http://www.obviex.com/samples/Code.aspx?Source=HashCS&Title=Hashing%20Data&Lang=C%23 Say I have a password which is 30 characters max, and a salt which is 16 characters max. The password and the hash are stored in seperate fields in the same table. They are both hashed using SHA512 and are both being stored as byte arrays in C#, what size to I need to make the binary data type in order to hold the password, and to hold the salt. Thanks!
Hi,I am using MSDE together with Enterprise Manager.I have a table with a field named description.This field will be filled by a web forms's textbox web control.The textbox's maxsize attribute is set to "3000" characters.What size do I have to adjust for my DB field description?Is the size of 3000 in Enterpise Manager equal to 3000 characters for the textbox?I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.
We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?
Thanks ahead of time DMW
Edit: We are running SQL Server 2000 and the db at the time was running in simple mood
I have been running a merge publication & 4-5 subscriber all running perfectly good, but today I started getting this error below? I generated a new dynamic snapshot and re init the subscriber, but still the same. I synchronized the other subscribers and they are all running with no errors, even after a init. I.E. I would think this is data related, to this subscriber, but I have no further ideas how to track it down?
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options.
End of file reached, terminator missing or field data incomplete
Field size too large
The process could not bulk copy into table '"dbo"."MSmerge_contents"'.
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
is it possible to change the appearence of input fields for parameters on the report server? My parameter is Multi-value with quite large amount of available values. On report server, user can (without scrolling) see only the first value. Parameter values are quite long, so user has to move alternally with both vertical and horizontal scrollbars to find the right value.
I am running a stored procedure thru' isql. This returns only one field. The sp is executed fine but if the data has length greater than 255 the data is truncated. How do u capture the full output ?
Hi all, I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.
Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.
The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).
As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.
There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.
I am moving data from a flat file source to a SQL Server table. But I want to add a columm that IS in the destination table, but NOT in the source file. Say the table column name is XXX in destination table, and there will be a global variable called @[User::XXX] that remains constant throughout the package. I would like to put the variable value into the destination column, even though the source file does not contain the field. Is there an easy way to do this?
The problem is, ErrorColumn contains ID. Is the following code reference safe ? columnname = Me.ComponentMetaData.InputCollection.FindObjectByID(Row.ErrorColumn).Name
Is there any possibility for FindObjectByID to return NULL Reference in any case ?
I run a website which uses SQL 2000 and VB ASP. I would like to add a section to the site which posts the most popular data being queried from my SQL server. I'm sure this is possible, but I don't know where to start. Please let me know if any of you need specifics regarding my data and set up.
Hello. I work on a proprietary software package at work that has a SQL query engine. I'm able to query tables that I know exist but I would like to know what other tables are there in the db. Unfortunately I don't know the name of the database. Is there a SQL statement that will show me the name of the database that I'm running my query on?
Quick question guys, I remember running into this before but can't remember if I was able to complete it or not. What is the syntax format in the 'Report Parameters' dialog box selecting 'Non-queried' in the "value " field I want to select multiple value parameters ie 001, 002, 003, 004 for one "label" Every combination I tried it's not taking. Thanks in advance
is there any way i can make a field in a table accomodate more than 1023 charcters? i used the 'varchar' datatype and used a length of 2500, but still, I can't fill up a field with more than 1023 characters. Is there any way to change it? Also, is there any way to used a symbol or special character in a field? Can SQL server identify such a character? like the alpha or beta symbol...
I am loading from SQL Server 2008 to Access 2010 using SSIS. One of the columns in the table I am loading into is a Number datatype and Fieldsize is long integer. The values are being truncated, so I want to change the Fieldsize to DOUBLE. However, when I do that I receive the error below. What should I do? I would like not to change my Windows registry.
This error can be caused by one of the following:
The maximum number of columns allowed in a table or the maximum number of locks for a single file is exceeded.
The indexed property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table.
An expression is not specified in the Expression property of a calculated field.
If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry. However, this is not a recommended option.
If you use Registry Editor incorrectly, you could cause serious problems that require you to reinstall the operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
Make a backup of the registry. Find the MaxLocksPerFile registry value by using the Windows Registry Editor, and then increase the value. The MaxLocksPerFile value is saved as part of the following key:
If the Indexed property of a field and duplicate data is located in the table, reset the Indexed property to the previous setting, or remove duplicate records from the table.
While I was loading to the same table a few days ago, I received a warning and the task took approx 9 hours. I am attaching the screen shot.
have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.
For a very general example: In the .NET code, the following variables are recognized: sex="M" Paid=4350.00 Outstanding=28000.50
One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..
EVAL EXPR BOOL Sex='F' and Paid/Outstanding < 27.50 BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F' INT Paid*52.33
In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?
If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....
I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.
3. Add Union All component connected to Script source component
4. Add Script transformation component connected to Union All component
5. Mark F1 and F2 as input collumns
6. In the Input and Output section of the script transformation component check that length of the fields is 50 and 110 correspondingly
7. Edit Union All component: change Union All Input 1 to <ignore> for Output collumns F1, and F2; change Union All Input 1 to F2 for F1 and F1 for F2; change back <ignore> for F1, <ignore> for F2; set back F1 for F1, F2 for F2; Click OK to save
8. In the Input and Output section of the script transformation component check that length of the both fields is 110 now.
So, be careful to select output columns values in Union All component. If you choose wrong field by accident, and replaced it with correct one straight away, the length of output column could be stored not as you expect an need.
I have a problem about calculating data from the table. I want to sent a row to the local variables and calculate them by C#. Thank you in advance for every suggestion.
