Problem Importing A Signed Number
Apr 9, 2008I have a flat file that contains 20.00- value and I am trying to import it into a decimal field and it does not like the sign. What do I need to do to convert this via SSIS? Thank you
View 1 Replies
ADVERTISEMENT
'Invalid Column Number' When Importing
Jun 16, 2003When I try to import a Pervasive table into SQL Server 2K, I get the error msg of 'Invalid column number'.
Is that because SQL Server cant handle more than 1024 columns ?
I think my Pervasive table has more than 2000 columns
Thanks in advance
Importing Text Files With Different Number Of Columns
Oct 6, 2007Hi!
I have a bit of a problem with importing (DTS Package) from a text file into an existing DB.
The file has no column names and every file only contain 1 row. This row varies in number of columns.
My first thought was to create a table with the maximum numbers oc columns that can appear in the text file.
An example:
Table with 4 columns; Col001, Col002, Col003 and Col004
The first text file:
123,Peter,Ocean Street,NY
The second text file:
356,John,Park Avenue
My aim is that the second file should append to the table, leaving Col004 as (NULL)
Any ideas how to get this to work?
Expekt
Limit Number Of Rows When Importing A Flat File
Nov 22, 2006Hello,
Using ssis, how can I limit the number of rows I wish to import from a flat file?
thanks in advance
Self-Signed Certificate
Mar 10, 2006Can anybody can tell me how to do self-signed certificate on sql server? What is it ? Do we really need?
Many thanks.
How To Add A Signed CLR Assembly
Aug 30, 2012I'm trying to add an assembly to my SSDT project, and in my assembly, it requires EXTERNAL access, which also means that the assembly has to be signed, and an asymmetric key must be created from the assembly. Â I can't figure out how to get this to work from the SSDT project.
View 8 Replies View RelatedProblem With Encryption Using Self-signed SSL Cert
Apr 21, 2008Hello,
I have a SQL 2005 test system set up that I need to test with protocol encryption, and had in mind to peform this test using a self-signed SSL certificate. I have read the guides and requirements from MS as well as the few blogs I could find on the subject and I've *almost* gotten it to work, but I'm failing at what seems to be the last step - getting clients to connect.. Here is what I've done:
-Created a self-signed certificate (I've tried using SSL Diagnostics from the IIS Diagnostics package, makecert.exe from .NET SDK, and OpenSSL) with the 'Server Authentication' purpose, with a CN that matches the server name, along with a private key. I've imported the cert in the local computer's personal store (also in LC's Trusted Root).
-SQL Server Configuration Manager sees the certificate and allows selecting it in the configuration. I've done this and selected 'Force Encryption' in the protocol settings.
-Restarted SQL Server, errorlog says 'The certificate was sucessfully loaded for encryption'.
-Tried connecting locally using SSMS and setting encryption: No problem.
-Exported the cert from the store and imported it into the LC's Trusted Root store on the client comp.
Here's where the problems begin.
1: Client is still apparently able to connect *unencrypted* to the SQL Server (I tested with SSMS on the client machine, it states under connection properties that the conn is not encrypted)
2: When trying to connect encrypted, SSMS returns an error during the pre-login handshake saying "The certificate's CN name does not match the passed value." When using the same cert in IIS there are no problems whatsoever, and the CN in the cert does indeed match the server name so I'm thinking the real error is hiding beneath the one that is thrown.
Also tried to configure the SQL Client Network Utility to force encryption from the client side, but this didn't seem to have any effect. I tried to use Network Monitor to capture network traffic, but since it appears to be encrypted (SQL encrypts login handshake anyway afaik) I couldn't get much out of it.. Does anyone have any tips?
How To Determine Objects Signed By A Certificate
Apr 30, 2007Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?
View 5 Replies View RelatedLoad Multiple Signed Assemblies
Aug 18, 2006I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:
CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:dbainAssembly.dll'
CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey
GRANT UNSAFE ASSEMBLY TO CLRAssembler
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler
REVOKE CONNECT SQL FROM CLRAssembler
Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.
The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.
Is this the only way to do this? Or am I missing something?
Convert Character Representation Of A Signed Decimal
Apr 25, 2008SQL/SERVER 2000:
Data transform task which copies data from a text file to a db table.
Text file field value = 0000000242E (signed decimal)
DB column data type = decimal(11,2)
How do I get this value correctly converted? Getting "invalid data value" error message.
thanks for any help
(urgent)how Should I Treat Signed Decimal Values In Sql Server
Jul 30, 2007Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
any help will be appreciated.
Regards
Karen
(urgent)how Should I Treat Signed Decimal Values In Sql Server
Jul 30, 2007
Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
any help will be appreciated.
Regards
Karen
Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns
Aug 25, 2007Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
Importing Of Varchar Field Data In Number Field
Dec 5, 2007i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
Differences Between Signed Vb Script Class And Vb Script Language
Jun 15, 2006on ActiveX Script task
tia
The Number Of Requests For XXXServerXXXUser Has Exceeded The Maximum Number Allowed For A Single User
Oct 15, 2007
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time
Sep 24, 2007Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number
Jun 23, 2015Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableÂ
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
How To Change A Decimal Number To Percent Format Number?
Oct 8, 2006in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
Limitations In Term Of Number Of Tasks And Number Of Columns
Jun 5, 2007Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
How To Number Records / Duplicates Receiving Same Number
Feb 19, 2013I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies View RelatedHow To Query A Number (street Number)...
Jul 30, 2007I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
Maximum Number Of Tables - Is 4^15 Big A Number?
Nov 26, 2006Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?
View 3 Replies View RelatedPage Number && Records Number
Jun 20, 20061. how to show page number & total page number in report body?
2. how to show total records number?
Format A Number For Use As Number In Excel
Dec 20, 2006I want to format a number like "#,##0.00" in order to handle it in Excel as a number (i.e. compute a sum).
Excel is able to show a number in a specail format and still allow to compute with ...
Thanks in advance,
Peter
Field LIKE Number,number,number
Nov 26, 2006I am testing something in Visual Basic
that talks to a database and I want to
filter results by -> field1 like "###".
However, that 'like' and '#' is VB syntax.
How do you say that in SQL?
Importing..
Sep 14, 1998I am trying to import a database into Sql 6.5 from Access 97`.
How shall I go about this ?
Thanks.
mark.
Importing From XML
Aug 26, 2004Hello everyone and thanks in advance.
Ok, here's the deal. I am 1 of 2 developers at my organization, the other is a COBOL programmer. Long story short, every night he sends me a 22MB XML file containing our company's inventory. This contains about 40,000 items. In C#, I'm basically looping through all the XML nodes and on each node I load up an object with the particular properties (manufacturer, SKU, serial number, etc.) and call a Stored Procedure to to save the item.
In saving the item, I'm checking to see if it was already in my inventory and I either perform an update or an insert on the table as approriate. The query does a couple of other things like retreiving the Purchase Order Number, Cubic Feet, etc. from other tables. All in all, the query takes about 1-2 seconds to complete. I've optimized the query as much as I know how (including the addition of indexes), but this still takes at least 6 hours to complete the 40,000 items. Is this the best approach? Any other suggestions?
So far the only things I've thought about changing is:
1. Break it out into multiple queries to avoid poor compilation of execution plans
2. Multithread the C# app so that I'm running like 10 queries at once.
3. Get the COBOL programmer to give me a delimited file and do a BULK INSERT on it into an empty HEAP, then run a single INSERT and a single UPDATE (using CASE statements)
Again, Thanks in advance!
Importing XML
Mar 17, 2004Hi everybody:
I want to know how to import to SQL Server an XML File
Thanks for your help and quick answer.
Cristopher Serrato
Importing .txt To SQL
Jul 23, 2005Has anyone any idea what could be causing this problem.I've a large txt file, with each item on each row seperated withquaotaion marks and seperated from each other by a comma.I can import it no problem to Access, but when I try to import it toSQL server, SQL Server gives me that "cannot find.... within first 8KB"of data error.Any ideas?Ciarán
View 2 Replies View RelatedImporting From .txt
Jul 23, 2005orange May 14, 5:16 pm show optionsNewsgroups: comp.databases.ms-accessFrom: "orange" <orange...@mail.ru> - Find messages by this authorDate: 14 May 2005 14:16:49 -0700Local: Sat,May 14 2005 5:16 pmSubject: import complex dataReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report AbuseI've got a database in .txt file similar to this:BookName;Author;Year;ReviewedBy;Rating;PagesNemesis;Isaac Asimov;1989;13,31,24;good;110Sense & Sensibility;Jane Austen;1970;45,32;great;120The Bicentennial Man;Isaac Asimov;1965;14;excellent;124...the data in field 'ReviewedBy' should be connected to names of peoplethat read the book. I'd like to have a separate table in Access likethis:Id;Reviewer13;Bob...24;Jim...31;TomHow do I import that data into database?
View 1 Replies View RelatedImporting A Csv File Using Dts
Jun 22, 2007Hi i'v e installed the file: SQLServer2005_DTS.msiBecause i've heard that i need that to import a csv file into a ms sql database. now i have no idea how to work it, like to even make it open.Where would i open it from? I reckon i'v eread enough literature about using it, to be able to have a bash... thanks in advance...
View 3 Replies View RelatedImporting Csv To Sql Database
Sep 26, 2007Hi All,Here is my code Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader") con = New SqlConnection(strCon) con.Open() Dim strselect As String = "" Try strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)" strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')" cmd = New SqlCommand(strselect, con) cmd.ExecuteNonQuery() con.Close()i have defined connection string in web.config file and my csv file is inside C:UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."Regards
View 1 Replies View Related