Grid display: create

Showing posts with label create. Show all posts

Thursday, March 29, 2012

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSa
l.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't
know how?
With this value i want to fill the calculated member.
Any help is appreciated.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25])
, if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSal.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't know how?
With this value i want to fill the calculated member.
Any help is appreciated.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25]), if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
sql

Tuesday, March 27, 2012

Get a list of changed records in a table

Is there a way to get a list of records that have changed since a specific
Date/Time?
I could create a trigger and a new table and work off that data but I have a
lot of tables I want to query so I don't necessarily want to create triggers
for each one.You cannot get this unless you have a datetime column on the table that you
update when data changes. or a triggering mechanism to store changed data.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>|||> Is there a way to get a list of records that have changed since a specific
> Date/Time?
No, SQL Server does not keep this information for you automagically.
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
If you allow ad hoc access to your tables, then this is how it's done, I'm
afraid. If you deny INSERT/UPDATE rights to the table and force those
actions through stored procedures, then you could perform the logging within
a stored procedure, which at least avoids some of the problems with doing
this in a trigger. But for DELETE you would have to log to a separate table
or use a trigger.
While it doesn't help you today, SQL Server 2008 will have several options
to make this easier... from change tracking and change data capture to the
extreme of audit all actions.
A|||You could use the new OUTPUT clause to automatically pump the pre-existing
data into another table and then query that 2nd table
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>

Gerating Excel Reports from SSIS

I want to be able to create an Excel report in SSIS after querying the data from a SQLSERVER table.

I have a IS package where I'm loading all the data required in the report and the final step of the IS package I would like to build the reports. I think it makes sense to take this approach instead of setting up a RSS package.

AS anyone seen any Blogs which explains such a flow?

You can use an Excel destination in your data flow, but beyond that, you won't be able to apply unique formatting rules, grid lines, images, charts, etc...

Not without programming a script component, anyway.

Monday, March 26, 2012

generic user versus named user

Generic calculated measure

I have to create a calculated measure which is applicable for 16 measures.

How can i specify it in a generic manner?

for eg:

create member currentcube.ytd

sum(ytd(),[measures].[sales units];

here my scenario requires the calculation of YTD for many other measures also other than sales units

so can i have something instead of [measures].[units], so that it will be generic.

Thanks in advance.

The standard SSAS Time Intelligence enhancements provide this type of capability:

http://msdn2.microsoft.com/en-us/library/ms175440.aspx

SQL Server 2005 Books Online

Defining Time Intelligence Calculations using the Business Intelligence Wizard

The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:

Period to date.

Period over period growth.

Moving averages.|||

You can also create is as a calculated member in a seperate dimension called ValueType or something.

I can't give you tested precise answer because i'm installing SP2 at hte moment, but for your example it would look something like

create calculated member CurrentCube.ValueType.Ytd as

sum(ytd();

It will display the Ytd of whatever measure is in context. If it should only apply to certain measures you could scope on those.

Regards GJ

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.

The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@."SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @."'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}

//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}

The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.

Comments and Suggestion will be much appreciated.

Hi Shane,

This can be used to extend the below code I found on google for Visual Basic generit audit as well...

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Just adding this code to David Ziffer's work (after opening connection):

Dim SourceTableStr As String = ""

Dim cmd As New SqlCommand

With cmd

.CommandText = "SELECT object_name(resource_associated_entity_id) " & _

"FROM sys.dm_tran_locks WHERE " & _

"request_session_id = @.@.spid and " & _

"resource_type = 'OBJECT' "

.Connection = Connection

.CommandType = CommandType.Text

SourceTableStr = cmd.ExecuteScalar().ToString

End With

And somewhere down the code, add:

TableName = SourceTableStr

Then, also make sure you

GRANT VIEW SERVER STATE to [username]

on the master database on your server, since this permission is required to access sys.dm_tran_locks ( I suppose that's also required for your C# code unless you assume the user has such right e.g. sysadmin).

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.

Comments and Suggestion will be much appreciated.

Hi Shane,

This can be used to extend the below code I found on google for Visual Basic generit audit as well...

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Just adding this code to David Ziffer's work (after opening connection):

Dim SourceTableStr As String = ""

Dim cmd As New SqlCommand

With cmd

.CommandText = "SELECT object_name(resource_associated_entity_id) " & _

"FROM sys.dm_tran_locks WHERE " & _

"request_session_id = @.@.spid and " & _

"resource_type = 'OBJECT' "

.Connection = Connection

.CommandType = CommandType.Text

SourceTableStr = cmd.ExecuteScalar().ToString

End With

And somewhere down the code, add:

TableName = SourceTableStr

Then, also make sure you

GRANT VIEW SERVER STATE to [username]

sql

generic account vs. named account

Good morning,
We are having a debate here about development. We in
programming would like to create a generic user account
for example TransDev and have the users use that to create
an ODBC connection to the database versus having it
reference a person. The network admin group says that's a
horrible thing to request. I didn't find such an instance
on your website which answered a lot of my other
questions. Does anyone have a perspective from a security
standpoint where such a request would be a bad thing?
Thanks in advance.
Laura Mayer
Database Analyst
C&S Wholesale Grocers
802-257-6855first, if everyone is using the same id, it's much more difficult to
tell who screwed something up. also much easier for that login and
password to fall into the wrong hands.
since your network admins seem to hate your idea, force them to do some
work (if they run your authentication servers). have them create an
ntgroup and add all the users that need access to the db. then you give
the ntgroup the permissions it needs in the db. then when someone new
needs access to the db or someone leaves, the network admins will have
to go change the ntgroup and you won't have to do anything.
Laura wrote:

> Good morning,
> We are having a debate here about development. We in
> programming would like to create a generic user account
> for example TransDev and have the users use that to create
> an ODBC connection to the database versus having it
> reference a person. The network admin group says that's a
> horrible thing to request. I didn't find such an instance
> on your website which answered a lot of my other
> questions. Does anyone have a perspective from a security
> standpoint where such a request would be a bad thing?
> Thanks in advance.
> Laura Mayer
> Database Analyst
> C&S Wholesale Grocers
> 802-257-6855|||Thanks for that. What do you think about the ODBC
connection? Currently we are setting up the connection
using a persons name with the appropriate connections. I
would like to have a generic account. Could I have them
create that and put it in the group? Would that be an
appropriate middle ground?
Thanks
Laura
>--Original Message--
>first, if everyone is using the same id, it's much more
difficult to
>tell who screwed something up. also much easier for that
login and
>password to fall into the wrong hands.
>since your network admins seem to hate your idea, force
them to do some
>work (if they run your authentication servers). have
them create an
>ntgroup and add all the users that need access to the
db. then you give
>the ntgroup the permissions it needs in the db. then
when someone new
>needs access to the db or someone leaves, the network
admins will have
>to go change the ntgroup and you won't have to do
anything.
>
>Laura wrote:
>
create
a
instance
security
>.
>|||not really. you would put the ntusers into the ntgroup. then when
setting up the odbc, you'd choose windows authentication instead of
sqlserver authentication.
for example, you have users joe and john both members of domain1.
you create a group in domain1 called sqlgrp.
add domain1\joe and domain1\john to domain1\sqlgrp.
assign db permissions to domain1\sqlgrp.
set up odbc to use windows authentication.
that way there are no additional passwords to manage, only one set of db
permissions to manage, greater security with windows accounts versus
sqlserver accounts.
Laura wrote:
> Thanks for that. What do you think about the ODBC
> connection? Currently we are setting up the connection
> using a persons name with the appropriate connections. I
> would like to have a generic account. Could I have them
> create that and put it in the group? Would that be an
> appropriate middle ground?
> Thanks
> Laura
> difficult to
> login and
> them to do some
> them create an
> db. then you give
> when someone new
> admins will have
> anything.
> create
> a
> instance
> security|||Adding to the comments of 'ch', from your ODBC connection string you can use
the:
Trusted_Connection=yes;
option to force the authentication to be "trusted". Stay away from generic
access accounts they can not be monitored, creating a huge security hole
(IMO).
Steve
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:bb1501c40834$75b42b80$a501280a@.phx.gbl...
> Thanks for that. What do you think about the ODBC
> connection? Currently we are setting up the connection
> using a persons name with the appropriate connections. I
> would like to have a generic account. Could I have them
> create that and put it in the group? Would that be an
> appropriate middle ground?
> Thanks
> Laura
> difficult to
> login and
> them to do some
> them create an
> db. then you give
> when someone new
> admins will have
> anything.
> create
> a
> instance
> security

generating XML from SQL

Hi all,

Can anybody advice me how can i go about creating an XML from SQL. The need arised when our jobs which create Xmls from the select statement using vbscript hang in the executing state because the file gets a sharing voilation so we have to change the generated filename on the vbscript code frquently so i thought about can i generate it directly from SQL......

Any ideas ?

this solution would be very helpful for me

Thanks in ADVANCE

Jacx

This may help you

XML support in SQL Server 2005 (just in case you're not familiar with what is available):
http://msdn2.microsoft.com/en-us/library/ms345117.aspx#sql2k5xml_topic3

Constructing XML using SQL:
http://msdn2.microsoft.com/en-us/ms178107.aspx

Friday, March 23, 2012

Generating table from xsd file in SQL Server 2005

Hi there,

I would like to create a table in SQL Server 2005 from an xsd file. Is this possible? From what I've read it seems possible but I haven't found the way to do it yet...

Thanks!

Craig

Hello,

You can use SQLXMLBulkload object and add annotations to the xsd schema file to map elements/attributes to tables/columns.

Read more about this here: http://msdn2.microsoft.com/en-us/library/ms171993(SQL.90).aspx.

You have to set SchemaGen property to true and Bulkload property to false.

Hope this helps.

Regards,

Monica Frintu

Generating SQL Scripts

Hi,
How can I generate a script file for all the tables in my database with the data. The script file when run should create all the tables with the constraints and also have the data inserted into them.
Thanks in advance
P.C. VaidyanathanYou can generate the schema easily by going to the Enterprise Manager and right clicking on the dababase name "All Tasks","Generate SQL Scripts". I don't know of anything to script inserting the data. Can you use DTS? Not only will it create the schema and load the data but it will generate all the files used to create the schema.|||I agree with Paul Young, DTS would be your best option to create the tables, load the data nad maybe even do aditional preprocessing to your tables.

This is how we do it on a daily basis with fairly large tables all the time. Realizing size is relative, I am talking about databases as small as a few thousand rows to ones as large as 200 million.

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind Regards
Here is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have
>
|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Sorry, my methods don't copy the data.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the[vbcol=seagreen]
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully
> best
> ----
> --
> ----
> --
> have
>
|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. www.dbghost.com
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
sql

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind RegardsHere is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> > Hi All
> >
> > I have SQL Data Base . I am using asp application to access this Data
> Base.
> >
> > I want to create a script to generate Data Base on other machines. I
have
> > some data in that Data Base, which I required to run my application.
> >
> > So the final SCRIPT should contain all relations, default values ,
> > Identities and Data.
> >
> > Which method you advise to achieve these.
> >
> >
> > Kind Regards
> >
> >
> >
> >
> >
>|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Sorry, my methods don't copy the data.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> > Here is an article I wrote about generating SQL Server scripts.
Hopefully
> > this will provide you with enough information to help you determine the
> best
> > way to generate your script.
> >
> > http://www.dbazine.com/larsen4.shtml
> >
> > --
> >
> ----
> --
> ----
> --
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> > news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> > > Hi All
> > >
> > > I have SQL Data Base . I am using asp application to access this Data
> > Base.
> > >
> > > I want to create a script to generate Data Base on other machines. I
> have
> > > some data in that Data Base, which I required to run my application.
> > >
> > > So the final SCRIPT should contain all relations, default values ,
> > > Identities and Data.
> > >
> > > Which method you advise to achieve these.
> > >
> > >
> > > Kind Regards
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:
> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind RegardsHere is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have[vbcol=seagreen]
>|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Sorry, my methods don't copy the data.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully[vbcol=seagreen]
> best
> ----
> --
> ----
> --
> have
>|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. [
url]www.dbghost.com[/url]
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base
.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base
.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>

Generating Scripts to Populate Tables

I would like to find a utility that can scan through an existing Sql
Server 2000 database and create scripts to re-insert/re-populate the
data into another DB with the same table structures. Like a back-up
utility.
Can anyone recommend one (or tell me which ones to avoid)? I
would like one that takes dependencies between tables into
consideration.

ThanksTry this stored procedure:
http://vyaskn.tripod.com/code.htm#inserts
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Chris Jones" <sarophes@.yahoo.com> wrote in message
news:ad3e5184.0408110953.4a2c4c20@.posting.google.c om...
> I would like to find a utility that can scan through an existing Sql
> Server 2000 database and create scripts to re-insert/re-populate the
> data into another DB with the same table structures. Like a back-up
> utility.
> Can anyone recommend one (or tell me which ones to avoid)? I
> would like one that takes dependencies between tables into
> consideration.
> Thanks

generating scripts in SqlServer 2005?

Okay, in Sql 2000, when I wanted to generate scripts, I could easily have
both the create and drop commands scripted into the same document with one
step. How do I do that with Sql 2005. I can generate the script for
creating the table and for dropping the table, but I cannot see the flag
where I can have both scripted at the same time.> step. How do I do that with Sql 2005.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1sql

Generating scripts in SQL 2005

In SQL 2000 EM you were able to generate a script that only contained GRANT statements, (by unchecking the CREATE & DROP commands on the 2nd tab, and selecting Object Permissions on the 3rd tab). From what I can see in SQL 2005 MS you can't do this.

All that you can seem to do is generate a sciprt containing all the CREATE statements with the GRANT statement(s) after each object.

This is a bit of a pain as I want to script all the object permissions, without having to wade through hundreds of CREATE TABLE and CREATE PROCEDURE statements.

How can get just a script of GRANT statements?

Thanks.

It is not possible to generate scripts for object permissions through Management Studio like you could in Enterprise Manager. However, you can write a small SMO program to get the information the you need. This example should get you started on how to generate GRANT scripts for stored procedures.

ObjectPermissionInfo[] a = new ObjectPermissionInfo[5];

Database db = srv.Databases["pubs"];

foreach (StoredProcedure sp in db.StoredProcedures)

{

a = sp.EnumObjectPermissions();

foreach (ObjectPermissionInfo op in a)

{

Console.WriteLine("GRANT " + op.PermissionType.ToString() + " ON " + op.ObjectName.ToString() + " TO " + op.Grantee.ToString());

}

~Peter

|||Thanks for answering, I'll try what you suggest.

Wednesday, March 21, 2012

Generating scripts and creating backups

Hi All,
When to opt for generating scripts and exporting data and when to create a
backup and restore the backup?
kd
If you are looking to maintain a standby server or a backup server, then
BACKUP/RESTORE is the safest way, as backup makes exact copies of the
database.
If you want to send your data to a non-SQL Server environment or database,
then you might want to export data out into text files. Also, even between 2
SQL Servers, if you just want to copy some tables (and not the entire
database), then you could go with DTS or BCP to export the data.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:4DC56605-A663-424F-98A4-147F1CE189AE@.microsoft.com...
> Hi All,
> When to opt for generating scripts and exporting data and when to create a
> backup and restore the backup?
> kd
|||you might want to geneerate scripts if your database is incorporated as part
of an automated build process and coupled to some software product.
Greg Jackson
PDX, Oregon

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Thursday, March 29, 2012

get a value from a measure

get a value from a measure

Tuesday, March 27, 2012

Get a list of changed records in a table

Gerating Excel Reports from SSIS

Monday, March 26, 2012

generic user versus named user

Generic calculated measure

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

generic account vs. named account

generating XML from SQL

Friday, March 23, 2012

Generating table from xsd file in SQL Server 2005

Generating SQL Scripts

Generating SQL Script

Generating SQL Script

Generating SQL Script

Generating Scripts to Populate Tables

generating scripts in SqlServer 2005?

Generating scripts in SQL 2005

Wednesday, March 21, 2012

Generating scripts and creating backups

Generating primary keys

Grid display

Blog Archive

About Me