Easy Stuff

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??I bet that you'll get a lot better response if you post this question in the MS-Access (http://www.dbforums.com/f84) forum.

-PatP|||thnx Pat...|||That didn't hurt much, now did it ?!?!

Anywho, while I'm sure that somebody here could have answered your question, why bother to ask here when there are oodles of folks that are readily available that can answer you? Better still, they can offer lots of insight because they actually USE MS-Access as their tool of choice.

-PatP

Easy Start

Hello All, I am trying to get started with SQL programming and don't know
where to begin.
I would like to use Visual Studio.Net and access a "local database". For
example, something simple like a "member database" for a volunteer
organization.
My first concern is how and what to install so I can create tables and
update them. My next concern (will be) how I would distribute or install th
e
program along with whatever SQL engine is used by the end-user.
Is there any documentation available that describes this for someone
starting at the beginning (me) ?
TIA for any help you can provide.
STIPPYz.STIPPYz wrote:
> Hello All, I am trying to get started with SQL programming and don't know
> where to begin.
> I would like to use Visual Studio.Net and access a "local database". For
> example, something simple like a "member database" for a volunteer
> organization.
> My first concern is how and what to install so I can create tables and
> update them. My next concern (will be) how I would distribute or install
the
> program along with whatever SQL engine is used by the end-user.
> Is there any documentation available that describes this for someone
> starting at the beginning (me) ?
> TIA for any help you can provide.
> STIPPYz.
SQL Server Express may suit your requirements. It's available as a free
download and is free to distribute within the terms of the licence:
http://www.microsoft.com/sql/editio...ss/default.mspx
Lots more information is linked from the above page, including all the
technical documentation. If you've never developed databases before
however I suggest you invest in some books or courses.
David Portas
SQL Server MVP
--|||That sounds like exactly what I am looking for !
I have developed database applications using tables in Paradox format and
was programming in Borland C++ Builder, but I am trying to transition to
VS.net and keep stumbling over db issues. Thanks for the link, I'm going to
start working on it right away......... 8-)
STIPPYz
"David Portas" wrote:

> STIPPYz wrote:
> SQL Server Express may suit your requirements. It's available as a free
> download and is free to distribute within the terms of the licence:
> http://www.microsoft.com/sql/editio...ss/default.mspx
> Lots more information is linked from the above page, including all the
> technical documentation. If you've never developed databases before
> however I suggest you invest in some books or courses.
> --
> David Portas
> SQL Server MVP
> --
>

easy design tool

I have fairly simple table queries, and only a few dicey things, which I can handle in access. Is their a design tool for sql as easy and convenient as access?

Can someone suggest a good beginner's book on sql server 2005?

Use Access.

Explore how an Access Data Project works (it has a SQL Server backend).

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

sql

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

Dynamically populating an IN() clause within an SSIS package.

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.|||I think the simplest solution is to use a script task to read your file and create the list of users for an IN clause as you mention above. You'd put the list into a variable, and then build your query in an expression-based variable, and set your ole db source's data access mode to "sql command from variable".

Maybe the script would look something like this:

Code Snippet

Public Sub Main()
Dim UserNames() As String = System.IO.File.ReadAllLines(Dts.Variables("FileName").Value.ToString())
Dim s As New System.Text.StringBuilder
Dim IsFirst As Boolean = True
For Each UserName As String In UserNames
If Not IsFirst Then
s.Append(",")
End If
s.Append("'")
s.Append(UserName)
s.Append("'")
IsFirst = False
Next
Dts.Variables("InList").Value = s.ToString()
'Windows.Forms.MessageBox.Show("InList = " + Dts.Variables("InList").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

|||

2 more:

Have a for each loop to iterate through the file to get each value; then inside of the conatiner have the query logic using and equi-join (=). The thing is that you would run the query as many times as values in the file Have an script task to read the file and build the query; put it in a variable; then you can use that variable as the source uof your query in a Excute SQL task or OLE DB source component.|||

AdamSQLMan wrote:

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

There are many ways to accomplish this. My first thought is this:

Use a data flow to load the flat file, and output it to a data reader destination. Then use a script task to process the data reader into a comma delimited string, and use that in an expression to build your query for use in a second data flow.

|||

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.
|||

JayH wrote:

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.

I Agree.

|||

This would make a good interview question. How would you filter a resultset from a list of IDs in a text file.

Whatever the first answer is then ask what you would do if the tool suggested wasn't available. If the file was a lot bigger than you thought, if it might have invalid data etc.

dynamically exporting data to Access file

I am looking a way to export SQL Server 2005 DB tables to Access file dynamically. Like if i have added or removed any tables from SQL DB then when i run SSIS package it should export that table with data to Access file. is there any easy way to do this. if it is not them please someone please tell me which controls should i look at and which technique i should use to do this.

The only way I can think of is to generate packages programatically; as on each run you have to check what tables are available and whether they exists or not in the Access DB. There is section in BOL that talks about creating packages programatically in case you are interested.

I don't know what is your ultimate goal, but perhaps a DB backup may be what you need?

|||

Thanks Rafael, it is actully a process for extraction of data from SQL Server DB and then export that data to Access file. so the end result should be a package which get tables and data from SQL Server and push that information to Access file every time we run it. will you please give me more inforamtion about BOL and if you have got any good scource about it please point towords it. that would be really helpfull. or any other suggestion

Regards,

Haroon

|||

I google search gives: http://www.google.com/search?q=SSIS+package+programmatically&hl=en&pwst=1&start=10&sa=N

This is the topic in BOL:

http://msdn2.microsoft.com/en-us/library/ms136025(SQL.90).aspx

I Suggested to look into building packages programmatically given the fact that the number and name of tables in not known at run time. Other approach may be to take out the 'dinamically' part of your requirment and yous build one package/dataflow per each table you want to move.

Dynamically changing Web Service Task parameters at runtime?...

Okay, this one might stump you guys.

I am trying to access a web service that has parameters (three of them, all strings). But I want to access this service repeatedly, and drive the thing from a table.

I've got the Foreach Loop correctly getting variables from the recordset I populate from the table. That works and I can run a script with msgbox showing all the correct values being read from the table.

My problem is taking these variables and sticking them in the Name / Type / Value entries in the Input tab of the Web Service Task. There doesn't seem to be any mechanism for dynamically updating these at runtime, and if there isn't, that is a HUGE hole in the product. Who accesses Web Services with the same parameters every time?!?
No doubt you know this by now Lazamataz but this can't be done. you're right, it IS a hole in the product!

-Jamie|||

I'm trying to do this to...

Is there a way of accessing web service parameters through a script and the runtime / tasks?

|||

Absolutely, yes. Buy Donald Farmer's book (available at the usual places). That has a downloadable chapter that shows you how.

-Jamie

Dynamically changing Web Service Task parameters at runtime?...

Okay, this one might stump you guys.

I am trying to access a web service that has parameters (three of them, all strings). But I want to access this service repeatedly, and drive the thing from a table.

I've got the Foreach Loop correctly getting variables from the recordset I populate from the table. That works and I can run a script with msgbox showing all the correct values being read from the table.

My problem is taking these variables and sticking them in the Name / Type / Value entries in the Input tab of the Web Service Task. There doesn't seem to be any mechanism for dynamically updating these at runtime, and if there isn't, that is a HUGE hole in the product. Who accesses Web Services with the same parameters every time?!?
No doubt you know this by now Lazamataz but this can't be done. you're right, it IS a hole in the product!

-Jamie|||

I'm trying to do this to...

Is there a way of accessing web service parameters through a script and the runtime / tasks?

|||

Absolutely, yes. Buy Donald Farmer's book (available at the usual places). That has a downloadable chapter that shows you how.

-Jamie

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
>