Edit a dataset

I want to query a set of tables in order to create a dataset that I can look
at (in a grid in my application) and decide manually whether I want to
include the records for further processing such as including in a report. I
thought to have a bit field which I can treat as a boolean value and then
check or uncheck it in my application. My tables do not contain the bit
field.
Is it possible to generate such a field in my query which can be edited
subseqently by the operator in the grid in the application? I have tried
this with a query, but am not allowed to edit a derived field.
Another thought was to create a temporary table to contain an ID and the bit
field. My original query would populate the temporary table with the ID and
my bit field would be set to 1 (true). After this I create my dataset by
linking my temporary table (via the ID) with my original main table. Then
there is no hindrance to my editing the bit field.
Is there a better way of doing this operation?
Thanks,
Steve.
> Is it possible to generate such a field in my query which can be edited
> subseqently by the operator in the grid in the application? I have tried
> this with a query, but am not allowed to edit a derived field.
I think you ought to be able to edit the derived value in your DataSet as
long as you don't try save the value to the database.

> Is there a better way of doing this operation?
One method is to add the selection option column to the DataTable after
loading the data. I think this sort of approach is better than returning
the GUI column from the SQL query. For example:
dataSet1.Tables["Table"].Columns.Add("Selected", typeof(bool));
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:566D47D9-F29D-46F5-80D8-C148A40D6394@.microsoft.com...
>I want to query a set of tables in order to create a dataset that I can
>look
> at (in a grid in my application) and decide manually whether I want to
> include the records for further processing such as including in a report.
> I
> thought to have a bit field which I can treat as a boolean value and then
> check or uncheck it in my application. My tables do not contain the bit
> field.
> Is it possible to generate such a field in my query which can be edited
> subseqently by the operator in the grid in the application? I have tried
> this with a query, but am not allowed to edit a derived field.
> Another thought was to create a temporary table to contain an ID and the
> bit
> field. My original query would populate the temporary table with the ID
> and
> my bit field would be set to 1 (true). After this I create my dataset by
> linking my temporary table (via the ID) with my original main table. Then
> there is no hindrance to my editing the bit field.
> Is there a better way of doing this operation?
> Thanks,
> Steve.
|||Dan - many thanks.
I'll give that a try.
Regards,
Steve.
"Dan Guzman" wrote:

> I think you ought to be able to edit the derived value in your DataSet as
> long as you don't try save the value to the database.
>
> One method is to add the selection option column to the DataTable after
> loading the data. I think this sort of approach is better than returning
> the GUI column from the SQL query. For example:
> dataSet1.Tables["Table"].Columns.Add("Selected", typeof(bool));
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:566D47D9-F29D-46F5-80D8-C148A40D6394@.microsoft.com...
>

Edit a dataset

I want to query a set of tables in order to create a dataset that I can look
at (in a grid in my application) and decide manually whether I want to
include the records for further processing such as including in a report. I
thought to have a bit field which I can treat as a boolean value and then
check or uncheck it in my application. My tables do not contain the bit
field.
Is it possible to generate such a field in my query which can be edited
subseqently by the operator in the grid in the application? I have tried
this with a query, but am not allowed to edit a derived field.
Another thought was to create a temporary table to contain an ID and the bit
field. My original query would populate the temporary table with the ID and
my bit field would be set to 1 (true). After this I create my dataset by
linking my temporary table (via the ID) with my original main table. Then
there is no hindrance to my editing the bit field.
Is there a better way of doing this operation?
Thanks,
Steve.> Is it possible to generate such a field in my query which can be edited
> subseqently by the operator in the grid in the application? I have tried
> this with a query, but am not allowed to edit a derived field.
I think you ought to be able to edit the derived value in your DataSet as
long as you don't try save the value to the database.
> Is there a better way of doing this operation?
One method is to add the selection option column to the DataTable after
loading the data. I think this sort of approach is better than returning
the GUI column from the SQL query. For example:
dataSet1.Tables["Table"].Columns.Add("Selected", typeof(bool));
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:566D47D9-F29D-46F5-80D8-C148A40D6394@.microsoft.com...
>I want to query a set of tables in order to create a dataset that I can
>look
> at (in a grid in my application) and decide manually whether I want to
> include the records for further processing such as including in a report.
> I
> thought to have a bit field which I can treat as a boolean value and then
> check or uncheck it in my application. My tables do not contain the bit
> field.
> Is it possible to generate such a field in my query which can be edited
> subseqently by the operator in the grid in the application? I have tried
> this with a query, but am not allowed to edit a derived field.
> Another thought was to create a temporary table to contain an ID and the
> bit
> field. My original query would populate the temporary table with the ID
> and
> my bit field would be set to 1 (true). After this I create my dataset by
> linking my temporary table (via the ID) with my original main table. Then
> there is no hindrance to my editing the bit field.
> Is there a better way of doing this operation?
> Thanks,
> Steve.|||Dan - many thanks.
I'll give that a try.
Regards,
Steve.
"Dan Guzman" wrote:
> > Is it possible to generate such a field in my query which can be edited
> > subseqently by the operator in the grid in the application? I have tried
> > this with a query, but am not allowed to edit a derived field.
> I think you ought to be able to edit the derived value in your DataSet as
> long as you don't try save the value to the database.
> > Is there a better way of doing this operation?
> One method is to add the selection option column to the DataTable after
> loading the data. I think this sort of approach is better than returning
> the GUI column from the SQL query. For example:
> dataSet1.Tables["Table"].Columns.Add("Selected", typeof(bool));
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:566D47D9-F29D-46F5-80D8-C148A40D6394@.microsoft.com...
> >I want to query a set of tables in order to create a dataset that I can
> >look
> > at (in a grid in my application) and decide manually whether I want to
> > include the records for further processing such as including in a report.
> > I
> > thought to have a bit field which I can treat as a boolean value and then
> > check or uncheck it in my application. My tables do not contain the bit
> > field.
> >
> > Is it possible to generate such a field in my query which can be edited
> > subseqently by the operator in the grid in the application? I have tried
> > this with a query, but am not allowed to edit a derived field.
> >
> > Another thought was to create a temporary table to contain an ID and the
> > bit
> > field. My original query would populate the temporary table with the ID
> > and
> > my bit field would be set to 1 (true). After this I create my dataset by
> > linking my temporary table (via the ID) with my original main table. Then
> > there is no hindrance to my editing the bit field.
> >
> > Is there a better way of doing this operation?
> >
> > Thanks,
> > Steve.
>

EDB on WM. Do Do sort Orders generate index tables?

Hi,

I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger?
In the MSDN documentation I can not find anything on this.

Thanks,
GiulioHi, does anybody know the answer?
I am stuck with this.

thanks,
giulio|||

Hi Giulio2000

Moving to Sql Server compact edition forum where it has got better chances of being answered.

-Thanks,

|||

Yes, indexs do get generated. You can create a sort order and then specify the sort order you want to use while opening the database. EDB supports up to 16 different sort orders.

Manish Agnihotri

Program Manager

SQL Compact

sql

Easy User Question

I have a user set up on a SQL 200 server, which I am using to connect to a
database from an ASP.NET application I am making.
When I run the application from the same system as the database is on, the
connection works correctly ( my connection string is:
user id=BlueMaster;data source=BLUE;initial
catalog=BlueSky;password=bluerocks
)
but when I try to connect when running the application off another system,
the connection fails. What do I have to do to set up the user so the
connection will work from remote systems'
ThanksCheck you are using SQL server and Windows authentication in SQL Server (the
defualt is Windows only)
Do this from Enterprise manager - right click the server name, go to
properties and the Security tab. If you do have to change the mode you will
be prompted to restart SQL Server - this is necessary for the security
setting to take effect. If this is not the problem, can you please post the
error message coming back
"Ryan" <web@.balancestudios.com> wrote in message
news:e5321K71DHA.1100@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a user set up on a SQL 200 server, which I am using to connect to a
> database from an ASP.NET application I am making.
> When I run the application from the same system as the database is on, the
> connection works correctly ( my connection string is:
> user id=BlueMaster;data source=BLUE;initial
> catalog=BlueSky;password=bluerocks
> )
> but when I try to connect when running the application off another system,
> the connection fails. What do I have to do to set up the user so the
> connection will work from remote systems'
> Thanks
>

---

|||What error message do you get when it fails?
Rand
This posting is provided "as is" with no warranties and confers no rights.

EA - Managment - Process Info

Dear All,
Within this screen I am getting the same person in the
same databases duplicated.
Is this
1. The fault of the application thats creating the
connection
2. SQL Server doing something
3. Something that I shouldn't be worried about.
Thanks
PeterHi,
First one will happen if you are not closing the connection made. This you
need to
really worry and need to rectify inside your code by closing the connection
as soon as the task is completed.
2. SQL Server will create mutiple threads, those you do not want to worry.
It will be cleared automatically
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
> Dear All,
> Within this screen I am getting the same person in the
> same databases duplicated.
> Is this
> 1. The fault of the application thats creating the
> connection
> 2. SQL Server doing something
> 3. Something that I shouldn't be worried about.
> Thanks
> Peter|||Thanks Hari
Peter

>--Original Message--
>Hi,
>First one will happen if you are not closing the
connection made. This you
>need to
>really worry and need to rectify inside your code by
closing the connection
>as soon as the task is completed.
>
>2. SQL Server will create mutiple threads, those you do
not want to worry.
>It will be cleared automatically
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
>
>.
>

DynamicSQL & the Index Tuning wizard.

Some facts first:
1. Large application running on SQL Server 2000.
2. Stored Procedures use a lot of dynamic SQL.
3. Performance is a major issue.
4. Focussing on the database:
4.1 Start up SQL Profiler.
4.2 Run the application for a period of time (normal customer usage) to
generate the workload file.
4.3 Run the ITW on this workload file.
Questions:
(a) What kind of output can I expect from the ITW since the t-sql code is
heavily using dynamic SQL?
(b) Will the ITW be able to properly analyze the workload file in this
scenario?
(c) Are there any other issues that I should be aware of when using the SQL
Profiler and ITW for dynamic SQL code analysis?
TIA
Cheers!
SQLCatZ
SQLCatz,
Yes, the ITW will work fine with dynamic SQL. You might also want to
look at the profiler trace yourself and run some queries against it. I
look for:
1) CPU intensive queries (CPU column)
2) IO intensive queries (reads, writes columns)
3) Long running queries (duration column)
If you get the top 10 culprits from each of those categories, you will
have eliminated 90% of your poor performing queries. You want to get the
biggest bangs for your buck, and not waste time on things that don't
really matter.
Use the ITW as a guide to making decisions about which indexes to apply,
in most cases you won't want to blindly implement what it suggests.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
SQLCatz wrote:
> Some facts first:
> 1. Large application running on SQL Server 2000.
> 2. Stored Procedures use a lot of dynamic SQL.
> 3. Performance is a major issue.
> 4. Focussing on the database:
> 4.1 Start up SQL Profiler.
> 4.2 Run the application for a period of time (normal customer usage) to
> generate the workload file.
> 4.3 Run the ITW on this workload file.
> Questions:
> (a) What kind of output can I expect from the ITW since the t-sql code is
> heavily using dynamic SQL?
> (b) Will the ITW be able to properly analyze the workload file in this
> scenario?
> (c) Are there any other issues that I should be aware of when using the SQL
> Profiler and ITW for dynamic SQL code analysis?
> TIA
> Cheers!
> SQLCatZ
>
sql

Dynamically formatting data using XSD

Hi,
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
Sameer
Hello Sameer,
I think you might be getting confused as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent
|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting confused as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concept
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directly
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent

Dynamically formatting data using XSD

Hi,
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
SameerHello Sameer,
I think you might be getting as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concep
t
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directl
y
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent

Dynamically edit DataSet

Hi,

I have a web application, where the user specifies the criteria for filtering data (with like, In, between... operators and data retrieved from the db). So, the selection criteria query (SQL) is generated. This is somthing similar to Query Wizard. It builds the SQL for WHERE clause only. This needs to be attached with the query (the developer specified during report-design, which is under dataset->commandtext in RDL file).

To clarify, do not want to use parameters. The idea is, the report design need not be modified each time any filters to be added or removed. The user can simply, add/remove the fields and corresponding sql statements for each field from the db (e.g. QueryWizard table).

The functionality to attach the where clause (from web page) to query (specified in reprot-design) is ready. I need to know, is there anyway I can retrieve the query from the RDL programmatically and set it back before the report is rendered (generated).

I heard something about QueryDefinition.Query, which returns the query, but not sure.

Anybody, any suggestion, will be a great help !!

Have you tried Report Builder? What you want sounds very close to what Report Builder is doing (RS 2005)|||

I have already explored the option of using the Report Builder. I do not want the end-user to design the report. The end-user should be able to specify the criteria (easy-steps thru Query Wizard) and view the report.

However, in case of Report Builder also, if I want to add new fields for selection criteria, the design of the report requires modification, which is what I dont want to do.

Is there any option? Cannt I edit the dataset programmatically ?

|||

Well, DataSet.Query.CommandText can be an expression.
You can try something like this: ="select * from mytable " + ReportParameters!Filter.Value

Dynamic where in stored procedure help

Hi all,

I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample,
where field1= coalesce(@.parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?

Thanks,

JamesYou're on the right track. You'll find a great article at http://www.sommarskog.se/dyn-search.html.|||it is better then dynamic sql

you can use isnull function also|||I think it depends.

My understanding is that with dynamic WHERE clauses, SQL Server does not necessarily always store the most efficient execution plan; in the long run, this can hurt the performance of your query, especially if you have a ton of such dymanic elements in the where clause.

That said, I hate maintaining dynamic sql code.

ou might run some tests and see what you get performance-wise.|||But, on the OTHER hand, rebuilding an execution plan each time may be better for the particular combination of parameters than using the one excution plan that was built the first time for the query. E.g., if there are 10 parameters and the first time through, you enter values for all 10. An execution plan is built. Then, the normal user searches with one parameter most of the time. The original execution plan won't be ideal for that query.

It all depends. I went to a conference where some very good examples of both methods were displayed, profiled, etc. Very enlightening. Test and test some more for your particular case.|||I made some performance tests one day concerning the issue:

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

Dynamic table and column list

Thanks in advance (even CELKO) DDL attached
I DID NOT create this application. I am trying to fix it. The application
recieves a Access database table once a month. This table contains employee
s
and multiple columns detailing begining and ending balances, pay amounts
etc... The tables contain UP TO 240 columns per table The time periods rol
l
forward so some columns drop off as others are added. 3 years 36 tables.
TERRRIBLE NASTY MESS!!
I created a couple of tables that allowed me to ennumerate all the tables
(manually) and all the columns in each table. I then grouped by column name
and created a table that will contain all the columns (total 388) so that I
can create one record for each employee.
I need to be able to insert/update the results table from each monthly table
without having to manually type each column list for the insert update.
I did read: http://www.sommarskog.se/dynamic_sql.html
But I'm thinking I may need to do something to loop through the
IMPORT_MASTER to run against each tables columns
CREATE TABLE [dbo].[IMPORT_MASTER] (
[DataFileID] [int] IDENTITY (1, 1) NOT NULL ,
[FileName] [varchar] (50) NULL ,
[DateRecieved] [datetime] NOT NULL ,
[DateProcessed] [datetime] NULL
) ON [PRIMARY]
GO
INSERT IMPORT_MASTER Values ('masterfile_011604','2004-01-16
00:00:00.000',NULL)
INSERT IMPORT_MASTER Values ('masterfile_022004','2004-02-20 00:00:00.000',
NULL)
INSERT IMPORT_MASTER Values ('masterfile_032004','2004-03-20 00:00:00.000',
NULL)
CREATE TABLE [dbo].[IMPORT_Process] (
[ColumnKeyID] [int] IDENTITY (1, 1) NOT NULL ,
[DataFileID] [int] NOT NULL ,
[ColumnName] [varchar] (50) NULL ,
[ColumnProcess] [varchar] (50) NULL
) ON [PRIMARY]
GO
INSERT IMPORT_PROCESS VALUES (1,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (1,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (1,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (1,'Col1',NULL)
INSERT IMPORT_PROCESS VALUES (1,'Col2',Null)
INSERT IMPORT_PROCESS VALUES (1,'Col3',NULL)
INSERT IMPORT_PROCESS VALUES (1,'MCol1',Null)
INSERT IMPORT_PROCESS VALUES (1,'MCol2',Null)
INSERT IMPORT_PROCESS VALUES (1,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (2,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (2,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col2',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col4',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col5',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol4',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol5',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (3,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (3,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col5',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col7',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col8',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol4',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol5',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol7',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol8',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol9',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol10',NULL)
CREATE TABLE [dbo].[IMPORT_TRANSACTION] (
[ImportTransID] [int] IDENTITY (1, 1) NOT NULL ,
[DataFileID] [int] NOT NULL ,
[EmployeeID] [INT] NOT NULL ,
[familyid] [varchar] (50) NULL ,
[firstname] [varchar] (50) NULL ,
[lastname] [varchar] (50) NULL ,
[autopay] [bit] NOT NULL ,
[autoselfpay] [bit] NOT NULL ,
[AutoStartART] [money] NULL ,
[Col1] [money] NULL ,
[Col2] [money] NULL ,
[Col3] [money] NULL ,
[Col4] [money] NULL ,
[Col5] [money] NULL ,
[Col6] [money] NULL ,
[Col7] [money] NULL ,
[Col8] [money] NULL ,
[Col9] [money] NULL ,
[MCol1] [money] NULL ,
[MCol2] [money] NULL ,
[MCol3] [money] NULL ,
[MCol4] [money] NULL ,
[MCol5] [money] NULL ,
[MCol6] [money] NULL ,
[MCol7] [money] NULL ,
[MCol8] [money] NULL ,
[MCol9] [money] NULL ,
[MCol10] [money] NULL
) ON [PRIMARY]
GOStvJston wrote:

> Thanks in advance (even CELKO) DDL attached
> I DID NOT create this application. I am trying to fix it. The applicatio
n
> recieves a Access database table once a month. This table contains employ
ees
> and multiple columns detailing begining and ending balances, pay amounts
> etc... The tables contain UP TO 240 columns per table The time periods r
oll
> forward so some columns drop off as others are added. 3 years 36 tables.
> TERRRIBLE NASTY MESS!!
> I created a couple of tables that allowed me to ennumerate all the tables
> (manually) and all the columns in each table. I then grouped by column na
me
> and created a table that will contain all the columns (total 388) so that
I
> can create one record for each employee.
> I need to be able to insert/update the results table from each monthly tab
le
> without having to manually type each column list for the insert update.
> I did read: http://www.sommarskog.se/dynamic_sql.html
>
You say you want to fix it but I don't understand what fixing it has to
do with what you are attempting here. Here's what I'd assume: 1) Create
a normalized data model in SQL. 2) Create a data loading process that
populates the new data model from the Access one.
For 2) the obvious solution is DTS, in which transformations can map
columns and tables dynamically to your new model. Even assuming you
wanted to implement that in TSQL only (using linked servers?) I don't
see how your metadata tables will help much. A more standard approach
would be to use staging tables that matched the source structure and
then transform those staging tables to the normalized ones using
INSERTs. Maybe you could explain a bit more about what you are trying
to achieve.

> But I'm thinking I may need to do something to loop through the
> IMPORT_MASTER to run against each tables columns
Why? But if so, use a WHILE loop.
David Portas
SQL Server MVP
--|||David,
There is now no way to balance the system from one month to another. This
entire application model is going away but I have to try and produce a
balance sheet for manual work that was done the last three years to finish
nailing the coffin.. Entries hand entered, adjustments to balances made
etc... I hadn't thought about using DTS though This only needs to be done
one time so I'm trying to do it as quick and dirty as I can while still bein
g
able to document what I'm doing so the process could be replicated if needed
.
"David Portas" wrote:

> StvJston wrote:
>
> You say you want to fix it but I don't understand what fixing it has to
> do with what you are attempting here. Here's what I'd assume: 1) Create
> a normalized data model in SQL. 2) Create a data loading process that
> populates the new data model from the Access one.
> For 2) the obvious solution is DTS, in which transformations can map
> columns and tables dynamically to your new model. Even assuming you
> wanted to implement that in TSQL only (using linked servers?) I don't
> see how your metadata tables will help much. A more standard approach
> would be to use staging tables that matched the source structure and
> then transform those staging tables to the normalized ones using
> INSERTs. Maybe you could explain a bit more about what you are trying
> to achieve.
>
> Why? But if so, use a WHILE loop.
> --
> David Portas
> SQL Server MVP
> --
>|||StvJston (StvJston@.discussions.microsoft.com) writes:
> I DID NOT create this application. I am trying to fix it. The
> application recieves a Access database table once a month. This table
> contains employees and multiple columns detailing begining and ending
> balances, pay amounts etc... The tables contain UP TO 240 columns per
> table The time periods roll forward so some columns drop off as others
> are added. 3 years 36 tables. TERRRIBLE NASTY MESS!!
> I created a couple of tables that allowed me to ennumerate all the
> tables (manually) and all the columns in each table. I then grouped by
> column name and created a table that will contain all the columns (total
> 388) so that I can create one record for each employee.
> I need to be able to insert/update the results table from each monthly
> table without having to manually type each column list for the insert
> update.
I had a look at your tables, and I read your narrative, but I had
difficulties to bring it together. What is what? Unless you are taking
the DTS path that David suggested, it would help with more details.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Solved this. Created a DTS package that completed the task. Thanks for the
pointers
"Erland Sommarskog" wrote:

> StvJston (StvJston@.discussions.microsoft.com) writes:
> I had a look at your tables, and I read your narrative, but I had
> difficulties to bring it together. What is what? Unless you are taking
> the DTS path that David suggested, it would help with more details.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Dynamic SQL Statement from ASP.net Session Object

I want to use Reporting Services in a web intranet application I'mdeveloping. In the application, I'm using forms authenticationagainst a SQL database that stores the username and "filter values"among other things. These are loaded as Session objects at login.
The "filter values" determine what data is retrieved, since users onlyhave rights to view specific data. Stored procedures run a querystatement to include "WHERE @.filterValue", and the .net code grabs theSession object's filtervalue, ie "Division = 'Admin'" as the sqlparameter. This works great on the other pages in the application.
But I have not a clue how to grab the filtervaluse Session object and plug it into Reporting Services..
Any help/direction will be tremendously appreciated! I work forthe local sheriff's office and am the "Lone .NET / Reporting Services"Ranger".
Thanks!
Lynnette
So no one has any ideas?

Dynamic SQL Queries!

An ASP application retrieves the DISTINCT records from all the columns
of a SQL Server DB table & populates them in drop-down lists. The no.
of drop-down lists on the web page depends upon the no. of columns in
the DB table. For e.g. if the DB table has, say, 5 columns, the web
page will show 5 drop-down lists.
Assume that the DB table stores information pertaining to books like
book name, category to which the book belongs to like sports, science,
music etc., author, publisher, publishing date etc.
Now suppose that a user selects an author named Author1 from the
drop-down list. When he does so, the page should get submitted & all
books that Author1 has penned should be displayed to the user. For e.g.
if Author1 has written 10 books, the user should be shown 10 records.
Now after Author1 has been selected & the appropriate records displayed
to the user, suppose the user selects an option from another drop-down
list like for e.g. the publisher drop-down list. An author can get his
books published by different publishers. When the user selects, say,
Publisher1, from the drop-down list, the user should now be displayed
all the records that Author1 has written BUT which have been published
by Publisher1 only. Now Author1 has written 10 books. Out of these 10
books, Publisher1 has published 4 books. So under such circumstances, 4
records should be retrieved & displayed to the user. The SQL query
would be
SELECT * FROM tblBooks WHERE Author='Author1' AND
Publisher='Publisher1'
The problem I am having is in adding the second WHERE clause i.e. 'AND
Publisher='Publisher1' in the SELECT query. Please note that all the
drop-down lists EXCEPT for the author drop-down list should change
again & contain only those records as options which are common to both
Author1 & Publisher1.
Arpan> The problem I am having is in adding the second WHERE clause i.e. 'AND
> Publisher='Publisher1' in the SELECT query.
So what is the problem? That query is syntactically valid so it isn't
obvious what your question is.
I would have expected separate tables for publisher and books joined by
a third table for the many-to-many relationship. Is it the join that
you have a problem with? For example:
SELECT ...
FROM tblBooks AS B
JOIN tblBookPublishers AS J
ON B.isbn = J.isbn
JOIN tblPublishers AS P
ON J.publisher_id = P.publisher_id
WHERE B.Author='Author1'
AND P.Publisher='Publisher1' ;
And by the way, books can have more than one author too, so author
probably shouldn't appear in the books table and you need at least two
more tables there.
If you need more help, please read this first:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I do understand that your are right in saying that the records should
have been in seperate tables but the fact is the database that my
clients have wasn't created by a database expert; so they have all the
records in one table only & don't wish to seperate the records in
different tables because of time constraint.
Had related records been in different tables, there wouldn't have been
any problem but since that isn't the case, my problems have increased.
Any further suggestions?|||> Any further suggestions?
What is your question?
Please also include a CREATE TABLE statement if your question is about
a query.
David Portas
SQL Server MVP
--

Dynamic SQL Issues with Functions/SP

I am working on a reporting application that uses dynamic SQL to generate
result sets. My client initially used a non-dynamic user-defined function
that returned a table, but when I made the SQL dynamic, it turns out that
EXEC statements cannot be used within a function. Is there a way to return a
table variable from a stored procedure if I convert the function into a
stored procedure? Are there any workarounds for this?
--
Larry Menzin
American Techsystems Corp.>> Is there a way to return a table variable from a stored procedure if I
Table variables cannot be used as parameters or return values, however you
can use regular resultsets for similar functionality. Also see some other
alternatives at:
http://www.sommarskog.se/share_data.html
Anith|||Why are you trying to make udf dynamic? Why not, in your dynamic SQL, simply
include a call to the function?
Thomas
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||You can return a result set from a Stored Procedure with a SELECT statement.
Here's a really simplified example that uses dynamic SQL:
CREATE PROCEDURE dbo.usp_test
AS
DECLARE @.MySQL AS NVARCHAR(4000)
SET @.MySQL = N'SELECT * FROM master.dbo.sysmessages'
EXEC dbo.sp_executesql @.MySQL
GO
EXEC dbo.usp_test
GO
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
> a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
No
Can you explain what are you trying to accomplish?
AMB
"Larry Menzin" wrote:

> I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||I am trying to join 4 result sets together, where each set is currently
generated as a table output variable from a UDF. UDF output is used directly
in JOIN statements in a stored procedure. If I convert UDFs to stored
procedures can I use stored procedure result sets in joins like UDF table
variables are used in joins, i.e.,
Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
ExceSP1(Parameters3) ON ...
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> No
> Can you explain what are you trying to accomplish?
>
> AMB
> "Larry Menzin" wrote:
>|||You can use VIEWs in the manner you're talking about. Another option is to
create Temporary Tables with SELECT INTO or INSERT INTO in your SP and JOIN
them.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0E155215-A119-4980-B1B9-7DFC110E22AF@.microsoft.com...
>I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used
> directly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||Larry,
No, you can not reference stored procedure from the "from" clause, but as
Anith pointed, there are other alternatives, like capturing the sp resultset
into a table (temporary or permanent) and use that table in subsequent
statements. Click the link posted by Anith Sen.
AMB
"Larry Menzin" wrote:
> I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used direct
ly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||> If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
No and I don't see a reason why you would need to do that. You should be abl
e to
do the following:
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
...
This assumes that all of the functions are table-valued functions. If there
is
commonality amongst the parameters in the functions, you could create a sing
le
stored procedure that returns the data like so:
Create Procedure ReportXYZ(parameter1 as ...)
As
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
Return
Notice that no dynamic SQL is needed for any of this. You would simply call
the
stored procedure which would return the raw data for you.
Thomas|||Here's one of I'm sure many articles on this subject:
http://www.databasejournal.com/feat...cle.php/3386661
jp
Larry Menzin wrote:
> Alejandro,
> Can I capture the result set from the SP into a table variable, i.e.,
> DECLARE @.CurrentPeriodDataPrelim TABLE(
> Product VarChar(40)
> ,Region varchar(40)
> ,Market varchar(40)
> ,Channel varchar(40)
> ,MRC float
> ,Net float
> ,NumerLabel nVarChar(150)
> )
>
> Declare @.strSQL nvarchar(2000)
> Set @.strSQL = N'
> INSERT INTO @.CurrentPeriodDataPrelim (Product, Region, Market, Channel, MR
C,
> NET, NumerLabel)
> SELECT cat as Product, ...
> Or do I have to use temp tables with the '#' designation if I don't want t
o
> use permanent tables. Also, don't I worry about temp tables going out of
> scope?
>
>