How do I add a static column to a matrix?

Folks,
I'm getting data back from the database in the following format:
row_id | col_Name | col_Value
0 | ID | 10
0 | Name | First
0 | Jan | 1
0 | Feb | 2
1 | ID | 20
1 | Name | Seconds
1 | Jan | 10
1 | Feb | 20
...
We are grouping based on the row_id db column, thus creating dynamic columns
for ID, Name, Jan, Feb, etc.
However, I need to customize the ID and Name column which is, to my
understanding, not something that can be done if they are dynamic columns in
the matrix.
So, what I was thinking of doing was to make two static columns, ID and
Name, and then filter those rows out of the dynamic columns. However, when I
make a second ColumnGrouping in the RDL file for these, and define the two
static columns, what happens is that they appear set up such that these
columns would be in a separate row, rather than off to the left of the rows
of dynamic columns (hopefully I'm explaining this well enough).
What would be the best way to accomplish this, presuming that we can't
simply have the returned data changed to a better format?
Thanks!
randyAssuming you want to transform your table to:
ID | Name | Jan | Feb
10 | First | 1 | 2
20 | Seconds | 10 | 20
etc.
The easiest is to tweak your SQL, but I am assuming this is the result of a
SP that you can't since you said:
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
I'm not sure if you can, but you might have to do this with a custom code
module to transform your data and then bind the report to the dataset from
code. Maybe someone else knows way to modify the underlying dataset
structure.
"Randy Chapman" <RandyChapman@.discussions.microsoft.com> wrote in message
news:5F87A271-7591-4453-975F-06394CC8E2A8@.microsoft.com...
> Folks,
> I'm getting data back from the database in the following format:
> row_id | col_Name | col_Value
> 0 | ID | 10
> 0 | Name | First
> 0 | Jan | 1
> 0 | Feb | 2
> 1 | ID | 20
> 1 | Name | Seconds
> 1 | Jan | 10
> 1 | Feb | 20
> ...
> We are grouping based on the row_id db column, thus creating dynamic
> columns
> for ID, Name, Jan, Feb, etc.
> However, I need to customize the ID and Name column which is, to my
> understanding, not something that can be done if they are dynamic columns
> in
> the matrix.
> So, what I was thinking of doing was to make two static columns, ID and
> Name, and then filter those rows out of the dynamic columns. However,
> when I
> make a second ColumnGrouping in the RDL file for these, and define the two
> static columns, what happens is that they appear set up such that these
> columns would be in a separate row, rather than off to the left of the
> rows
> of dynamic columns (hopefully I'm explaining this well enough).
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
> Thanks!
> randy
>

HOW DO I ACCESS SQL SERVER TO USE NORTHWIND DB

I am attempting to access the SQL server in order to use the Northwind database.

Typing in via the command prompt the following:

osql –E -1 “EXEC sp_attach_db N’Northwind’,N’c:\SQL Server 2000 Sample Databases\northwind.mdf’”

The command prompt response:

[SQL Native Client] "An error has occurred while establishing a connection to the server when connecting to the SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connects."

Also, I loaded SQL Server Management Studio Express and not sure if I even need this. I click on the Icon but I’m not sure what I need to do to establish a logon and connect.

Any help will be appreciated.

Thanks!!

configure sql server express to

accept remote connection by

runing the sqlserver surface are configuration

Start>programs>sqlserver2005>configuration tools>surface area configuration

>configuration for service and connection>remote connection>local and REMOTE connection

|||

Make sure that you follow all of the instructions in the following KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277.

How do deduplication on Fact Table

Hi, everyone,

l've a fact table DEVICE with following structure,

DEVICE_NAME VARCHAR(50)
DEVICE_DATE DATETIME
DEVICE_NUMBER INT
Where DEVICE_NAME and DEVICE_DATE form a PRIMARY KEY

So l would like to import a text file with same information into this table.

My problem is, text file contains records which will violate my primary key constraint. In that case, l would only insert the record with DEVICE_NUMER not equal to ZERO and discard and log the others.

In case of the records violtae primary key constraints have DEVICE_NUMBER not equal to ZERO, discard both and log it.

So anyone has good suggestion on this?

In the Data Flow you can check for all of these situations. Start with a text file source.

To prevent insertion of record with zero device_number, use a Conditional Split transform. For anew output, write an expression to test for DEVICE_NUMBER == 0. Any rows that match will follow that output. Connect the following component to the default output, thus discarding those rows.

To prevent insertion of rows that already exist, use a Lookup. Set the Error Configuration, to Redirect errors. This means that when the lookup does not find a match, those "new" rows will flow to the error output, and "matched" rows will flow doen the default output. Connect the following component to the error output, again discarding the matched rows.

If you suspect you have new rows, but they can be duplicates within the file itself, use an Aggregate transform, which has the option to provide unique rows only.

how data file and log file grow?

Greetings,

The following shows how the Properties of a database look like:

Data Files:

File Name: student_dat
Location: e:\data\MSSQL\Data\student.mdf
Space allocated (MB): 62
'Automatically grow file' checked
File growth: 'By percent 10%' checked
Maximum file size: 'Unrestricted file growth' checked

Transaction Log:

File Name: student_log
Location: e:\data\MSSQL\Data\student.ldf
Space allocated (MB): 52
'Automatically grow file' checked
File growth: 'By percent 10%' checked
Maximum file size: 'Unrestricted file growth' checked

The physical files look like this:

Name Size Type Modified
---------------
student.ldf 52,416 KB Database File 2/11/2004 10:34PM
student.mdf 63,424 KB Database File 2/11/2004 10:34PM

My question is now that it has been specified 'Unrestricted file growth'
for both the data and the log file, why both haven't increased any in
size since 2/11/2004? Actually, the modified timestamps of some other
databases files are also '2/11/2004 10:34PM'. That's weird.

I found the following message that's relevent to the above timestamp in
the SQL Server Logs:

=====
2004-02-11 22:34:10.12 server SQL Server terminating because of
system shutdown.
=====

I'm pretty sure there have been a lot updates taking place on this
database. We don't hear any complaints from the customers that they
have had any problems (such as, no space left) with the databases.
Did the SQL server write the data and log somewhere else?

Any insight on what's going on would be appreciated.

BingBing Du (bdu@.iastate.edu) writes:
> The physical files look like this:
> Name Size Type Modified
> ---------------
> student.ldf 52,416 KB Database File 2/11/2004 10:34PM
> student.mdf 63,424 KB Database File 2/11/2004 10:34PM
> My question is now that it has been specified 'Unrestricted file growth'
> for both the data and the log file, why both haven't increased any in
> size since 2/11/2004? Actually, the modified timestamps of some other
> databases files are also '2/11/2004 10:34PM'. That's weird.

If the files are said to be modified in November this year, then there
is something fishy.

> I found the following message that's relevent to the above timestamp in
> the SQL Server Logs:
>=====
> 2004-02-11 22:34:10.12 server SQL Server terminating because of
> system shutdown.
>=====

But if the date is supposed to be in February, then it seems that files
has not been touched since. Either the files were detached, or the
databases has not been set to autoclose. Or the entire SQL Server instance
has not been running since then.

On my machine, about all my databases has a last modified date of
2004-03-01. I started SQL Server today, but I have not visited all
databases. But on startup, SQL Server performs recovery of all databases,
and thus writes to them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the instant response!

No, not November. The files were last modified on 2/11/2004. That's
Feb. 11, 2004. If the files were detached, the users would not be able
to access the databases, right? But we have not heard any complaints.
I don't think those databases haven't been touched since 2/11/2004.
These databases are in production. A lot updates are going on every
day. From my understanding, any updates (insert, update, delete) should
trigger the modified timestamp change on the files. Also, the SQL
server instance has been running fine since then. The only thing I'm
not sure is 'databases has not been set to autoclose' you mentioned.
Where can I find that setting?

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I've checked the sysdatabases table. The 'status' is 16 for all our
user databases. Per SQL Server Online Book, '1 = autoclose, set with
sp_dboption' and
'16 = torn page detection, set with sp_dboption'. Do I need to change
'status'?

Another thing was the SQL Server Agent was not set to start
automatically whenever the SQL Server instance is started. Our server
instance terminated and started on 2/11/2004. We started the SQL Server
Agent manually on 2/15/2004. Would this have done anything to make the
database files not been changed since 2/11/2004?

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I've checked the sysdatabases table. The 'status' is 16 for all our
user databases. Per SQL Server Online Book, '1 = autoclose, set with
sp_dboption' and
'16 = torn page detection, set with sp_dboption'. Do I need to change
'status'?

Another thing was the SQL Server Agent was not set to start
automatically whenever the SQL Server instance is started. Our server
instance terminated and started on 2/11/2004. We started the SQL Server
Agent manually on 2/15/2004. Would this have done anything to make the
database files not been changed since 2/11/2004?

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||comp.databases.ms-sqlserver (anonymous@.devdex.com) writes:
> No, not November. The files were last modified on 2/11/2004. That's
> Feb. 11, 2004.

Not in any date format I would use. :-)

> If the files were detached, the users would not be able to access the
> databases, right? But we have not heard any complaints. I don't think
> those databases haven't been touched since 2/11/2004. These databases
> are in production. A lot updates are going on every day. From my
> understanding, any updates (insert, update, delete) should trigger the
> modified timestamp change on the files. Also, the SQL server instance
> has been running fine since then. The only thing I'm not sure is
> 'databases has not been set to autoclose' you mentioned. Where can I
> find that setting?

Looks like I spoke too soon when I said that my files had a date that
agreed with my latest startup of SQL Server. I forgot that I rarely to
to bed before midnight, so the times I saw was from the last shutdown
as in your case. So I guess that everything's normal.

As for autogrow not setting in, one has to assume that the number of inserts
vs. the number deletes balances each other fairly well.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So are you saying the time actually shown for those MDF and LDF files
was the time the Server instance last terminated? Any updates (lke
INSERT, DELETE or UPDATE) won't get the timestamps changed?

Bing

Erland Sommarskog wrote:

> comp.databases.ms-sqlserver (anonymous@.devdex.com) writes:
>>No, not November. The files were last modified on 2/11/2004. That's
>>Feb. 11, 2004.
>
> Not in any date format I would use. :-)
>
>>If the files were detached, the users would not be able to access the
>>databases, right? But we have not heard any complaints. I don't think
>>those databases haven't been touched since 2/11/2004. These databases
>>are in production. A lot updates are going on every day. From my
>>understanding, any updates (insert, update, delete) should trigger the
>>modified timestamp change on the files. Also, the SQL server instance
>>has been running fine since then. The only thing I'm not sure is
>>'databases has not been set to autoclose' you mentioned. Where can I
>>find that setting?
>
> Looks like I spoke too soon when I said that my files had a date that
> agreed with my latest startup of SQL Server. I forgot that I rarely to
> to bed before midnight, so the times I saw was from the last shutdown
> as in your case. So I guess that everything's normal.
> As for autogrow not setting in, one has to assume that the number of inserts
> vs. the number deletes balances each other fairly well.|||Bing Du (bdu@.iastate.edu) writes:
> So are you saying the time actually shown for those MDF and LDF files
> was the time the Server instance last terminated? Any updates (lke
> INSERT, DELETE or UPDATE) won't get the timestamps changed?

It does not seem like that.

I would guess that if you set the database to autoclose, the date would
be updated each time the database was closed.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How could I get the records recusrively (nested)?

How could I get the records recusrively (nested)?
I've a table ACCT with columns as follows.
ACCT_CD varchar(20)
TYPE_CD varchar(1)
It has following data.
ACCT1 F
ACCT10 F
ACCT2 F
ACCT3 F
ACCT4 F
ACCT5 F
ACCT6 F
ACCT7 F
ACCT8 F
ACCT9 F
GRP_1 G
GRP_2 G
SGRP_1 C
SGROUP C
I've another table ACCT_REL with columns as follows.
PARENT_ACCT varchar(20)
CHILD_ACCT varchar(20)
REL_TYP varchar(1)
It has following data.
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGROUP'
SGROUP ACCT1 C
SGROUP ACCT2 C
SGROUP GRP_2 C
SGROUP SGRP_1 C
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_1'
GRP_1 ACCT3 G
GRP_1 ACCT4 G
GRP_1 ACCT5 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_2'
GRP_2 ACCT6 G
GRP_2 ACCT7 G
GRP_2 ACCT8 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGRP_1'
SGRP_1 GRP_1 C
SGRP_1 ACCT9 C
SGRP_1 ACCT10 C
I want retrive all the child_acct for PARENT_ACCT = 'SGROUP'. If the
CHILD_ACCT has some records in the ACCT_REL table, then I would like to
get them also. It could have many levels of nesting. How could I get the
records recusrively?
E.g. In the above example, the expected result could be:
ACCT1
ACCT10
ACCT2
ACCT3
ACCT4
ACCT5
ACCT6
ACCT7
ACCT8
ACCT9
GRP_1
GRP_2
SGRP_1
Thanks,
DJ
*** Sent via Developersdex http://www.examnotes.net ***check this out... (on behalf of ML :)
http://milambda.blogspot.com/2005/0...or-monkeys.html|||Also check out CTEs (Common Table Expressions) if you are using 2005.
There is a good example posted in Omni's blog here:
http://omnibuzz-sql.blogspot.com/20...vs.ht
ml
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9CB52622-90FB-4E32-9F92-CC3456633DDD@.microsoft.com...
> check this out... (on behalf of ML :)
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>|||Get a copy of TREES & HIERARCHJIES IN SQL for several methods of
modeling this kidn of data. You do not need recursive procedural code
in the Nesteed sets model and do this in one simple query.|||
Can you please tell me how to do this?
An example would be helpful.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||I beleive --CELKO-- is suggesting that you change your data model to store
the data in a tree format.
Omni's post contains one method for doing this.
CTEs on SQL Server 2005 allow you to do it with a single sql statement using
your current data model, but performance may not be as good.
If you do a search on "TREES & HIERARCHIES IN SQL", or just the phrases SQL
TREES HIERARCHIES, you will find several examples, including some articles
by Joe (--CELKO--).
"DJ" <dominic_koyappillil@.yahoo.com> wrote in message
news:uQqaGahiGHA.3572@.TK2MSFTNGP04.phx.gbl...
>
> Can you please tell me how to do this?
> An example would be helpful.
> Thanks.
> *** Sent via Developersdex http://www.examnotes.net ***

How could I do that (Query)

Hello all,

I'm using SS 2000

Based on the following query, how could I get only one row for each
different field "F1"? I don't want to use temp table.

----
SELECT ?
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
----

One of the possible answer could be:

F1 F2 F3
-- --- ----
B 3 11 /* One row for "B" */
A 4 10 /* One row for "A" */

TIA

YannickYour question is not clear. Please tell us the logic you want to follow. How
do you define the value of F2 and F3?

Shervin

"Yannick Turgeon" <nobody@.nowhere.com> wrote in message
news:N%%cb.13673$yD1.1527468@.news20.bellglobal.com ...
> Hello all,
> I'm using SS 2000
> Based on the following query, how could I get only one row for each
> different field "F1"? I don't want to use temp table.
> ----
> SELECT ?
> FROM (
> SELECT 'A' AS F1, 1 AS F2, 10 AS F3
> UNION ALL
> SELECT 'B', 2, 12
> UNION ALL
> SELECT 'B', 3, 11
> UNION ALL
> SELECT 'A', 4, 10) T
> ----
> One of the possible answer could be:
> F1 F2 F3
> -- --- ----
> B 3 11 /* One row for "B" */
> A 4 10 /* One row for "A" */
> TIA
> Yannick|||Do you mean taht this query represents some data in a table?

If so, and assuming that (f1,f2) is unique:

SELECT T.*
FROM
(SELECT f1, MAX(f2) AS f2
FROM T
GROUP BY f1) AS X
JOIN T
ON T.f1 = X.f1 AND T.f2 = X.f2

--
David Portas
----
Please reply only to the newsgroup
--|||Yannick Turgeon (nobody@.nowhere.com) writes:
> I'm using SS 2000
> Based on the following query, how could I get only one row for each
> different field "F1"? I don't want to use temp table.
> ----
> SELECT ?
> FROM (
> SELECT 'A' AS F1, 1 AS F2, 10 AS F3
> UNION ALL
> SELECT 'B', 2, 12
> UNION ALL
> SELECT 'B', 3, 11
> UNION ALL
> SELECT 'A', 4, 10) T
> ----

This could do it:

SELECT F1, MIN(F2), MIN(F3)
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
GROUP BY F1

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In my real situation, the SELECT ... UNION clause is replaced by a SELECT sub
query which return rows without unique key. Two rows could be exactly
the same. It does not matter which row is returned for a F1 but I want one
and only one row for each F1, and F2 and F3 must be from the same row.
This exclude "SELECT F1, MIN(F2), MIN(F3) ..."

I hope is clearer. Thanks for your help.

Yannick

Le Fri, 26 Sep 2003 14:17:09 -0700, Shervin Shapourian a crit*:

> Your question is not clear. Please tell us the logic you want to follow. How
> do you define the value of F2 and F3?
> Shervin|||Davis,

I haven't been clear enough. T is a subquery which return rows without
unique key. Two rows could be exactly the same.

Thanks for your time.

Yannick

Le Fri, 26 Sep 2003 22:22:33 +0100, David Portas a crit*:

> Do you mean taht this query represents some data in a table?
> If so, and assuming that (f1,f2) is unique:
> SELECT T.*
> FROM
> (SELECT f1, MAX(f2) AS f2
> FROM T
> GROUP BY f1) AS X
> JOIN T
> ON T.f1 = X.f1 AND T.f2 = X.f2
> --
> David Portas
> ----
> Please reply only to the newsgroup|||> I haven't been clear enough. T is a subquery

Then post the actual subquery, the DDL for the base tables and some sample
data as INSERT statements. Without that it's difficult to give a full
answer.

--
David Portas
----
Please reply only to the newsgroup
--|||OK, assuming F2 and F3 are integer values less than 10,000,000,000 this qury
returns what you want. SF2 and SF3 columns of the result set are string
fields, you can convert them back to integer values if you want.

select f1,
left(max(convert(char(10),f2) + convert(char(10),f3)), 10) as SF2,
right(max(convert(char(10),f2) + convert(char(10),f3)), 10) as SF3
from YourSubQuery
group by f1

Shervin

"Yannick Turgeon" <nobody@.nowhere.com> wrote in message
news:pan.2003.09.27.14.26.06.827858@.nowhere.com...
> In my real situation, the SELECT ... UNION clause is replaced by a SELECT
sub
> query which return rows without unique key. Two rows could be exactly
> the same. It does not matter which row is returned for a F1 but I want one
> and only one row for each F1, and F2 and F3 must be from the same row.
> This exclude "SELECT F1, MIN(F2), MIN(F3) ..."
> I hope is clearer. Thanks for your help.
> Yannick
>
> Le Fri, 26 Sep 2003 14:17:09 -0700, Shervin Shapourian a crit :
> > Your question is not clear. Please tell us the logic you want to follow.
How
> > do you define the value of F2 and F3?
> > Shervin

How come Im getting error saying transaction log Full?

Hello,

I have a process that failed with the following error message. The SQL server error messages also said the Transaction log was full and there was not enough disc space.

Strangely, when I checked all the drives on the server, there was plenty of free space, the smallest amount free on one drive, where SQL server is located was 20GB. I am confused.

I backed up the DB to another server and deleted a lot of the transaction logs and now the drive has 30GB and is okay.

Does anyone know how the transaction log fills up? Can I change a setting somewhere to increase the maximum size of the transaction log? Or maybe change the location where it is saved to another drive? Or have it automatically cleared out every once in a while?

Any of your input is greatly appreciated.

Thanks.

Rodney

-----------------------
DS-DBMS-E400: UDA driver reported the following on connection 'Data Target
(ODBC)':
DMS-E-DBPARSER, The underlying database detected an error during processing of
the SQL request.
[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database
'ancosalesdm' is full. Back up the transaction log for the database to free up
some log space.
(for details, see Build_SAL_FA_ShipSKU_0584.log)
[PROGRESS - 00:32:42] Build Node 68 'SAL_FA_ShipSKU'; failed
DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74':
DMS-E-GENERAL, A general exception has occurred during operation 'execute
immediate'.
The log file for database 'ancosalesdm' is full. Back up the transaction log
for the database to free up some log space.
DMS-E-GENERAL, A general exception has occurred during operation 'execute
immediate'.
General SQL Server error: Check messages from the SQL Server.
DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74':
DMS-E-GENERAL, A general exception has occurred during operation 'rollback
transaction'.
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
DMS-E-GENERAL, A general exception has occurred during operation 'rollback
transaction'.
General SQL Server error: Check messages from the SQL Server.
-----------------------In Enterprise Manager, right click the DB and go to Properties. From there you can check to see if your Transaction Log has a size limit, and can adjust it if necessary.|||If you are not doing transaction log backups, you need to set the recovery mode on this database to Simple. This will make sure it doesn't just keep growing until it fills up.|||all this is good, but if a transaction affects a really large amount of data the recovery mode is irrelevant. the log will continue growing (if the setting to allow growth is on) until it reaches the limit specified or the limit of the disk.|||That's true, and if this is the case, you will need to break the transaction into smaller pieces. You can also insert CHECKPOINT at various places in your transaction if you need to, but this would be a pretty rare case.sql

How cany I publish 1 table from an attached database?

Hello,

In my vb.net application, I have an attached database called dbMaster with the following tables:

tblCustomer, tblProducts, tblUsers

In visual studio 2005, is it possible to publish only one table? My problem is, is that I want to send my end users updated data from my tblCustomer, and am finding that on my test laptop, if I publish dbMaster, it sends all tables when I just want one to be sent.

Thanks in advance.

Are you taking about setting up a Replication Publication? SQL Express doesn't support Replication Publications.

Mike

|||

Yes, I was thinking along those lines, but could not find any support for it... ok thanks.

|||

Replication will allow you to specify specific tables, refered to as Articles, as part of the Publication. You'll just need to look at one of the other Editions of SQL Server to be the Publisher. I typically recommend SQL Workgroup Edition for small scale systems.

Mike

|||I'm working on a mobile application. I want to have a desktop database that is kept in sync with a handheld database, with updates occurring on both ends. To keep them synchronized, I am investigating the use of both RDA and Replication. I've gotten RDA to work, but have found a major drawback: Every time you sync, you have to basically pull down a new version of ALL the data, not just the changes.

So I started to look into using Replication. If I understand correctly, using Replication, you can do synchronization where only the changes have to be exchanged between the handheld device and the desktop computer. But as I just found out from the above post, you cannot create a Publication using SQLExpress.

Why do I want to use SQLExpress? Because I need a version of SQL Server that I can distribute for free with my application. Am I wrong, or is SQLExpress that only one that has a free redistributable?

Taking a step back, am I missing something? Is there a better (simpler?) alternative to RDA and Replication?

Thanks for any advice!

Tom

How can wrap a case statement around us

I would like to maybe wrap a case statement around the following so that I
can set any results that come back as NULL to 0 (the performance of updating
afterwards is horrible)
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
STAGE_PHX_FACT_POLICY.fire_fee_written =
whdata1.dbo.premium_detail.billed_premium
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Thanks you!>> ...around the following so that I can set any results that come back as
Lookup COALESCE or ISNULL function in SQL Server Books Online
Anith|||"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:3B52A763-5C4B-4030-ACDF-7661406D5149@.microsoft.com...
>I would like to maybe wrap a case statement around the following so that I
> can set any results that come back as NULL to 0 (the performance of
> updating
> afterwards is horrible)
>
> update STAGE_PHX_FACT_POLICY
> set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
> STAGE_PHX_FACT_POLICY.fire_fee_written =
> whdata1.dbo.premium_detail.billed_premium
> from whdata1.dbo.premium_detail
> inner join whdata1.dbo.lob_xref
> on whdata1.dbo.premium_detail.premium_lob =
> whdata1.dbo.lob_xref.premium_lob
> where whdata1.dbo.lob_xref.a_lob = '6'
> and whdata1.dbo.premium_detail.policy_number =
> STAGE_PHX_FACT_POLICY.policy_number
> and whdata1.dbo.premium_detail.policy_date_time =
> STAGE_PHX_FACT_POLICY.policy_date_time
You can use COALESCE or ISNULL to do this in a much simpler way. As you
didn't specify what needed updating, I'll take a guess that it's both
values:
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if =
COALESCE(whdata1.dbo.premium_detail.fee,0) ,
STAGE_PHX_FACT_POLICY.fire_fee_written =
COALESCE(whdata1.dbo.premium_detail.billed_premium,0)
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Dan

How can this be ordered/grouped?

I have the following four columns:
1 3 ok 1
1 2 ok 1
1 1 server error 3
2 3 ok 1
2 2 ok 1
2 1 ok 1
9 3 ok 1
9 2 ok 1
9 1 not found 3
13 3 ok 1
13 2 ok 1
13 1 ok 1
Their data types are int, int, string, int. Call the columns
groupid, ordinalid, status, quantstatus.
I want the above to sort this way:
9 3 ok 1
9 2 ok 1
9 1 not found 3
1 3 ok 1
1 2 ok 1
1 1 server error 3
13 3 ok 1
13 2 ok 1
13 1 ok 1
2 3 ok 1
2 2 ok 1
2 1 ok 1
Notice those with a 3 in quantstatus appear first. They are ordered by:
groupid, ordinalid desc
Any suggestions?
Thanks,
BrettTry,
select
*
from
t
order by
case when left(groupid, 1) = '9' then 0 else 1 end asc,
ltrim(groupid) asc,
ordinalid desc
AMB
"Brett" wrote:

> I have the following four columns:
> 1 3 ok 1
> 1 2 ok 1
> 1 1 server error 3
> 2 3 ok 1
> 2 2 ok 1
> 2 1 ok 1
> 9 3 ok 1
> 9 2 ok 1
> 9 1 not found 3
> 13 3 ok 1
> 13 2 ok 1
> 13 1 ok 1
> Their data types are int, int, string, int. Call the columns
> groupid, ordinalid, status, quantstatus.
> I want the above to sort this way:
> 9 3 ok 1
> 9 2 ok 1
> 9 1 not found 3
> 1 3 ok 1
> 1 2 ok 1
> 1 1 server error 3
> 13 3 ok 1
> 13 2 ok 1
> 13 1 ok 1
> 2 3 ok 1
> 2 2 ok 1
> 2 1 ok 1
> Notice those with a 3 in quantstatus appear first. They are ordered by:
> groupid, ordinalid desc
> Any suggestions?
> Thanks,
> Brett
>
>|||That starts off OK. The first four groups are fine. But when a larger set
a data is considered, the query breaks down. Notice where group 19 and 20
are. They're column four has a 3. They should be where 13 and 14 are. How
can this be fixed?
9 3 ok 1
9 2 ok 1
9 1 na 3
1 3 ok 1
1 2 ok 1
1 1 na 3
10 3 ok 1
10 2 ok 1
10 1 na 3
11 3 ok 1
11 2 ok 1
11 1 na 3
13 3 ok 1
13 2 ok 1
13 1 ok 1
14 3 ok 1
14 2 ok 1
14 1 ok 1
17 3 ok 1
17 2 ok 1
17 1 ok 1
18 3 ok 1
18 2 ok 1
18 1 ok 1
19 3 ok 1
19 2 ok 1
19 1 na 3
2 3 ok 1
2 2 ok 1
2 1 ok 1
20 3 ok 1
20 2 ok 1
20 1 na 3
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9C230AE9-FCFB-4EFE-A559-C99868B2BE66@.microsoft.com...
> Try,
> select
> *
> from
> t
> order by
> case when left(groupid, 1) = '9' then 0 else 1 end asc,
> ltrim(groupid) asc,
> ordinalid desc
>
> AMB
> "Brett" wrote:
>|||Brett,
Try
select
groupid, ordinalid, status, quantstatus
from T as T1
order by
case where exists (
select * from T as T2
where T2.groupid = T1.groupid
and T2.quantstatus = 3
) then 0 else 1 end,
groupid, ordinalid desc
Steve Kass
Drew University
Brett wrote:

>I have the following four columns:
>1 3 ok 1
>1 2 ok 1
>1 1 server error 3
>2 3 ok 1
>2 2 ok 1
>2 1 ok 1
>9 3 ok 1
>9 2 ok 1
>9 1 not found 3
>13 3 ok 1
>13 2 ok 1
>13 1 ok 1
>Their data types are int, int, string, int. Call the columns
>groupid, ordinalid, status, quantstatus.
>I want the above to sort this way:
>9 3 ok 1
>9 2 ok 1
>9 1 not found 3
>1 3 ok 1
>1 2 ok 1
>1 1 server error 3
>13 3 ok 1
>13 2 ok 1
>13 1 ok 1
>2 3 ok 1
>2 2 ok 1
>2 1 ok 1
>Notice those with a 3 in quantstatus appear first. They are ordered by:
>groupid, ordinalid desc
>Any suggestions?
>Thanks,
>Brett
>
>
>|||The CASE statement isn't fully constructed.
CASE input_expression
WHEN when_expression THEN result_expression
END
You need an expression before and after the WHEN part. Right now you only
have one expression.
Thanks,
Brett
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23skWdcACFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Brett,
> Try
> select
> groupid, ordinalid, status, quantstatus
> from T as T1
> order by
> case where exists (
> select * from T as T2
> where T2.groupid = T1.groupid
> and T2.quantstatus = 3
> ) then 0 else 1 end,
> groupid, ordinalid desc
> Steve Kass
> Drew University
> Brett wrote:
>|||>> You need an expression before and after the WHEN part. Right now you
Not quite. Apart from Steve's typo with using "where" instead of WHEN, the
case expression is valid. You may want to check out SQL Server Books Online
for searched case, under the topic CASE.
ORDER BY CASE WHEN EXISTS (
SELECT * FROM T AS T2
WHERE T2.groupid = T1.groupid
AND T2.quantstatus = 3
) THEN 0 ELSE 1 END, groupid DESC, ordinalid DESC ;
Anith|||Sorry, didn't catch that. It works Perfect!
What exactly does this part do:
THEN 0 ELSE 1 END
Alejandro used it also.
Thanks,
Brett
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OlxPpABCFHA.1392@.tk2msftngp13.phx.gbl...
> Not quite. Apart from Steve's typo with using "where" instead of WHEN, the
> case expression is valid. You may want to check out SQL Server Books
> Online for searched case, under the topic CASE.
> ORDER BY CASE WHEN EXISTS (
> SELECT * FROM T AS T2
> WHERE T2.groupid = T1.groupid
> AND T2.quantstatus = 3
> ) THEN 0 ELSE 1 END, groupid DESC, ordinalid DESC ;
> --
> Anith
>|||Thanks for the catch, Anith!
SK
Anith Sen wrote:

>Not quite. Apart from Steve's typo with using "where" instead of WHEN, the
>case expression is valid. You may want to check out SQL Server Books Online
>for searched case, under the topic CASE.
>ORDER BY CASE WHEN EXISTS (
> SELECT * FROM T AS T2
> WHERE T2.groupid = T1.groupid
> AND T2.quantstatus = 3
> ) THEN 0 ELSE 1 END, groupid DESC, ordinalid DESC ;
>
>|||On Mon, 31 Jan 2005 22:52:42 -0500, Brett wrote:

>What exactly does this part do:
>THEN 0 ELSE 1 END
Hi Brett,
It's part of the CASE expression. If the WHEN clause (containing the
subquery with exists) evaluates to true, the CASE expression will evaluate
to 0 (the THEN value); otherwise, it'll return 1 (the ELSE part). This
value (0 or 1) won't be in the result set, but it's the first value in the
ORDER BY clause. So after ordering, all rows for which the EXISTS subquery
is true will be first (sorted on the value 0); all others last (sorted on
the value 1).
Second and third argument to the ORDER BY clause are responsible for
fixing the order within these two sub-groups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How can I write this SQL statement when the string value is a variable

Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, MattJust declare @.StockSymbol as nchar or nvarchar and you can simply use

SET @.AlertSymbol = @.StockSymbol

The N in N'foo' tells SQL Server that 'foo' is in unicode. The same applies to all n<bar> datatypes.|||Originally posted by mt404
Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, Matt

Nchar/varchar has higher precedence than char/varchar. Hence, an implicit conversion should take care of this for you.

e.g.

declare @.AlertSymbol nvarchar(10),
@.StockSymbol varchar(10)

set @.StockSymbol='MSFT'

set @.AlertSymbol=@.StockSymbol

--sql2k
select sql_variant_property(@.AlertSymbol,'BaseType'), @.AlertSymbol|||Thaks to both of you for helping me out and teaching me what the N'foo' actually meant.

How can I use full memory in x64 OS with 32bit SQL

I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
use sp_configure:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'max server memory (MB)', 14336
go
reconfigure with override
go
Stop and start SQL Server. I chose the max memory of 14GB, since you should
leave 2GB for the OS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.|||"Tom Moreau" wrote:
> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?|||Just run my code and restart SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:8F14CDBE-1D38-4DBD-AE07-2B760EE0901A@.microsoft.com...
"Tom Moreau" wrote:
> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you
> should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:

I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:

i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim

How can I use arrays in parameters.

Following code is just select category 1 but for example What can I do if I
want to select 1 and 2?
declare @.CategoryID as int
set @.CategoryID = 1
SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)Hi
You would need to use dynamic SQL to have multiple values in a IN clause
when using a variable number of values.
Regards
Mike
"Murat BUDAK" wrote:

> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>
>|||http://www.sommarskog.se/arrays-in-sql.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Murat BUDAK" <mbudak@.islem.com> wrote in message
news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:96082D73-8AD1-4597-A0E6-55D9F16E4386@.microsoft.com...
> You would need to use dynamic SQL to have multiple values in a IN clause
> when using a variable number of values.
Mike,
You should read Erland's article that I posted the link to. You do not need
dynamic SQL for this.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Impresive. Thanks for this article :o)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Og0LJquAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Murat BUDAK" <mbudak@.islem.com> wrote in message
> news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> I
>

How can i use a for loop in a query ?

hi all i have the following stored procedure.

declare @.AreaID int

select @.AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @.AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

Are you asking how to iterate over the results returned from your stored procedure?

depending on how you retrieve you results either from a data reader or you used a data adapter and filled it to a dataset

//if filled to a dataset you can do thisif (MyDataSet.Tables.Count != 0){foreach (DataRow rowin MyDataSet.Tables[0].Rows) {//now you can access each row returned }}//if using a data readerwhile (DataReader.Read()){//hear you can access each row }

|||

Hi,

if i lets say hardcode the areaid to lets say 1 i get a set of results. i want to next get the results for areaid 2 so as if i want to runt the stored procedure again. But can i lets say have some sort of loop which will pass the parameter (areaid ) into the stored procedure and get the results in c# ?

that would be i think a bit too long winded ... is that right ?

|||

Sorry, but I do not understand what you want.

|||

What I understood from the problem is that you want to execute the same query for around 300 AREAID.
If I am correct following query will solve your problem:

SELECT table1.Job_AreaId, DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId
where table1.Job_AreaId in
(
SELECT the list of distinct AreaId for ex: select AreaId from MasterTable
)
Group by table1.Job_AreaId

Hope this helps you!!!

|||

Hi ,

Thanks for the response . It sure did help much appreciated

robby