Reading a flat file

I get the following error when reading a flat file : [Credit Information 1 [1]] Error: Data conversion failed. The data conversion for column "AccountName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I did check all the mappings, and everything seems to be fine, the field is read in as a string. I also check for any strange characters that can possibly cause this error but the value of the field only contains a person's name and spaces at the end.

Does anyone have any ideas what might be the cause of the error?What is the source data type of the AccountName field?

What is the data type of the mapped field in SSIS?

What code page are you working with? 1252?sql

READ Only Cursor

I have the following cursor decleration that was working before. Each time I
run it now I get the follwoing message:
FOR UPDATE cannot be specified on a READ ONLY cursor.
How do I resolve this?
declare export_cursor cursor for
select [RecordKey]
from [ExportData]
for updateTry,
declare export_cursor cursor
SCROLL_LOCKS
for
select [RecordKey]
from [ExportData]
for update
...
AMB
"Emma" wrote:

> I have the following cursor decleration that was working before. Each time
I
> run it now I get the follwoing message:
> FOR UPDATE cannot be specified on a READ ONLY cursor.
> How do I resolve this?
> declare export_cursor cursor for
> select [RecordKey]
> from [ExportData]
> for update|||I tried the SCROLL_LOCKS and it did not work. The way I had it was working
before. Will recreating the database have anything to do with the cursor not
working?
"Alejandro Mesa" wrote:
> Try,
> declare export_cursor cursor
> SCROLL_LOCKS
> for
> select [RecordKey]
> from [ExportData]
> for update
> ...
>
> AMB
> "Emma" wrote:
>|||Have you considered replacing the cursor with set-based code? Cursors
in general are a bad idea. Update cursors are worse.
--
David Portas
SQL Server MVP
--|||Try,
declare export_cursor cursor
KEYSET
for
select [RecordKey]
from [ExportData]
for update
...
AMB
"Emma" wrote:
> I tried the SCROLL_LOCKS and it did not work. The way I had it was working
> before. Will recreating the database have anything to do with the cursor n
ot
> working?
> "Alejandro Mesa" wrote:
>|||I figured it out. The table has to have a unique index in order for the
update cursor to work. The database was being replicated before and I took
replication off and deleted all the rowid’s added by replication. The rowi
d
was being used as the unique index before.
What is set-based code?
"David Portas" wrote:

> Have you considered replacing the cursor with set-based code? Cursors
> in general are a bad idea. Update cursors are worse.
> --
> David Portas
> SQL Server MVP
> --
>|||Set-based code basically means the standard SELECT, UPDATE, DELETE and
INSERT statements. These operate on sets of rows at a time rather than
individual row-by-row processing.
Set-based SQL is generally much more efficient, more concise and easier to
develop and maintain than cursors. Most of the time cursors are unnecessary
and set-based SQL should be your first choice for performing any data
manipuldation.
David Portas
SQL Server MVP
--

Read a Database Datetime (datatype) value and pass it into a Parameter

I'm running the following stored proceudre that I will eventually be using checboxes and a sqlDataAdapter to fill a DataGrid using VB.Net.

When I attempt to execute the SP on the server side for testing, it throws me the error "syntax error converting datetime from character string".

conversion of datatypes is something I'm still new to so I can't begin to understand how to write the code thus why I'm seeking help. Here's the SP Code:

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTERPROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18)='00:AC:12:E5:76:9C',

@.CREATED asdatetime='4/25/2007 8:40:50 AM',

@.MODIFIED asdatetime='5/19/2007 5:05:04 AM',

@.WORKSTATION_NAME as varchar(13)='B000E7FF53C72',

@.IP_ADDRESS as varchar(15)='171.136.201.142',

@.USER_NAME as varchar(8)='nbe5533',

@.OPERATING_SYSTEM as varchar(25)='Windows XP Professional',

@.SERVICE_PACK as varchar(3)='2.0',

@.BAND_VERSION as varchar(7)='5.06 b2',

@.WORKSTATION_OU as varchar(200)='CN=B001321D14A41,OU=Desktops,OU=Agents,OU=Card,OU=Customer Service and Support,OU=Utility,OU=NCG,OU=Workstations,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.WORKSTATION_OWNER as varchar(200)='CN=Davis\, Wally NBE5533,OU=Distributed Server Support,OU=NCG Administrators,OU=Accounts,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.MANUFACTURER as varchar(26)='Dell Computer Corporation;',

@.MODEL as varchar(40)='Latitude D600;',

@.CHASSIS as varchar(10)='8;12;',

@.SERIAL_NUMBER as varchar(30)='.83SFB51.CN486434737027.;',

@.PROCESSOR as varchar(100)='Intel(R) Pentium(R) M processor 1600MHz;',

@.HARD_DRIVE as varchar(40)='FUJITSU MHV2040AH;',

@.HARD_DRIVE_SIZE as varchar(30)='40007761920;',

@.MEMORY as varchar(22)='1073741824;1073741824;',

@.NAME as varchar(100)='KB887979',

@.VERSION as varchar(20)='VALUE DOES NOT EXIST',

@.BUILD as varchar(45)='1.3',

@.INSTALL_STATUS as varchar(20)='1',

@.INSTALL_DATE as varchar(21)='3/31/2005 2:40:34 PM',

@.PACKAGE_NAME as varchar(90)='CRYSTAL_REPORTS_ACTIVEX_VIEWER_10.0_9.2_8.6_8.5_20.05.08.01_WKS_XP2KNT_BAND_I1^EDE'

AS

-- SET NOCOUNT ON;

SELECT main.MAC, main.CREATED, main.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNERJOIN

hardware ON main.MAC = hardware.MAC INNERJOIN

network ON main.MAC = network.MAC INNERJOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE'%'+@.MAC+'%'AND MAIN.CREATED LIKE'%'+@.CREATED+'%'AND MAIN.MODIFIED LIKE'%'+@.MODIFIED+'%'AND NETWORK.WORKSTATION_NAME LIKE'%'+@.WORKSTATION_NAME+'%'AND NETWORK.IP_ADDRESS LIKE'%'+@.IP_ADDRESS+'%'AND NETWORK.USER_NAMELIKE'%'+@.USER_NAME+'%'AND NETWORK.OPERATING_SYSTEM LIKE'%'+@.OPERATING_SYSTEM+'%'AND NETWORK.SERVICE_PACK LIKE'%'+@.SERVICE_PACK+'%'AND NETWORK.BAND_VERSION LIKE'%'+@.BAND_VERSION+'%'AND NETWORK.WORKSTATION_OU LIKE'%'+@.WORKSTATION_OU+'%'AND NETWORK.WORKSTATION_OWNER LIKE'%'+@.WORKSTATION_OWNER+'%'AND HARDWARE.MANUFACTURER LIKE'%'+@.MANUFACTURER+'%'AND HARDWARE.MODEL LIKE'%'+@.MODEL+'%'AND HARDWARE.CHASSIS LIKE'%'+@.CHASSIS+'%'AND HARDWARE.SERIAL_NUMBER LIKE'%'+@.SERIAL_NUMBER+'%'AND HARDWARE.PROCESSOR LIKE'%'+@.PROCESSOR+'%'AND HARDWARE.HARD_DRIVE LIKE'%'+@.HARD_DRIVE+'%'AND HARDWARE.HARD_DRIVE_SIZE LIKE'%'+@.HARD_DRIVE_SIZE+'%'AND HARDWARE.MEMORY LIKE'%'+@.MEMORY+'%'AND SOFTWARE.NAME LIKE'%'+@.NAME+'%'AND SOFTWARE.VERSION LIKE'%'+@.VERSION+'%'AND SOFTWARE.BUILD LIKE'%'+@.BUILD+'%'AND SOFTWARE.INSTALL_STATUS LIKE'%'+@.INSTALL_STATUS+'%'AND SOFTWARE.INSTALL_DATE LIKE'%'+@.INSTALL_DATE+'%'AND SOFTWARE.PACKAGE_NAME LIKE'%'+@.PACKAGE_NAME+'%'

Thank you,

Wallace

hi, this is because you were concatenating a datetime variable with a string '%'

change this lines on your where clause

--AND MAIN.CREATED LIKE '%'+@.CREATED+'%'
AND MAIN.CREATED = @.CREATED
--AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'
AND MAIN.MODIFIED = @.MODIFIED|||

CREATED LIKE '%'+@.CREATED+'%'

Wallace,

I guess that I am at a loss about why you are concatenating wildcards to a datetime value.

Is there a particular problem you are attempting to solve by so doing?

Without converstion, you cannot add characters ( '%' ) to a datetime datatype. (What exactly do you hope to accomplish by adding '%' to the datetime?)

I am assuming that Main.Created is a datetime datetype.

In fact, I wonder if any of the above parameters really need to have '%' added to each side of the value...

|||

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

Are you doing this like to compare parts of dates? Like:

drop table dateRow
go
create table dateRow
(
dateValue datetime
)
insert into dateRow
select '20070101'
union all
select '20070201'
union all
select '20070301'
union all
select '20070401'
union all
select '20070501'
union all
select '20070601'
go
--find rows from 2007
select *
from dateRow
where convert(varchar(8),datevalue,112) like '2007_'
go
--find rows from January
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_01__'

--find rows from June
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_06__'

Interesting idea...Probably not perfect in terms of performance. A better way to do this involves having a table of dates that you can join to your date value (if you don't have time values.) Then you can index the month, year, or day values for fast searching.

Here is an article with technique to load the date table: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||

This doesn't cause any errors on my server...

Code Snippet

CREATE PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, @.Modified
GO

I think the error comes from the concatenation of the datetime parameter as I indicated earlier.

LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'

The following fails with such an error...

Code Snippet

ALTER PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, ( '%' +@.Modified + '%' )
GO

EXECUTE dbo.uspPvtSelectCommand

Server: Msg 241, Level 16, State 1, Procedure uspPvtSelectCommand, Line 6
Conversion failed when converting datetime from character string.

|||

Hi Arnie,

I figured I would give everyone who's been so kind to help with a little more information. I have a DataGrid in my vb.net app.

I have 25 fields/columns and when I go to click on "Preview" from the DataGrid, it hit's the second field parameter "CREATED" and throws the error, " Enter a value for parameter "CREATED". What I have are a bunch of checkboxes on my form, so that our Managers can click on any combination of checkboxs (that represents a field in one of 4 tables), it will pull up only those checkboxex (fields) of that data, use the SP to join those fields and then store it in the datagrid and then a separate sub-routine that exports it to Excel. So, right now, when I go back to recreate a new SQLDataAdapter in vb.net, when I select the stored procedure, it doesn't see the list of parameters, but, it is connected to the right db server so, the problem seems to be the way my Stored procedure is written.

I have since removed the concatenation and changed it so that MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED but still the error.

Any further assistance would be appreciated. Here is what the SP looks like now.

USE platform_validation_tool

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTERPROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18),

@.CREATED asdatetime,

@.MODIFIED asdatetime,

@.WORKSTATION_NAME as varchar(13),

@.IP_ADDRESS as varchar(15),

@.USER_NAME as varchar(8),

@.OPERATING_SYSTEM as varchar(25),

@.SERVICE_PACK as varchar(3),

@.BAND_VERSION as varchar(7),

@.WORKSTATION_OU as varchar(200),

@.WORKSTATION_OWNER as varchar(200),

@.MANUFACTURER as varchar(26),

@.MODEL as varchar(40),

@.CHASSIS as varchar(10),

@.SERIAL_NUMBER as varchar(30),

@.PROCESSOR as varchar(100),

@.HARD_DRIVE as varchar(40),

@.HARD_DRIVE_SIZE as varchar(30),

@.MEMORY as varchar(22),

@.NAME as varchar(100),

@.VERSION as varchar(20),

@.BUILD as varchar(45),

@.INSTALL_STATUS as varchar(20),

@.INSTALL_DATE as varchar(21),

@.PACKAGE_NAME as varchar(90)

AS

-- SET NOCOUNT ON;

SELECT main.MAC,getdate(), @.CREATED, @.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNERJOIN

hardware ON main.MAC = hardware.MAC INNERJOIN

network ON main.MAC = network.MAC INNERJOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE'%'+@.MAC+'%'AND MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED AND NETWORK.WORKSTATION_NAME LIKE'%'+@.WORKSTATION_NAME+'%'AND NETWORK.IP_ADDRESS LIKE'%'+@.IP_ADDRESS+'%'AND NETWORK.USER_NAMELIKE'%'+@.USER_NAME+'%'AND NETWORK.OPERATING_SYSTEM LIKE'%'+@.OPERATING_SYSTEM+'%'AND NETWORK.SERVICE_PACK LIKE'%'+@.SERVICE_PACK+'%'AND NETWORK.BAND_VERSION LIKE'%'+@.BAND_VERSION+'%'AND NETWORK.WORKSTATION_OU LIKE'%'+@.WORKSTATION_OU+'%'AND NETWORK.WORKSTATION_OWNER LIKE'%'+@.WORKSTATION_OWNER+'%'AND HARDWARE.MANUFACTURER LIKE'%'+@.MANUFACTURER+'%'AND HARDWARE.MODEL LIKE'%'+@.MODEL+'%'AND HARDWARE.CHASSIS LIKE'%'+@.CHASSIS+'%'AND HARDWARE.SERIAL_NUMBER LIKE'%'+@.SERIAL_NUMBER+'%'AND HARDWARE.PROCESSOR LIKE'%'+@.PROCESSOR+'%'AND HARDWARE.HARD_DRIVE LIKE'%'+@.HARD_DRIVE+'%'AND HARDWARE.HARD_DRIVE_SIZE LIKE'%'+@.HARD_DRIVE_SIZE+'%'AND HARDWARE.MEMORY LIKE'%'+@.MEMORY+'%'AND SOFTWARE.NAME LIKE'%'+@.NAME+'%'AND SOFTWARE.VERSION LIKE'%'+@.VERSION+'%'AND SOFTWARE.BUILD LIKE'%'+@.BUILD+'%'AND SOFTWARE.INSTALL_STATUS LIKE'%'+@.INSTALL_STATUS+'%'AND SOFTWARE.INSTALL_DATE LIKE'%'+@.INSTALL_DATE+'%'AND SOFTWARE.PACKAGE_NAME LIKE'%'+@.PACKAGE_NAME+'%'

Thanks and Sincerely,

Wallace

|||

Wallace,

I need you to clarify.

If I understand this correctly, you are passing into the stored procedure the checkbox values for each of the parameters listed. Is that correct?

And what checkboxes are checked determines what data is expected?

OR,

Is there actual values in the input parameters, AND the presence of a check indicated to use that value in the WHERE clause...

|||That's correct, I want to pass values, being read from the database, into the sql sp parameters, based on which checkboxes are checked.

The values will be read from the Database, passed into the parameter, and joined via the SP into a DataGrid. I have a separate vb.net sub routine that will take all of this and export to excel.

Thanks,

Wally

|||

I'm trying to understand and help, but I'm still confused. You replied "That's correct..." to two diametrically opposed questions.

Do you wish to collect multiple rows of data from the database and display that data in the DataGrid?

How do you determine what should be displayed?

Are all columns always returned and displayed?

Is this a search routine where you pass in some search criteria, hence the input parameters?

What is the application code that calls the stored procedure? (Please post.)

|||

Arnie,

We have a database that has 4 tables, 25 columns of data to pull from. Software/Hardware data is pulled from the PC's and stored on this database.

The vb.net application I'm creating will connect to a database called "platform_validation_tool" running on SQL 2005, and

will use an SQLConnection, SQLDataAdapter and DataSet to mirror the tables.

Whether the user checks one checkbox or all 25, I want it to pull any column of data based on those checkboxes checked, i.e. using the SQLDataAdapter and it's methods, it will search the database, pass it into the input parameters, and then return it to the DataGrid for display. That is the first step. Right now, I can't get the stored procedure to work because of the datetime datatype to convert to a string. Once I can jump this hurdle, then I'll setup the rest of the code in vb.net. Here's some of the vb.net code that will allow me to retrieve the data from the database by identifying the specific SourceColumn and then Fill the DataGrid:

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

etc, and so on.,

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName".

Let me know if it needs further explanation. I've only been in programming for 3 months. Thank you for your patience.

Wally

|||hi,

i'm just curious on how did you present the values in your checkboxes? a.) is it along side on a data grid? or b.) does your checkbox has an input box along side it where the user can input a search string then ticks the checkbox if it should be included in the search?

if your doing b. does the user need to input the time for the created and modified date?|||How about posting some of the vb.net code that invokes the sp?
SQLDataSource definition, etc.|||

DaleJ,

My code is at work and until I return there, simplest way I can put it is the routine goes like this:

The checkboxes are just inside of a GroupBox on a separate Form and not imbedded within or next to the DataGrid.

If checkbox1.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

Else checkbox1.checked = False

End If

If checkbox2.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

Else checkbox2.checked = False Then

End If

Item(1), Item(2), item(3), etc. is the logical order of the column parameters, i.e. Item(0) = @.ReturnValue, Item(1) = @.MAC, Item(2) = @.CREATED, Item(3) = @.MODIFIED, etc.

There will be 25 checkboxes in all, from 4 tables. There's other code that I still need to plug in but this is the jist of it. I just can't get the stored procedure to input any of the data from the MODIFIED and CREATED columns in the database (using DateTime datatype) to convert over to a varchar string.

Finally, after the checkboxes have been evaluated as checked or unchecked, it will run the stored procedure on all the checkboxes whose boolean is True with the Fill method below .

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName" and fills the DataSet.

It may seem elementary but it's the easiest way for me to start out learning to code until I get a couple of vb.net classes under my belt.

Wallace

|||

The following code example 'should' find a match for any parameters passed in by your users. If this works for you, I suspect that you could do away with the checkboxes on the form AND the IF-End IF blocks -they will not be needed. Just set all the parameters

Since you are starting out learning, I suggest that you quickly drop using all caps. We all have learned to read using mixed case and we recognize and read mixed case with greater ease than all caps. However, there is a 'tradition' of using caps for the SQL language words.

Also, rigorously following good formatting principles will make your code easier to read, and easier to maintain.

Code Snippet

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]
( @.MAC varchar(18),
@.Created datetime,
@.Modified datetime,
@.Workstation_Name varchar(13),
@.IP_Address varchar(15),
@.User_Name varchar(8),
@.Operating_System varchar(25),
@.Service_Pack varchar(3),
@.Band_Version varchar(7),
@.Workstation_OU varchar(200),
@.Workstation_Owner varchar(200),
@.Manufacturer varchar(26),
@.Model varchar(40),
@.Chassis varchar(10),
@.Serial_Number varchar(30),
@.Processor varchar(100),
@.Hard_Drive varchar(40),
@.Hard_Drive_Size varchar(30),
@.Memory varchar(22),
@.Name varchar(100),
@.Version varchar(20),
@.Build varchar(45),
@.Install_Status varchar(20),
@.Install_Date varchar(21),
@.Package_Name varchar(90)
)
AS

SET NOCOUNT ON;

SELECT
m.MAC,
getdate(),
m.Created,
m.Modified,
h.Manufacturer,
h.Model,
h.Chassis,
h.Serial_Number,
h.Processor,
h.Hard_Drive,
h.Hard_Drive_Size,
h.Memory,
n.Workstation_Name,
n.IP_Address,
n.[User_Name],
n.Operating_System,
n.Service_Pack,
n.Band_Version,
n.Workstation_OU,
n.Workstation_Owner,
s.MAC,
s.[Name],
s.Version,
s.Build,
s.Install_Status,
s.Install_Date,
s.Package_Name
FROM Main m
JOIN Hardware h
ON m.MAC = h.MAC
JOIN Network n
ON m.MAC = n.MAC
JOIN Software s
ON m.MAC = s.MAC
WHERE ( m.MAC = @.MAC
AND m.Created = coalesce( nullif( @.Created, 0 ), m.Created )
AND m.Modified = coalesce( nullif( @.Modified, 0 ), m.Modified )
AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )
AND n.IP_Address = coalesce( nullif( @.IP_Address, '' ), n.IP_Address )
AND n.[User_Name] = coalesce( nullif( @.User_Name, '' ), n.[User_Name] )
AND n.Operating_System = coalesce( nullif( @.Operating_System, '' ), n.Operating_System )
AND n.Service_Pack = coalesce( nullif( @.Service_Pack, '' ), n.Service_Pack )
AND n.Band_Version = coalesce( nullif( @.Band_Version, '' ), n.Band_Version )
AND n.Workstation_OU = coalesce( nullif( @.Workstation_OU, '' ), n.Workstation_OU )
AND n.Workstation_Owner = coalesce( nullif( @.Workstation_Owner, '' ), n.Workstation_Owner )
AND h.Manufacturer = coalesce( nullif( @.Manufacturer, '' ), h.Manufacturer )
AND h.Model = coalesce( nullif( @.Model, '' ), h.Model )
AND h.Chassis = coalesce( nullif( @.Chassis, '' ), h.Chassis )
AND h.Serial_Number = coalesce( nullif( @.Serial_Number, '' ), h.Serial_Number )
AND h.Processor = coalesce( nullif( @.Processor, '' ), h.Processor )
AND h.Hard_Drive = coalesce( nullif( @.Hard_Drive, '' ), h.Hard_Drive )
AND h.Hard_Drive_Size = coalesce( nullif( @.Hard_Drive_Size, '' ), h.Hard_Drive_Size )
AND h.Memory = coalesce( nullif( @.Memory, '' ), h.Memory )
AND s.[Name] = coalesce( nullif( @.Name, '' ), s.[Name] )
AND s.Version = coalesce( nullif( @.Version, '' ), s.Version )
AND s.Build = coalesce( nullif( @.Build, '' ), s.Build )
AND s.Install_Status = coalesce( nullif( @.Install_Status, '' ), s.Install_Status )
AND s.Install_Date = coalesce( nullif( @.Install_Date, '' ), s.Install_Date )
AND s.Package_Name = coalesce( nullif( @.Package_Name, '' ), s.Package_Name )

GO

|||

Hey Arnie,

Thank you for the tips. I'll be sure to apply them as a newcomer.

The code you supplied me for the @.CREATED and @.MODIFIED parameters didn't create any errors when I Executed the stored procedure. I went into the datagrid on my vb.net form, clicked on the preview button, and again, it threw me the error, "Enter a value for paramter "CREATED". What I did then was added two dates (format is > 04/04/2007) in the datagrid "value" fields to see what it would return. The results it returned are as follows: Type = Int32, Value = 0. It looks as if it reads the data I inputted into the datagrid value field it recognizes this date format as an Integer but somehow didn't return this data in the results window.

Now, when it hit the 4th line, where I started adding the code you updated, "AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )", in the WHERE clause, it threw this error:

Msg 306, Level 16, State 1, Procedure uspPvtSelectCommand, Line 34

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The bottom line is that the stored procedure will not read the date and time info from the CREATED or the MODIFIED fields in the main.MAC table. This is the format in the database for both fields -> 4/25/2007 5:05:04 AM

I'm still trying to find some material on converting this date and time string value to a datetime datatype value.

Any other suggestions are most welcome.

Wally

Read a Database Datetime (datatype) value and pass it into a Parameter

I'm running the following stored proceudre that I will eventually be using checboxes and a sqlDataAdapter to fill a DataGrid using VB.Net.

When I attempt to execute the SP on the server side for testing, it throws me the error "syntax error converting datetime from character string".

conversion of datatypes is something I'm still new to so I can't begin to understand how to write the code thus why I'm seeking help. Here's the SP Code:

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18) = '00:AC:12:E5:76:9C',

@.CREATED as datetime = '4/25/2007 8:40:50 AM',

@.MODIFIED as datetime = '5/19/2007 5:05:04 AM',

@.WORKSTATION_NAME as varchar(13) = 'B000E7FF53C72',

@.IP_ADDRESS as varchar(15) = '171.136.201.142',

@.USER_NAME as varchar(8) = 'nbe5533',

@.OPERATING_SYSTEM as varchar(25) = 'Windows XP Professional',

@.SERVICE_PACK as varchar(3) = '2.0',

@.BAND_VERSION as varchar(7) = '5.06 b2',

@.WORKSTATION_OU as varchar(200) = 'CN=B001321D14A41,OU=Desktops,OU=Agents,OU=Card,OU=Customer Service and Support,OU=Utility,OU=NCG,OU=Workstations,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.WORKSTATION_OWNER as varchar(200) = 'CN=Davis\, Wally NBE5533,OU=Distributed Server Support,OU=NCG Administrators,OU=Accounts,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.MANUFACTURER as varchar(26) = 'Dell Computer Corporation;',

@.MODEL as varchar(40) = 'Latitude D600;',

@.CHASSIS as varchar(10) = '8;12;',

@.SERIAL_NUMBER as varchar(30) = '.83SFB51.CN486434737027.;',

@.PROCESSOR as varchar(100) = 'Intel(R) Pentium(R) M processor 1600MHz;',

@.HARD_DRIVE as varchar(40) = 'FUJITSU MHV2040AH;',

@.HARD_DRIVE_SIZE as varchar(30) = '40007761920;',

@.MEMORY as varchar(22) = '1073741824;1073741824;',

@.NAME as varchar(100) = 'KB887979',

@.VERSION as varchar(20) = 'VALUE DOES NOT EXIST',

@.BUILD as varchar(45) = '1.3',

@.INSTALL_STATUS as varchar(20) = '1',

@.INSTALL_DATE as varchar(21) = '3/31/2005 2:40:34 PM' ,

@.PACKAGE_NAME as varchar(90) = 'CRYSTAL_REPORTS_ACTIVEX_VIEWER_10.0_9.2_8.6_8.5_20.05.08.01_WKS_XP2KNT_BAND_I1^EDE'

AS

-- SET NOCOUNT ON;

SELECT main.MAC, main.CREATED, main.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNER JOIN

hardware ON main.MAC = hardware.MAC INNER JOIN

network ON main.MAC = network.MAC INNER JOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE '%'+@.MAC+'%' AND MAIN.CREATED LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%' AND NETWORK.WORKSTATION_NAME LIKE '%'+@.WORKSTATION_NAME+'%' AND NETWORK.IP_ADDRESS LIKE '%'+@.IP_ADDRESS+'%' AND NETWORK.USER_NAME LIKE '%'+@.USER_NAME+'%' AND NETWORK.OPERATING_SYSTEM LIKE '%'+@.OPERATING_SYSTEM+'%' AND NETWORK.SERVICE_PACK LIKE '%'+@.SERVICE_PACK+'%' AND NETWORK.BAND_VERSION LIKE '%'+@.BAND_VERSION+'%' AND NETWORK.WORKSTATION_OU LIKE '%'+@.WORKSTATION_OU+'%' AND NETWORK.WORKSTATION_OWNER LIKE '%'+@.WORKSTATION_OWNER+'%' AND HARDWARE.MANUFACTURER LIKE '%'+@.MANUFACTURER+'%' AND HARDWARE.MODEL LIKE '%'+@.MODEL+'%' AND HARDWARE.CHASSIS LIKE '%'+@.CHASSIS+'%' AND HARDWARE.SERIAL_NUMBER LIKE '%'+@.SERIAL_NUMBER+'%' AND HARDWARE.PROCESSOR LIKE '%'+@.PROCESSOR+'%' AND HARDWARE.HARD_DRIVE LIKE '%'+@.HARD_DRIVE+'%' AND HARDWARE.HARD_DRIVE_SIZE LIKE '%'+@.HARD_DRIVE_SIZE+'%' AND HARDWARE.MEMORY LIKE '%'+@.MEMORY+'%' AND SOFTWARE.NAME LIKE '%'+@.NAME+'%' AND SOFTWARE.VERSION LIKE '%'+@.VERSION+'%' AND SOFTWARE.BUILD LIKE '%'+@.BUILD+'%' AND SOFTWARE.INSTALL_STATUS LIKE '%'+@.INSTALL_STATUS+'%' AND SOFTWARE.INSTALL_DATE LIKE '%'+@.INSTALL_DATE+'%' AND SOFTWARE.PACKAGE_NAME LIKE '%'+@.PACKAGE_NAME+'%'

Thank you,

Wallace

hi, this is because you were concatenating a datetime variable with a string '%'

change this lines on your where clause

--AND MAIN.CREATED LIKE '%'+@.CREATED+'%'
AND MAIN.CREATED = @.CREATED
--AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'
AND MAIN.MODIFIED = @.MODIFIED|||

CREATED LIKE '%'+@.CREATED+'%'

Wallace,

I guess that I am at a loss about why you are concatenating wildcards to a datetime value.

Is there a particular problem you are attempting to solve by so doing?

Without converstion, you cannot add characters ( '%' ) to a datetime datatype. (What exactly do you hope to accomplish by adding '%' to the datetime?)

I am assuming that Main.Created is a datetime datetype.

In fact, I wonder if any of the above parameters really need to have '%' added to each side of the value...

|||

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

Are you doing this like to compare parts of dates? Like:

drop table dateRow
go
create table dateRow
(
dateValue datetime
)
insert into dateRow
select '20070101'
union all
select '20070201'
union all
select '20070301'
union all
select '20070401'
union all
select '20070501'
union all
select '20070601'
go
--find rows from 2007
select *
from dateRow
where convert(varchar(8),datevalue,112) like '2007_'
go
--find rows from January
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_01__'

--find rows from June
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_06__'

Interesting idea...Probably not perfect in terms of performance. A better way to do this involves having a table of dates that you can join to your date value (if you don't have time values.) Then you can index the month, year, or day values for fast searching.

Here is an article with technique to load the date table: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||

This doesn't cause any errors on my server...

Code Snippet

CREATE PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, @.Modified
GO

I think the error comes from the concatenation of the datetime parameter as I indicated earlier.

LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'

The following fails with such an error...

Code Snippet

ALTER PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, ( '%' +@.Modified + '%' )
GO

EXECUTE dbo.uspPvtSelectCommand

Server: Msg 241, Level 16, State 1, Procedure uspPvtSelectCommand, Line 6
Conversion failed when converting datetime from character string.

|||

Hi Arnie,

I figured I would give everyone who's been so kind to help with a little more information. I have a DataGrid in my vb.net app.

I have 25 fields/columns and when I go to click on "Preview" from the DataGrid, it hit's the second field parameter "CREATED" and throws the error, " Enter a value for parameter "CREATED". What I have are a bunch of checkboxes on my form, so that our Managers can click on any combination of checkboxs (that represents a field in one of 4 tables), it will pull up only those checkboxex (fields) of that data, use the SP to join those fields and then store it in the datagrid and then a separate sub-routine that exports it to Excel. So, right now, when I go back to recreate a new SQLDataAdapter in vb.net, when I select the stored procedure, it doesn't see the list of parameters, but, it is connected to the right db server so, the problem seems to be the way my Stored procedure is written.

I have since removed the concatenation and changed it so that MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED but still the error.

Any further assistance would be appreciated. Here is what the SP looks like now.

USE platform_validation_tool

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18),

@.CREATED as datetime,

@.MODIFIED as datetime,

@.WORKSTATION_NAME as varchar(13),

@.IP_ADDRESS as varchar(15),

@.USER_NAME as varchar(8),

@.OPERATING_SYSTEM as varchar(25),

@.SERVICE_PACK as varchar(3),

@.BAND_VERSION as varchar(7),

@.WORKSTATION_OU as varchar(200),

@.WORKSTATION_OWNER as varchar(200),

@.MANUFACTURER as varchar(26),

@.MODEL as varchar(40),

@.CHASSIS as varchar(10),

@.SERIAL_NUMBER as varchar(30),

@.PROCESSOR as varchar(100),

@.HARD_DRIVE as varchar(40),

@.HARD_DRIVE_SIZE as varchar(30),

@.MEMORY as varchar(22),

@.NAME as varchar(100),

@.VERSION as varchar(20),

@.BUILD as varchar(45),

@.INSTALL_STATUS as varchar(20),

@.INSTALL_DATE as varchar(21),

@.PACKAGE_NAME as varchar(90)

AS

-- SET NOCOUNT ON;

SELECT main.MAC, getdate(), @.CREATED, @.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNER JOIN

hardware ON main.MAC = hardware.MAC INNER JOIN

network ON main.MAC = network.MAC INNER JOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE '%'+@.MAC+'%' AND MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED AND NETWORK.WORKSTATION_NAME LIKE '%'+@.WORKSTATION_NAME+'%' AND NETWORK.IP_ADDRESS LIKE '%'+@.IP_ADDRESS+'%' AND NETWORK.USER_NAME LIKE '%'+@.USER_NAME+'%' AND NETWORK.OPERATING_SYSTEM LIKE '%'+@.OPERATING_SYSTEM+'%' AND NETWORK.SERVICE_PACK LIKE '%'+@.SERVICE_PACK+'%' AND NETWORK.BAND_VERSION LIKE '%'+@.BAND_VERSION+'%' AND NETWORK.WORKSTATION_OU LIKE '%'+@.WORKSTATION_OU+'%' AND NETWORK.WORKSTATION_OWNER LIKE '%'+@.WORKSTATION_OWNER+'%' AND HARDWARE.MANUFACTURER LIKE '%'+@.MANUFACTURER+'%' AND HARDWARE.MODEL LIKE '%'+@.MODEL+'%' AND HARDWARE.CHASSIS LIKE '%'+@.CHASSIS+'%' AND HARDWARE.SERIAL_NUMBER LIKE '%'+@.SERIAL_NUMBER+'%' AND HARDWARE.PROCESSOR LIKE '%'+@.PROCESSOR+'%' AND HARDWARE.HARD_DRIVE LIKE '%'+@.HARD_DRIVE+'%' AND HARDWARE.HARD_DRIVE_SIZE LIKE '%'+@.HARD_DRIVE_SIZE+'%' AND HARDWARE.MEMORY LIKE '%'+@.MEMORY+'%' AND SOFTWARE.NAME LIKE '%'+@.NAME+'%' AND SOFTWARE.VERSION LIKE '%'+@.VERSION+'%' AND SOFTWARE.BUILD LIKE '%'+@.BUILD+'%' AND SOFTWARE.INSTALL_STATUS LIKE '%'+@.INSTALL_STATUS+'%' AND SOFTWARE.INSTALL_DATE LIKE '%'+@.INSTALL_DATE+'%' AND SOFTWARE.PACKAGE_NAME LIKE '%'+@.PACKAGE_NAME+'%'

Thanks and Sincerely,

Wallace

|||

Wallace,

I need you to clarify.

If I understand this correctly, you are passing into the stored procedure the checkbox values for each of the parameters listed. Is that correct?

And what checkboxes are checked determines what data is expected?

OR,

Is there actual values in the input parameters, AND the presence of a check indicated to use that value in the WHERE clause...

|||That's correct, I want to pass values, being read from the database, into the sql sp parameters, based on which checkboxes are checked.

The values will be read from the Database, passed into the parameter, and joined via the SP into a DataGrid. I have a separate vb.net sub routine that will take all of this and export to excel.

Thanks,

Wally

|||

I'm trying to understand and help, but I'm still confused. You replied "That's correct..." to two diametrically opposed questions.

Do you wish to collect multiple rows of data from the database and display that data in the DataGrid?

How do you determine what should be displayed?

Are all columns always returned and displayed?

Is this a search routine where you pass in some search criteria, hence the input parameters?

What is the application code that calls the stored procedure? (Please post.)

|||

Arnie,

We have a database that has 4 tables, 25 columns of data to pull from. Software/Hardware data is pulled from the PC's and stored on this database.

The vb.net application I'm creating will connect to a database called "platform_validation_tool" running on SQL 2005, and

will use an SQLConnection, SQLDataAdapter and DataSet to mirror the tables.

Whether the user checks one checkbox or all 25, I want it to pull any column of data based on those checkboxes checked, i.e. using the SQLDataAdapter and it's methods, it will search the database, pass it into the input parameters, and then return it to the DataGrid for display. That is the first step. Right now, I can't get the stored procedure to work because of the datetime datatype to convert to a string. Once I can jump this hurdle, then I'll setup the rest of the code in vb.net. Here's some of the vb.net code that will allow me to retrieve the data from the database by identifying the specific SourceColumn and then Fill the DataGrid:

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

etc, and so on.,

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName".

Let me know if it needs further explanation. I've only been in programming for 3 months. Thank you for your patience.

Wally

|||hi,

i'm just curious on how did you present the values in your checkboxes? a.) is it along side on a data grid? or b.) does your checkbox has an input box along side it where the user can input a search string then ticks the checkbox if it should be included in the search?

if your doing b. does the user need to input the time for the created and modified date?|||How about posting some of the vb.net code that invokes the sp?
SQLDataSource definition, etc.|||

DaleJ,

My code is at work and until I return there, simplest way I can put it is the routine goes like this:

The checkboxes are just inside of a GroupBox on a separate Form and not imbedded within or next to the DataGrid.

If checkbox1.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

Else checkbox1.checked = False

End If

If checkbox2.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

Else checkbox2.checked = False Then

End If

Item(1), Item(2), item(3), etc. is the logical order of the column parameters, i.e. Item(0) = @.ReturnValue, Item(1) = @.MAC, Item(2) = @.CREATED, Item(3) = @.MODIFIED, etc.

There will be 25 checkboxes in all, from 4 tables. There's other code that I still need to plug in but this is the jist of it. I just can't get the stored procedure to input any of the data from the MODIFIED and CREATED columns in the database (using DateTime datatype) to convert over to a varchar string.

Finally, after the checkboxes have been evaluated as checked or unchecked, it will run the stored procedure on all the checkboxes whose boolean is True with the Fill method below .

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName" and fills the DataSet.

It may seem elementary but it's the easiest way for me to start out learning to code until I get a couple of vb.net classes under my belt.

Wallace

|||

The following code example 'should' find a match for any parameters passed in by your users. If this works for you, I suspect that you could do away with the checkboxes on the form AND the IF-End IF blocks -they will not be needed. Just set all the parameters

Since you are starting out learning, I suggest that you quickly drop using all caps. We all have learned to read using mixed case and we recognize and read mixed case with greater ease than all caps. However, there is a 'tradition' of using caps for the SQL language words.

Also, rigorously following good formatting principles will make your code easier to read, and easier to maintain.

Code Snippet

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]
( @.MAC varchar(18),
@.Created datetime,
@.Modified datetime,
@.Workstation_Name varchar(13),
@.IP_Address varchar(15),
@.User_Name varchar(8),
@.Operating_System varchar(25),
@.Service_Pack varchar(3),
@.Band_Version varchar(7),
@.Workstation_OU varchar(200),
@.Workstation_Owner varchar(200),
@.Manufacturer varchar(26),
@.Model varchar(40),
@.Chassis varchar(10),
@.Serial_Number varchar(30),
@.Processor varchar(100),
@.Hard_Drive varchar(40),
@.Hard_Drive_Size varchar(30),
@.Memory varchar(22),
@.Name varchar(100),
@.Version varchar(20),
@.Build varchar(45),
@.Install_Status varchar(20),
@.Install_Date varchar(21),
@.Package_Name varchar(90)
)
AS

SET NOCOUNT ON;

SELECT
m.MAC,
getdate(),
m.Created,
m.Modified,
h.Manufacturer,
h.Model,
h.Chassis,
h.Serial_Number,
h.Processor,
h.Hard_Drive,
h.Hard_Drive_Size,
h.Memory,
n.Workstation_Name,
n.IP_Address,
n.[User_Name],
n.Operating_System,
n.Service_Pack,
n.Band_Version,
n.Workstation_OU,
n.Workstation_Owner,
s.MAC,
s.[Name],
s.Version,
s.Build,
s.Install_Status,
s.Install_Date,
s.Package_Name
FROM Main m
JOIN Hardware h
ON m.MAC = h.MAC
JOIN Network n
ON m.MAC = n.MAC
JOIN Software s
ON m.MAC = s.MAC
WHERE ( m.MAC = @.MAC
AND m.Created = coalesce( nullif( @.Created, 0 ), m.Created )
AND m.Modified = coalesce( nullif( @.Modified, 0 ), m.Modified )
AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )
AND n.IP_Address = coalesce( nullif( @.IP_Address, '' ), n.IP_Address )
AND n.[User_Name] = coalesce( nullif( @.User_Name, '' ), n.[User_Name] )
AND n.Operating_System = coalesce( nullif( @.Operating_System, '' ), n.Operating_System )
AND n.Service_Pack = coalesce( nullif( @.Service_Pack, '' ), n.Service_Pack )
AND n.Band_Version = coalesce( nullif( @.Band_Version, '' ), n.Band_Version )
AND n.Workstation_OU = coalesce( nullif( @.Workstation_OU, '' ), n.Workstation_OU )
AND n.Workstation_Owner = coalesce( nullif( @.Workstation_Owner, '' ), n.Workstation_Owner )
AND h.Manufacturer = coalesce( nullif( @.Manufacturer, '' ), h.Manufacturer )
AND h.Model = coalesce( nullif( @.Model, '' ), h.Model )
AND h.Chassis = coalesce( nullif( @.Chassis, '' ), h.Chassis )
AND h.Serial_Number = coalesce( nullif( @.Serial_Number, '' ), h.Serial_Number )
AND h.Processor = coalesce( nullif( @.Processor, '' ), h.Processor )
AND h.Hard_Drive = coalesce( nullif( @.Hard_Drive, '' ), h.Hard_Drive )
AND h.Hard_Drive_Size = coalesce( nullif( @.Hard_Drive_Size, '' ), h.Hard_Drive_Size )
AND h.Memory = coalesce( nullif( @.Memory, '' ), h.Memory )
AND s.[Name] = coalesce( nullif( @.Name, '' ), s.[Name] )
AND s.Version = coalesce( nullif( @.Version, '' ), s.Version )
AND s.Build = coalesce( nullif( @.Build, '' ), s.Build )
AND s.Install_Status = coalesce( nullif( @.Install_Status, '' ), s.Install_Status )
AND s.Install_Date = coalesce( nullif( @.Install_Date, '' ), s.Install_Date )
AND s.Package_Name = coalesce( nullif( @.Package_Name, '' ), s.Package_Name )

GO

|||

Hey Arnie,

Thank you for the tips. I'll be sure to apply them as a newcomer.

The code you supplied me for the @.CREATED and @.MODIFIED parameters didn't create any errors when I Executed the stored procedure. I went into the datagrid on my vb.net form, clicked on the preview button, and again, it threw me the error, "Enter a value for paramter "CREATED". What I did then was added two dates (format is > 04/04/2007) in the datagrid "value" fields to see what it would return. The results it returned are as follows: Type = Int32, Value = 0. It looks as if it reads the data I inputted into the datagrid value field it recognizes this date format as an Integer but somehow didn't return this data in the results window.

Now, when it hit the 4th line, where I started adding the code you updated, "AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )", in the WHERE clause, it threw this error:

Msg 306, Level 16, State 1, Procedure uspPvtSelectCommand, Line 34

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The bottom line is that the stored procedure will not read the date and time info from the CREATED or the MODIFIED fields in the main.MAC table. This is the format in the database for both fields -> 4/25/2007 5:05:04 AM

I'm still trying to find some material on converting this date and time string value to a datetime datatype value.

Any other suggestions are most welcome.

Wally

Re-"pointing" Stored Procedures

How do I "point" a set of stored procedures to operate on different linked servers?

In other words, I have the following linked servers:

DatabaseA
DatabaseB
DatabaseC
DatabaseD
...and in the future, there may be added additional linked servers.

All the linked servers have identical schema, but they contain unique data--each linked server represents a company.

I have a database which will contain stored procedures which I will want to operate against these linked servers. How can I "redirect" my stored procedures to operate against a chosen linked server?

If these were not linked servers, but SQL Server databases, I'd be able to replicate the same stored procedures in each database. Then, when I called a stored procedure, it would act against the data in that database. But these aren't SQL Server databases, so that idea is out.

Unfortunately, the USE command cannot be used within a stored procedure, and even if you could, I can't get it to respond to a database name given as a variable. That idea is out.

The only alternative I have left is to use the string catenation facility of the EXECUTE command. Unfortunately, with 100s of complex queries, setting that up is going to be a nightmare.

Does anyone have any ideas?With all due respect, having separate companies in separate databases is a bad idea. You've set a new standard - separate companies in separate databases on linked servers that are not SQL Server. Congrats.

Assuming this design is something over which you have no control (sounds like a consulting gig), how 'bout another new standard - separate SQL Server databases with stored procedures that act on separate linked servers (which are really linked databases) that are not SQL Server? You maintain 1:1 relationship b/w SQL Server databases and the linked servers their stored procedures act on.

Then, you write stored procedure generator that replaces all linked server references with the 4-part name to which the db corresponds (using syscomments, PatIndex and cursors) and sticks 'em in the right database. Ugly, eh? Remember, we're reaching for new heights.

You will need a separate database that is your (source) control database in which your stored procedures originate - sounds like you already have that - and a table that maps your SQL Server databases to your linked servers.

Good luck.|||Wow, that sounds like one heck of a way to get hurt really, really bad!

Could you build views that combine the data from all of the various servers, or DTS packages to scoop them into a single container to make management simpler?

If you really, truly want to continue to process the data on N different servers (with N being a variable, not a constant), then I'd suggest that you convert your stored procedures to DTS packages... These could operate against DSN or udl names, which would at least confine the chaos to a much smaller area.

Good luck!
-PatP|||Pat and Max, you guys both came up with good ideas.

Yes, this is a consulting gig. Yes, these are pre-existing, PervasiveSQL databases which are the backend to someone else's product. The separate database for each company is thier concept and there is nothing I can do about that.

We have an existing reporting package that operates against an MSSQL backend. Our client has asked us to port our product to work with this PervasiveSQL-multi-company arrangement.

I like both your ideas. Here's another I came up with while waiting for responses:

Write my stored procedures using a token for the database name. Store those stored procedures as text in a table, much like syscomments stores stored procedures. Then write a "master" execution stored procedure which loads the stored procedure text from that table, uses the replace command to substitute the correct database name for the token, and then use the EXECUTE command to execute the stored procedure. The problem I see with my idea, as compared to your ideas, is that I am not acutally executing stored procedures, hence I lose any precompilation advantage of a true stored procedure.

Thanks for you ideas guys.|||The problem with objects being directly accessed from a linked server is the long naming convention. I guess you guys would agree that most bugs related to objects from linked servers were due to typos on the full object name.

In one of my projects here in the Philippines, a GIS application needs to pull data from a central data repository. The problem is the GIS server (with its own database server) is not allowed to directly access the data repository.

As a work around, I defined both the GIS database server and the central repository in another server as linked servers. Thus, the two servers are linked via the third server. then I did the following:

1. I defined details of the data to needed by the GIS apps and put it in a view in the central repository.
CREATE VIEW vw_PROPERTY_RE_GIS
AS
SELECT B.s_prop_no, B.s_re_no, A.s_PLOP, A.s_loc_id
FROM T_PROPERTY A, T_RE_PROPERTY B
WHERE A.s_prop_no = B.s_prop_no AND A.s_loc_id = b.s_loc_id

2. Defined a view in the third to access the view in the data repository.
CREATE VIEW vw_PROPERTY_RE_GIS
as
SELECT * FROM CNTRL_DB.REMS.REMS.vw_PROPERTY_RE_GIS
3. Defined a view in the GIS DB to access the view in the third server.
CREATE VIEW vw_PROPERTY_RE_GIS
as
SELECT * FROM ALPS.REMS.ALPS.vw_PROPERTY_RE_GIS

Thus, whenever my GIS app would need information about a certain real estate, it would simply kick the simple query SELECT * FROM vw_PROPERTY_RE_GIS.

And if by chance there is a need to add more fields on the data to be extracted, I would just have to modifiy the view on the central repository.

This approach works if there is restriction on how servers are linked and performance is not so much an issue.|||I would NEVER recommend this, but you can use the USE statement in a stored procedure and pass the linked server name as a variable. But you have to use...wait for it...DYNAMIC SQL.

(Pause for shrieks of horror from all competent DBAs...)

I've only implemented this once, for a database schema snapshot application that had to run against any and all databases on a server. It was a mess to program, but it has run very smoothly and reliably since then. The code hasn't changed much in four years and two SQL Server upgrades.|||Blindman,

How were you able to accomplish this ?

I've attempted to use the USE statement in stored procedures and, when compiled, they've always come back with an error stating the the USE statement is not permited in stored procedures. The USE page in the Books Online also state this.

Additionally, the use of a variable for the database with the USE statment is also prohibited. I've tested this several times and have not been successful. Apparently, the USE statement requires a literal for the database name.

If you've found some way to get around these problem, let me know, 'cause they'd make tackling these problems a whole lot easier.

Thanks.

Ken|||create procedure GetDataFromPubs
as
begin
declare @.SQLString varchar(4000)
set @.SQLString = 'Use Pubs Select * from Authors'
execute (@.SQLString)
end

The executed statement runs in it's own scope, and so after completion focus returns to the calling database.

RDA push error

While trying to push a tracked table using RDA.push, I get the following error:

Error Code: 80004005

The message cannot be built. The make message failed.

Minor Err: 28581

Source: Microsoft SQL server 2005 Mobile Edition.

All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.

Any help to find the reason for this error will be greatly appreciated.

- Paul

As far as I remember there was a bug around when you have more than 128 columns. This got fixed later in the SP1.

Thanks,

Laxmi

|||

Thanks. Laxmi.

SQL server 2005 SP1 was indeed missing, and I installed it. But now the Pull fails, with same error code, and minor error 0. Still investigating what is happening.

Thanks,

Paul

|||

Hi,

The problem persists with SQL server 2005 SP1. Any other idea please?

- Paul

|||

Sorry I should have been more clear when I have said SP1. What I meant was SQL Server Mobile/Everywhere/Compact SP1 and not SQL Server SP1. Please visit the URL below to get you more details on SQL Server Mobile/Everywhere/Compact SP1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=774655&SiteID=1

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

I see this message from time to time in my replication thread, and shortly afterwards processes using the SDF file will start complaining that it has been corrupted.

Here is the stack trace that I receive:

28037 : A request to send data to the computer running IIS has failed. For more information, see HRESULT.

28581 : The message cannot be built. The Make Message failed.

at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()

at System.Data.SqlServerCe.SyncAsyncResult.BeginSyncAndStatusReporting()

at System.Data.SqlServerCe.SyncAsyncResult.SyncThread()

The only thing that I can think of that is unusual is that one of my tables has nearly 1,000,000 rows in it. I have no more than 16 columns in any table. My large table has only 7 columns.

What exactly does the message mean? Would this be causative of my sdf corruption problems, or symptomatic?

How can I determine what SP's have been installed so I can give you better information?

Thanks,

David Johnson

RDA push error

While trying to push a tracked table using RDA.push, I get the following error:

Error Code: 80004005

The message cannot be built. The make message failed.

Minor Err: 28581

Source: Microsoft SQL server 2005 Mobile Edition.

All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.

Any help to find the reason for this error will be greatly appreciated.

- Paul

As far as I remember there was a bug around when you have more than 128 columns. This got fixed later in the SP1.

Thanks,

Laxmi

|||

Thanks. Laxmi.

SQL server 2005 SP1 was indeed missing, and I installed it. But now the Pull fails, with same error code, and minor error 0. Still investigating what is happening.

Thanks,

Paul

|||

Hi,

The problem persists with SQL server 2005 SP1. Any other idea please?

- Paul

|||

Sorry I should have been more clear when I have said SP1. What I meant was SQL Server Mobile/Everywhere/Compact SP1 and not SQL Server SP1. Please visit the URL below to get you more details on SQL Server Mobile/Everywhere/Compact SP1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=774655&SiteID=1

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

I see this message from time to time in my replication thread, and shortly afterwards processes using the SDF file will start complaining that it has been corrupted.

Here is the stack trace that I receive:

28037 : A request to send data to the computer running IIS has failed. For more information, see HRESULT.

28581 : The message cannot be built. The Make Message failed.

at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()

at System.Data.SqlServerCe.SyncAsyncResult.BeginSyncAndStatusReporting()

at System.Data.SqlServerCe.SyncAsyncResult.SyncThread()

The only thing that I can think of that is unusual is that one of my tables has nearly 1,000,000 rows in it. I have no more than 16 columns in any table. My large table has only 7 columns.

What exactly does the message mean? Would this be causative of my sdf corruption problems, or symptomatic?

How can I determine what SP's have been installed so I can give you better information?

Thanks,

David Johnson

RDA push error

While trying to push a tracked table using RDA.push, I get the following error:

Error Code: 80004005

The message cannot be built. The make message failed.

Minor Err: 28581

Source: Microsoft SQL server 2005 Mobile Edition.

All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.

Any help to find the reason for this error will be greatly appreciated.

- Paul

As far as I remember there was a bug around when you have more than 128 columns. This got fixed later in the SP1.

Thanks,

Laxmi

|||

Thanks. Laxmi.

SQL server 2005 SP1 was indeed missing, and I installed it. But now the Pull fails, with same error code, and minor error 0. Still investigating what is happening.

Thanks,

Paul

|||

Hi,

The problem persists with SQL server 2005 SP1. Any other idea please?

- Paul

|||

Sorry I should have been more clear when I have said SP1. What I meant was SQL Server Mobile/Everywhere/Compact SP1 and not SQL Server SP1. Please visit the URL below to get you more details on SQL Server Mobile/Everywhere/Compact SP1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=774655&SiteID=1

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

I see this message from time to time in my replication thread, and shortly afterwards processes using the SDF file will start complaining that it has been corrupted.

Here is the stack trace that I receive:

28037 : A request to send data to the computer running IIS has failed. For more information, see HRESULT.

28581 : The message cannot be built. The Make Message failed.

at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()

at System.Data.SqlServerCe.SyncAsyncResult.BeginSyncAndStatusReporting()

at System.Data.SqlServerCe.SyncAsyncResult.SyncThread()

The only thing that I can think of that is unusual is that one of my tables has nearly 1,000,000 rows in it. I have no more than 16 columns in any table. My large table has only 7 columns.

What exactly does the message mean? Would this be causative of my sdf corruption problems, or symptomatic?

How can I determine what SP's have been installed so I can give you better information?

Thanks,

David Johnson

RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}

Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.

Fipil.

Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||

I'm not using Replication, but RDA. So, there are no a publication created.

|||Moving to CE forum.|||

I solved the problem.

The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).

|||

Hi,Fipil

I met the same problem with u.

Glad that you have slove the problem :-)

But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?

Thanks & Best Regards,

Hua wen gui

|||

Hi,

I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...

|||

So, you did not connect the DB with Login "test" in your PDA program, right?

My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().

meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.

It's a strange problem, any suggestion?

Thanks

|||

Yes, you are right. I changed original name of user by 'test' while writting post here.

Did you mean DB on server or db on PDA?

|||

The DB on server.

I'd like to pull data from DB Server to PDA with RDA.pull.

But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)

I can not find out the problem.

I'm not sure If the problem on IIS or SQL Server?

Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.

RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}

Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.

Fipil.

Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||

I'm not using Replication, but RDA. So, there are no a publication created.

|||Moving to CE forum.|||

I solved the problem.

The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).

|||

Hi,Fipil

I met the same problem with u.

Glad that you have slove the problem :-)

But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?

Thanks & Best Regards,

Hua wen gui

|||

Hi,

I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...

|||

So, you did not connect the DB with Login "test" in your PDA program, right?

My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().

meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.

It's a strange problem, any suggestion?

Thanks

|||

Yes, you are right. I changed original name of user by 'test' while writting post here.

Did you mean DB on server or db on PDA?

|||

The DB on server.

I'd like to pull data from DB Server to PDA with RDA.pull.

But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)

I can not find out the problem.

I'm not sure If the problem on IIS or SQL Server?

Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.

RDA pull exception

Dear All,

I created a sample test application to implement RDA, after I made all required configuration, I got the following exception after call rda.pull() :

{
Error # 1 of 1
Error Code: -2147024809
Message : An error has occurred on the computer running IIS. Try restarting the IIS server.
Minor sqlError.: 28022
Source : Microsoft SQL Server 2005 Mobile Edition}

Note: rda.submitQuery is executed successfully ..

any ideas

Thanks and regards

Hullo Ataha,

Now i facing the same pbm faced u, could u solve this error, then pls let me know, how to do it ?

RDA pull exception

Dear All,

I created a sample test application to implement RDA, after I made all required configuration, I got the following exception after call rda.pull() :

{
Error # 1 of 1
Error Code: -2147024809
Message : An error has occurred on the computer running IIS. Try restarting the IIS server.
Minor sqlError.: 28022
Source : Microsoft SQL Server 2005 Mobile Edition}

Note: rda.submitQuery is executed successfully ..

any ideas

Thanks and regards

Hullo Ataha,

Now i facing the same pbm faced u, could u solve this error, then pls let me know, how to do it ?

RDA pull error

When I try to do a pull I'm getting the following error message:

'Failure setting up a non parameterized query, possible incorrect SQL query.'

Now when I run this query in QA it works and returns me data. I'm also doing a pull on another table that works.

so I have this that works for one pull:

rda.Pull("Customers", 'select customerId, firstname, lastname, state, city from Customers", rdaConn, RdaTrackOption.TrackingOff);

and then I have this one that fails and gives me the above mentioned error:

rda.Pull("Sales", "select salesID, buyer, SaleDate from Sales', rdaConn, RdaTrackOption.TrackingOff);

and it fails.

another issue I'm having is, I can't run any rda.Pull() with RdaTrackOption.TrackingOn, any ideas on why I can't do that?

any help is greatly apprecated as for I've been going nuts on trying to figure this stuff out for the pass 2 weeks.

Anything to do with quotes? Your select starts with a double and ends in a single quote.|||

No, that was a typo when typing it in here. I should've just did a copy & paste in here.

RDA or Merge Replication?

Hi,
Please help me with the following scenario:
I want to implement an application using eMbedded Visual C++ 3.0 (and then
4.0) to capture data locally on my Windows CE device by SQL (so, I would
like to have an .sdf file on my device where I write data.) This data would
be written continuously for a certain amount of time or if there is a
connection detected to a server somewhere (with a central database) I would
like to send the data that is stored locally to that server. (wireless by
WiFi) So, I know I need Microsoft SQL Server 2000 Windows CE edition. First
of all, I realized I have to use OLE DB because ADO is not supported for CE
4.0+.. but that's ok. So, for my scenario, should I use RDA or Merge
Replication? Again, all I want is the application running on the device to
write data in the .sdf file and then upload it to a server (I don't need any
data from the server sent to the device.) It's only one-way... from the
device to
the server. The data is very simple... just a time stamp with some
statistics .. so a certain table in the server would just grow continuously
adding new rows with new data received from the device... It is possible
that I will have multiple devices that will connect to this server and write
their data as well. So, should I use RDA or Merge Replication? Note that
I would like to also be able to write the data straight to the server (this
would be
an option: e.g., the user could choose and say if the server is online, the
data
is updated straight in the server rather than first update the .sdf file and
then
update the server. but if this is not possible, please let me know.)
From what I read, in RDA you have to do a Pull to get a table, you modify it
and then
Push it back. Can I just do successive pushes (because that's all I'm really
interested in
unless I modify already existing data which is not needed in my case) or do
I have to go
through Pull then Push all the time? So, again.. should I use Merge
Replication or RDA?
Thank you in advance for all your help!
Hi, here's my 2c:
Maybe instead of using Push/Pull you may use SubmitSql that doesn't need a
Previous Pull. (It seems to me that your needs are too simple to set-up a
merge replication). HTH.
"vvf" <novvfspam@.hotmail.com> ha scritto nel messaggio
news:euFumhmZFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Please help me with the following scenario:
> I want to implement an application using eMbedded Visual C++ 3.0 (and then
> 4.0) to capture data locally on my Windows CE device by SQL (so, I would
> like to have an .sdf file on my device where I write data.) This data
> would
> be written continuously for a certain amount of time or if there is a
> connection detected to a server somewhere (with a central database) I
> would
> like to send the data that is stored locally to that server. (wireless by
> WiFi) So, I know I need Microsoft SQL Server 2000 Windows CE edition.
> First
> of all, I realized I have to use OLE DB because ADO is not supported for
> CE
> 4.0+.. but that's ok. So, for my scenario, should I use RDA or Merge
> Replication? Again, all I want is the application running on the device to
> write data in the .sdf file and then upload it to a server (I don't need
> any
> data from the server sent to the device.) It's only one-way... from the
> device to
> the server. The data is very simple... just a time stamp with some
> statistics .. so a certain table in the server would just grow
> continuously
> adding new rows with new data received from the device... It is possible
> that I will have multiple devices that will connect to this server and
> write
> their data as well. So, should I use RDA or Merge Replication? Note that
> I would like to also be able to write the data straight to the server
> (this
> would be
> an option: e.g., the user could choose and say if the server is online,
> the
> data
> is updated straight in the server rather than first update the .sdf file
> and
> then
> update the server. but if this is not possible, please let me know.)
> From what I read, in RDA you have to do a Pull to get a table, you modify
> it
> and then
> Push it back. Can I just do successive pushes (because that's all I'm
> really
> interested in
> unless I modify already existing data which is not needed in my case) or
> do
> I have to go
> through Pull then Push all the time? So, again.. should I use Merge
> Replication or RDA?
> Thank you in advance for all your help!
>
>

RDA or Merge Replication?

Hi,
Please help me with the following scenario:
I want to implement an application using eMbedded Visual C++ 3.0 (and then
4.0) to capture data locally on my Windows CE device by SQL (so, I would
like to have an .sdf file on my device where I write data.) This data would
be written continuously for a certain amount of time or if there is a
connection detected to a server somewhere (with a central database) I would
like to send the data that is stored locally to that server. (wireless by
WiFi) So, I know I need Microsoft SQL Server 2000 Windows CE edition. First
of all, I realized I have to use OLE DB because ADO is not supported for CE
4.0+.. but that's ok. So, for my scenario, should I use RDA or Merge
Replication? Again, all I want is the application running on the device to
write data in the .sdf file and then upload it to a server (I don't need any
data from the server sent to the device.) It's only one-way... from the
device to
the server. The data is very simple... just a time stamp with some
statistics .. so a certain table in the server would just grow continuously
adding new rows with new data received from the device... It is possible
that I will have multiple devices that will connect to this server and write
their data as well. So, should I use RDA or Merge Replication? Note that
I would like to also be able to write the data straight to the server (this
would be
an option: e.g., the user could choose and say if the server is online, the
data
is updated straight in the server rather than first update the .sdf file and
then
update the server. but if this is not possible, please let me know.)
From what I read, in RDA you have to do a Pull to get a table, you modify it
and then
Push it back. Can I just do successive pushes (because that's all I'm really
interested in
unless I modify already existing data which is not needed in my case) or do
I have to go
through Pull then Push all the time? So, again.. should I use Merge
Replication or RDA?
Thank you in advance for all your help!
Hi, here's my 2c:
Maybe instead of using Push/Pull you may use SubmitSql that doesn't need a
Previous Pull. (It seems to me that your needs are too simple to set-up a
merge replication). HTH.
"vvf" <novvfspam@.hotmail.com> ha scritto nel messaggio
news:euFumhmZFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Please help me with the following scenario:
> I want to implement an application using eMbedded Visual C++ 3.0 (and then
> 4.0) to capture data locally on my Windows CE device by SQL (so, I would
> like to have an .sdf file on my device where I write data.) This data
> would
> be written continuously for a certain amount of time or if there is a
> connection detected to a server somewhere (with a central database) I
> would
> like to send the data that is stored locally to that server. (wireless by
> WiFi) So, I know I need Microsoft SQL Server 2000 Windows CE edition.
> First
> of all, I realized I have to use OLE DB because ADO is not supported for
> CE
> 4.0+.. but that's ok. So, for my scenario, should I use RDA or Merge
> Replication? Again, all I want is the application running on the device to
> write data in the .sdf file and then upload it to a server (I don't need
> any
> data from the server sent to the device.) It's only one-way... from the
> device to
> the server. The data is very simple... just a time stamp with some
> statistics .. so a certain table in the server would just grow
> continuously
> adding new rows with new data received from the device... It is possible
> that I will have multiple devices that will connect to this server and
> write
> their data as well. So, should I use RDA or Merge Replication? Note that
> I would like to also be able to write the data straight to the server
> (this
> would be
> an option: e.g., the user could choose and say if the server is online,
> the
> data
> is updated straight in the server rather than first update the .sdf file
> and
> then
> update the server. but if this is not possible, please let me know.)
> From what I read, in RDA you have to do a Pull to get a table, you modify
> it
> and then
> Push it back. Can I just do successive pushes (because that's all I'm
> really
> interested in
> unless I modify already existing data which is not needed in my case) or
> do
> I have to go
> through Pull then Push all the time? So, again.. should I use Merge
> Replication or RDA?
> Thank you in advance for all your help!
>
>

RDA or Merge Replication?

Hi,
Please help me with the following scenario:
I want to implement an application using eMbedded Visual C++ 3.0 (and then
4.0) to capture data locally on my Windows CE device by SQL (so, I would
like to have an .sdf file on my device where I write data.) This data would
be written continuously for a certain amount of time or if there is a
connection detected to a server somewhere (with a central database) I would
like to send the data that is stored locally to that server. (wireless by
WiFi) So, I know I need Microsoft SQL Server 2000 Windows CE edition. First
of all, I realized I have to use OLE DB because ADO is not supported for CE
4.0+.. but that's ok. So, for my scenario, should I use RDA or Merge
Replication? Again, all I want is the application running on the device to
write data in the .sdf file and then upload it to a server (I don't need any
data from the server sent to the device.) It's only one-way... from the
device to
the server. The data is very simple... just a time stamp with some
statistics .. so a certain table in the server would just grow continuously
adding new rows with new data received from the device... It is possible
that I will have multiple devices that will connect to this server and write
their data as well. So, should I use RDA or Merge Replication? Note that
I would like to also be able to write the data straight to the server (this
would be
an option: e.g., the user could choose and say if the server is online, the
data
is updated straight in the server rather than first update the .sdf file and
then
update the server. but if this is not possible, please let me know.)
From what I read, in RDA you have to do a Pull to get a table, you modify it
and then
Push it back. Can I just do successive pushes (because that's all I'm really
interested in
unless I modify already existing data which is not needed in my case) or do
I have to go
through Pull then Push all the time? So, again.. should I use Merge
Replication or RDA?
Thank you in advance for all your help!Hi, here's my 2c:
Maybe instead of using Push/Pull you may use SubmitSql that doesn't need a
Previous Pull. (It seems to me that your needs are too simple to set-up a
merge replication). HTH.
"vvf" <novvfspam@.hotmail.com> ha scritto nel messaggio
news:euFumhmZFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Please help me with the following scenario:
> I want to implement an application using eMbedded Visual C++ 3.0 (and then
> 4.0) to capture data locally on my Windows CE device by SQL (so, I would
> like to have an .sdf file on my device where I write data.) This data
> would
> be written continuously for a certain amount of time or if there is a
> connection detected to a server somewhere (with a central database) I
> would
> like to send the data that is stored locally to that server. (wireless by
> WiFi) So, I know I need Microsoft SQL Server 2000 Windows CE edition.
> First
> of all, I realized I have to use OLE DB because ADO is not supported for
> CE
> 4.0+.. but that's ok. So, for my scenario, should I use RDA or Merge
> Replication? Again, all I want is the application running on the device to
> write data in the .sdf file and then upload it to a server (I don't need
> any
> data from the server sent to the device.) It's only one-way... from the
> device to
> the server. The data is very simple... just a time stamp with some
> statistics .. so a certain table in the server would just grow
> continuously
> adding new rows with new data received from the device... It is possible
> that I will have multiple devices that will connect to this server and
> write
> their data as well. So, should I use RDA or Merge Replication? Note that
> I would like to also be able to write the data straight to the server
> (this
> would be
> an option: e.g., the user could choose and say if the server is online,
> the
> data
> is updated straight in the server rather than first update the .sdf file
> and
> then
> update the server. but if this is not possible, please let me know.)
> From what I read, in RDA you have to do a Pull to get a table, you modify
> it
> and then
> Push it back. Can I just do successive pushes (because that's all I'm
> really
> interested in
> unless I modify already existing data which is not needed in my case) or
> do
> I have to go
> through Pull then Push all the time? So, again.. should I use Merge
> Replication or RDA?
> Thank you in advance for all your help!
>
>

rda localconnection

get the following erroer when trying to connect to local db on ppc both emulator and device

using vb 2005 to create a PPC 2003 appliction

Unknown connection option in connection string: provider.

the error occurs at the following code

rdaPress.LocalConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=\My Documents\newsprint.sdf"

any help would be appreciated

Thanks

GS

Grant - get rid of the Provider= clause. Something like this should suit your needs:

"Data Source='\my documents\newsprint.sdf';Password='';Max Database Size='128';Default Lock Escalation ='100';"

-Darren

rda localconnection

get the following erroer when trying to connect to local db on ppc both emulator and device

using vb 2005 to create a PPC 2003 appliction

Unknown connection option in connection string: provider.

the error occurs at the following code

rdaPress.LocalConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=\My Documents\newsprint.sdf"

any help would be appreciated

Thanks

GS

Grant - get rid of the Provider= clause. Something like this should suit your needs:

"Data Source='\my documents\newsprint.sdf';Password='';Max Database Size='128';Default Lock Escalation ='100';"

-Darren

RC1 oledb provider missing registry key

The RC1 installer (and the previous beta too) doesn't write in the following registry key:

[HKEY_CLASSES_ROOT\CLSID\{32CE2952-2585-49a6-AEFF-1732076C2945}\OLE DB Provider]
@.="Microsoft SQL Server CE 3.0 OLE DB Provider for Windows"

so this provider doesn't appear on the oledb "Data Link Properties" panels. Manually entering the above key solves the problem. The last summer CTP version has been done this trick.

Excellent - thanks for the info

Rapidly Changing Dimension

Hi All,
I'm trying to figure out how I would best model the following situation
:
I'm trying to model a retailing case which is fairly easy except for
one mind boggling thing (at least for me). I'm having a SKU dimension
of around 150.000 unique products which is already a SCD Type 2 for
some attributes. In addition I'm willing to track changes of the sales
and purchase price. However these prices change almost weekly for quite
a lot of these products leading to a huge dimensional table when using
type 2.
As this is a numerical attribute I'm thinking about putting it into the
fact table; However first of all my fact table will grow with a couple
of gigs (around 1 billion rows) and secondly as not every product is
sold every day I do not have the possibility to view price over a
period on a day to day basis.
A second option would be to have a separate fact table (and olap cube)
and making a linked measure for both prices. However I don't know how
to fetch the correct price in the basic cube when the price-cube does
not have the same granularity of the date dimension but more of a
start-end date structure.
Anyone some brilliant ideas? I ran out of mind juice on this one.
Hello DePuurt,
I would put the sales price in the fact table, and use type 1 to track
the current price in the product dimension. This should solve the day
to day price reporting and give you the changes of sales price over
time.
I also put a post together ages ago on different forms of type 2
implementation.
Check out:
http://bi-on-sql-server.blogspot.com...-changing.html
Hope it helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||A good idea, certainly valid.
However, I still have the issue on reporting the sales price over a
period of time. I want to be able to give a full price history of a
specific product over time; even if the thing didn't sell at all;
Unless I do you a complete full blown type 2 on the product dimension
I'm still not able to do this. An option would be to split the price of
the product dimension and have a subdimension tacking it. This tracking
dimension would have the natural key and the start/end date and both
prices. Creating a join with the original product dimension would yield
the exact information BUT now it's not in the cube if I go for OLAP.
Actually it all comes down to the desired functionality. Having a big
dimension table is the meast desired option in the main cube (sales
analysis), but is actually achievable when I accept the performance
drop. A second option yielding the same functionality would be to
create a second cube on prices. This means building one on the product
dimension with tracking dimension on price and this for the same date
granularity as the main cube. Using the lookup function the user
wouldn't notice it and performance would not be hindered when running
SQL reports. MDX is actually still quite fast, so I can take a small
hit (llokup) there. The last thing would be to go for your option, thus
limiting the possibilities for reporting price over time.
Thanks a lot,
DP.
|||Hi DePuurt,
150K rows in a dimension table is nothing to be worried about....on a
recent project we had a 20M row dimension table...LOL!
But you are seeing one of the problems with type 2 dimensions when they
change quickly.....one client of mine had 90M rows in his customer
dimension table linking to 6B rows in a summary fact table...obviously
every question was slowed down...
The answer is to maintain history for type 2 dimensions without
maintaining it in the type 2 dimension table...
We do this all the time for big clients.
Peter
www.peternolan.com