Showing posts with label created. Show all posts
Showing posts with label created. Show all posts

Thursday, March 29, 2012

EDB record limits and EDB vs RAPI

Two simple questions:

1. Im insterting records in a recently created EDB databse for WM 5.0, but only 16 records can be witten, does any body knows where is the property to accept more than only 16 records?

2. is there ann RAPI for manage EDB. because my aplication use a lot the RAPI that comes for CEDB, but what about EDB? does it exist?

The answer to the second part of the question is that RAPI support for EDB is not yet there.

For the first question, I would like to see the code that you are using to write the records.

Thanks.

EDB record limits and EDB vs RAPI

Two simple questions:

1. Im insterting records in a recently created EDB databse for WM 5.0, but only 16 records can be witten, does any body knows where is the property to accept more than only 16 records?

2. is there ann RAPI for manage EDB. because my aplication use a lot the RAPI that comes for CEDB, but what about EDB? does it exist?

The answer to the second part of the question is that RAPI support for EDB is not yet there.

For the first question, I would like to see the code that you are using to write the records.

Thanks.

Tuesday, March 27, 2012

Easy way to copy over calculated fields to another dataset

I have a dataset in which I created about 10 calculated fields. I
often use this dataset for other reports. How can I copy over those
fields to the dataset I create in my other reports quickly?Hi.
Edit the code (RDL) of the report, look for the dataset fields, copy
and paste them onto the code of the new report.
Regards,
Paulo Cunhasql

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)
did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear examnotes <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>sql

Wednesday, March 21, 2012

Dynanic table creation

Hello friends
I want to have a trigger for creating table on dialy basis.
for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on.
please help for writing the trigger for the same.
thanks
This seems like a job for SQL Server Agent, not a trigger. I'dwrite a stored procedure to create the table, and schedule it via SQLServer Agent to run every day.
|||

Thank you very much.....
Could you please tell me how should I create dynamic table in the query? like, I have to append the today's date to a fixed string and create the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?

Please reply back

|||

swatib wrote:

Thank you very much.....
Couldyou please tell me how should I create dynamic table in the query?like, I have to append the today's date to a fixed string andcreate the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?

Please reply back


Your dynamic CREATE TABLE statement would look something like this:
DECLARE @.Sql varchar(200)
SELECT @.Sql = 'CREATE TABLE Data' + REPLACE(CONVERT(char(12),GETDATE(),113),' ','') + '(column1 varchar(10), column2 int)'
EXECUTE(@.Sql)

And you can use Enterprise Manager to access SQL Server Agent toschedule the job. Click on your server name, then chooseManagement. Under that, click on SQL Server Agent. Underthat, right-click on Jobs and choose New Job. Enter the nameyou'd like to call this job, enter a T-SQL step to EXECUTE your storedprocedure, and set the Schedule.

|||Thank you very much sir for the details reply and the solution.

Monday, March 19, 2012

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Sunday, March 11, 2012

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

Dynamically Created Stored Procs??

Okay. Scenario:

We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.

From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.

This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.

So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?

Or if not, is there some round-about yet effective way of doing this?

Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).

When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.

If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?

The structure of the where statement can cause the execution plan to be changed.

So my question again is why you wouldn't want a dynamically created stored proc for this.

Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.

Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.

n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes

Every new request becomes a log of the report in a table.

The get a list and select what they want... the table stores the proc name which gets executed

You'll come to a point when you've created almost all variations.

And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long

You need to build a process to denormalize the data so they pop...

I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.

Don't look now...but your Oracle is showing...

:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.

And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...

ever wait MINUTES for a web based report?

dynamically created dataset

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

Hi,

Starting with SQL Server 2005, Microsoft has added rdlc reports. These reports can be embedded within a C# application by using the reportviewer.

Here is an example: http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx.

References: http://msdn2.microsoft.com/en-us/library/ms251671(VS.80).aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I've messed around with the rdlc reports w/ the reportviewer but i don't see how that would help me.

the report viewer lets you swap which report is shown in the viewer, but i want to swap the dataset that the report gets its data from.|||What i ended up doing is making a stored procedure that accepted a 'where clause' parameter that was built in the application. i then use the SP as the data source for the report.

IF @.WhereClause + '' <> '' SET @.statement = @.statement + ' AND ' + @.WhereClause|||Several in fact as explained in this article.

dynamically created dataset

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

Hi,

Starting with SQL Server 2005, Microsoft has added rdlc reports. These reports can be embedded within a C# application by using the reportviewer.

Here is an example: http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx.

References: http://msdn2.microsoft.com/en-us/library/ms251671(VS.80).aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I've messed around with the rdlc reports w/ the reportviewer but i don't see how that would help me.

the report viewer lets you swap which report is shown in the viewer, but i want to swap the dataset that the report gets its data from.|||What i ended up doing is making a stored procedure that accepted a 'where clause' parameter that was built in the application. i then use the SP as the data source for the report.

IF @.WhereClause + '' <> '' SET @.statement = @.statement + ' AND ' + @.WhereClause|||Several in fact as explained in this article.

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

Dynamically change the servername

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

You need to take a look at creating "package configurations". Try doing a search in Books Online for this. It helps you to do exactly what you are looking for.

Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan

Sunday, February 26, 2012

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?
Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?
|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Um, by not changing your table names?
>
|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>
|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>
|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> I have created three trigger on three table. But the table names change
>> every year. Instead of changing 3 trigger every year, how can I change
>> it
>> more efficient? Thanks.
> Um, by not changing your table names?
>|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> >> I have created three trigger on three table. But the table names change
> >> every year. Instead of changing 3 trigger every year, how can I change
> >> it
> >> more efficient? Thanks.
> >
> > Um, by not changing your table names?
> >
> >
>|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>> Yes, I mean by not changing the table name in the triggers. Like a global
>> constant. Say I set up currentyear = 2004 and next year is I set up
>> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> >> I have created three trigger on three table. But the table names
>> >> change
>> >> every year. Instead of changing 3 trigger every year, how can I
>> >> change
>> >> it
>> >> more efficient? Thanks.
>> >
>> > Um, by not changing your table names?
>> >
>> >
>>
>