Non Significant '0' Removed From Alphanumeric Code
Feb 2, 2007
Hi all,
I have a problem with alphanumeric codes in SSIS.
I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.
In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!
Does anyone have an idea how to avoid this ?
View 9 Replies
ADVERTISEMENT
Apr 30, 2014
is there a significant difference of using the WITH statement rather than a Temp Table besides less typing. l
View 1 Replies
View Related
Jul 23, 2005
Recently, using PERFMON, I've been rather dismayed to find that ourapplication is averaging 3 - 4 lock timeouts per second, andfrequently has extended periods of several minutes where this figurereaches the hundreds.Average LockWaits/sec are less than 0.05, and TableLockEscalations/secare less than 0.5These last two seem very good to me, and as a result I wouldintuitively expect a LockTimeout figure of near-zero.Can anyone suggest why the measured LockTimeout value might be so high?Is the measured value actually considered "high" at all? Doubtlessthis depends on a number of things (transaction rate, number of usersetc), but a rule-of-thumb opinion would be welcomed.
View 4 Replies
View Related
Feb 8, 2007
I have encountered what I consider to be a significant bug when importing Excel spreadsheets using SSIS.
If in the first data row a particular column does not contain a value (i.e. it is null) then SSIS assumes that every row has a null value in that column. This disfunctional beahaviour has cost me hours and hours of wasted time loading and debugging data.
Hopefully someone from the appropriate area within Microsoft will read this post and get this bug fixed.
View 4 Replies
View Related
Oct 25, 2006
SQL 2000 Connection String:user id=MyUserName;password=MyPassword;initial catalog=MyDB;server=MyServer;Connect Timeout=30 This SELECT statement returns its 10 results nearly instantly:SELECT * FROM MyTableDitto from above, but completes in 30-40 seconds:SELECT * FROM [dbo].[MyTable]Ditto from above, but completes nearly instantly:
SELECT TOP 1000 * FROM [dbo].[MyTable] Obviously I have stopped using the [dbo] syntax in my SqlCommand's (SELECT's and EXECUTE's) but still would like to know why this is.vr, Rich
View 3 Replies
View Related
Jan 5, 2006
Hi all,
I am wondering if anyone has similar experience.
I did the migration by detaching the database from SQL 2000 running on W2K Pro and attaching it to SQL 2005 running on XP Pro. Some queries with simple aggregate functions such as AVG() have been slowed by at least an order of magnitude. I understand that SQL server can intelligently adapt to improve the performance. I am not sure how much it can improve at this point of time.
H
View 7 Replies
View Related
Jul 26, 2007
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
View 9 Replies
View Related
Oct 14, 2014
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
It is SQL 2012 SP1 with compatibility level set to 110
View 9 Replies
View Related
Apr 13, 2007
*** Product Removed **** is a rendering extension for Microsoft SQL Server 2005 Reporting Services that allows exporting reports in the DOC, RTF and WordprocessingML formats. All RDL report features, including tables, matrices, charts, textboxes, lists and images are converted with the highest degree of precision to Microsoft Word documents.
Read More at: *** URL Removed ****
Download Link: *** URL Removed ***
Post your queries and questions to *** Product Removed Forum: *** URL Removed ***
Contact Information:
*** Got rid of this too ***
View 1 Replies
View Related
Jun 22, 2007
I'm baffled by a problem I noticed yesterday, I have a 3 NODE SQL 2005 x64 ENT cluster which was setup with SSIS and Notification services upon setup. I have gone through patching SQL 2005 with SP1 (initial setup) SP2 and Hotfix 3052 through the last two months and it in the NT applicaiton log that SSIS was patched and started up to 6/15. Yesterday I received an email stating SSIS was not installed by one of our developers, upon logging in a confirmed it's no longer there! I re-installed through ADD/RM Programs SQL 2005 and ran through the SP2 and HF 3052 setup.. but upon scanning the Application logs I can't find any record of SSIS or Notification services being uninstalled. The MSinstaller shows the initial install of both packages, and then the re-install today but there's no log of uninstalling it. Does anyone know where else I can look or has can explain this odd occurance?
View 1 Replies
View Related
Jan 8, 2007
Hello,
I am in the process of porting over an application from Access To SQL implementing SQL Server 2005 Express. My intention is to implement this database on a full time server and upgrade to a full blown version of SQL later. Am I correct in assuming that there is not limit on the number of concurrent connections to SQL Server Express since it was stated that the "Workload Governor" has been removed? Or does something else control the number of users that can be simultaneously connected to the server.
My reason for asking is I have 7 machines that need to access the server. I also have 2 databases that need to be accessed from each machine. If there is no limit, I will keep my databases seperate. However, if there is a limit, I will most likely merge the tables into 1 db.
Thank you,
Robert
View 3 Replies
View Related
Jul 20, 2005
When a workstation losts connection to server,it can leave an uncomplete transaction. ThenSQL Server removes the transaction.Could anyone guide me how to set the delaybefore SQL Server do it ?Thanks in advanceJohn S.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Dec 5, 2006
We have a publisher that got red-crossed(Run to problem). I decide to remove and recreate it. However, after remove it, the publisher still stays in Replication Monitor. The remain thing has no distributor and logreader, but only snapshot agent. When to check the property of the agent, we got a error message basically say the job does not exist, which makes sense.
Now, it does not show up in any places, except Replication Monitor. Well, it cannot be removed from Replication Monitor. Can any one tell us how to clear it from Replication Monitor?
Thank you.
More Infor: The replication was set up on 2000.(2000 pub, 2000 dis and 2000 sub) with 2005 Management Studio, and the publication was removed with 2005 Management Studio.
View 1 Replies
View Related
Aug 7, 2007
According to SQL Server 2005 Book Online, the following "Important" note applies to SET_ANSI_NULLS and SET_ANSI_PADDING:
"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."
However, these options are automatically inserted in the auto-generated scripts (such as CREATE TABLE) in SQL Server 2005 Management Studio.
I am working on new scripts that include these options. What are the replacements for these options? Does the above note apply to ALTER DATABASE options? Please give me advice!
Thanks!
View 2 Replies
View Related
Mar 6, 2008
Hi,
I Recently uninstalled Visual Studio 2005 and SQL Server 2005 Tools from my machine, then I installed Visual Studio 2008 and SQL Server 2005 again. this way I can write normal .NET applications with vs 2008 and still be able to develop my reports with the BIDS.
I thought everything was working oke, until I had to modify a report. When I open the report and preview it...it works fine, but when I go back to the Data Tab, it clears out the mdx queries of all the datasets and I get the default blank designer.
I really need this fixed a.s.a.p. otherwise I can't make fixes to the reports.
Can anyone help?
Regards,
Patrick
View 1 Replies
View Related
Jan 7, 2012
This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed. For example, if I wanted to remove user "sam@mail.com"
The table results displayed would be:
Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com
Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com
Ideally, it should only display each row once, and not have the semicolon error. It seems to be a union error, because when I comment out the First and second union statements, it runs fine.
-- Delete email address from a.Batch
IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)
[Code] ......
View 2 Replies
View Related
Feb 25, 2014
How to find when (date/time) a database was last removed from AlwaysOn Listener?
Is there any query (DMV) to find the details easily?
View 1 Replies
View Related
Jun 9, 2015
In last week my database was crashed and some how i managed to restore it back on SQL2K12 but after restoration all the relationships are removed and sql server is showing below message when i open diagram of the database.Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.how to get back all the relationships of the tables.
View 2 Replies
View Related
Jul 23, 2005
Dear group:I have removed my hard drive from my laptop (which is now toast) andhave managed to recover nearly all the data from it by installing thedrive into my desktop. I was hoping to reboot the dektop to see if Icould load the operating system on the laptop's hard drive so I coulddo a manual backup of the SQL database on it. This does not work.Does anyone know of a way to recover my SQL database and all its tablesgiven the circumstances above?TIAISZ
View 5 Replies
View Related
Jul 14, 2006
I have installed SQL server 2005 on Win SERVER 2003, then uninstalled and then re-installed but I have found out that the previous instances are still listed and the the previous collation too (I have changed reg setting and and language option re-installation).
It looks like that removing program does not remove completely every object, probably registry values
Do you know how to perform a good uninstallation removing everything (add/remove program does not work properly as written before) ?
Any help will be very appreciated.
Thank
View 2 Replies
View Related
Aug 30, 2006
I've been building and testing my reports and have found that if I decide that a report parameter should be removed, removing the parameter causes the report to fail.
If I remove a report parameter via the 'Report Parameters' menu, and I also remove the associated data dimension, filter expression and parameter check boxes, when I preview the report I get an error message. It appears the report is searching for the old parameter name even after it has been deleted.
This leaves me with the option of either restoring the parameter as it was, or rebuilding the report from scratch. I've done some investigation and the parameters seem to exist within the XML code that underlies the report despite the parameters being deleted.
Is there a way to avoid this problem?
View 3 Replies
View Related
May 1, 2007
I am attempting to configure my SQL Server instance to use a service account with the minimum privileges. I thought I had everything configured correctly, when I realized that having the service account as a member of the "SQLServer2005MSSQLUser" Windows Group meant that the service account was now in the "sysadmin" fixed server role. This was not the configuration I wanted.
I went through the Books Online article "Setting Up Windows Service Accounts" and made sure the login had access to the appropriate folders used by SQL Server. Then I stopped the SQL Server service and tried to restart it, without success. These are the error messages:
Code Snippet
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
FCB::Open failed: Could not open file E:MSSQL$STAGINGDatamodel.mdf for file number 1. OS error: 5(Access is denied.).
TDSSNIClient initialization failed with error 0x5, status code 0x1.
TDSSNIClient initialization failed with error 0x5, status code 0x90.
I checked some other posts on this board, and they suggested the problem might be that the "VIA" protocol was enabled. I checked for this protocol in the Configuration Manager, and it is DISABLED in both the SQL Server 2005 Network Configuration and the SQL Native Client Configuration. What else could be causing this error?
The errors do not occur when I add the service account back to the "SQLServer2005MSSQLUser" Windows Group. The SQL Server service starts successfully when the account is back in this group.
Here are my answers to the questions posted at the top of this board:
What is the MS SQL version? - SQL Server 2005 SP2 (9.00.3054.00)
What is the SKU of MS SQL? - Enterprise Edition (SKU ID: 1804890536)
What is the SQL Server Protocol enabled? - TCPIP, Named Pipes
Does the server start successfully? - NO
If not what is the error messages in the SQL server ERRORLOG? - See above.
If SQL Server is a named instance, is the SQL browser enabled? - YES
What is the account that the SQL Server is running under? - Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not applicable, Windows Firewall is not used
Do you make firewall exception for SQL Browser UDP port 1434?
Not Applicable, Windows Firewall is not used
View 7 Replies
View Related
Jul 6, 2015
I find that dynamic masking does not work on joining tables in SQL Server 2016 CTP2.1.
For examples, I create the following table:
CREATE TABLE [dbo].[HRM_StaffAppointment](
[StaffID] [nvarchar](11) NOT NULL,
[ApptSeqNo] [smallint] NOT NULL,
[ReportingDept] [nvarchar](10) NULL,
[Code] ...
Then I apply mask on StaffID and RankDesc.
alter [dbo].[HRM_StaffAppointment] alter column [StaffID] add masked with (function='default()')
alter [dbo].[HRM_StaffAppointment] alter column [RankDesc]
add masked with (function='default()')
When User A logged in and query on HRM_StaffAppointment, StaffID and RankDesc are perfectly masked. But User A can remove the masking using another table:
CREATE TABLE [dbo].[staffID](
[staffID] [nvarchar](255)
) ON [PRIMARY]
select a.*
from dbo.HRM_StaffAppointment as a
left join dbo.staffID as b
on a.StaffID = b.StaffID
It looks like a security hole to me, or I'm doing anything wrong?
View 4 Replies
View Related
Apr 19, 2007
Hi,
I had to remove AD from win server 2003 after that I didn't have any SQL to uninstall in Add or remove programs folder.
Is there any other way to uninstall SQL Server?
best rgds - timppa
View 1 Replies
View Related
Nov 23, 2007
I enabled logging by right clicking on the control flow and specifying a log provider.
after a while, i deleted the log provider from the tree view and the connection manager it was using from the connection manager collection.
now i cannot open the package.
here are the errors i can collect from the failure;
Error 1 Error loading ImportMedsSubmissionFiles.dtsx: Element "{A4F838A2-2660-4422-A0D2-669BAFBC6CAE}" does not exist in collection "LogProviders". Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1
Error 2 Error loading ImportMedsSubmissionFiles.dtsx: Error loading value "<DTSelectedLogProvider xmlnsTS="www.microsoft.com/SqlServer/Dts" DTS:InstanceID="{A4F838A2-2660-4422-A0D2-669BAFBC6CAE}"/>" from node "DTSelectedLogProvider". Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1
Error 3 Error loading ImportMedsSubmissionFiles.dtsx: Error loading value "<DTS:LoggingOptions xmlnsTS="www.microsoft.com/SqlServer/Dts"><DTSroperty DTS:Name="LoggingMode">2</DTSroperty><DTSroperty DTS:Name="FilterKind">0</DTSroperty><DTSroperty DTS:Name="EventFilter" DTSataType="8">18,7,OnError,19,OnExecStatusChang" from node "DTS:LoggingOptions". Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1
Error 4 Error loading 'ImportMedsSubmissionFiles.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1
is there some file to edit or a way recreate or revive the package, even if it means just duping the package into believeing what it wants is there?
as a baseline altertnative, is there any way to retrieve the work?
it consists of a boatload of derived column transformations of way too many columns that i would really rather not rewrite, so if its lying about on my drive somewhere in an xml file that can be used to recreate what i had, it would be just ducky.
thanks
drew
View 6 Replies
View Related
Jun 15, 2005
Is it possible to have an auto increment alphanumeric primary key eg A1, A2, A3
Thanks
Paul.
View 1 Replies
View Related
Apr 9, 2008
I have set a column called "Anumber", I am
using a computed value to get the PK ID # and
a the letter "A" to it.
('A'+CONVERT([varchar](10),[requestid],(0)))
The above works fine unless I copy a record,
The copied record never displays the correct
value like "A55" it keeps "NULL" as it value.
Is their a better way to achieve a result as
described above ?
I am coming from Foxpro to SQL, Any advice would
be great.
Thanks
View 6 Replies
View Related
Apr 10, 2008
Hi experts,
I am executing a script to add a column to few tables and drop existing constraints, create new foreign key constraints, indexes etc. After we test this on our development database we have to deliver the script to the customer.
My concern here is, when I have to drop the constraint with the name, I am not able to do it because its been created with alphanumeric char in the constraint name. So I end up getting the constraint name for each table. And moreover,
I wont be able to deliver the same script to the customer as the alphanumeric in the constraint names on the customer's database will be different.
Any suggestions to overcome these alphanumeric in the constraint names?
Thanks much
View 20 Replies
View Related
Jan 10, 2008
Greetings all,
I have upsized 2 foxpro table to SQL Express.
In table 2 called Orphan I have/had a field that
would autonumber with the letter "U" first.
Example would create a AlphaNumber Like "U00001223"
In Foxpro this is a easy function, But I have been
unable to find a similiar way to do this in SQLEXPRESS.
Foxpro would have you define the field as Unique Number with
template defintion of "U"N8
In SQL I know you can use Numeric on a field for a unique number
but cant find setting to proced number with a "U"
Is this doable is in SQL and does anybody have a
reference point or example they could share ?
Any help would be great and hope my question made sense..
View 8 Replies
View Related
Dec 20, 2007
All,
I'm having trouble with a query where I need to limit a resultset by comparing (using a WHERE clause) a field that is alphanumeric with one that is numeric. I've tried converting, casting, and case statements, but I either get an overflow error or a big slap on the wrist by SQL Server 2005. Does anyone have any good solutions to this? I've been racking my brain for a while now.
Thanks for the help!
-Scott Mescall
View 3 Replies
View Related
Feb 3, 2007
Hello,
I have a SQL database with about 300 company names and corresponding phone numbers. I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.
I know there are some examples on codeproject.com, but they are a bit over my head... besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do. Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?
Any idea?
Thanks,
-Derek
View 3 Replies
View Related
Oct 26, 2004
Hi there,The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002 Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?
View 4 Replies
View Related
Nov 27, 2014
I'm using SQL 2008 with table [AgentDetails] and fields [IDCode],[FirstName],[LastName],..etc. [IDCode] is alphanumeric [AAA001].
Is it possible to increment both alpha & numeric when new record is inserted. e.g.
AAA001,AAA002......AAA999,AAB000,AAB001,...AAB999,AAC000,AAC001...etc. with a user function or some stored procedure.
View 2 Replies
View Related
