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...
>
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
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.
>
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
sqlEasy way to view DB
Hi,
I have several DB with 80+ tables in each. I would like to find out what is
the best method to learn and view the relationships of these tables. I made
a SQL diagram of all the tables but since there are so many tables, it is
not easy to view the 'big picture'. Does anyone have any suggestions or may
be a tool that shows DBs in a better view?
Thank you.Greg has a nice product that would serve you well here.
http://www.ag-software.com/ags_scribe_index.aspx
--
-oj
RAC & QALite!
http://www.rac4sql.net
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:%23doHPiayDHA.3772@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have several DB with 80+ tables in each. I would like to find out what
is
> the best method to learn and view the relationships of these tables. I
made
> a SQL diagram of all the tables but since there are so many tables, it is
> not easy to view the 'big picture'. Does anyone have any suggestions or
may
> be a tool that shows DBs in a better view?
> Thank you.
>
I have several DB with 80+ tables in each. I would like to find out what is
the best method to learn and view the relationships of these tables. I made
a SQL diagram of all the tables but since there are so many tables, it is
not easy to view the 'big picture'. Does anyone have any suggestions or may
be a tool that shows DBs in a better view?
Thank you.Greg has a nice product that would serve you well here.
http://www.ag-software.com/ags_scribe_index.aspx
--
-oj
RAC & QALite!
http://www.rac4sql.net
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:%23doHPiayDHA.3772@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have several DB with 80+ tables in each. I would like to find out what
is
> the best method to learn and view the relationships of these tables. I
made
> a SQL diagram of all the tables but since there are so many tables, it is
> not easy to view the 'big picture'. Does anyone have any suggestions or
may
> be a tool that shows DBs in a better view?
> Thank you.
>
easy way to list tables & columns?
I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
easy way to list tables & columns?
I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
Bob
Step-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */
|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
Bob
Step-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */
|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
easy way to list tables & columns?
I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software
Tuesday, March 27, 2012
Easy stored procedure
Hi,
I have two tables...
Employees Departments
| EmployeeID | DeptID
| Name | Name
| Department |
|______________ | _____________
I would like to know how could i make an insert procedure on employees, inse
rting in the employeeid the next numeric value (in the table), and take in c
ount that the name of the employee must dont exists ...
Thanks and Regards.
Any Help will be grateful, urls, articles, anything...
Josema.See my reply in .programming
Vishal Parkar
vgparkar@.yahoo.co.in
I have two tables...
Employees Departments
| EmployeeID | DeptID
| Name | Name
| Department |
|______________ | _____________
I would like to know how could i make an insert procedure on employees, inse
rting in the employeeid the next numeric value (in the table), and take in c
ount that the name of the employee must dont exists ...
Thanks and Regards.
Any Help will be grateful, urls, articles, anything...
Josema.See my reply in .programming
Vishal Parkar
vgparkar@.yahoo.co.in
Monday, March 26, 2012
Easy question on UPDATE
I have one field organization_operating_name that is on two tables vendor and
vendor_loc
vendor_loc
I want to update the vendor name to the vendor_loc name
I tried this but get errors...
update vendor_loc
set organization_operating_name = vendor.organization_operating_name
where organization_operating_name like 'DO NOT%'
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'vendor' does not match with a table name or alias name
used in the query.
vendor is a valid table name...so I must be missing something.
jeff
--
Message posted via http://www.sqlmonster.comSee Example C under UPDATE in Books Online, and also "Changing Data
Using the FROM Clause".
Simon|||thanks Simon,
the lightbulb went off...
Simon Hayes wrote:
>See Example C under UPDATE in Books Online, and also "Changing Data
>Using the FROM Clause".
>Simon
--
Message posted via http://www.sqlmonster.com|||UPDATE Vendor_Loc
SET organization_operating_name
= (SELECT organization_operating_name
FROM Vendors
WHERE organization_operating_name LIKE 'DO NOT%');
You would never use the proprietary UPDATE .. FROM syntax because the
results are unpredictable. It will fail to discover cardinality
violations, does not port, and depends on the physical arrangment of
the data. .
Thursday, March 22, 2012
Easier way of building pivot tables in MS SQL Server
Dear All
--
Hope this helps.
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
I would be very grateful for any help with this.
wujtehacjuszWhat version of SQL Server?
SQL Server 2005 has the PIVOT command.
On Jul 10, 5:19 am, wujtehacjusz <wujtehacj...@.gmail.comwrote:
Quote:
Originally Posted by
Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
--
Hope this helps.
Dan Guzman
SQL Server MVP
"wujtehacjusz" <wujtehacjusz@.gmail.comwrote in message
news:1184059198.208520.109590@.q75g2000hsh.googlegr oups.com...
Quote:
Originally Posted by
Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
>
Wednesday, March 21, 2012
Dynamicly load tables for DataSources/DataDestinations
Hi everybody,
The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.
I hope somebody can help me. Thanks.
Is the metadata of these tables the same? If so you could loop over the list of tables using a ForEach loop and execute the same data-flow for each one of them. Reply here, search this forum or read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx if you need help in doing that.
If the metadata is NOT the same then you'll need a seperate data-flow for each in which case there isn't much point in storing the name of the source tables somewhere.
If you are intent on dynamically setting the name of the table to extract from then this should help also: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
-Jamie
sqlMonday, March 19, 2012
Dynamically select tables
Hi,
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
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|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:
> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
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|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:
> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet
Dynamically pick the source and destination tables
I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.
If the metadata changes (that is the column names change and/or data types) then you cannot do this without manually accounting for the differences.If the structures are the same, then you can build SQL statements to select against the appropriate table name. You can build the SQL in a variable expression.|||
Would you please give an example for this.
|||A little bit of searching will help you out...I found: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx|||
All the source tables have different data type and columns, so I think it is not possible to have 1 common SSIS for all of them.
We are storing our packages under the FileSystem on the server and executing them via jobs. So my question is if we make the changes in the package in BIDS(our Solution file) will it be reflected in the job or we'll have to import the package in File System?
|||Paarul wrote:
All the source tables have different data type and columns, so I think it is not possible to have 1 common SSIS for all of them.
We are storing our packages under the FileSystem on the server and executing them via jobs. So my question is if we make the changes in the package in BIDS(our Solution file) will it be reflected in the job or we'll have to import the package in File System?
If you edit the package that's being referenced in the job, then the changes will be picked up on the next iteration.
Sunday, March 11, 2012
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 Drop and re-create all indexes for all tables
Hi There
I have a database where i want to move all data and indexes to separate drives via filegroups.
Apparantly there is no better way to do this in 2005 than in 2000, which means i need to drop and re-created clustered and non clustered index accordingly on the new filegroups.
What i did in 2000 was loop through tables and indexes dropping the indexes and dynamically re-creating the index defintions from sysindexes etc.
Is there an easier way to do this is 2005, in a nutshell how would i dynamically script the create index statements for all indexes for all tables in 2005.
Thanx
If you need just rebuild indexes, you could use ALTER with REBUILD instead of DELETE and CREATE|||Implementing partitions would allow you to shift things around as desired.|||Hi Konstantin i do not see the ON FILEGROUP syntax in BOL with ALTER INDEX ?dynamically creating temp tables
I have a dynamic sql which uses "select into" to create a temp table. It has
to be dynamic because the fieldnames are soft coded.I need to join this temp
table (that i created using the dynamic sql) with another table.Since the
temp table goes out of scope after the exec statement i am not able to use i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!
>|||Actually i have to use that temp table in 2 queries.If i include those selec
t
stetements within the same block , the dynamic sql would be big, and the
execution of the dynamic statement could be slow.Correct me if I am wrong.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> Actually i have to use that temp table in 2 queries.If i include those
> select
> stetements within the same block , the dynamic sql would be big, and the
> execution of the dynamic statement could be slow.Correct me if I am wrong.
You're using dynamic SQL and #temp tables. I doubt the size of your dynamic
SQL is going to have a measurable impact on that kind of performance.
If the dynamic SQL is too big for a single varchar(8000) you can always try:
EXEC ( @.tempTableCreation +';' + @.sqlJoin1 + ';' + @.sqlJoin2 )
to be dynamic because the fieldnames are soft coded.I need to join this temp
table (that i created using the dynamic sql) with another table.Since the
temp table goes out of scope after the exec statement i am not able to use i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!
>|||Actually i have to use that temp table in 2 queries.If i include those selec
t
stetements within the same block , the dynamic sql would be big, and the
execution of the dynamic statement could be slow.Correct me if I am wrong.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> Actually i have to use that temp table in 2 queries.If i include those
> select
> stetements within the same block , the dynamic sql would be big, and the
> execution of the dynamic statement could be slow.Correct me if I am wrong.
You're using dynamic SQL and #temp tables. I doubt the size of your dynamic
SQL is going to have a measurable impact on that kind of performance.
If the dynamic SQL is too big for a single varchar(8000) you can always try:
EXEC ( @.tempTableCreation +';' + @.sqlJoin1 + ';' + @.sqlJoin2 )
dynamically creating temp table names
Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.
For example:
DECLARE @.l_personsUID int
select @.l_personsUID = 9842
create table ##Test1table /*then the @.l_personsUID */
(
resultset1 int
)
The key to the problem is that I want to use the variable
@.l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.
Thanks for you help.
Billy"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.
May I ask why?
You can probably do this by dynamically building the string.
But it's going to be messy.
> Thanks for you help.
> Billy|||billy_cormic@.hotmail.com (Billy Cormic) wrote in message news:<dd2f7565.0311251937.cf18cf9@.posting.google.com>...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.
> Thanks for you help.
> Billy
You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.
A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:
http://www.algonet.se/~sommar/dynam...html#Sales_yymm
Simon|||I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.
"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<2jWwb.144035$ji3.17559@.twister.nyroc.rr.com>...
> "Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
> news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> > Hello,
> > I am interested in dynamically creating temp tables using a
> > variable in MS SQL Server 2000.
> > For example:
> > DECLARE @.l_personsUID int
> > select @.l_personsUID = 9842
> > create table ##Test1table /*then the @.l_personsUID */
> > (
> > resultset1 int
> > )
> > The key to the problem is that I want to use the variable
> > @.l_personsUID to name then temp table. The name of the temp table
> > should be ##Test1table9842 not ##Test1table.
> May I ask why?
> You can probably do this by dynamically building the string.
> But it's going to be messy.
>
> > Thanks for you help.
> > Billy|||>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<
Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.
Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.|||OK. I will just create anohter table... not a bunch of temp tables to
hold the results.
thanks
joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311261052.12098cb6@.posting.google.com>...
> >> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000. <<
> Learn to write correct SQL instead. The use of temp tables is usually
> a sign of really bad code -- the temp tables are almost always used to
> hold steps in a procedural solution instead of a having a set-oriented
> non-proceudral solution. This also says that you have no data model
> and that any user, present or future, can change it on the fly.
> Oh, if you don't care about performance, portability, readability,
> security, and all that other stuff, then you can use dynamic SQL to
> screw up your application this way.
Dynamically Creating Table Name and Copying To Linked Server Help
I have tables on Server A that are created on a monthly basis. A table name
is created at the beginning of each month using an algorirthm as follows:
DECLARE @.TableName varchar (25)
SET @.TableName = 'Compare' + Month + Year
I have a script that creates the table and inserts data into (Server A)
table with no problem.
However, I need to copy the table monthly from my Server A onto a linked
server - Server B. Because these tables are created using an algorithm, I'm
having a problem using the following:
SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
...because of the limitations that T-SQL has with DDL on remote servers.
Thanks,
MichaelMichael Mach (Michael.Mach@.cmaaccess.com) writes:
> I have tables on Server A that are created on a monthly basis. A table
> name is created at the beginning of each month using an algorirthm as
> follows:
> DECLARE @.TableName varchar (25)
> SET @.TableName = 'Compare' + Month + Year
> I have a script that creates the table and inserts data into (Server A)
> table with no problem.
> However, I need to copy the table monthly from my Server A onto a linked
> server - Server B. Because these tables are created using an algorithm,
> I'm having a problem using the following:
> SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
> ...because of the limitations that T-SQL has with DDL on remote servers.
First of all: which versions of SQL Server are the two servers?
Second, what are the sizes of these tables? Why you do make new tables
each month? Why not keep year and month as a key in the table?
Knowing more about the actual business problem makes it easier to
suggestion a solution.
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|||This sounds like you have re-discovered magnetic tape files. We used
to label them the same way, only we used yy-ddd as the IBM convention
30+ years ago.
Without any more details, I think you should have all the data for
several years in one schema, and when you need to archive it, use
tapes, optical disk or some other permanent storage media. The dates
in the rows will be part of your search condition, or you can have
views for each month.|||Good point. We're using SQL 2000.
The table designs and naming conventions were setup some time ago. We do
plan to go back and redesign this.
I did find a solution though - that is to create the name of the table and
table schema on the destination table, then insert into this table from the
source table.
Thanks!
Michael
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF3E45EBBYazorman@.127.0.0.1...
> Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> First of all: which versions of SQL Server are the two servers?
> Second, what are the sizes of these tables? Why you do make new tables
> each month? Why not keep year and month as a key in the table?
> Knowing more about the actual business problem makes it easier to
> suggestion a solution.
> --
> 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|||Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> Good point. We're using SQL 2000.
> The table designs and naming conventions were setup some time ago. We do
> plan to go back and redesign this.
Good. :-)
> I did find a solution though - that is to create the name of the table
> and table schema on the destination table, then insert into this table
> from the source table.
Glad to hear that you got it working!
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
is created at the beginning of each month using an algorirthm as follows:
DECLARE @.TableName varchar (25)
SET @.TableName = 'Compare' + Month + Year
I have a script that creates the table and inserts data into (Server A)
table with no problem.
However, I need to copy the table monthly from my Server A onto a linked
server - Server B. Because these tables are created using an algorithm, I'm
having a problem using the following:
SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
...because of the limitations that T-SQL has with DDL on remote servers.
Thanks,
MichaelMichael Mach (Michael.Mach@.cmaaccess.com) writes:
> I have tables on Server A that are created on a monthly basis. A table
> name is created at the beginning of each month using an algorirthm as
> follows:
> DECLARE @.TableName varchar (25)
> SET @.TableName = 'Compare' + Month + Year
> I have a script that creates the table and inserts data into (Server A)
> table with no problem.
> However, I need to copy the table monthly from my Server A onto a linked
> server - Server B. Because these tables are created using an algorithm,
> I'm having a problem using the following:
> SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
> ...because of the limitations that T-SQL has with DDL on remote servers.
First of all: which versions of SQL Server are the two servers?
Second, what are the sizes of these tables? Why you do make new tables
each month? Why not keep year and month as a key in the table?
Knowing more about the actual business problem makes it easier to
suggestion a solution.
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|||This sounds like you have re-discovered magnetic tape files. We used
to label them the same way, only we used yy-ddd as the IBM convention
30+ years ago.
Without any more details, I think you should have all the data for
several years in one schema, and when you need to archive it, use
tapes, optical disk or some other permanent storage media. The dates
in the rows will be part of your search condition, or you can have
views for each month.|||Good point. We're using SQL 2000.
The table designs and naming conventions were setup some time ago. We do
plan to go back and redesign this.
I did find a solution though - that is to create the name of the table and
table schema on the destination table, then insert into this table from the
source table.
Thanks!
Michael
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF3E45EBBYazorman@.127.0.0.1...
> Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> First of all: which versions of SQL Server are the two servers?
> Second, what are the sizes of these tables? Why you do make new tables
> each month? Why not keep year and month as a key in the table?
> Knowing more about the actual business problem makes it easier to
> suggestion a solution.
> --
> 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|||Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> Good point. We're using SQL 2000.
> The table designs and naming conventions were setup some time ago. We do
> plan to go back and redesign this.
Good. :-)
> I did find a solution though - that is to create the name of the table
> and table schema on the destination table, then insert into this table
> from the source table.
Glad to hear that you got it working!
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
Friday, March 9, 2012
Dynamically create a snapshot
We would like to dynamically create a snapshot, based on other events. We
have snapshots based on tables which are created from overnight processes,
but which can sometimes overrun. Snapshots scheduled to run at fixed times
can then fail because the tables don't yet exist.
Any thoughts on this please ?
Thanks in advance.
--
Mike StephenMike,
Reporting Services has a web service method named
CreateReportHistorySnapshot. You can create a script to call this method
programmatically...
Searching for the method name in books online will get you started, then you
can back up into the Web services and scripting calls.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Stephen" wrote:
> We would like to dynamically create a snapshot, based on other events. We
> have snapshots based on tables which are created from overnight processes,
> but which can sometimes overrun. Snapshots scheduled to run at fixed times
> can then fail because the tables don't yet exist.
> Any thoughts on this please ?
> Thanks in advance.
> --
> Mike Stephen
have snapshots based on tables which are created from overnight processes,
but which can sometimes overrun. Snapshots scheduled to run at fixed times
can then fail because the tables don't yet exist.
Any thoughts on this please ?
Thanks in advance.
--
Mike StephenMike,
Reporting Services has a web service method named
CreateReportHistorySnapshot. You can create a script to call this method
programmatically...
Searching for the method name in books online will get you started, then you
can back up into the Web services and scripting calls.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Stephen" wrote:
> We would like to dynamically create a snapshot, based on other events. We
> have snapshots based on tables which are created from overnight processes,
> but which can sometimes overrun. Snapshots scheduled to run at fixed times
> can then fail because the tables don't yet exist.
> Any thoughts on this please ?
> Thanks in advance.
> --
> Mike Stephen
Wednesday, March 7, 2012
Dynamically Add Data to Crystal Report from Different Tables
Hi All
I am sending query to the Crystal report which i have designed.
but at design time i am using table1.
At run time i am sending query which contains table2 .
Table1 and Table2 are same it differs only in data, all fields are same.
Can i send the query to crystal report at run time if my crystal report(at design time) is using group by field at design time.
It's giving error of HResult exception.
Can we provide Multiple Tables Data at dynamically???
I am using crystal report 8.5 n SQL Server 2000
Plz Help me if anybody knows abt it.
Thanks in Advance
Regards
Henry JonesHi,
Yes u can, tell me whats u r Client Application is made of
Claasic VB, VS 2003/2005 Languages ???
Favaz|||See if you find code samples here
http://support.businessobjects.com/
I am sending query to the Crystal report which i have designed.
but at design time i am using table1.
At run time i am sending query which contains table2 .
Table1 and Table2 are same it differs only in data, all fields are same.
Can i send the query to crystal report at run time if my crystal report(at design time) is using group by field at design time.
It's giving error of HResult exception.
Can we provide Multiple Tables Data at dynamically???
I am using crystal report 8.5 n SQL Server 2000
Plz Help me if anybody knows abt it.
Thanks in Advance
Regards
Henry JonesHi,
Yes u can, tell me whats u r Client Application is made of
Claasic VB, VS 2003/2005 Languages ???
Favaz|||See if you find code samples here
http://support.businessobjects.com/
Subscribe to:
Posts (Atom)