Database Discussion Boards

Ed.Poore9-Mar-06 1:03

9-Mar-06 1:03

I havn't come across anything (but havn't used SQL 2005 that much) but my guess is that you will have to write the code yourself.

Ed

PC/PPC Sync

Willem_Le_Roux8-Mar-06 22:15

Willem_Le_Roux

8-Mar-06 22:15

Hi guys and gals!

I am using C# (VS2003.NET) to create my PPC application and SQL CE as my database back end. I have a little app that captures information on my mobile device. How can I sync my PPC data with a SQL Server 2005 Express DB on my server?

Any help will be GREATLY appreciated.

Kind greetings

Willem Le Roux

Re: PC/PPC Sync

Mike Dimmick9-Mar-06 11:57

Mike Dimmick

9-Mar-06 11:57

SQL Server CE has built-in replication capabilities. These use IIS as a front-end, and you need to install the appropriate components for your version of SQL Server. See here[^] for information on these components. I don't know if SQL Server CE 2.0 is compatible with SQL Server 2005 Express.

The second method is to use the SqlClient namespace to connect directly to your SQL Server, and use queries against the SQL Server 2005 database to manually synchronise your SQL Server CE database. This is more effort than the first option but doesn't require any changes to SQL Server. You will also need to make your SQL Server available over whatever communications method you're using; you may not be comfortable exposing your SQL Server to the internet. I wouldn't be.

Both of these options tie you into using SQL Server on the host, which you'll have to judge for yourself whether that's a problem. The third option is to write your synchronisation mechanism yourself. You might consider using Web Services as the communication protocol.

Stability. What an interesting concept. -- Chris Maunder

Import data from Excel

thainam8-Mar-06 20:31

thainam

8-Mar-06 20:31

Hi All ADO.NET Guru,
I'm writing the following code to import Excel into DataSet.
But I get the error "Unspecified error"

            Dim m_sConn1 As String<br />
            m_sConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br />
               "Data Source= " & fileName & ";" & _<br />
               "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""<br />
<br />
            Dim objConn As New System.Data.OleDb.OleDbConnection(m_sConn1)<br />
            objConn.Open()<br />
            Dim objAdapter As New OleDbDataAdapter("Select * From [Sheet1$]", m_sConn1)<br />
            Dim objDataset As New DataSet<br />
            Dim objDataview As New DataView<br />
            objAdapter.Fill(objDataset, "Sheet1")<br />
<br />
            objConn.Close()

Please help me.
Thanks,
Tran Thai Nam

Re: Import data from Excel

mcljava13-Mar-06 9:54

mcljava

13-Mar-06 9:54

In your Select statement try using a range. For example:

<br />
Dim query as String<br />
query = "SELECT * FROM [" + sheet + "$" + range + "]"<br />

You may also have to add TableMappings

Mike Luster
CTI/IVR/Telephony SME

Re: Import data from Excel

mcljava15-Mar-06 18:38

mcljava

15-Mar-06 18:38

Another thing I want to mention after being burned by OLE DB and Excel. Even though you have IMEX=1 set, don't be surprised if the data fill contains some unexpected nulls. This happens because the underlying ISAM driver for Excel uses the first 8 cells of a column by default to determine the type. If you are dealing with spreadsheets from other people, chances are they are not 100% clean.

Fear not, all you need to do is edit the Registry and change a setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

TypeGuessRows should be set to a value appropriate for determining the data type of your column. You can specify 0 and it will scan 16,000 rows which is probably overkill in most cases. This is an intertive process to get to the point where you have an acceptable value and clean results. Once you finish the tuning remember to add the registry setting to your setup program

Good Luck

Mike Luster
CTI/IVR/Telephony SME

Multiple tables

LeaderOne8-Mar-06 20:08

LeaderOne

8-Mar-06 20:08

How can i make a search multiple tables using a keyword?

those tables doesn't have similarities or doesn't have relationships.

i'm using SQL Server 2005 and in ASP.Net

or can anyone give me a sample stored proc?

thanks in advance... Smile | :)

Re: Multiple tables

HimaBindu Vejella8-Mar-06 22:19

HimaBindu Vejella

8-Mar-06 22:19

Select * from tbalename where filedname like '%vision%'
gets all the records contains vision
U append the query based on your requirement

"Every morning I check Forbes 40 richest Indians list , if i'm not there I go to work."

Resetting the auto-increment

Brendan Vogt8-Mar-06 19:52

Brendan Vogt

8-Mar-06 19:52

Hi,

I have a members table, and the primary key ID is of type int, which has been setup to automatically increment the ID by 1 when a new member has been entered. I have added a lot of members while testing, but when I go live with the site then I would like to reset the auto-incrementer so that when a new member is added it starts back at 1 again. How do I do this?

I hope someone can help!!

Regards,
ma se

Re: Resetting the auto-increment

albCode8-Mar-06 22:08

albCode

8-Mar-06 22:08

dbcc checkident('table_name'reseed,0)

Re: Resetting the auto-increment

Brendan Vogt8-Mar-06 22:19

Brendan Vogt

8-Mar-06 22:19

Thanks, but where do I type this in? SQL Management studio? Where can I read more about this?

Re: Resetting the auto-increment

albCode8-Mar-06 22:31

albCode

8-Mar-06 22:31

Open Query analizer use ur Databse and write taht line code
dbcc checkident('table_name',reseed,0) and run this query

check this also http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_5lv8.asp

--run this query

use name_of_dataabse
dbcc checkident('table_name',reseed,0)

Sql sending SMS or email, or launching an application.

Sirag8-Mar-06 18:35

Sirag

8-Mar-06 18:35

Hi all..
Please help me make SQL server, send alerts with special text, as SMS or email to a specified person whose phone or email are present in the database.The sending process should occur when the data related to this person is updated, or when his birthday is met for example(as a reminder).
And how can SQL launch an application, for example vb.net or asp.net application as a responce for an event, alert, or job.

AND Thanks before...

Sirag..

Re: Sql sending SMS or email, or launching an application.

shm5314-Mar-06 7:45

shm53

14-Mar-06 7:45

Have you looked at SQL mail? There is a COTS package called Postie that will send mail too. SQL mail requires Outlook to be installed on the SQL Server. Check also DatabaseMail with SQL 2005

Insert Values from Radio Button into a SQL Table

Armando_Ramirez8-Mar-06 18:18

Armando_Ramirez

8-Mar-06 18:18

Im developing in Visual Studio 2005 and trying to insert the value of a Radio Button into a specific row of a SQL Table.

Is there a "codeless" way to do this ????

Thanx ¡

Armando
---
Mexico

Re: Insert Values from Radio Button into a SQL Table

shm5314-Mar-06 7:28

shm53

14-Mar-06 7:28

PLESE RE_POST IN PROPER AREA. THAT IS NOT A SQL QUESTION

help me>>>

confusegal8-Mar-06 17:56

confusegal

8-Mar-06 17:56

I am developing a system using Dreamweaver ASP.NET VB, with MS SQL server. I need to know how to insert signature in database (store) and make it appear at a page that comes with the person's name, position and etc for user's view. each page for one person's particular. Sleepy | :zzz:

How to setup a column to auto-increment

Mundo Cani8-Mar-06 8:07

Mundo Cani

8-Mar-06 8:07

Hello,

I have an identity column that is set up to auto-increment via the "Identity Increment" property for the column. This is working fine.

However, I have another column that I'm calling "Sequence". This is not an identity column, it just specifies the position of each record which is subject to change. I want to set up the column to auto-increment so I don't have to manually enter the Sequence number for each record I add. Is there a way to set up a column to auto-increment without making the column an Identity column, and still allowing me to manually change the Sequence numbers later if I want?

BTW: I'm adding data to the table in Microsoft SQL Server Management Studio.

Thanks!

Ian

Re: How to setup a column to auto-increment

goyal manish8-Mar-06 18:20

goyal manish

8-Mar-06 18:20

Use follwing:
objCol.AutoIncrement = true;
objCol.AutoIncrementSeed = 1;
objCol.AutoIncrementStep = 1;
objCol.ColumnName = "ID";
Smile | :)

Re: How to setup a column to auto-increment

Mundo Cani10-Mar-06 9:28

Mundo Cani

10-Mar-06 9:28

Thanks for the reply. I should have specified that I'm creating the table in Microsoft SQL Server Management Studio. These column properties don't seem to be exposed in the table designer. At least I can't find them...

Ian

Re: How to setup a column to auto-increment

shm5314-Mar-06 7:23

shm53

14-Mar-06 7:23

You caqn not have more that one identity column per table. The way to handle what you are attempting may be to insert the new record, get the identity using the SCOPE_IDENTITY() function then updating the record with that value

ROLLBACK TRANSACTION

Brendan Vogt8-Mar-06 2:58

Brendan Vogt

8-Mar-06 2:58

If I have 2 INSERT statements in my stored procedure, one to add a member to the member table, and one to add the member's role to the MemberInRoles tables.

Let say the second INSERT fails, and I do a ROLLBACK TRANSACTION, will it remove the row added in the first INSERT statement?

How far does ROLLBACK TRANSACTION go? Will it reverse everything?

Re: ROLLBACK TRANSACTION

Colin Angus Mackay8-Mar-06 3:08

Colin Angus Mackay

8-Mar-06 3:08

ma se wrote:
will it remove the row added in the first INSERT statement?

It depends where you put the BEGIN TRANSACTION

ma se wrote:
How far does ROLLBACK TRANSACTION go?

Back to where you put BEGIN TRANSACTION.

ma se wrote:
Will it reverse everything?

It will reverse everything on that connection to the point that BEGIN TRANSACTION was called. Other users of the database are unaffected (unless they are reading uncommitted changes [aka dirty read]- in which case they are declaring they realise some of the changes they are reading may not be valid)

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Re: ROLLBACK TRANSACTION

Brendan Vogt8-Mar-06 3:18

Brendan Vogt

8-Mar-06 3:18

Ok thanks!!

When I do a COMMIT TRANSACTION, do I need to do it for both INSERTS, or does using it once commit both INSERTS?

Re: ROLLBACK TRANSACTION

Colin Angus Mackay8-Mar-06 3:20

Colin Angus Mackay

8-Mar-06 3:20

ma se wrote:
When I do a COMMIT TRANSACTION, do I need to do it for both INSERTS, or does using it once commit both INSERTS?

The COMMIT TRANSACTION will commit all changes since the BEGIN TRANSACTION. So, do it once after both inserts.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.