Error when updating bigint columns using ADO

I am attempting to write a conversion of our product for Compact Edition; we already provide it based on SQL Server. The database interface uses ADO through a Python-win32com adaptor, and has worked fine so far. (Note: *not* ADO.net, just plain old COM)

Now, a curious thing happens. When inserting new data through a Recordset, everything works fine - except for columns defined as bigint. There are no exceptions thrown, but when you read the columns back they contain nothing but zeroes. Do the same to any other column type - I've tried integer, numeric, float, nvarchar and ntext so far, and they all seem to work just fine. It does not seem to be conversion-related either, since I've tested the exact same data to various column types. And using bigint on regular SQL Server works just fine.

The code involved is quite unspectacular, and simply switching the column types to integer would solve the immediate problem, but causes potential future issues since we normally store internal IDs in bigint columns, and the values may grow quite large.
This may indicate a problem with compatibility betweeen the 3rd party python ADO adaptor and the SQL CE OLEDB provider.

Error when trying to create new SQL Server login via ado.Net

I get an error when I try to execute the following code using ADO.Net
in VB.Net:
conn.open
sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
command=new SqlCommand(sqlString)
command.connection = conn
command.ExecuteNonQuery()
The error occurs when executing the ExecuteNonQuery line and is:
"Line1: Incorrect syntax near 'LOGIN' "
The database connection is connected to the master database of SQL 2005
Express using the sa account. This line executes just fine in Sql
Server Studio Manger when connected as sa. Am I not allowed to create
database logins via ado.net?!?! I need to for my application.
Thx,
MarcusHi
It seems to be something wrong with quoatations. Print the sqlString and see
if you can execute it in QA
"holysmokes99" <holysmokes99@.hotmail.com> wrote in message
news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>I get an error when I try to execute the following code using ADO.Net
> in VB.Net:
> conn.open
> sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
> command=new SqlCommand(sqlString)
> command.connection = conn
> command.ExecuteNonQuery()
> The error occurs when executing the ExecuteNonQuery line and is:
> "Line1: Incorrect syntax near 'LOGIN' "
> The database connection is connected to the master database of SQL 2005
> Express using the sa account. This line executes just fine in Sql
> Server Studio Manger when connected as sa. Am I not allowed to create
> database logins via ado.net?!?! I need to for my application.
> Thx,
> Marcus
>|||I suggest you double check your connection string to make sure you are
connected to the SQL Express instance since this message indicates you are
connected to a pre-SQL 2000 instance. You can insert code like the example
below to query the instance version. My SQL Express instance returns
"9.00.1399.06".
command=new SqlCommand("SELECT SERVERPROPERTY('ProductVersion')")
Dim version as String = command.ExecuteScalar().ToString()
MessageBox.Show(version)
Hope this helps.
Dan Guzman
SQL Server MVP
"holysmokes99" <holysmokes99@.hotmail.com> wrote in message
news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>I get an error when I try to execute the following code using ADO.Net
> in VB.Net:
> conn.open
> sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
> command=new SqlCommand(sqlString)
> command.connection = conn
> command.ExecuteNonQuery()
> The error occurs when executing the ExecuteNonQuery line and is:
> "Line1: Incorrect syntax near 'LOGIN' "
> The database connection is connected to the master database of SQL 2005
> Express using the sa account. This line executes just fine in Sql
> Server Studio Manger when connected as sa. Am I not allowed to create
> database logins via ado.net?!?! I need to for my application.
> Thx,
> Marcus
>|||> It seems to be something wrong with quoatations. Print the sqlString and
> see if you can execute it in QA
No need to print the SQL statement here since it is in clear text in Marcus'
code snippet:
CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVeLQavUGHA.4436@.TK2MSFTNGP10.phx.gbl...
> Hi
> It seems to be something wrong with quoatations. Print the sqlString and
> see if you can execute it in QA
>
> "holysmokes99" <holysmokes99@.hotmail.com> wrote in message
> news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>|||Hi,Dan
Yep, I got it. thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uahPs5yUGHA.5900@.tk2msftngp13.phx.gbl...
> No need to print the SQL statement here since it is in clear text in
> Marcus' code snippet:
> CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVeLQavUGHA.4436@.TK2MSFTNGP10.phx.gbl...
>|||Oh, Jeesh, My bad!!! Dan you were spot on. I was connecting to an
instance of SQL Server 2000, and not Express. I have both on my machine
and forgot to reference it as "Marcus\SQLEXPRESS", instead using
"Marcus" only. CREATE LOGIN is new for SQL Server 2005 and thus I got a
syntax error when connecting to SQL 2000. Thanks for your insight.
On a sort-of-related note, is there a way for me to iterate through all
the SQL Server Express instances on the network? Using SQL-DMO, the
code below gives me the names of the servers, but some of the SQL
Express servers are named "[server_name]\SQLEXPRESS", while others are
just "[server_name]" (even though they do not have anything other that
SQL Express on them). If they were all suffixed with "SQLEXPRESS" then
that would be easy, but it doesn't seem to work that way.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Dim oServer As SQLDMO.SQLServer2
oSQLApp = New SQLDMO.Application
oNames = oSQLApp.ListAvailableSQLServers
For i As Integer = 1 To oNames.Count
TextBox1.AppendText(oNames.Item(i) + Environment.NewLine)
Next
End sub
Thanks!
Marcus|||Check the following:
1) SQL Brower service is enabled and running
2) Port 1434 isn't blocked (e.g. Windows firewall)
3) The SQL Express instance is configured for remote connections
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1143659183.191249.143360@.e56g2000cwe.googlegroups.com...
> Oh, Jeesh, My bad!!! Dan you were spot on. I was connecting to an
> instance of SQL Server 2000, and not Express. I have both on my machine
> and forgot to reference it as "Marcus\SQLEXPRESS", instead using
> "Marcus" only. CREATE LOGIN is new for SQL Server 2005 and thus I got a
> syntax error when connecting to SQL 2000. Thanks for your insight.
> On a sort-of-related note, is there a way for me to iterate through all
> the SQL Server Express instances on the network? Using SQL-DMO, the
> code below gives me the names of the servers, but some of the SQL
> Express servers are named "[server_name]\SQLEXPRESS", while others are
> just "[server_name]" (even though they do not have anything other that
> SQL Express on them). If they were all suffixed with "SQLEXPRESS" then
> that would be easy, but it doesn't seem to work that way.
> Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button2.Click
> Dim oNames As SQLDMO.NameList
> Dim oSQLApp As SQLDMO.Application
> Dim oServer As SQLDMO.SQLServer2
> oSQLApp = New SQLDMO.Application
> oNames = oSQLApp.ListAvailableSQLServers
> For i As Integer = 1 To oNames.Count
> TextBox1.AppendText(oNames.Item(i) + Environment.NewLine)
> Next
> End sub
>
> Thanks!
> Marcus
>|||Thanks, Dan. You were right again. On the machine with SQL Express
only, Windows Firewall was on. When I disabled it, the server name and
sql server instancen name came up when I ran the code above, i.e.
"training\sqlexpress", not just the server name as before when firewall
was active. Why did the server name come up at all then when the
firewall was enabled, when using the SQL-DMO code above?
Cheers,
Marcus|||> Why did the server name come up at all then when the
> firewall was enabled, when using the SQL-DMO code above?
IIRC, before SQL Server 2000, network broadcast packets sent to facilitate
server enumeration. According to Perter's blog
(http://blogs.msdn.com/sql_protocols.../24/473502.aspx), legacy
behavior can show default instances even with UDP 1434 blocked.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1143736505.471840.78380@.i39g2000cwa.googlegroups.com...
> Thanks, Dan. You were right again. On the machine with SQL Express
> only, Windows Firewall was on. When I disabled it, the server name and
> sql server instancen name came up when I ran the code above, i.e.
> "training\sqlexpress", not just the server name as before when firewall
> was active. Why did the server name come up at all then when the
> firewall was enabled, when using the SQL-DMO code above?
> Cheers,
> Marcus
>sql

error when connect to sqlserver from another machine

hi ..

i install sqlserver and make app with vb6 using ADO to connect to the database and successfully

i put that application on another pc on the network
when i open it it says "can't login computername\guest" ?!!

any help

thanks in advance.any answers|||Are both of your pcs on a domain or a workgroup? If it's not domain, you need to create a same account/password pair on both machines and use that account to login.|||thanks

Xinwei Hong for reply

the two machines on a workgroup so .. could you explain pcs under domain in few lines ?
now i will create two users on the two machines and tell you later

thanks in advance.
|||By "pcs under domain", I mean whether your PC is under a domain which is usually used in median/large company. In this case, domain controller will help you do a lot of things.|||thanks for reply

you says i can do a lot of things if i'm under domain like what ?and what is the domain controler ? could you explain ?

and if you have short time i can talk to you in messanger it will be kind from you

thanks|||i make 2 users with same name but ..
the same problem occurs ?|||It's hard to describe "domain controller" and related stuff in short words. I recommend you find some documents related to Windows Networks to read.

Are you sure your client is running under the user account you created and you used that account to login to the server? It means you may need to login the client Windows system and run your client app under that account. If you user Administrator or even logon the computer without a user/password, it won't work.|||is this means that i need a password for administrator user to login to the database server ?|||Not necessary. SQL server runs as a service under various account(e.g. network service, local system, administrator account, etc.).

Error Setting a database in Crystal Reports 10

I have created a report in Crystal Reports 10 that is connect to an SQL Server 2005 database. I have used OLE DB (ADO).

The problem is that i want now to connect this report to another database instead, using "Set Datasource Location" and is giving me the error:
"Failed to open a rowset.
Details:ADO Error Code: 0x90040e14
Source:Microsoft OLE DB Provider for SQL Server
Description: Cannot resolve te collation conflict between 'Latin1_General_CI_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation.
SQL State:42000
Native Error:468"

I went verify the two databases and i saw (properties of the database in sql 2005) that the two had diferent Collations.

One had "Latin1_General_CI_AS" and the other had "SQL_Latin1_General_CP1_CI_AS"

I have changed the first one to "SQL_Latin1_General_CP1_CI_AS" but when i set the new database and i do a refresh the same error appears.

I do not know what i am doing wrong. Can you help me?

Thank you in advanceTry --> 'Verify DataBase' after 'Set DataSource Location'|||When i verify the database it says: "The database is up to date".
When i do the preview after that the same error ocurrs.|||Anyone can help? I can't solve this problem that i am having.