Grid display: February 2012

Sunday, February 26, 2012

Generate Script in SQL 2005

In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
Somehow, I could not to do that in SQL 2005, it does not give me the options
to generate only Indexes.
Any idea?This is a severe shortcoming in the tool (both not being able to right-click
the indexes node under a table and script all, and not being able to select
multiple items anywhere in Object Explorer). But thankfully, there is help
coming soon with Service Pack 2, which swaps out the Summary tab for a new
context-sensitive tab called Object Explorer Details. In this tab, when you
expand a table and highlight the Indexes node on the left, you can select
multiple items on the right, right-click them, and follow a Script > menu.
I talked about this briefly here:
http://www.aaronbertrand.com/rant.asp?r=499
"TBoon" <allblacks15@.hotmail.com> wrote in message
news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
> In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
> Somehow, I could not to do that in SQL 2005, it does not give me the
> options to generate only Indexes.
> Any idea?
>|||In addition to Aaron's, post:
Meanwhile, consider doing some SMO coding: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TBoon" <allblacks15@.hotmail.com> wrote in message news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
> In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
> Somehow, I could not to do that in SQL 2005, it does not give me the options to generate only
> Indexes.
> Any idea?
>|||Hi,Aaron
Hmm, it is possible that I do not understand you , but I can highlight more
than one item (SPfor example) in Object Explorer and script them out to the
new window
I have SQL Server 2005 Dev Edition
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GNKeHPDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> This is a severe shortcoming in the tool (both not being able to
> right-click the indexes node under a table and script all, and not being
> able to select multiple items anywhere in Object Explorer). But
> thankfully, there is help coming soon with Service Pack 2, which swaps out
> the Summary tab for a new context-sensitive tab called Object Explorer
> Details. In this tab, when you expand a table and highlight the Indexes
> node on the left, you can select multiple items on the right, right-click
> them, and follow a Script > menu.
> I talked about this briefly here:
> http://www.aaronbertrand.com/rant.asp?r=499
>
>
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message
> news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
>> Somehow, I could not to do that in SQL 2005, it does not give me the
>> options to generate only Indexes.
>> Any idea?
>|||I don't know how you could do that. When I click on an object like a table,
and hold shift or control to try and click a second table, the second table
is highlighted (even if I am still holding shift or control).
Maybe you could take a screen shot of what you are talking about, because I
don't know of any node in Object Explorer that allows this (SP2 or prior).
We're talking about Management Studio here, right? Not Query Analyzer?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23B5w9lSDHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hi,Aaron
> Hmm, it is possible that I do not understand you , but I can highlight
> more than one item (SPfor example) in Object Explorer and script them out
> to the new window
> I have SQL Server 2005 Dev Edition|||Hi ,Aaron
> We're talking about Management Studio here, right? Not Query Analyzer?
Sure , here is the morning , but I had already two coffees :-))))))
>I don't know how you could do that. When I click on an object like a
>table, and hold shift or control to try and click a second table, the
>second table is highlighted (even if I am still holding shift or control).
In Summery Tab I click on SP --hold Ctrl button and then secon SP --
I can send you a print screen if you want , just tell me the email address
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ONxNC3SDHHA.3524@.TK2MSFTNGP06.phx.gbl...
>I don't know how you could do that. When I click on an object like a
>table, and hold shift or control to try and click a second table, the
>second table is highlighted (even if I am still holding shift or control).
> Maybe you could take a screen shot of what you are talking about, because
> I don't know of any node in Object Explorer that allows this (SP2 or
> prior).
> We're talking about Management Studio here, right? Not Query Analyzer?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23B5w9lSDHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> Hi,Aaron
>> Hmm, it is possible that I do not understand you , but I can highlight
>> more than one item (SPfor example) in Object Explorer and script them out
>> to the new window
>> I have SQL Server 2005 Dev Edition
>|||> In Summery Tab
(!) I was talking about Object Explorer (the left pane). I never used the
Summary Tab.
And as luck would have it, I don't have a pre-SP2 server within reach to
even see what you're talking about. Again, I was talking about in Object
Explorer, which I had initially expected to allow you to select multiple
objects.
A|||Ok,
I was confused by your statement
<coming soon with Service Pack 2, which swaps out the >Summary tab for a new
> context-sensitive tab called Object Explorer Details.
Well, I just wanted to say that on the righ pane (Summary Tab) you can do
that
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e0aKhcXDHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> In Summery Tab
> (!) I was talking about Object Explorer (the left pane). I never used the
> Summary Tab.
> And as luck would have it, I don't have a pre-SP2 server within reach to
> even see what you're talking about. Again, I was talking about in Object
> Explorer, which I had initially expected to allow you to select multiple
> objects.
> A
>

Generate Script in SQL 2005

In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
Somehow, I could not to do that in SQL 2005, it does not give me the options
to generate only Indexes.
Any idea?This is a severe shortcoming in the tool (both not being able to right-click
the indexes node under a table and script all, and not being able to select
multiple items anywhere in Object Explorer). But thankfully, there is help
coming soon with Service Pack 2, which swaps out the Summary tab for a new
context-sensitive tab called Object Explorer Details. In this tab, when you
expand a table and highlight the Indexes node on the left, you can select
multiple items on the right, right-click them, and follow a Script > menu.
I talked about this briefly here:
http://www.aaronbertrand.com/rant.asp?r=499
"TBoon" <allblacks15@.hotmail.com> wrote in message
news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
> In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
> Somehow, I could not to do that in SQL 2005, it does not give me the
> options to generate only Indexes.
> Any idea?
>|||In addition to Aaron's, post:
Meanwhile, consider doing some SMO coding: http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TBoon" <allblacks15@.hotmail.com> wrote in message news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gb
l...
> In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
> Somehow, I could not to do that in SQL 2005, it does not give me the optio
ns to generate only
> Indexes.
> Any idea?
>|||Hi,Aaron
Hmm, it is possible that I do not understand you , but I can highlight more
than one item (SPfor example) in Object Explorer and script them out to the
new window
I have SQL Server 2005 Dev Edition
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23GNKeHPDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> This is a severe shortcoming in the tool (both not being able to
> right-click the indexes node under a table and script all, and not being
> able to select multiple items anywhere in Object Explorer). But
> thankfully, there is help coming soon with Service Pack 2, which swaps out
> the Summary tab for a new context-sensitive tab called Object Explorer
> Details. In this tab, when you expand a table and highlight the Indexes
> node on the left, you can select multiple items on the right, right-click
> them, and follow a Script > menu.
> I talked about this briefly here:
> http://www.aaronbertrand.com/rant.asp?r=499
>
>
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message
> news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
>|||I don't know how you could do that. When I click on an object like a table,
and hold shift or control to try and click a second table, the second table
is highlighted (even if I am still holding shift or control).
Maybe you could take a screen shot of what you are talking about, because I
don't know of any node in Object Explorer that allows this (SP2 or prior).
We're talking about Management Studio here, right? Not Query Analyzer?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23B5w9lSDHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hi,Aaron
> Hmm, it is possible that I do not understand you , but I can highlight
> more than one item (SPfor example) in Object Explorer and script them out
> to the new window
> I have SQL Server 2005 Dev Edition|||Hi ,Aaron
> We're talking about Management Studio here, right? Not Query Analyzer?
Sure , here is the morning , but I had already two coffees :-))))))

>I don't know how you could do that. When I click on an object like a
>table, and hold shift or control to try and click a second table, the
>second table is highlighted (even if I am still holding shift or control).
In Summery Tab I click on SP --hold Ctrl button and then secon SP --
I can send you a print screen if you want , just tell me the email address
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:ONxNC3SDHHA.3524@.TK2MSFTNGP06.phx.gbl...
>I don't know how you could do that. When I click on an object like a
>table, and hold shift or control to try and click a second table, the
>second table is highlighted (even if I am still holding shift or control).
> Maybe you could take a screen shot of what you are talking about, because
> I don't know of any node in Object Explorer that allows this (SP2 or
> prior).
> We're talking about Management Studio here, right? Not Query Analyzer?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23B5w9lSDHHA.3228@.TK2MSFTNGP03.phx.gbl...
>|||> In Summery Tab
(!) I was talking about Object Explorer (the left pane). I never used the
Summary Tab.
And as luck would have it, I don't have a pre-SP2 server within reach to
even see what you're talking about. Again, I was talking about in Object
Explorer, which I had initially expected to allow you to select multiple
objects.
A|||Ok,
I was confused by your statement
<coming soon with Service Pack 2, which swaps out the >Summary tab for a new
> context-sensitive tab called Object Explorer Details.
Well, I just wanted to say that on the righ pane (Summary Tab) you can do
that
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e0aKhcXDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> (!) I was talking about Object Explorer (the left pane). I never used the
> Summary Tab.
> And as luck would have it, I don't have a pre-SP2 server within reach to
> even see what you're talking about. Again, I was talking about in Object
> Explorer, which I had initially expected to allow you to select multiple
> objects.
> A
>

Generate Script in SQL 2005

In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
Somehow, I could not to do that in SQL 2005, it does not give me the options
to generate only Indexes.
Any idea?
This is a severe shortcoming in the tool (both not being able to right-click
the indexes node under a table and script all, and not being able to select
multiple items anywhere in Object Explorer). But thankfully, there is help
coming soon with Service Pack 2, which swaps out the Summary tab for a new
context-sensitive tab called Object Explorer Details. In this tab, when you
expand a table and highlight the Indexes node on the left, you can select
multiple items on the right, right-click them, and follow a Script > menu.
I talked about this briefly here:
http://www.aaronbertrand.com/rant.asp?r=499
"TBoon" <allblacks15@.hotmail.com> wrote in message
news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
> In SQL2K, we can generate scripts just for PK and FK, or just Indexes.
> Somehow, I could not to do that in SQL 2005, it does not give me the
> options to generate only Indexes.
> Any idea?
>
|||Hi,Aaron
Hmm, it is possible that I do not understand you , but I can highlight more
than one item (SPfor example) in Object Explorer and script them out to the
new window
I have SQL Server 2005 Dev Edition
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GNKeHPDHHA.3524@.TK2MSFTNGP06.phx.gbl...
> This is a severe shortcoming in the tool (both not being able to
> right-click the indexes node under a table and script all, and not being
> able to select multiple items anywhere in Object Explorer). But
> thankfully, there is help coming soon with Service Pack 2, which swaps out
> the Summary tab for a new context-sensitive tab called Object Explorer
> Details. In this tab, when you expand a table and highlight the Indexes
> node on the left, you can select multiple items on the right, right-click
> them, and follow a Script > menu.
> I talked about this briefly here:
> http://www.aaronbertrand.com/rant.asp?r=499
>
>
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message
> news:uo0a3dODHHA.4404@.TK2MSFTNGP03.phx.gbl...
>
|||I don't know how you could do that. When I click on an object like a table,
and hold shift or control to try and click a second table, the second table
is highlighted (even if I am still holding shift or control).
Maybe you could take a screen shot of what you are talking about, because I
don't know of any node in Object Explorer that allows this (SP2 or prior).
We're talking about Management Studio here, right? Not Query Analyzer?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23B5w9lSDHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hi,Aaron
> Hmm, it is possible that I do not understand you , but I can highlight
> more than one item (SPfor example) in Object Explorer and script them out
> to the new window
> I have SQL Server 2005 Dev Edition
|||Hi ,Aaron
> We're talking about Management Studio here, right? Not Query Analyzer?
Sure , here is the morning , but I had already two coffees :-))))))

>I don't know how you could do that. When I click on an object like a
>table, and hold shift or control to try and click a second table, the
>second table is highlighted (even if I am still holding shift or control).
In Summery Tab I click on SP --hold Ctrl button and then secon SP --
I can send you a print screen if you want , just tell me the email address
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ONxNC3SDHHA.3524@.TK2MSFTNGP06.phx.gbl...
>I don't know how you could do that. When I click on an object like a
>table, and hold shift or control to try and click a second table, the
>second table is highlighted (even if I am still holding shift or control).
> Maybe you could take a screen shot of what you are talking about, because
> I don't know of any node in Object Explorer that allows this (SP2 or
> prior).
> We're talking about Management Studio here, right? Not Query Analyzer?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23B5w9lSDHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
|||> In Summery Tab
(!) I was talking about Object Explorer (the left pane). I never used the
Summary Tab.
And as luck would have it, I don't have a pre-SP2 server within reach to
even see what you're talking about. Again, I was talking about in Object
Explorer, which I had initially expected to allow you to select multiple
objects.
A
|||Ok,
I was confused by your statement
<coming soon with Service Pack 2, which swaps out the >Summary tab for a new
> context-sensitive tab called Object Explorer Details.
Well, I just wanted to say that on the righ pane (Summary Tab) you can do
that
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e0aKhcXDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> (!) I was talking about Object Explorer (the left pane). I never used the
> Summary Tab.
> And as luck would have it, I don't have a pre-SP2 server within reach to
> even see what you're talking about. Again, I was talking about in Object
> Explorer, which I had initially expected to allow you to select multiple
> objects.
> A
>

Generate Script and Insert Data

I have a question regarding generating SQL script from a database.If you use SQL Server Management Express, it lets you generate SQLscript that can be used to recreate table structure, stored procedures,etc. But it does not include the actual data stored in the tables.

Is it possible to generate SQL to *INSERT* data as well? If yes, then how?

You can get a template query for inserting data to 1 table by right clicking the table in Object Explorer->choose Script Table As->INSERT To. However there is no such generated script to do actual INSERTs and it is not necessary, because there are serveral easy way to transfer data into the new table, for example you can just use such INSERT command:

INSERT INTO newTable SELECT * FROM oldTable

|||

Here is software that does exactly what you want:http://www.denovations.com/products/sqlpopulator/

I'm using it by myself while creating database installation script and should say this solution matched all my requirements.

Generate Script - Specified cast is not valid

When trying to generate a script, from SQL Server Management Studio, for the
following Store Procedure ( SP ) I receive the following error. I have
dropped and recreated the Store Procedure but I continue to get the error.
CREATE PROCEDURE dbo.usp_CreditCardSaveTargetQueue_S_Count
@.RecordCount INT OUTPUT
AS
SET NOCOUNT ON
SELECT @.RecordCount = COUNT(*)
FROM CreditCardSaveTargetQueue
WITH(NOLOCK) WHERE validation = 'X'
GO
-- ****************************************
**********************
Error:
TITLE: Microsoft.SqlServer.SqlEnum
--
Failed to retrieve dependency information (object_id(System.Int32)
1952726009.object_type(System.Int16) 4.relative_id(System.Int32)
731149650.relative_type(System.DBNull) .object_name(System.String)
usp_CreditCardSaveTargetQueue_S_Count.object_schema(System.String)
dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).
ADDITIONAL INFORMATION:
Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)I'm having the same problem and have tried the same resolution
(dropping/recreating stored procedure). If I try to script the database and
include this stored procedure, it fails. If I try to view dependencies to
this stored procedure it fails. Specified cast is not valid.
(Microsoft.SqlServer.SqlEnum).
"BarDev" wrote:

> When trying to generate a script, from SQL Server Management Studio, for t
he
> following Store Procedure ( SP ) I receive the following error. I have
> dropped and recreated the Store Procedure but I continue to get the error.
>
> CREATE PROCEDURE dbo.usp_CreditCardSaveTargetQueue_S_Count
> @.RecordCount INT OUTPUT
> AS
> SET NOCOUNT ON
> SELECT @.RecordCount = COUNT(*)
> FROM CreditCardSaveTargetQueue
> WITH(NOLOCK) WHERE validation = 'X'
> GO
> -- ****************************************
**********************
> Error:
> TITLE: Microsoft.SqlServer.SqlEnum
> --
> Failed to retrieve dependency information (object_id(System.Int32)
> 1952726009.object_type(System.Int16) 4.relative_id(System.Int32)
> 731149650.relative_type(System.DBNull) .object_name(System.String)
> usp_CreditCardSaveTargetQueue_S_Count.object_schema(System.String)
> dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).
> --
> ADDITIONAL INFORMATION:
> Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)
>|||I'm having the same problem and have tried the same resolution
(dropping/recreating stored procedure). If I try to script the database and
include this stored procedure, it fails. If I try to view dependencies to
this stored procedure it fails. Specified cast is not valid.
(Microsoft.SqlServer.SqlEnum).
"BarDev" wrote:

> When trying to generate a script, from SQL Server Management Studio, for t
he
> following Store Procedure ( SP ) I receive the following error. I have
> dropped and recreated the Store Procedure but I continue to get the error.
>
> CREATE PROCEDURE dbo.usp_CreditCardSaveTargetQueue_S_Count
> @.RecordCount INT OUTPUT
> AS
> SET NOCOUNT ON
> SELECT @.RecordCount = COUNT(*)
> FROM CreditCardSaveTargetQueue
> WITH(NOLOCK) WHERE validation = 'X'
> GO
> -- ****************************************
**********************
> Error:
> TITLE: Microsoft.SqlServer.SqlEnum
> --
> Failed to retrieve dependency information (object_id(System.Int32)
> 1952726009.object_type(System.Int16) 4.relative_id(System.Int32)
> 731149650.relative_type(System.DBNull) .object_name(System.String)
> usp_CreditCardSaveTargetQueue_S_Count.object_schema(System.String)
> dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).
> --
> ADDITIONAL INFORMATION:
> Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)
>

Generate SCRIPT

Hello,
Using the new "SQL Server Management Studio" for SQL Server 2005, how would
I generate scripts for more than one object at a time. Let us say I want to
generate scripts for all tables, how would I do that?.
Thanks,
Ganesh
You can select multiple objects displayed in the Summary
page - then right click and script.
-Sue
On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:

>Hello,
>Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>I generate scripts for more than one object at a time. Let us say I want to
>generate scripts for all tables, how would I do that?.
>Thanks,
>Ganesh
|||Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
Thanks,
Ganesh
"Sue Hoegemeier" wrote:

> You can select multiple objects displayed in the Summary
> page - then right click and script.
> -Sue
> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>
>
|||From Object Explorer...Right-click database...tasks...generate scripts
i think you can figure out the rest.
HTH,
JL
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:B59BAC5D-1499-4F45-919E-1967F02F5382@.microsoft.com...
> Hello,
> Using the new "SQL Server Management Studio" for SQL Server 2005, how
> would
> I generate scripts for more than one object at a time. Let us say I want
> to
> generate scripts for all tables, how would I do that?.
> Thanks,
> Ganesh
|||Under the defaults, It's to the right of registered servers
and object explorer when you first open up Management
Studio. Otherwise, hit F7 to display it. Or from the Menu go
to view and then select Summary.
-Sue
On Fri, 20 Jan 2006 12:57:04 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
>Thanks,
>Ganesh
>"Sue Hoegemeier" wrote:

Generate Script

Hi
I am looking for a script that will generate all the foreign keys, defaults
and check constraints only from all the tables in my database.
Can anyone point me in the right direction?Right click on the tables in EM, All Tasks--> Generate Script
Go to the options tab and select what you want
http://sqlservercode.blogspot.com/
"Jaco" wrote:

> Hi
> I am looking for a script that will generate all the foreign keys, default
s
> and check constraints only from all the tables in my database.
> Can anyone point me in the right direction?|||sorry, should have said I want to do this without EM
"SQL" wrote:
> Right click on the tables in EM, All Tasks--> Generate Script
> Go to the options tab and select what you want
> http://sqlservercode.blogspot.com/
> "Jaco" wrote:
>|||Jaco,
SQL-DMO? Check out the Script method in the SQL BOL.
HTH
Jerry
"Jaco" <Jaco@.discussions.microsoft.com> wrote in message
news:3E4BAF12-3CFC-49E3-95ED-43B2E193FDDF@.microsoft.com...
> sorry, should have said I want to do this without EM
> "SQL" wrote:
>

generate Script

I want to generate a script of my database (SQL Server) but with the DATA... when i use the option task|generate script, the result is only the structure!...
Can someone help me please!:rolleyes:
tanks
Franky
franky@.boucheros.comCan you give a short example of what your expected script would look like?|||CREATE table1...
CREATE table2...
...
CREATE view1...
CREATE view2...
...

CREATE PROCEDURE dbo.procs1...
CREATE PROCEDURE dbo.procs2...
...

INSERT INTO table1 ...
INSERT INTO table1 ...
INSERT INTO table1 ...
...
INSERT INTO table2 ...
INSERT INTO table2 ...
INSERT INTO table2...|||Unless you don't have much data or want to import into something like MySql i'm not sure why you would want to do this :confused:

You can use the script generator to make you DataBase Structure and then something like a DTS package to dump the data and read it back again ??|||It is possible to save script with data (using sysobjects, syscolumns,....) but what if some tables have identity or timestamp columns?
May be it is good idea to save data in script for particular tables - not for all tables.

Good luck.|||Hi,

Wingman is right.

1. You can Script the Structure of the table then do DTS.

2. Use DTS to transfer data and save the package for reuse. DTS is a more powerful tool ,very simple and interesting if you know it.
Instead of using the sysobjects and syscolumns and do research on and make it more complicated, you can design your own DTS package and eliminate the Identity or Timestamp columns if you dont need or you can even modify the data using ActiveXscripts in DTS.
Everything is possible using DTS. You can tranform the data completely as you desired.

For more information, Play with DTS using BOL ( For Beginners ) and you will start doing wonders.

Have fun :)

Varad01

Generate SCRIPT

Hello,
Using the new "SQL Server Management Studio" for SQL Server 2005, how would
I generate scripts for more than one object at a time. Let us say I want to
generate scripts for all tables, how would I do that?.
Thanks,
GaneshYou can select multiple objects displayed in the Summary
page - then right click and script.
-Sue
On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:

>Hello,
>Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>I generate scripts for more than one object at a time. Let us say I want to
>generate scripts for all tables, how would I do that?.
>Thanks,
>Ganesh|||Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
Thanks,
Ganesh
"Sue Hoegemeier" wrote:

> You can select multiple objects displayed in the Summary
> page - then right click and script.
> -Sue
> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>
>|||From Object Explorer...Right-click database...tasks...generate scripts
i think you can figure out the rest.
HTH,
JL
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:B59BAC5D-1499-4F45-919E-1967F02F5382@.microsoft.com...
> Hello,
> Using the new "SQL Server Management Studio" for SQL Server 2005, how
> would
> I generate scripts for more than one object at a time. Let us say I want
> to
> generate scripts for all tables, how would I do that?.
> Thanks,
> Ganesh|||Under the defaults, It's to the right of registered servers
and object explorer when you first open up Management
Studio. Otherwise, hit F7 to display it. Or from the Menu go
to view and then select Summary.
-Sue
On Fri, 20 Jan 2006 12:57:04 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
>Thanks,
>Ganesh
>"Sue Hoegemeier" wrote:
>

Generate SCRIPT

Hello,
Using the new "SQL Server Management Studio" for SQL Server 2005, how would
I generate scripts for more than one object at a time. Let us say I want to
generate scripts for all tables, how would I do that?.
Thanks,
GaneshYou can select multiple objects displayed in the Summary
page - then right click and script.
-Sue
On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>Hello,
>Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>I generate scripts for more than one object at a time. Let us say I want to
>generate scripts for all tables, how would I do that?.
>Thanks,
>Ganesh|||From Object Explorer...Right-click database...tasks...generate scripts
i think you can figure out the rest.
HTH,
JL
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:B59BAC5D-1499-4F45-919E-1967F02F5382@.microsoft.com...
> Hello,
> Using the new "SQL Server Management Studio" for SQL Server 2005, how
> would
> I generate scripts for more than one object at a time. Let us say I want
> to
> generate scripts for all tables, how would I do that?.
> Thanks,
> Ganesh|||Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
Thanks,
Ganesh
"Sue Hoegemeier" wrote:
> You can select multiple objects displayed in the Summary
> page - then right click and script.
> -Sue
> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
> >Hello,
> >Using the new "SQL Server Management Studio" for SQL Server 2005, how would
> >I generate scripts for more than one object at a time. Let us say I want to
> >generate scripts for all tables, how would I do that?.
> >
> >Thanks,
> >Ganesh
>|||Under the defaults, It's to the right of registered servers
and object explorer when you first open up Management
Studio. Otherwise, hit F7 to display it. Or from the Menu go
to view and then select Summary.
-Sue
On Fri, 20 Jan 2006 12:57:04 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
>Thanks,
>Ganesh
>"Sue Hoegemeier" wrote:
>> You can select multiple objects displayed in the Summary
>> page - then right click and script.
>> -Sue
>> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
>> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>> >Hello,
>> >Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>> >I generate scripts for more than one object at a time. Let us say I want to
>> >generate scripts for all tables, how would I do that?.
>> >
>> >Thanks,
>> >Ganesh
>>

Generate Schema

Hello, how do I generate a schema of a database in sql server 2005? I want to
see a graphic representation of all the table and fields, what the primary
keys are, relathionships, and so forth.
thanks
toney
You can also use Generate Script. In SSMS, right click on a database,
Tasks\Generate Scripts...
Ekrem ?nsoy
"toney" <toney@.discussions.microsoft.com> wrote in message
news:DEF49EE6-73D2-4251-AEDB-AF861B39E928@.microsoft.com...
> Hello, how do I generate a schema of a database in sql server 2005? I want
> to
> see a graphic representation of all the table and fields, what the primary
> keys are, relathionships, and so forth.
> thanks
> --
> toney
>
|||Ekrem, how would the Generate Scripts wizard generate a visual
reporesentation of the structure of the DB? I see how it creates samples code.
toney
"Ekrem ?nsoy" wrote:

> You can also use Generate Script. In SSMS, right click on a database,
> Tasks\Generate Scripts...
> --
> Ekrem ?nsoy
>
> "toney" <toney@.discussions.microsoft.com> wrote in message
> news:DEF49EE6-73D2-4251-AEDB-AF861B39E928@.microsoft.com...
>
|||On Nov 8, 5:02 pm, toney <to...@.discussions.microsoft.com> wrote:
> Hello, how do Igeneratea schema of a database insqlserver 2005? I want to
> see a graphic representation of all the table and fields, what the primary
> keys are, relathionships, and so forth.
> thanks
> --
> toney
You can create a database diagram to show relationships. if you
expand the database node in OE, you'll see a "database diagrams"
folder. right click on that to create a diagram.
also you might find this tool useful: http://www.elsasoft.org

Generate row number in query

How can I
Generate row number in query
Thank youTales Mein wrote:
> How can I
> Generate row number in query
>
> Thank you
In SQL Server 2005:
SELECT
ROW_NUMBER() OVER (ORDER BY key_col) AS row_number,
key_col, col1, col2, ...
FROM your_tbl ;
If that's not the answer you wanted then read my signature to
understand how some more information would help us to help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||And if you are on 2000, then:
http://support.microsoft.com/defaul...b;EN-US;q186133
Anith|||2005 ?
i wish.
I need 2000
thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138140087.744818.153810@.g14g2000cwa.googlegroups.com...
> Tales Mein wrote:
> In SQL Server 2005:
> SELECT
> ROW_NUMBER() OVER (ORDER BY key_col) AS row_number,
> key_col, col1, col2, ...
> FROM your_tbl ;
> If that's not the answer you wanted then read my signature to
> understand how some more information would help us to help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:elygrUTIGHA.516@.TK2MSFTNGP15.phx.gbl...
> And if you are on 2000, then:
> http://support.microsoft.com/defaul...b;EN-US;q186133
> --
> Anith
>

Generate Reports

How I make to generate reports of the replicated data ?
The report must have the format : origin,destination,datetime,table name,
and records of the table.
Thanks
FDB,
in order to answer correctly, I'd like to know a bit more about:
(1) what type of replication you are using.
(2) origin is presumably the first computer to add/update the record? If so
then this will need to be an additional field with a default.
(3) destination - is this any different than the table you're examining?
(4) datetime - is this the time the record was originally added ot the time
the record was added due to replication
Is this report a list of records or statistical aggregates?
TIA,
Paul Ibison
|||I am using Merge replication.
I have two sql servers in distinct sites, and each site make changes to
data independently.
This report is a list of records, for I check which data they had been
inserted in both databases.
The purpose is I to know if all the data had been transferred correctly.
Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eH%23OpOXJEHA.3596@.tk2msftngp13.phx.gbl...
> FDB,
> in order to answer correctly, I'd like to know a bit more about:
> (1) what type of replication you are using.
> (2) origin is presumably the first computer to add/update the record? If
so
> then this will need to be an additional field with a default.
> (3) destination - is this any different than the table you're examining?
> (4) datetime - is this the time the record was originally added ot the
time
> the record was added due to replication
> Is this report a list of records or statistical aggregates?
> TIA,
> Paul Ibison
>
|||OK - thanks for the explanation. In that case you might like to look at "validating data" in BOL. There is a section towards the bottom of the page on merge replication. You can choose to use rowcounts only, rowcounts and checksums, or rowcounts and compa
ring binary checksums (all Subscribers must be running SQL Server 2000 to use this option). There is a replication alert which fires if validation is failed (Replication: Subscriber has failed data validation). This can be set up in EM, using stored procs
or using the agent parameters - details in BOL.
HTH,
Paul Ibison

Generate Reporting Services directly to PDF file

Hi,

I want my Reporting Services generate Report directly to PDF file after showing just like i did with Crystal Reports. I get problem with RS because RS does not show it printer icon in my client PC.

Could you help me to show how to do it ?

Thanks and Regards,

Kusno

it's easy to call the reporting service web service and call the report you want then stream the result and save it as PDF for more info and example please read this article

http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp

generate report very slow?

I have posted about slow performance on report
now i create the secondary filegroup(2nd harddisk)--Account Ledge table
and put one table(684846 rows of data) from primary to secondary filegroup
but seems not improved a lot
any other idea '?You should check your query and indexes first. Maybe you are ordering
according to some expression that is not supported by an index? Maybe your
Where and Join expressions are not supported by indexes? Just by the info
you gave us it is hard to tell where the problem lies. You should include
DDL statements (Create table etc.), sample data and query you are using.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:ejom%23G2KEHA.1612@.TK2MSFTNGP12.phx.gbl...
> I have posted about slow performance on report
> now i create the secondary filegroup(2nd harddisk)--Account Ledge table
> and put one table(684846 rows of data) from primary to secondary filegroup
> but seems not improved a lot
> any other idea '?
>

generate report very slow?

I have posted about slow performance on report
now i create the secondary filegroup(2nd harddisk)--Account Ledge table
and put one table(684846 rows of data) from primary to secondary filegroup
but seems not improved a lot
any other idea '?You should check your query and indexes first. Maybe you are ordering
according to some expression that is not supported by an index? Maybe your
Where and Join expressions are not supported by indexes? Just by the info
you gave us it is hard to tell where the problem lies. You should include
DDL statements (Create table etc.), sample data and query you are using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:ejom%23G2KEHA.1612@.TK2MSFTNGP12.phx.gbl...
> I have posted about slow performance on report
> now i create the secondary filegroup(2nd harddisk)--Account Ledge table
> and put one table(684846 rows of data) from primary to secondary filegroup
> but seems not improved a lot
> any other idea '?
>

generate report very slow?

I have posted about slow performance on report
now i create the secondary filegroup(2nd harddisk)--Account Ledge table
and put one table(684846 rows of data) from primary to secondary filegroup
but seems not improved a lot
any other idea ??
You should check your query and indexes first. Maybe you are ordering
according to some expression that is not supported by an index? Maybe your
Where and Join expressions are not supported by indexes? Just by the info
you gave us it is hard to tell where the problem lies. You should include
DDL statements (Create table etc.), sample data and query you are using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:ejom%23G2KEHA.1612@.TK2MSFTNGP12.phx.gbl...
> I have posted about slow performance on report
> now i create the secondary filegroup(2nd harddisk)--Account Ledge table
> and put one table(684846 rows of data) from primary to secondary filegroup
> but seems not improved a lot
> any other idea ??
>

Generate report using XML as data source

Hi
How can I generate report using XML file as Datasource?
And I need to pass XML Filename dynamically at run-time..
--
Thanks in advance
BhaveshToday you have to write a data extension. In the future (Yukon) there will
be controls that can be used and it should be significantly easier to do
this sort of thing (load a dataset with the xml data and pass that to the
control).
Bruce L-C
"Bhavesh" <Bhavesh@.discussions.microsoft.com> wrote in message
news:1ED00147-3CAB-4515-A722-1D943AE2C574@.microsoft.com...
> Hi
> How can I generate report using XML file as Datasource?
> And I need to pass XML Filename dynamically at run-time..
> --
> Thanks in advance
> Bhavesh|||Pass to which Control?
I am reading XML data in dataset, in my asp page and passing it to Crystal
report control..and everything is working fine..
But I want to do this using SQL Server reporting service..
How can I do this?
Thanks
Bhavesh
"Bruce Loehle-Conger" wrote:
> Today you have to write a data extension. In the future (Yukon) there will
> be controls that can be used and it should be significantly easier to do
> this sort of thing (load a dataset with the xml data and pass that to the
> control).
> Bruce L-C
> "Bhavesh" <Bhavesh@.discussions.microsoft.com> wrote in message
> news:1ED00147-3CAB-4515-A722-1D943AE2C574@.microsoft.com...
> > Hi
> > How can I generate report using XML file as Datasource?
> >
> > And I need to pass XML Filename dynamically at run-time..
> >
> > --
> > Thanks in advance
> > Bhavesh
>
>|||What I am saying is that with the shipping product you have to create a data
extension. Read up in bol. Version 2 of RS (ships with Yukon, the next
version of SQL Server) will have controls (web and winform) so you can do it
the way you are thinking about.
Another alternative to a data extension is to rethinking where the work is
done. Have the data extraction occur within RS and then integrate in using
either URL integration or Web services.
Bruce L-C
"Bhavesh" <Bhavesh@.discussions.microsoft.com> wrote in message
news:D7E6E9F7-ECFC-493C-9FA5-9F63FDBC241D@.microsoft.com...
> Pass to which Control?
> I am reading XML data in dataset, in my asp page and passing it to Crystal
> report control..and everything is working fine..
> But I want to do this using SQL Server reporting service..
> How can I do this?
> Thanks
> Bhavesh
> "Bruce Loehle-Conger" wrote:
> > Today you have to write a data extension. In the future (Yukon) there
will
> > be controls that can be used and it should be significantly easier to do
> > this sort of thing (load a dataset with the xml data and pass that to
the
> > control).
> >
> > Bruce L-C
> >
> > "Bhavesh" <Bhavesh@.discussions.microsoft.com> wrote in message
> > news:1ED00147-3CAB-4515-A722-1D943AE2C574@.microsoft.com...
> > > Hi
> > > How can I generate report using XML file as Datasource?
> > >
> > > And I need to pass XML Filename dynamically at run-time..
> > >
> > > --
> > > Thanks in advance
> > > Bhavesh
> >
> >
> >|||Thanks Bruce..
"Bruce Loehle-Conger" wrote:
> What I am saying is that with the shipping product you have to create a data
> extension. Read up in bol. Version 2 of RS (ships with Yukon, the next
> version of SQL Server) will have controls (web and winform) so you can do it
> the way you are thinking about.
> Another alternative to a data extension is to rethinking where the work is
> done. Have the data extraction occur within RS and then integrate in using
> either URL integration or Web services.
> Bruce L-C
> "Bhavesh" <Bhavesh@.discussions.microsoft.com> wrote in message
> news:D7E6E9F7-ECFC-493C-9FA5-9F63FDBC241D@.microsoft.com...
> > Pass to which Control?
> > I am reading XML data in dataset, in my asp page and passing it to Crystal
> > report control..and everything is working fine..
> >
> > But I want to do this using SQL Server reporting service..
> > How can I do this?
> >
> > Thanks
> > Bhavesh
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Today you have to write a data extension. In the future (Yukon) there
> will
> > > be controls that can be used and it should be significantly easier to do
> > > this sort of thing (load a dataset with the xml data and pass that to
> the
> > > control).
> > >
> > > Bruce L-C
> > >
> > > "Bhavesh" <Bhavesh@.discussions.microsoft.com> wrote in message
> > > news:1ED00147-3CAB-4515-A722-1D943AE2C574@.microsoft.com...
> > > > Hi
> > > > How can I generate report using XML file as Datasource?
> > > >
> > > > And I need to pass XML Filename dynamically at run-time..
> > > >
> > > > --
> > > > Thanks in advance
> > > > Bhavesh
> > >
> > >
> > >
>
>

generate report using recordset

I am generating crystal reports using recordset in ASP.Now i want to generate the report using reporting services 2000 with the same recordset.how can i do this.pls suggest me.

Thanks

If this is a server report, you need to write a custom data extension as shown here. Alternatively, you can bind the dataset to a local report as explained here.|||

Hi Teo

Thanks for the information...

Here my scenario is different..let me explain you..

First i am generating a recordset object with populated data and putting it into session..

then i am creating an object of crystal report and accessing its database and then its table.

finally filling this table with session data table.here is the code like...

Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")

Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)

Set session("Database") = session("oRpt").Database

Set session("Tables") = session("Database").Tables

set Session("oTable1") = session("Tables").Item(1)

Session("oTable1").SetPrivateData 3, Session("rstFutureActivity")

where Session("rstFutureActivity") is the recordset object...

Now i want this session object of recordset with Reporting services 2000.

how can i perform this.Pls help me.

|||

Amit,

There is no ReportViewer control for classic ASP (VB6). All incarnations of Reporting Services and their related solutions are .NET based.

|||

Hi Adam,

thanks for reply...

you mean i can't use recordset data to show using reporting services.

Is there any way to make a component in .net which can access recordset data from session object and then pass it to

reporting services for generate the report.

Any suggestion will be appreciable...

thanks

|||

Apologies for not noting that you use classic ASP.You can scratch out the local report option then. A custom data extension is the way to go (see the article link in my first post).

1. You application would serialize the recordset to XML.

2. Pass to your server report as a parameter.

3. CDE will map it to a RS dataset.

The article I mentioned uses an ADO.NET dataset but the implementation approach is essentially the same.

|||How can i map xml data to RS dataset..any more help regarding the same|||Your custom data extension will. Did the article I pointed you to failed to explain this?

Generate report that link to analysis services

Is there any samples to download that show how to create matrix report that
link to microsoft sql server 2000 analysis services? I understand that user
can use the mdx query (Analysis Services) to generate a report..But is there
an easy way just link to datasource (cube) that is created in analysis
services (same as table)? For example, I have create a cube name sales in
the analysis services and i try to create report by using the report wizard,
build a query at reporting services by using sql statement "select * from
sales"... It give me a funny field name which are found at report wizard
(under Design the table stage).
Thank You.The current release of SQL Server Reporting Services 2000 allows you to
execute MDX queries against Analysis Services cubes. A very good MSDN
article with samples about the integration of RS 2000 and AS 2000 is
provided at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
A graphical query designer for creating MDX queries will be available in the
RS 2005 release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alvin" <alvinleo@.time.net.my> wrote in message
news:uxvnmp1XEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Is there any samples to download that show how to create matrix report
that
> link to microsoft sql server 2000 analysis services? I understand that
user
> can use the mdx query (Analysis Services) to generate a report..But is
there
> an easy way just link to datasource (cube) that is created in analysis
> services (same as table)? For example, I have create a cube name sales in
> the analysis services and i try to create report by using the report
wizard,
> build a query at reporting services by using sql statement "select * from
> sales"... It give me a funny field name which are found at report wizard
> (under Design the table stage).
> Thank You.
>

Generate Report Query

Hi every body
the image below describe entity relation diagram of my database i need to Write Query the generate report of licensed buildings (that has license saved in BuildingLicense Table ) and not licensed buildings in section or street

i need to gerate report like table below

select
B.streat,
Licensed = sum(case when BL.LicNo is not null then 1 else 0 end),
NotLicensed = sum(case when BL.LicNo is null then 1 else 0 end)
from
Building B (nolock)
left outer join BuildingLicensce BL (nolock)
on BL.Building_NO = B.BuildingNo
group by
B.streat

if you also what the Serial Number column, create a temp table with identity columns and dump above results intot the temp table.

|||Thanks MR

Lakshmana Kumar K for your interest
i tried you query
in my databast i stored 5 records in buildings table and when i run query i have this result

in result the sum of licensed and not licensed buildings give the total number of buildings
in your query it gives 8 Why?

I understoond your Query
in your Query u get the count of Licensecs for buildings in the street
but i just need the number of buildings which have license Record not the Count of Licenses
|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]|||Finally i find the Answer

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

and this is the result

i create temp table [LicBuilPerStreet]
Thanks MR

Lakshmana Kumar K 4 your Interest

Generate Report Model from Cube with perspectives

1. Is there a way to generate SMDL programmatically from a cube besides using the RS Webservice API?

2. Is there a way to programmatically modify the SMDL once it has been created without doing crazy xml manipulation?

3. How can I create a Report Model from a cube with multiple perspectives? I want the model to only contain one perspective of the cube.

First, there's a real lack of responses on this forum....but hopefully this will help someone else that ran into these issues.

1. It's better to modify the SMDL that's generated from the API then to try and create it from scratch

2. The only way is with xml manipulation but it's actually not that bad. The schema is pretty simple. It just looks bloated at first with lots of repeating elements.

3. When the SMDL is generated, each perspective in the cube becomes a perspective in the report model. Then you can trim down the perspectives you don't want by removing the corresponding element from the SMDL and saving it back to reporting services.

Other things I notice is that once you modify the SMDL, you can't regenerate it using the Regenerate API call. Instead, you have to delete it and recreate it again. Also, once you recreate a model, you have to link up all the reports that were previously associated with it. I have no idea why RS removes the datasource from reports when the model is deleted. It's probably better to just leave it and if the datasource isn't there, then throw an error.

|||

Other things I notice is that once you modify the SMDL, you can't regenerate it using the Regenerate API call. Instead, you have to delete it and recreate it again. Also, once you recreate a model, you have to link up all the reports that were previously associated with it. I have no idea why RS removes the datasource from reports when the model is deleted. It's probably better to just leave it and if the datasource isn't there, then throw an error.

Actually, you will want to use the SetModelDefinition API instead of Renenerate. Items in the Report Server database are linked by GUID, not name. Just because it has the same name, we don't assume that it is the same item.

|||I do use SetModelDefinition API to modify the SMDL. But after a cube has been processed and the structure changed, I will need to regenerate the model base on the new cube. If I do that, all my reports break because the datasource reference is no longer valid even though the model has been recreated. This makes sense in the situations where the report may contain references to dimensions/measures no longer in the datasource. However, in my case, dimensions/measures are often added to the cube and not removed. I just think it's better to leave the datasource and have the report fail if dimensions/measures are missing instead of ALWAYS failing with an invalid datasource.

Generate report if data changes

Apologies if this is a FAQ, but I can't find any references to it:
Is it possible to regenerate a report if data changes? i.e. I have a status
column in one of my tables and would like to auto-generate a report (and
email it to a subscription list) if the contents of that column changes.Anythings possible, but "out of the box" I don't know of a feature that can
do this.
You'd probably need some kind of webserver that polled the database to check
for changes.
Once the change is found, you could then execute the report to run (via
scheduler).
"georges" <georges@.discussions.microsoft.com> wrote in message
news:05913C6B-81E7-4312-901D-3BB5A457026A@.microsoft.com...
> Apologies if this is a FAQ, but I can't find any references to it:
> Is it possible to regenerate a report if data changes? i.e. I have a
> status
> column in one of my tables and would like to auto-generate a report (and
> email it to a subscription list) if the contents of that column changes.
>|||*webservice, not webserver - typo.
"georges" <georges@.discussions.microsoft.com> wrote in message
news:05913C6B-81E7-4312-901D-3BB5A457026A@.microsoft.com...
> Apologies if this is a FAQ, but I can't find any references to it:
> Is it possible to regenerate a report if data changes? i.e. I have a
> status
> column in one of my tables and would like to auto-generate a report (and
> email it to a subscription list) if the contents of that column changes.
>|||As far as I know, I dont think any feature exists from reporting services but
can be done if you are using sql server 2005, notification services.
Amarnath, MCTS.
"georges" wrote:
> Apologies if this is a FAQ, but I can't find any references to it:
> Is it possible to regenerate a report if data changes? i.e. I have a status
> column in one of my tables and would like to auto-generate a report (and
> email it to a subscription list) if the contents of that column changes.
>

Generate report from list of parameters?

I have one report with two datasets, and consequently two queries. The first
one gets a list of ReportID's for a particular day, while the second dataset
uses each ReportID to create a report.
Query 1:
SELECT ReportID
FROM Table1
WHERE Date=@.Date
Query 2:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID = @.ReportID
If I try to Union '(All)' to Query1, Query2 will return info for all
ReportID's in the system, not just what was generated in Query1. I also
tried creating a comma delimited field of all the ReportID's and using WHERE
ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
Query2 while only running Query1 once?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1On Feb 28, 6:28 am, "ana9" <u28955@.uwe> wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
If I'm understanding you correctly, you should be able to do one of a
few things:
Option 1:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID in (SELECT ReportID
FROM Table1
WHERE Date=@.Date )
Option 2:
Create a cursor (or while loop) to loop through Query 1 and execute
Query 2 in the loop for each ReportID (possibly populating a single
dataset or multiple datasets).
Ofcourse Option 1 is far less performance intensive. If I'm not
understanding you correctly, let me know.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||That was very helpful, thank you. Option1 is a variation of what I had tried
before. The only issue (which I failed to mention) is that Query2 is
actually a report that gets populated from about 20 datasets. So each
dataset may not return the exact same number of rows, in which case some of
the information will not be displayed in the correct manor. Also, for some
of those datasets I use SELECT Top # so using Option1 will not allow me to
get the correct amount of results.
Your suggestion for Option2 seems like it would be perfect for this scenario,
however I was under the impression that you can't create while loops in SQL.
Could you point me in the direction of some resources/examples? Thank you.
EMartinez wrote:
>> I have one report with two datasets, and consequently two queries. The first
>> one gets a list of ReportID's for a particular day, while the second dataset
>[quoted text clipped - 18 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>If I'm understanding you correctly, you should be able to do one of a
>few things:
>Option 1:
>SELECT *
>FROM *A whole bunch of tables*
>WHERE ReportID in (SELECT ReportID
>FROM Table1
>WHERE Date=@.Date )
>Option 2:
>Create a cursor (or while loop) to loop through Query 1 and execute
>Query 2 in the loop for each ReportID (possibly populating a single
>dataset or multiple datasets).
>Ofcourse Option 1 is far less performance intensive. If I'm not
>understanding you correctly, let me know.
>Hope this is helpful.
>Regards,
>Enrique Martinez
>Sr. SQL Server Developer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1|||You might also try
SELECT ReportID INTO #Reports
FROM Table1
WHERE Date=@.Date
SELECT *
FROM *A whole bunch of tables*
inner join #Reports
On ReportID = #Reports.ReportID
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.
"ana9" wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>

generate report from data warehouse

Dear all,

I use sql server 2005 as a data warehouse.

A report get data from this data warehouse. I found that it is very slowly.

How can I tune the sql or give me some advices about generate report from a data

warehouse ?

Thanks you

You should look into creating Analysis Services cubes on your data warehouse, depending on the types of reports you are looking for.|||

Hello. If you are running a relational data warehouse, without any cubes(OLAP), you can find som good advice here: (http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/default.mspx?mfr=true)

, that are still relevant.

HTH

Thomas Ivarsson

Generate Report Export from Stored Procedure

I have what should be a simple task, but I cannot find any information on
how to accomplish it. All I want is a stored procedure that I can call on
demand to export a report to a given format, path, and file name. I have
found ways to write a VB.Net application to do this using the "Render"
command, but it sure seems like I should be able to do it directly from SQL
Server.
Any help would be greatly appreciated!!!
John A. Prejean
Guardian Computer
john@.gcit.netT-SQL is not ".NET enabled" yet (sounds like more support will be in SQL
2005). You might try sp_OACreate in the meantime.
Jeff
"John A. Prejean" <john@.gcit.net> wrote in message
news:etrheKdlEHA.3476@.tk2msftngp13.phx.gbl...
> I have what should be a simple task, but I cannot find any information on
> how to accomplish it. All I want is a stored procedure that I can call on
> demand to export a report to a given format, path, and file name. I have
> found ways to write a VB.Net application to do this using the "Render"
> command, but it sure seems like I should be able to do it directly from
SQL
> Server.
> Any help would be greatly appreciated!!!
> John A. Prejean
> Guardian Computer
> john@.gcit.net
>

Generate Report Direct to PDF

Hello All,
I have been asked to find a solution for generating an SSRS report
directly to PDF without the client ever viewing it. There are a couple of
particualrs about our requirements:
1. The report is being called from a third party piece of software
2. We have no, or at most, very limited control of this third party app
(some URL control possible)
3. The location the PDF is to be generated to is static
Any information, links, articles, sample code would be muy, muy appreciated
Gracias
Michael CReporting Services provides a set of Web Services that you can use to get
find targeting report, render it as binery stream in one of the output
format (PDF, XLS, HTML...).
You can simply wite an application to call corresponding web methods of the
reporting services. This application can be any type: desktop, web app or
even Windows services..., based on your need.
I have no idea how the said third part app calls the report. But it is
fairly simple to build a ASP.NET app, which works this way (you need to set
web reference to the report server and know how to consume web services in
your app):
1. User is presented a web page, which allow he to select a report and enter
required report parameters, his email address is also collected, where the
report would be sent to in the format of his choice (PDF, XLS...);
2. When the user submit the web form to server, one of the web methods of
the reporting services is called to render the requested report into binary
stream (PDF or XLS...) and returnd to ASP.NET app;
3. The binary stream of the report is saved to a temporary location on the
ASP.NET app server;
4. The ASP.NET app compose an email, attach the saved report, send the email
to user's email address.
After step 2, of course, you can also stream back the report binary data to
user's browser, in this can, user's browser would prompt user with a
download dialog box. User can choose either open it or save it.
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:C75284F9-60BA-415C-8D2D-CB41F245A232@.microsoft.com...
> Hello All,
> I have been asked to find a solution for generating an SSRS report
> directly to PDF without the client ever viewing it. There are a couple of
> particualrs about our requirements:
> 1. The report is being called from a third party piece of software
> 2. We have no, or at most, very limited control of this third party app
> (some URL control possible)
> 3. The location the PDF is to be generated to is static
> Any information, links, articles, sample code would be muy, muy
> appreciated
> Gracias
> Michael C|||Thanks Norman,
I'm not too familiar with ASP.NET but necessity is the mother of
invention! Ill give this a shot.
Michael C
"Norman Yuan" wrote:
> Reporting Services provides a set of Web Services that you can use to get
> find targeting report, render it as binery stream in one of the output
> format (PDF, XLS, HTML...).
> You can simply wite an application to call corresponding web methods of the
> reporting services. This application can be any type: desktop, web app or
> even Windows services..., based on your need.
> I have no idea how the said third part app calls the report. But it is
> fairly simple to build a ASP.NET app, which works this way (you need to set
> web reference to the report server and know how to consume web services in
> your app):
> 1. User is presented a web page, which allow he to select a report and enter
> required report parameters, his email address is also collected, where the
> report would be sent to in the format of his choice (PDF, XLS...);
> 2. When the user submit the web form to server, one of the web methods of
> the reporting services is called to render the requested report into binary
> stream (PDF or XLS...) and returnd to ASP.NET app;
> 3. The binary stream of the report is saved to a temporary location on the
> ASP.NET app server;
> 4. The ASP.NET app compose an email, attach the saved report, send the email
> to user's email address.
> After step 2, of course, you can also stream back the report binary data to
> user's browser, in this can, user's browser would prompt user with a
> download dialog box. User can choose either open it or save it.
>
> "Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
> news:C75284F9-60BA-415C-8D2D-CB41F245A232@.microsoft.com...
> > Hello All,
> > I have been asked to find a solution for generating an SSRS report
> > directly to PDF without the client ever viewing it. There are a couple of
> > particualrs about our requirements:
> >
> > 1. The report is being called from a third party piece of software
> > 2. We have no, or at most, very limited control of this third party app
> > (some URL control possible)
> > 3. The location the PDF is to be generated to is static
> >
> > Any information, links, articles, sample code would be muy, muy
> > appreciated
> >
> > Gracias
> > Michael C
>|||You mentioned URL access. You can easily tell reporting services to execute
a report to PDF format by using URL access from you third party application.
When you call the desired report add to the calling URL this name=value pair
" &rs:Format=PDF" (no quotes of course).
--
Thanks, Jim
"Michael C" wrote:
> Thanks Norman,
> I'm not too familiar with ASP.NET but necessity is the mother of
> invention! Ill give this a shot.
> Michael C
>
> "Norman Yuan" wrote:
> > Reporting Services provides a set of Web Services that you can use to get
> > find targeting report, render it as binery stream in one of the output
> > format (PDF, XLS, HTML...).
> >
> > You can simply wite an application to call corresponding web methods of the
> > reporting services. This application can be any type: desktop, web app or
> > even Windows services..., based on your need.
> >
> > I have no idea how the said third part app calls the report. But it is
> > fairly simple to build a ASP.NET app, which works this way (you need to set
> > web reference to the report server and know how to consume web services in
> > your app):
> >
> > 1. User is presented a web page, which allow he to select a report and enter
> > required report parameters, his email address is also collected, where the
> > report would be sent to in the format of his choice (PDF, XLS...);
> >
> > 2. When the user submit the web form to server, one of the web methods of
> > the reporting services is called to render the requested report into binary
> > stream (PDF or XLS...) and returnd to ASP.NET app;
> >
> > 3. The binary stream of the report is saved to a temporary location on the
> > ASP.NET app server;
> >
> > 4. The ASP.NET app compose an email, attach the saved report, send the email
> > to user's email address.
> >
> > After step 2, of course, you can also stream back the report binary data to
> > user's browser, in this can, user's browser would prompt user with a
> > download dialog box. User can choose either open it or save it.
> >
> >
> > "Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
> > news:C75284F9-60BA-415C-8D2D-CB41F245A232@.microsoft.com...
> > > Hello All,
> > > I have been asked to find a solution for generating an SSRS report
> > > directly to PDF without the client ever viewing it. There are a couple of
> > > particualrs about our requirements:
> > >
> > > 1. The report is being called from a third party piece of software
> > > 2. We have no, or at most, very limited control of this third party app
> > > (some URL control possible)
> > > 3. The location the PDF is to be generated to is static
> > >
> > > Any information, links, articles, sample code would be muy, muy
> > > appreciated
> > >
> > > Gracias
> > > Michael C
> >
> >

Generate report automaically and email them : Please help

Please help me if possible.
My report takes a parameter say "name".
I want reports to be generated automatically for various values of
"name" and each of the generated report should be sent as email to the
person every month or so.
How do I go about achieving this? I am very new to reporting services
and I am stuck with very little documentation.
A detailed explanation or even a hint in the right direction would be
greatly appreciated.Use a data driven subscription. This will require setting up an additional
table to hold the values, but then there is a wizard to walk through in the
Report Manager to set this up. Take a look at the SQL-RS Books On Line to
get the details as it takes several steps to get it going.
Brian
"Aravind" <r.aravind@.gmail.com> wrote in message
news:1121867840.826151.118570@.o13g2000cwo.googlegroups.com...
> Please help me if possible.
> My report takes a parameter say "name".
> I want reports to be generated automatically for various values of
> "name" and each of the generated report should be sent as email to the
> person every month or so.
> How do I go about achieving this? I am very new to reporting services
> and I am stuck with very little documentation.
> A detailed explanation or even a hint in the right direction would be
> greatly appreciated.
>|||Yea, just make sure you have the enterprise edition of SQL server before you
start as it won't work in standard. The EE version is 10x the cost of
standard.
"goodman93" wrote:
> Use a data driven subscription. This will require setting up an additional
> table to hold the values, but then there is a wizard to walk through in the
> Report Manager to set this up. Take a look at the SQL-RS Books On Line to
> get the details as it takes several steps to get it going.
> Brian
> "Aravind" <r.aravind@.gmail.com> wrote in message
> news:1121867840.826151.118570@.o13g2000cwo.googlegroups.com...
> > Please help me if possible.
> >
> > My report takes a parameter say "name".
> > I want reports to be generated automatically for various values of
> > "name" and each of the generated report should be sent as email to the
> > person every month or so.
> >
> > How do I go about achieving this? I am very new to reporting services
> > and I am stuck with very little documentation.
> >
> > A detailed explanation or even a hint in the right direction would be
> > greatly appreciated.
> >
>
>

Generate relational schema - old tables are always deleted and not recreated

I want to use the top down modelling features and generate tables from the OLAP objects by generating relational schema.

The problem:

First time using the "generate relational schema" - the tables are created.

Next time using this feature: new tables are generated, but all tables created in the past are lost. There is a warning, the schema can't be created, because there are user defined tables.

I found a way to reset this status, if I open the code for a dimension and delete the section from <Source to /Source>:

</Source>

After this I can recreate the tables. But there must be another way. I can't delete the source information from all object before each using of the "generate relational schema".

In the past, when I was evaluating the SSAS 2005, I had no such problems. What happens? Why are the tables deleted each time, if I generate relational schema?

The way you are trying to use Analysis Services is not exactly how it was envisioned originally.

The logic for which tables needs to be created is pretty simple. If object doesnt have bindings, the schema generation wizard will create a table in relational database supporting it.

Now, creating relational database schema is considered one time operation. Analysis Services is not exactly application to use for generating relational schemas. You go through the wizard, it creates all the tables for you and then you expected to keep and maintain set of tables created.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:[vbcol=seagreen]
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
>
>
>|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:[vbcol=seagreen]
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
>
>
>
>
>
>
>
>

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
> > hi, i need to create a function to do some like this...
> > update myTable set DateModify = getdate()
> > ... but... i need to get a minimum difference bettwen records...
> > milisecons... or...
> > how can i get an array or cursor (SQL server side) with 500 records
> > with different datetime programatically?|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
> > Hi,
> > I assume that you are asking about writing a procedure for this and not
> > a function. A function cannot update tables nor can it use getdate().
> > To do this in a procedure you can use the WAITFOR DELAY statement in
> > SQL.
> > Regards,
> > Kart
> > celerno wrote:
> > > hi, i need to create a function to do some like this...
> > > update myTable set DateModify = getdate()
> > > ... but... i need to get a minimum difference bettwen records...
> > > milisecons... or...
> > > how can i get an array or cursor (SQL server side) with 500 records
> > > with different datetime programatically?

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?
Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?
|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:[vbcol=seagreen]
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
>
|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:[vbcol=seagreen]
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
>
>
>
>

Sunday, February 26, 2012

Generate Script in SQL 2005

Generate Script in SQL 2005

Generate Script in SQL 2005

Generate Script and Insert Data

Generate Script - Specified cast is not valid

Generate Script - Specified cast is not valid

Generate SCRIPT

Generate Script

generate Script

Generate SCRIPT

Generate SCRIPT

Generate Schema

Generate row number in query

Generate Reports

Generate Reporting Services directly to PDF file

generate report very slow?

generate report very slow?

generate report very slow?

Generate report using XML as data source

generate report using recordset

Generate report that link to analysis services

Generate Report Query

Generate Report Model from Cube with perspectives

Generate report if data changes

Generate report from list of parameters?

generate report from data warehouse

Generate Report Export from Stored Procedure

Generate Report Direct to PDF

Generate report automaically and email them : Please help

Generate relational schema - old tables are always deleted and not recreated

generate real date time

generate real date time

generate real date time

Grid display

Blog Archive

About Me