How to create database from script file?

Hi
I use SQLServer 2000 with SP3
I have script file that has been created using "generate SQL script"
command from SQL server Enterprise manager.
But I can't find how to create database using this script file. Is there
any way to create database from script file?
I appreciate your help!Copy the script to the clipboard during the "generate sql script" command.
Start the query analyzer and past the script
Run the script.
(You could also save the script and open it in the query analyzer)
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>|||In the Options tab of generate script, you have the option to "Script
Database". This will include the CREATE DATABASE statement.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>sql

How to create Database Diagrams in SQL Server Management Studio?

Hi:

I installed SQL Server 2005 onto Vista RTM.

When launched SQL Server Mangement Studio -> Databases -> choose a database and expand.

Right click on top of "Database Diagrams" node, only options I've got are:

1. Working with SQL Server 2000 Diagram

2. Refresh.

Wondering did I missing something on my system in order to make database diagrams to work?

Thanks

Tommy

Only SQL Server 2005 with SP2 will be supported on Vista and Longhorn. You can install the latest SP2 CTP to see if your issue is fixed or not. See http://www.microsoft.com/sql/howtobuy/sqlonvista.mspx for more info.

You can download SP2 CTP from here: http://support.microsoft.com/kb/913089/.

|||I have the same problem as mentioned above, only I have SQL Server 2005 installed under XP. I right-click any Database Diagrams node and the only options are 'Working with SQL Server 2000 diagrams' and 'Refresh'. No option for creating a new diagram. I have installed SP2 for SQL Server 2005 to no avail. Does anyone have a solution for this problem?

How to create Database Diagrams in SQL Server Management Studio?

Hi:

I installed SQL Server 2005 onto Vista RTM.

When launched SQL Server Mangement Studio -> Databases -> choose a database and expand.

Right click on top of "Database Diagrams" node, only options I've got are:

1. Working with SQL Server 2000 Diagram

2. Refresh.

Wondering did I missing something on my system in order to make database diagrams to work?

Thanks

Tommy

Only SQL Server 2005 with SP2 will be supported on Vista and Longhorn. You can install the latest SP2 CTP to see if your issue is fixed or not. See http://www.microsoft.com/sql/howtobuy/sqlonvista.mspx for more info.

You can download SP2 CTP from here: http://support.microsoft.com/kb/913089/.

|||I have the same problem as mentioned above, only I have SQL Server 2005 installed under XP. I right-click any Database Diagrams node and the only options are 'Working with SQL Server 2000 diagrams' and 'Refresh'. No option for creating a new diagram. I have installed SP2 for SQL Server 2005 to no avail. Does anyone have a solution for this problem?

How to create cumulative S-curve?

Hi

I need to make two reports: one containing the Raleigh chart, the other containing a cumulative curve.

In the raleigh report I do this:
Per project I can see the amount of mandays on a timebase(weeks).

For the s-curve (cumulative curve) this needs to happen (and I dont know how):
I need to see the sum of the amount of mandays on a timebase(weeks).
An example:

In raleigh curve: week1 has 20md(mandays), week2 has 30md, week3 has 15md, week4 has 5md

In s-curve the same values should give: week1 has 20md, week2 has 50md(30+20), week3 has 65md(50+15), week4 has 70md (65 + 5)

I tried a sum of a sum but thats not allowed in RS
Anyone who knows how to generate an s-curve (cumulative curve)?

Assuming a cumulative s-curve is the sum of a series of data, as it moves through a series of weeks...

eg. Week 1 = 10 S-Curve Value = 10

Week 2 =

8 S-Curve

Value = 18

Week 3 = 15 S-Curve Value = 33

What you need to do is modify the data for the graph to include the

function "RunningValue". For example, if your value is called

"Mandays", the expression would be:

=RunningValue(Fields!Mandays.Value, Sum, Nothing)

This will give you a running total for each week on the graph and

should allow you to draw a cumulative graph (assuming what I think an

s-curve is, is what an s-curve is!).

More info here.

Regards,
Jon|||

Note: the RunningValue function is only available in charts starting in RS 2005. It is not available in RS 2000 charts.

-- Robert

|||

Yes it is RS2005 but it won't work!

I have a field directly retrieved from the sql server called "book_hours"
I make a calculated field on that one to get "book_days" (=book_hours / 7.5)

Then I make another calculated field to go in the data part of the chart called "cumulative_book_days". The expression is RunningValue(Fields!book_days.Value, Sum, Nothing)

From the moment That expression is added (It doesn't even have to be used in the chart), and I build the report, I get the following error:
An internal error occurred on the report server. See the error log for more details.

What is wrong with the expression?
Where can I find the log? (everything runs local and with windows authentication)

|||Your RS logs can normally be found in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles

I also get the same

problem as you when trying to define the running value as a calculated

field in the data set. In fact, VS.NET 2k5 crashes.

However, when I define it on the object by an expression, it runs fine.

What I did was define
RunningValue(Fields!book_days.Value,

Sum, "xxxx") in the Values properties of the data tab in the

chart. xxxx is the Category Group giving the scope in which you

want the running value.

You don't seem to be able to use "Nothing" for the scope parameter of

RunningValue as charts have multiple regions of scope, compared to a

standard table (with no groups) that only has it's own scope.

That seemed to work fine for me.

Robert - could this be a bug?

Regards,

Jon

|||

I keep getting an error, even after setting the scope to the category of the chart. I also used a database field as first parameter of the runningvalue function. (no success :s)

I'm gonna try to build the report from scratch again, maybe I overlooked something.

One other thing: the crashes can be avoided by first building the report. In the solution explorer you right-click the report and select 'Build'. Then if you recieve an error "An internal error occurred on the report server. See the error log for more details.", don't go to the preview tabpage of the report because VS2005 will crash!!

|||

I'm a bit confused:

where should the expression RunningValue(Fields!book_days.Value, Sum, "xxxx") go?
Should I make it in a new calculated field in the datasets pane, and then drag it to the Data Field Area of the chart?
OR
Should I drag the book_days field into the Data Field Area of the chart, then click on it and change it expression over there?

Edit: Ok I tried both, the first thing won't work: Adding a RunningValue Expression in a field in the dataset pane makes the report give that internal error.

The second thing also wont work, the RunningValue expression then just gives the same result as the expression =sum(Fields!book_days.Value)

What's going wrong?

Edit2: Oh another thing ... nothing is happening in those logfiles in the path mentioned above. Are there logging properties to be set? So there will be logged more?

|||Thanks for the advice. What error are you getting now, or is it the same?|||

It's just the same error :s
I even have set up a small example report to to experiment on that so I can minimize the scope of my problem.

I'll put a zipped solution of it online asap, maybe that will help

|||Option 2 is the way that I got it to work. I can only suggest it could be one of two things:

1) The field used in the "Category" (effectively the x axis) section is

causing an issue. Why, I don't know without seeing your report.

2) The scope parameter you have set for the running value doesn't

relate to the category field used. Again, it's a little difficult

for me to comment without seeing the report.

Just to clarify, the book_days field is simply a different result value for

each week?

Assuming you have a dataset like:

Week book_days

1 10

2 8

3 5

4 12

5 2

I'd have thought that specifiying

sum(Fields!book_days.Value) would just give you the same value for each

week (37), where as running value would sum them up for each week across the

x-axis(eg. 10, 18, 23, 35, 37).

Excuse the next post, I posted before I'd realised you'd replied.|||

Ok I made a small testsolution, hopefully somebody can find the time to look at it!
Location: http://users.telenet.be/master/CumulativeChart.zip

The shared datasource is set up with the following connection string: Data Source=localhost;Initial Catalog=master, but no tables are used in the database, the queries of the reports generate their own data.

There are 3 reports in the Solution:

Raleigh.rdl: This report shows a raleigh curve of the data (mandays on time)|||

Btw, if you are looking for a moving average in a chart, you may want to read this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=488875&SiteID=1

At the end of that thread I posted an example of how you can do this with the builtin charts of Reporting Services.

-- Robert

|||

All thanks for your comments, and Robert, thanks for the example ... it showed me what I was doing wrong.

I was grouping on the Category group, but since that each week is another value, the runningValue starts from scratch again each different group.

So the only thing I needed to do was setting the scope parameter to the Series group. Because I am showing the Mandays on a timebase per contract (= the series group), there is only 1 contract, so the group never changes, so the runningValue is never reset.

Now my graph finally looks like an S!

|||Glad you got your problem sorted, hypo.

I thought it had to be scoping issue! I just couldn't work out where.

I must admit, I struggled to get round it without checking Roberts

sample. I ended up "falsifying" a series group by grouping on a

fixed value (1, in this case) and then using that as the scope of the running

value function.

Robert - I've been trying to find a document that details scope within

reporting services. I know it's a common theory, but I'd like one

that's tailored to RS. All the sources I've seen seem a little

flakey. Do you have any suggestions of where I can find one?

Regards,
Jon|||

I'm the author of an upcoming MSDN article that will explain the moving average example (and several other new advanced samples) in detail. Particularly, I will also explain using RunningValues in charts and how to setup the scopes correctly. But I guess the article won't be published until August/Sept or so.

In general a scope is a dataset name, a data region name (list, table, matrix, chart, custom report item), or a group name within a data region.

I think the key here is to understand that in the RunningValue function, the scope is needed to determine when to "reset" the running value. In a list or table there is only one direction for the running value, hence you can use Nothing as a reset scope (meaning: never reset the running value).

In a matrix and a chart you have two running value directions (matrix: horizontal or vertical, chart: categories or series). Hence, you have to specify a grouping scope inside the matrix or the chart to identify the running value direction.

For aggregations however (such as Sum, Avg, etc.), the scope determines how much data you want to aggregate.

-- Robert

how to create array column and how to retrive in sqlserver

hi

i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server

1.how to create array column in a table using sqlserver

if possible how can i use select query for that

there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||

Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.

|||

Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.

http://www.sommarskog.se/arrays-in-sql.html

how to create an copy of a certain record except one specific column that must be different &

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

how to create an copy of a certain record except one specific column that must be different

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

How to Create a Report in SSRS from Local XML File

Hi

i need to create a Report from XML file which available in my current folder.

can anybody suggest How to create a Data source from XML file and How to make a query for that.

Thanks

Mahesh

Are you doing this in an application with ReportViewer in LocalMode? Or is this going to be a server-side app (where you're just using the XML file in your folder as a test file)?

The way to do it is different, depending on the answer to this first question... but in both cases I will have the followup question:

Is the XML file in a regular, table-like format (like Dataset XML), so that it will be easy to make into a datasource? Or is the schema different altogether?

>L<

|||

Hi thanks for ur reply

now we have chenged the way i need to prepare a report

One web service will return a DataSet

based on that Dataset i need prepare a report.

Thanks

Mahesh

|||

hi Mahesh,

That change doesn't really change anything about my first question, although it answers the second one<g>.

The way you are going to do this is still going to be different depending on whether the report is rendered server-side or client-side. Are you writing a web-application, a win-forms application, or what? Are you using the reportviewer client control?

If you need to send that dataset as a source to a server-side rendering of the report, you are still going to have to send that information as XML to the server. (But that will be easy because the dataset will turn itself into the "right kind" of XML for this purpose.) If you need to do this, we will discuss further.

If you are rendering the report using reportviewer, so you can use local report mode, then you can almost certainly give the localmode report your dataset without any trouble, and without turning it into XML first. In fact, if you *are* using XML, you load it into a dataset to give to the viewer control <s>.

Hope this helps

>L<

|||

Hi Lisa

Thanks for giving more information

I need to prepare a Server side Report .

with sql server 2005 we got One VS 2005 IDE in that i have taken Report Server Project Here am creating my reports.

for this they are given me a webservice link that will return a Data set

based on that i need to prepare a Report.

Previously i know how to prepare a report from Sql server Database.

Thanks.

Mahesh

|||

OK. If the webservice returns a dataset, then it returns it as an XML message. You should be fine with this in a server-side report.

Define the datasource as follows:

The DataSource is of type XML.
The connection string for the web service datasource is in fact the URL for the web service! (/) For testing you can put up a local copy of it (http://localhost/test/my.xml) and assign that as your datasource.
The query string, believe it or not, can just be "*". (no quotes) if the XML is a "raw" dataset style. You can also use the (bleh) element query syntax that they've specified -- see here http://technet.microsoft.com/en-us/library/ms365158.aspx -- If you get stuck, post an example of what the web service response message looks like and I'll try to help.|||

Hi

Lisa

i was struck with writing query

i got the webservice responce

<?xml version="1.0" encoding="utf-8" ?>
- <DataSet xmlns="http://tempuri.org/">
- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="Project">
- <xs:complexType>
- <xsequence>
<xs:element name="ProjectUID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="ProjectName" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
- <diffgriffgram xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlnsiffgr="urnchemas-microsoft-com:xml-diffgram-v1">
- <NewDataSet xmlns="">
- <Project diffgr:id="Project1" msdata:rowOrder="0">
<ProjectUID>8f584354-259b-48fb-a82b-838dd0d75a52</ProjectUID>
<ProjectName>Reports</ProjectName>
</Project>
</NewDataSet>
</diffgriffgram>
</DataSet>

In DataSet dialog Box i have taken a query

first i tried with "*" only it is showing error

an tried with this

<Query>
<SoapAction>
http://tempuri.org/GetAttributes
</SoapAction>
<Method Namespace="http://tempuri.org/"
Name="GetAttributes">
</Method>
</Query>

It is also not displaying the values of ProjectUid and ProjectName.

can u help me how to write the query for this type of WebService Responce.

Thanks

Mahesh

|||

Mahesh, I thought they were sending you a dataset -- is the method GetAttributes the way you are supposed to get that dataset or does it represent a query you are trying to do to get the metadata *describing* the dataset?

Either way... To use the dataset "raw", the thing you are receiving should look like, well, a serialized dataset, like you would get from a dataset if you used its .WriteXML() or .GetXML() methods. You can't use the "*" (I don't think) unless it looks like that. I can't remember what MS blog I read about this in -- it works, but it is only the simplest way.

It is not the only way. Here is a very quick walkthrough that you should find helpful for your scenario: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx

Here is another walkthrough http://msdn.technetweb3.orcsweb.com/gsnowman/archive/2005/10/12/480321.aspx

And a blog entry that goes through it again http://blogs.msdn.com/bwelcker/archive/2005/11/13/492296.aspx

I think they both show you examples of query syntax.

Here is a tutorial about using XML as a datasource that will help you -- I'm pointing you at lesson 2, where you're learning how the web service is supposed to return the serialized data set but you probably want to look at all the lessons in this tutorial -- http://technet.microsoft.com/en-us/library/aa337489.aspx

Other references describing other aspects that I have found helpful.
http://technet.microsoft.com/en-us/library/aa964129.aspx
http://channel9.msdn.com/ShowPost.aspx?PostID=137650

>L<

How to create a new user.

Hi!

I have downloaded the MSDE from the Microsoft site.

The installation of Forums Stater Kit unable to add ASPNET account in MSDE users list.

Can anyone tell me how i should add this user by myself in to MSDE users.

i don't have any enterprise manager etc. thing. Just MSDE.

and also what will be the password for this account [ASPNET]http://www.1000files.com/Software_Development/Databases_and_Networks/web_based_msde_admin_tool_-_Shusheng_SQL_Tool_3887_Review.html is a link to a free web based admin tool. ASPNET is automagically created when you installed ASP.NET and the .net framework. You won't know the password. HTH

How to copy MSSQL DB

Hi

I'm a newbie with MSSQL. I hv MSSQL 7.0 running successfully on my notebook as a test environment & the folder is \MSSQL7\DATA. We have a production server running live. There are 2 files called recruitment.mdf & recruitment_log.ldf which resides on the server but is not in my notebook. When i copy it to my notebook, the Enterprise Mgr does not recognise it. It does not show in the database list. Why? What shld I do?? I wud appreciate step-by-step instructions. ThanksWhat do u mean "Enterprise Mgr does not recognise it"? U just copied the mdf and ldf files, or u have attached the database but still not show up at Enterprise Manager, or u even failed to attach it?|||please see the sp_attach_db and sp_detach_db system stored procedures in sql server book online. You can also do a backup and restore using the replace argument of the RESTORE DATABASE command over a blank database. The former is what I usually do. I do not think SQL 7 had the copy database wizard (that was so long ago) but if it does, stay away from it. It's a pain.

How to Copy Job

Hi
I have 2 similar instances in sql server 2005
in instance A i have some jobs.
I want them to copy to second instance B
Please guide me step by step.
Muralidaran rHave you tried to Googled (http://www.google.co.uk/search?hl=en&q=copy+jobs+in+sql+server&meta=) this?
There are a handful of articles out there that explain it step by step!|||created script of jobs in instance A

executed in instance b using sql server enterprise management studio.

thanks

muralidaran r