Hi,
I´m building an aplication with VB.net and SQL Server 9, but in the future it will be compatible with PostgreSQL (by another developer), so my question is, if anyone knows what "data types" in SQL Server 9 i must use to maintain compatibility with PostgreSQL, so that when the time come for the conversion i don t have to chage the "Data Types" on each table.
Any help would be apreciated.
I am trying to import one table from postgreSQL to SQL Server 2005 using sql server import and export wizard. When i test the connection after providing data source, location, username, password in the Data Link Properties section I get the message "Test Connection Succeeded". As soon as i press next to go onto next step i get the following error.
TITLE: SQL Server Import and Export Wizard
------------------------------
Cannot get string literals from the database connection "Provider=PostgreSQL.1;User ID=sa;Data Source=localhost;Location=TestMasterMap;Extended Properties=".
------------------------------
ADDITIONAL INFORMATION:
Object reference not set to an instance of an object. (DTSWizard)
------------------------------
BUTTONS:
OK
------------------------------
I have tried all sorts of different combinations for these properties but it always fails on this step. Can anybody help me with this?
I have a simple Integration Services project and the problem is that decimal fields are importing as real (I'm loosing the digits behind the decimal point).
The project contains a data flow task importing a flat file (.csv) to an SQL Server destination. My .csv file has two decimal type fields. Example:
Field 1: 12345.67
Field 2: .123456
My database table that I'm importing to has two fields. The only way that I can get this data to import is to define the fields as "float" in both the text file and database table. I want the database table fields to be defined as decimal or numeric, not float:
Field 1: decimal(7,2)
Field 2: decimal(6,6)
When all fields are defined as decimal (in both the flat file and database file), I get the following results:
Field 1: 12345.00
Field 2: .000000
How does one import decimal data from a flat file (.csv)?
Thank you in advance!
I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.
View 3 Replies View RelatedI am faced with the following dilemma: I have a Windows desktop application developed in Visual Basic .NET, it's a fairly large size, about 100,000 lines of code. I developed it in Visual Studio .NET 2003 and I recently converted it to Visual Studio 2005. My database is PostgreSQL 7.4. I have roughly 500 database objects, i.e. tables, views, sequences, and functions, in it. My biggest table probably has a few hundred thousand records in it.
Another developer in my company will soon be starting work on a Web application that will be pulling its data from the same database as the above mentioned Windows application. So basically we have a situation where two different applications, a Windows and a Web one, have to access the same database.
Currently the plan is to develop the Web application in ColdFusion. However, since I already have a large-scale application in VB.NET that will be accessing the same database I was thinking it might be a better idea to develop the Web application in ASP.NET, seeing as how ASP.NET is VB.NET. That would present more opportunities for the two applications to interact with each other.
And in that vain, my dilemma is this, should I switch my database to SQL Server 2005 or should I stay with PostgreSQL? I am happy with PostgreSQL so far but if I have one VB.NET and one ASP.NET application, doesn't it make more sense to have SQL Server database?
I know that PostgreSQL is free and SQL Sever costs money, but what about SQL Server Express 2005? That's free too, but how much of the functionality of SQL Server 2005 does it support? The organization I work for is small and it would be hard to convince management to spend money on a commercial database like SQL Server 2005 when what we have works just fine.
Any advice would be appreciated.
I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:
"The data types text and varchar are incompatible in the equal to operator. "
The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"
If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:
<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();
void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";
//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@prmuser'";
//SqlParameter spuser = new SqlParameter("@prmuser", SqlDbType.Text);
//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);
Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;
}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}
Hi,
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped:
[Department, DEP_CLEGALCODE]
One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
thanks.
i want to learn about the meanings of the different 'data types' in VS 2005 (database) i.e. 'nvarchar(50). could you please advice me of a good site (reference) to read.
View 1 Replies View RelatedHello all,I have been trying to find some information about exporting data frompostgresql on linux to ms-sqlserver 2000 on windows but have not beenable to get hold of any information. I would really appreciate ifsomeone can point me to some source of information.Thanks in advance,Nishy
View 2 Replies View RelatedHi all
I hope you can help me in some way.
Basically for the last 5 months i've developed a SQL 2k db for my company.
All of a sudden they quite fancy using Linux on the server, therefore i would be required to use PostgreSQL instead of SQL Server 2k.
I could do with some advice about how PostgreSQL and SQL Server compare? and how much work would be involved in migrating from one to the other.
Points i've found myself and may be an issue are:
I've used some of SQL Servers XML capabilites and as far as i can tell PostgreSQL doesn't support XML.
I've made extensive use of functions that return table variables etc... is there a similar idea i can use in PostgreSQL?
Extensive use of Stored Procedures and T-SQL etc...
DTS packages. Any equivalent or similar idea?
(ps i've also posted this in the PostgreSQL but since no one replyed i was hoping the sql server boffs could help).
I am wondering if it is possible to access a remote postgresql database using the sql server ce access classes. I do not want to run a postgresql server on windows ce, just access one from a mobile device.
View 7 Replies View RelatedDoes anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?
View 6 Replies View Relatedhello everyone!
I'm currently dumping tables from PostgreSQL 7 to SQL Server 2K and I've been wondering if it's possible to create a trigger between the two databases.
..like if there's an insert to a PostgreSQL table, the trigger inserts the same row to the SQL Server table.
If it is possible, please do send me a sample code.
Thanks!
I'm new to SQL Server, but could someone explain how to set up a field with an auto number/increment that can be used within an Access DB?
View 4 Replies View RelatedI am trying to write a generic stored procedure to return the data type of a given col in a given table in any database.
I would like to place this sp in master.
input parms are @TableName sysname and @ColName sysname
ouput parm is @data_type varchar(128)
set @data_type=''
select @data_type = data_type from information_schema.columns where table_name = @TableName and column_name = @ColName
when debugging this I always get 0 rows (@data_type remains '')
I have a thought that I can add an input parm for the database, say @DbName sysname
But I am having trouble updating the select stmt to be something like
...from @DbName +'.' + information_schema.columns.
Any Ideas?
Thanks for your help.
Very simple ...
Where can I find a web site that explains the difference between sql server express data types?
thx.
SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
hello,I am building a web page in asp.net 2.0 and i'm looking for a way to save various files uploaded by users (such as doc, pdf, cs, txt .... whatever). Obviously the normal way would be to store it on the filesystem of the web server (or any other middleware server).But I am asking if there is a way to store these files in the database, and not affect too much of the performance of the web page?I know a picture can be stored easily, but what about all the other file types? I would appreciate if someone can spead light of the subject.Thanks,Tranquil.
View 1 Replies View RelatedHi Friends,
I want to know if there are a manner of replication(two-way) between MSSQL SERVER and (postgresql or mysql or ORACLE).
Thanks
Is there a way to change the default data type in SQL Server? If I import a database into SQL Server it makes every non-numeric field a nvarchar field. Is there a way to change this so that these fields would be varchar or char by default? I cannot find any way to redo these as a batch/group update and have to modify every single field manually. Any insights greatly appreciated.
View 4 Replies View RelatedI am working on two versions of an application, one of which will be awindows forms application (which will need to be redistributable) andthe other will be a web application.I have MS Visual Studio 2005 (along with the developer's edition of MSSQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun'sapplication server, Tomcat and Apache web server. I am working onWindows XP Pro, and have installed the .NET 3 SDK and all relevantrelated products I could find (e.g. 2 extensions packages for VisualStudio).I have one MS Access database, to which my users should have read onlyaccess. I have, and have used, a tool for importing MS Accessdatabases into MySQL. I expect that SQL Server has a similar utilityhidden somewhere (where I haven't yet looked, though I HAVE beenlooking - obviously in the wrong places). I have located a similarutility for importing MS Access databases into PostgreSQL. I have notyet decided which servers to use for the web version, but that isanother story, for which I may raise another thread in due course (butI welcome suggestions which may reduce the effort required givenrequired effort for the windows forms app).My problem is for the windows form aplication (intended for use by asingle family). I expect to use ADO.NET. The question is, should Iimport the Access database into MS SQL, and redistribute it, along withMS SQL Server Express (or is that necessary), or distribute it just asan Access database and use the jet engine to access it. A relatedquestion is, "Does ADO.NET support creating new databases for a givenengine?" Imagine a recipe database. It is easy enough to create a SQLscript that creates all the required tables, indices, foreign keys,&c., but can I submit that SQL script to an ADO.NET object, along witha file name, and have it create, e.g., an Access database with thesupplied name. Or do I have to create a database file with nothing init other than the schema?I have more questions, but they'll have to wait.ThanksTed
View 5 Replies View RelatedI trust you'll bear with an SQL Server newbie with what may seem a rather inane request. I am designing a web app in Web Designer 2005 Express with SQL Server Express. Unfortunately, I'm finding a little confusing with some of the data types when designing tables. I have tried to find information on the various Microsoft sites (general site, MSDN, here) and while I found one document that had a table comparing data types in different implementations of SQL, it wasn't at all helpful. Most of my confusion is with the various string and char types; the numeric types seem pretty straight forward for the most part. However, it might be helpful to know the difference between money and smallmoney/datetime and smalldatetime, particularly space/size information and formatting options (unless the latter is up to the interface). It would also be helpful to know which string/char types correspond to any counterparts they might have in, for instance, Access (with which I am already quite exprienced). Or any particular quirks or idiosyncracies they might have. I don't expect anyone to write a full tutorial, but if someone could point me in the direction of a good online doc, it would be most appreciated. You might well ask, why not use Access databases? I would answer...I like to learn new stuff!
Thanks much.
Hi,
Can anybody give me some information on Table data types of SQL2K.
How and where can it be used. Does it make the queries faster, in case there are many users using ?
Please could anybody give me these details, to make me decide whether I can use it or not ?
Thanx and Regards,
Sushma
We have a query that joins column A int which is an int onto column B with contains only int's but was created as a varchar and can't be changed to an int at the moment.
Casting column a as a varchar in the ON of the join to left join seems to void the index altogether and the query just runs for every.
We are talking a few hundred million rows of data in each table.
Temp solution is select into a #Hash table as correct data type and index then use the #Hash table in the join.
I need to create a view of a sql table, but change the data types. I knowthe syntax below is not correct, and can't figure out if it is wrong or ifyou just can't do this. I have only created views before with the same datatype.CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01FROM F0005 AS F0005WHERE DRSY = '41' AND DRRT = 'S1')Thanks!!
View 4 Replies View RelatedHi Can anyone point me to a document somewhere that shows a mapping ofSQL Server 2000 datatypes to C datatypes? I am writing some extendedstored procedures which need to be able to process pretty much anydata type, so I want to make sure I am taking them from SRVPROC andstoring them in the correct C data type.Thanks,Bruce
View 1 Replies View RelatedHi,Can anybody tell me if it is possible to return a table data type (asopposed to a temporary table) from a stored procedure.I am currently studying for 70-229 using the Thomas Moore book but itdoesn't really make it clear, as the text suggests you can't returnthem but some of his sample questions suggest you can.I can't make it work in Query Analyser so I am assuming that you can'treturn them. Is that correct?Thanks in advance for any help,Simon.
View 1 Replies View RelatedIs there ANY data type other than varchar that is valid for localvariables and allows more than 8000 characters?
View 3 Replies View RelatedHello All,
We are developing customer support application. We will have so many customers after launching this product. But my problem is how will i store data of all these customers in SQL Server. Please suggest me.
Hi,
I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.
Thanks
John
Hi
Can anyone help me out in migrating Oracle stored procedures & Functions which contains collection types (user-defined datatypes like VARRAYS) into SQL Server 2005. How to achieve this taks? This is very important for me and it's very urgent. Please someone help me.
Thanks & Regards
K P Kumar
Hello,
First of all, this is my first time using SQL SERVER 2005 express, before that i'm using POSTGRESQL database.
I would like to know how what's the equivalent command for "BEGIN","ROLLBACK","COMMIT", these are the POSTGRESQL COMMAND use to start transaction, rollback transaction and commit transaction.
Example when i use this kind of command is . I need to insert data into 3 table. before insert into table1, i issue "begin", start to insert data into table1, if table1 no error, then i proceed to table 2 and table3. if table2 and table3 no error. then issue "commit" to commit the changes. but if any error happen between table1 and table 2 or table 2 and table3, i will issue "rollback" to roll any changes that i make to table1, table2 and table3.
Maybe some one can teach me how to achieve using SQL SERVER 2005 EXPRESS.
Thanks and Regards.
Beh Chun Yit
i have created asp.net page, one feild of this page text area.
when i insert some text through this page in "text feild" of SQL server on few words of this feild cut and inserted to text feild of SQL server but all text that i have written in text area feild.
can u please help me how to handle so that i can all text in text feild of SQL server data type text/next.