Get back data from Reports

I have just started working with RS 2005 and was wondering if / how I can do the following.

I created an asp.net website that has a report viewer to show my reports on the web.
In addition there is a page that lists all of the reports a user has access to, which is pretty much a series of links:

Report 1
Report 2
Report 3
.....

Now in my reports I generally create a grand total of one of the column and was wondering if there was some way to get that information back into an asp page, ie:

Report 1 - $125,713
Report 2 - ($23,111)
Report 3 - $1,762,142

I was thinking if there was some way to send a parameter to the report like Summary=True which would tell it to just return a XML bit of data holding the total for that report (Anything will work, doesn't have to be XML). So the page with the report links would call each of the reports.

I realize that it would probably be possible to do this through a stored procedure, but that would just create another point of maintenace. Instead I could create some function in all of my reports and have a standardized way of doing this so my webserver or database wouldnt have to be updated all the time.

Thanks, and let me know if there is any other information I can provide to make this clearer.

Try using the XML renderer. You will get a "report" which is really just an XML stream with the data. You can then XPath into it to get the values you are interested in.|||

Sounds promising. Being rather new to reports, are there any pages you can think of off hand that provide examples or explanations?

Get a SQL Backup Folder Path in asp.net

Hi

Anyone knows , get a SQL Backup Folder Path in asp.net.Some Systems it displayed like D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup and some Systems like E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup and etc. i need to get a path for backup programmatically.Please help me.

Tamil

You can back up the databse to any location you'd like. You specify the path when you back up the DB.

sql

Get @@IDENTITY of new inserted record,

I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000

Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.

My code so far that works... but Where do I put @.@.IDENTITY ?
How do I call or assign the @.@.IDENTITY value to a value in my aspx.vb code page?

Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
-------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla......)
VALUES (@.AppName, @.Acronym, @.Description, bla bla bla bla......)

----------

Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@.app_id").Value = Session("App_ID")
.Parameters("@.AppName").Value = txtAppName.Text
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........

End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''Session("App_ID") = whatever the @.@.IDENTITY is...'''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception

End Try
End Sub

Anyone have the lines of code that does this?

Any advise or examples :) thanks you.Well you have a few options here. to name a couple, place it in an output parameter or a return value. I think the better choice is an output param as return is typically reserved for returning custom errors and messages.

.NET Data Access Architecture Guide

Also have a look at scope_identity vs @.@.identity

Get > Array > Split > Listbox

Hi everyone,

having some problemsBasically, using ASP.NET 2.0 and here is my problem,

Get data from tablePut into arraySplit where there is a +remove +'sassign to listbox to give a list of everything in that tableThe + split the courses, so in my Order table I have A + B + C etc and I want all of the different options in a list box (note different records have different entries it isn't always a b c)I am testing my code, here is what I got

     Public Sub TitleChange(ByVal Sender As Object, ByVal E As EventArgs)

Try
Dim DBConn As SqlConnection
Dim DSPageData As DataSet = New DataSet()
Dim VarTxtAOE As String
DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=XXX")
'Dim DBDataAdapter As SqlDataAdapter
DBDataAdapter = New SqlDataAdapter("Select AOE FROM TBL_Role WHERE Title = @.ddlTitle", DBConn)
DBDataAdapter.SelectCommand.Parameters.Add("@.ddlTitle", SqlDbType.NVarChar)
DBDataAdapter.SelectCommand.Parameters("@.ddlTitle").Value = TitleDropDown.SelectedValue
DBDataAdapter.Fill(DSPageData, "Courses")
'Need to find out what this rows business is about whats the number about? and am I doing it correct?
'txtAOE.Text = DSPageData.Tables("Courses").Rows(0).Item("AOE")
'txtAOE.Items.Add(New ListItem(DSPageData.Tables(0).Rows(0).Item("AOE")))

VarTxtAOE = DSPageData.Tables("Courses").Rows(0).Item("AOE")
Dim VarArray() As String = VarTxtAOE.Split("+")
Response.Write(VarArray())
txtAOE.DataSource = VarArray()
txtAOE.DataBind()

'Response.Write(test)
'ListBox1.DataSource = test

'Response.Write(VarArray)

Catch TheException As Exception
lblerror.Text = "Error occurred: " & TheException.ToString()
End Try
End Sub

My response.Write works correctly, but my list box doesn't, also I don't want to say which bit of the array like I have done using 1, I just want to display the whole array in my list box. I am not worrying about removing the +'s at the minute, just splitting my data and putting each section into a listboxMaybe I am going about this the wrong way, but I have been trying a lot of different things and its hard to find any help

ThanksChris

You need to set the DataTextField and DataValueField properties of the ListBox and then call it's DataBind() function.

|||

Ok, so I put this in

td style="width: 40%">
AOE<br />
<asp:ListBox ID="txtAOE" runat="server" Width="200px" DataTextField="AOE" DataValueField="AOE" /></td>

but should I use AOE in my Datatext/valueField or VarArray? I mean in my data bits I have AOE in my DataSet but I want it in an array to split the data!

VarTxtAOE = DSPageData.Tables("Courses").Rows(0).Item("AOE")
Dim VarArray() As String = VarTxtAOE.Split("+")
Response.Write(VarArray(0))
txtAOE.DataSource = VarArray(0)
txtAOE.DataBind()

should I even put it in an array, is there an easier way to cut up my returned value and put it in a listbox?

Thanks
Chris

|||

I am thinking maybe I need to do a loop to loop through the array to put each entry in the listbox, or maybe just there is an easy way with the datasource to put all the different values in the listbox?

Chris

|||

Hi,

You mentioned "My response.Write works correctly, but my list box doesn't". So does it indicate that you can get the formated string which is going to be splitted successfully?

Suppose we can get a+b+c+d, so the array would be array[0]=a, array[1]=b, array[2]=c, array[3]=d. If you can get the array successfully, then the problem has changed to how to bind the array to you List Box control. See the following sample.

for (int i = 0; i < array.Length; i++) { ListItem li =new ListItem(); li.Text = array[i].ToString(); li.Value = array[i].ToString();this.ListBox1.Items.Add(li);}Then you can useListBox1.SelectedValue.ToString() to get the value you selected.

Hope that helps. Thanks.

|||

Hi,

Thanks for that reply, that does look like C# to me however but I will try to convert to VB.Net.

Yes I can get a piece of data out of the array but I have to specify the number so say response.write(vararray(1)) but your code looks about right looping though each one and assigning it to the listbox

Thanks
Chris

|||

:D

VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")
Dim VarArray() As String = VarDS.Split("+")

Dim i As Integer
For i = 0 To VarArray.Length - 1

Dim li As New ListItem()
li.Text = VarArray(i).ToString()
li.Value = VarArray(i).ToString()
Me.txtAOE.Items.Add(li)
Next i

Perfecto!! It works, my only issue is now, I need to do this again and again so how do I clear out my array because I will have this one, the next block will be for another list box, different values but same process, but it gives me a warning

Number of indices is less than the number of dimensions of the indexed array

I am guessing that after this one, I just need to clear the VarArray() then I can run the 2nd block, but how do I clear the array, unless that error means something else?

Thanks
Chris

|||

I have been trying but I still have the issue outlined above, I'll mention again

I need to do this again and again so how do I clear out my arraybecause I will have this one, the next block will be for another listbox, different values but same process, but it gives me a warning

Number of indices is less than the number of dimensions of the indexed array

Iam guessing that after this one, I just need to clear the VarArray()then I can run the 2nd block, but how do I clear the array, unless thaterror means something else?

Anyone know how I can get around this? I guess I could create multiple arrays as a temp to let it work?

Thanks

Chris

|||

Hi,

Actually you can useYourarray=null to clear your array. See the sample below:

  
Dim strAs String ="ssdfdsfds%sdfdsfdf%dgbtb%"Dim myarrAs String() = str.Split("%"C)For iAs Integer = 0To myarr.Length - 1Response.Write(myarr(i).ToString())NextResponse.Write("</br>")myarr =NothingDim str2As String ="3453534%567657657%24332%"myarr = str2.Split("%"C)For iAs Integer = 0To myarr.Length - 1Response.Write(myarr(i).ToString())Next
  

Hope that helps. Thanks.

|||

Worked like a treat, I didn't see the Nothing when I was looking around for a way to do it, so thats a useful one to know, thanks for all your help. After all this I should now be able to construct my own loops to set the hight of the list box :)

Thanks
Chris

genral network error on .net framework 1.1 using system.data.sqlclient in asp .net

hi i am using asp .net 1.1

i have deployed my application on server where sql server 2005 exists but if i try to connect to sql server from my development machine then it is not connecting and gives me error when

conn.open()

in my sql server it is windows authentication.

the error is general network error check network docs.

i have tried timeout=0 and pooling=false

but neither works please answer.

what should my connection string.

you're in the wrong forum for this question. this forum is for SQL Server 2005 Mobile Edition. I'd recommend one of the ASP.NET MSDN Forums for this question.

Darren

Generating XSD schema from an sql server table

I am using XSD schemas in .net but have to generate them manually.
Is it possible to automatically get the validation information from a table in SQL server?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
<chris4england> wrote in message
news:%23Xb8L4NlEHA.3520@.tk2msftngp13.phx.gbl...
>I am using XSD schemas in .net but have to generate them manually.
> Is it possible to automatically get the validation information from a
> table in SQL server?
You could probably write a stored procedure that generated XSD from the
sysobjects table, but it would be pretty complex and might not contain all
the validation you need.
This would be the only way that I know of. Part of the problem is that XSD's
generally contain information that isn't in SQL Server.
Bryant
|||In SQL Server 2005, you will be able to infer an XSD for the RAW and AUTO
modes in FOR XML.
E.g.,
select top 0 * from table for xml auto, xmlschema
In SQL Server 2000, you can generate an XDR schema in the following way
select top 0 * from table for xml auto, xmldata
and then use one of the XDR->XSD tools to generate the XSD from it.
Note that you probably still want to then edit the schema to add your own
constraints.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:u9ykRxblEHA.3104@.TK2MSFTNGP14.phx.gbl...
> <chris4england> wrote in message
> news:%23Xb8L4NlEHA.3520@.tk2msftngp13.phx.gbl...
> You could probably write a stored procedure that generated XSD from the
> sysobjects table, but it would be pretty complex and might not contain all
> the validation you need.
> This would be the only way that I know of. Part of the problem is that
> XSD's generally contain information that isn't in SQL Server.
> --
> Bryant
>

Generating XML Schema for SQL Server Table

how can persist schema for a sql server table into an xml file from .net application?

thanks

If you already loaded the structure in a dataset, you can just use the WriteXMLSchema Method on the dataset object.

http://msdn2.microsoft.com/en-us/library/system.data.dataset.writexmlschema.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Generating numbers in SQL

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem?. Are there any samples/codes available for this.

Any help would be highly appreciated.

thanks,
-sriramWhat are you doing this for? Uniqueness for records in a table? You can use an Identity Column for that...what is the business problem you are trying to solve?|||thanks for your reply.

My client wants to do it in the way i explained before. Is there a way??

-sriram|||You could do it a few ways, the incrementing number being the point of divergence. To get the current month you simply use the Month() function with GetDate()

ex:
SELECT Month(GetDate())

There are a number of ways to build the second part...

One would be to create an identity column and after the insert read the SCOPE_IDENTITY(), combine it with the Month(GetDate()) to get your number. The problem with this is that it relies on work after the insert which could be done through a stored proc or a trigger.

Another approach would be to select the top value from the column, trimming the month prefix and the creating your value by incrementing that number.

Do you need to stuff with zeros? Does the month need to be 1 or 01; does the second number need to be led by zeros?|||hi,

thanks for replying.

The second number needs to be led with zeros.

thanks,

-sriram|||You can use the SQL-Server Function Replicate() for this:

DECLARE @.MaxLength AS int
SET @.MaxLength = 6
DECLARE @.INT AS int
SET @.INT = 1
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 000001
SET @.INT = 1234
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 001234

|||Here is some code that will help; I tried not to use UDFs because I dont know what version of SQL you are using. Regardless this will show you how to parse the old value out and generate a new one.

-- Assumes that the fixed length is 10

Declare @.OldValuechar(10)
Set@.OldValue= '0100000365'

Declare @.OldNumberint
Set@.OldNumber= Cast(Substring(@.OldValue,3, LEN(@.OldValue)-2) as int)

Declare @.MonthNamevarchar(10)
Set@.MonthName= DateName(M, Cast(Left(@.OldValue,2) + '/01/2000' as datetime))

-- Show the old values
select @.OldValue
select@.OldNumber
select @.MonthName

Declare @.MonthPrefixchar(2)
Set@.MonthPrefix= (Select Case WHEN Month(GetDate()) >= 10 THEN Cast(Month(GetDate()) as char(2)) ELSE '0' + Cast(Month(GetDate()) as char(1)) END)

Declare @.NewNumbervarchar(8)
Set@.NewNumber= Cast(@.OldNumber + 1 as varchar)

Declare @.Prefix varchar(7)
Set@.Prefix= ''

While Len(@.Prefix) < (7 - len(@.NewNumber))
Set @.Prefix = '0' + @.Prefix

Declare @.NewValuechar(10)
Set@.NewValue= @.MonthPrefix + @.Prefix + Cast(@.NewNumber as char)

-- Show the New Value
Select @.NewNumber
Select@.NewValue|||thanks,

i think it makes sense.

it should work..

anyway thanks for your reply.

-sriramsql

Generating hash value

Following Microsoft recommendations, I'd like to store a one-way passport
hash of a user's password. .NET provides method
FormsAuthentication.HashPasswordForStoringinConfigFile (...) to generate a
hash value with either SHA1 or MD5 algorithm. My problem is that the
password is to be generated on a workstation with no .NET installed. How can
I generate a hash value without .NET in the same way as
HashPasswordForStoringinConfigFile does? Is there any sequence of Windows
Crypto API calls with the same effect? An external stored procedure on the
server side?
EliyahuYes, CryptoAPI supports calculating hashes using functions:
CryptCreateHash
CryptHashData
CryptGetHashParam (with dwParam = HP_HASHVAL to get actual hash buffer)
Start here:
http://msdn.microsoft.com/library/d...data_hashes.asp
The byte order in the capi buffer returned is identical to data in .NET
HashPasswordForStoringinConfigFile string.
You only need to convert the byte buffer into an ordered hex-string to match
the .NET hash string.
- Michel Gallant
MVP Security
"Eliyahu Goldin" <removemeegoldin@.monarchmed.com> wrote in message
news:Orw0CVVzDHA.2932@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Following Microsoft recommendations, I'd like to store a one-way passport
> hash of a user's password. .NET provides method
> FormsAuthentication.HashPasswordForStoringinConfigFile (...) to generate a
> hash value with either SHA1 or MD5 algorithm. My problem is that the
> password is to be generated on a workstation with no .NET installed. How c
an
> I generate a hash value without .NET in the same way as
> HashPasswordForStoringinConfigFile does? Is there any sequence of Windows
> Crypto API calls with the same effect? An external stored procedure on the
> server side?
> Eliyahu
>

---

|||> Crypto API calls with the same effect? An external stored procedure on the

quote:

---

> server side?

---

you can use XP_CRYPT (www.activecrypt.com). Free version supports SHA1, MD5
and DES hashes without limitations.|||Thanks Michel and Andy,
Your answers are exactly what I need.
Eliyahu

Generating DTS flat file connection

I'm trying to generate a DTS Package with VB.Net using the Microsoft DTSPackage Object Library and
and the Microsoft DTSDataPump Scripting Object Library
I have to load csv files into SQL tables.
I could generate both a SQL connection and a FlatFile connection and the transformationtask.

When I look at the transformationtask and click on the transformation tab I get this error

"Incomple file format information"

The problem is I don't find where I could set the FlatFile connection properties like "Text Qualifier" and
"row delimiter"

I tried this but it still shows CRLF as row delimiter when I look at the generated DTS Package

Dim oConnection As DTS.Connection2
Dim package As DTS.Package2
Dim filename As String
filename = "myfilename.csv"

oConnection = package.Connections.New("DTSFlatFile")
oConnection.Name = filename
oConnection.ConnectionProperties.Item("Row Delimiter").Value = vbTabWhy not just bcp the file out using a stored porcedure?

generating database script

Is it possible to generate the t-sql script for a database schema and the
data from .Net(C#) at all?
Cheers
Ollie Richesfound this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
>|||The only accessible code I know of for this is in DMO, which is a COM based
API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/in...rate_script.asp
With 2005, we can use SMO for this, which is managed code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> the
>|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
programmatic[vbcol=seagreen]
the[vbcol=seagreen]
>|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>

generating database script

Is it possible to generate the t-sql script for a database schema and the
data from .Net(C#) at all?
Cheers
Ollie Richesfound this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Is it possible to generate the t-sql script for a database schema and
the
> > data from .Net(C#) at all?
> >
> > Cheers
> >
> > Ollie Riches
> >
> >
>|||The only accessible code I know of for this is in DMO, which is a COM based API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/info_generate_script.asp
With 2005, we can use SMO for this, which is managed code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
>> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
>> --
>> Miha Markic [MVP C#] - RightHand .NET consulting & development
>> www.rthand.com
>> SLODUG - Slovene Developer Users Group www.codezone-si.info
>> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
>> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> > Is it possible to generate the t-sql script for a database schema and
> the
>> > data from .Net(C#) at all?
>> >
>> > Cheers
>> >
>> > Ollie Riches
>> >
>> >
>>
>|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> > thanks but I don't want a code generator, I am looking for a
programmatic
> > approach to scripting out the database schema and the dat contained in
the
> > database, just like you can through sql server 2000 enterprise manager
> >
> > Cheers
> >
> > Ollie Riches
> >
> > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> > news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> >> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> >>
> >> --
> >> Miha Markic [MVP C#] - RightHand .NET consulting & development
> >> www.rthand.com
> >> SLODUG - Slovene Developer Users Group www.codezone-si.info
> >>
> >> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> >> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> >> > Is it possible to generate the t-sql script for a database schema and
> > the
> >> > data from .Net(C#) at all?
> >> >
> >> > Cheers
> >> >
> >> > Ollie Riches
> >> >
> >> >
> >>
> >>
> >
> >
>|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>

generating database script

Is it possible to generate the t-sql script for a database schema and the
data from .Net(C#) at all?
Cheers
Ollie Riches
found this so far
http://www.sqlteam.com/item.asp?ItemID=17320
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
|||Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>
|||thanks but I don't want a code generator, I am looking for a programmatic
approach to scripting out the database schema and the dat contained in the
database, just like you can through sql server 2000 enterprise manager
Cheers
Ollie Riches
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Check out also free tool CodeSmith at www.ericjsmith.net/codesmith
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> www.rthand.com
> SLODUG - Slovene Developer Users Group www.codezone-si.info
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:%23nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
the
>
|||The only accessible code I know of for this is in DMO, which is a COM based API. You can find a tiny
(classical VB) example here, among other things:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
With 2005, we can use SMO for this, which is managed code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks but I don't want a code generator, I am looking for a programmatic
> approach to scripting out the database schema and the dat contained in the
> database, just like you can through sql server 2000 enterprise manager
> Cheers
> Ollie Riches
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:eW5zGnEEFHA.1496@.TK2MSFTNGP14.phx.gbl...
> the
>
|||thanks
Ollie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eqgDR6EEFHA.3592@.TK2MSFTNGP15.phx.gbl...
> The only accessible code I know of for this is in DMO, which is a COM
based API. You can find a tiny[vbcol=seagreen]
> (classical VB) example here, among other things:
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> With 2005, we can use SMO for this, which is managed code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
> news:OCYb7qEEFHA.2700@.TK2MSFTNGP14.phx.gbl...
programmatic[vbcol=seagreen]
the
>
|||Ollie,
Not quite.
You can most certainly write a bunch of queries to read the master db and
peice the info together, and you can leverage on the FillSchema methods, but
ADO.NET is not a schema/script generator.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Ollie Riches" <ollie_riches@.hotmail.com> wrote in message
news:#nWrfWEEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate the t-sql script for a database schema and the
> data from .Net(C#) at all?
> Cheers
> Ollie Riches
>

Generate XML file from stored procedure

I need to develop some crystal reports on some .NET ado datasets.
This is easy to do if I actually had data to work with. It is much
easer creating reports with you have data.

Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project
and try to figure out where the developers are making the call to the
stored procedure and insert a line to writetoxmlfile. I would rather
not have to mess with their code.

Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.

I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.

Thanks for the help.

Tony"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0405100539.3807ad32@.posting.google.c om...
> I need to develop some crystal reports on some .NET ado datasets.
> This is easy to do if I actually had data to work with. It is much
> easer creating reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
> dataset is built on and generate an XML file. I could run the project
> and try to figure out where the developers are making the call to the
> stored procedure and insert a line to writetoxmlfile. I would rather
> not have to mess with their code.
> Is there a way working with SQL Server (either query analyzer or
> enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
> query analyzer, but I don't know how to save that as an actual file.
> Thanks for the help.
> Tony

I'm not entirely sure what you're looking for, but you can save the results
from Query Analyzer to a file, or use DTS or osql.exe to write the results
to file automatically. You would probably need to process the file further.
Alternatively, you may be looking for the FOR XML clause of the SELECT
statement - there are examples of using this in Books Online.

If this isn't helpful, perhaps you can give some more specific details of
what you need to achieve.

Simon|||I need an xml file.

When I execute a stored procedure with parameters, I need to output an
actual xml file.

Thanks,

Tony|||"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0405110638.41b97942@.posting.google.c om...
> I need an xml file.
> When I execute a stored procedure with parameters, I need to output an
> actual xml file.
> Thanks,
> Tony

From your previous post, you seem to have a procedure which returns XML,
presumably using the FOR XML clause. In that case, you have to use some
sort of client program to get the results into a file. For example, you
could use osql.exe:

osql.exe -S Server -d Database -E -Q "exec myProc" -o outputfile.txt

Or you can use DTS, or write your own client script using ADO COM objects.
But I believe that even with FOR XML, you get a fragment, not a well-formed
XML document, so you would still need to do some more work with the file to
get a real XML document. If you're using the ADO Command object, for
example, you need to use the Command object's "XML Root" property to add a
root which is then wrapped round the results.

But XML is a big topic, and there are other ways to get XML out of SQL
Server. You might want to post in microsoft.public.sqlserver.xml for more
information.

Simon

generate attributelist from tables

hi there!
I've been working on a project in ASP.NET and SQLserver 2000.
Since this is actually something for school, I must give a report on my work done here.
What I want to do is make an attributelist of all the tables that are in a database, or one at the time would already be a great help!
So for each table I would need in text format all its column names, types en 'allow null' property, but I just can't find out how or with what to do this.

thanks for your help!
Tombaselect * from INFORMATION_SCHEMA.COLUMNS

Generate an XML file from a stored procedure?!

I need to develop some crystal reports on some .NET ado datasets. This is easy to do if I actually had data to work with. It is much easer creating reports with you have data.
Therefore, I would like to run the stored procedure that the .NET ado dataset is built on and generate an XML file. I could run the project and try to figure out where the developers are making the call to the stored procedure and insert a line to writet
oxmlfile. I would rather not have to mess with their code.
Is there a way working with SQL Server (either query analyzer or enterprise manager, dts, or whatever) that I can generate an xml file.
I see that I can run a stored procedure and get an xml style return in query analyzer, but I don't know how to save that as an actual file.
================================================== ====================
*** I need to generate an xml file. How do I run a stored procedure and have the results as an xml file?
================================================== ====================
Thanks for the help.
Tony
One way I can think of is
in QA, Set the "Results to file" option.
Roji. P. Thomas
SQL Server Programmer
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:02CBE611-33FB-4ADD-A70D-6618C7448101@.microsoft.com...
> I need to develop some crystal reports on some .NET ado datasets. This
is easy to do if I actually had data to work with. It is much easer creating
reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project and
try to figure out where the developers are making the call to the stored
procedure and insert a line to writetoxmlfile. I would rather not have to
mess with their code.
> Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.
> ================================================== ====================
> *** I need to generate an xml file. How do I run a stored procedure and
have the results as an xml file?
> ================================================== ====================
> Thanks for the help.
> Tony
>
|||Assuming the stored procedure actually returns XML (i.e. it uses a FOR XML
clause), you could easily write a VB Script to do this as shown in the
following example. For this to work you need to install SQLXML 3.0 on the
client from which you'll run the script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=MyServer;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:02CBE611-33FB-4ADD-A70D-6618C7448101@.microsoft.com...
> I need to develop some crystal reports on some .NET ado datasets. This
is easy to do if I actually had data to work with. It is much easer creating
reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project and
try to figure out where the developers are making the call to the stored
procedure and insert a line to writetoxmlfile. I would rather not have to
mess with their code.
> Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.
> ================================================== ====================
> *** I need to generate an xml file. How do I run a stored procedure and
have the results as an xml file?
> ================================================== ====================
> Thanks for the help.
> Tony
>
|||Thanks for your help, but I don't have SQLXML. I don't even know what that is.
I tried outputting the file by setting QA to output results to a file. It works, but the file is not a real xml file. I am using FOR XML and get the results in the text in the results pane that looks like XML. I need an actual xml file.
Tony
|||SQLXML is the name of the XML mid-tier component that comes with SQL Server
2000 as part of OLEDB. See Books Online for more information. Later versions
of the mid-tier component have been made available for free as web downloads
(see http://msdn.microsoft.com/sqlxml).
The easiest way to programmatically get your FOR XML result into a file is
to write an ADO script that retrieves the FOR XML result as a result stream
(not a recordset!). You can associate the file's stream as the result
stream, so you should get the data directly into the file.
Best regards
Michael
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:7D7C7E80-D8EE-40F1-9064-F6CCFBE2A29B@.microsoft.com...
> Thanks for your help, but I don't have SQLXML. I don't even know what
> that is.
> I tried outputting the file by setting QA to output results to a file. It
> works, but the file is not a real xml file. I am using FOR XML and get
> the results in the text in the results pane that looks like XML. I need
> an actual xml file.
>
> Tony
|||Download SQLXML 3.0 from
http://www.microsoft.com/downloads/d...isplaylang=en,
install it on your client, and modify the script in my previous post to
access your server / database and execute your FOR XML stored procedure.
Hopefully that should do it.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:7D7C7E80-D8EE-40F1-9064-F6CCFBE2A29B@.microsoft.com...
> Thanks for your help, but I don't have SQLXML. I don't even know what
that is.
> I tried outputting the file by setting QA to output results to a file. It
works, but the file is not a real xml file. I am using FOR XML and get the
results in the text in the results pane that looks like XML. I need an
actual xml file.
>
> Tony

generate a xml file from sql server 2000?

hi there.

i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?

generate xml and cache it and use the xml file rather going to sql server database?

has anybody have any help on this?

the steps invloved:

1) first generate a xml file from table something like this:

select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?
SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?
SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?

which one should i use and how do i access after i gnerate a xml file

thanks.

Do you have a PrimaryKey (or clustered index) on that table? Yes, access such a large table in SQL2000 database would be slow, but you can speed up query by using clustered index seek, which I believe should be much quicker than accessing XML file. You can take a look at this link to learn more about clustered index:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp

|||

i do have primary keys and culstered index

but is that true that, if i access xml file it will boost my access speed?

i can cache my xml file and use from cache?

|||

My recomendation is run some test and look for these two System stored procs in the Master database sp_xml_preparedocument and sp_xml_removedocument because you can use them to do a lot of things with XML in SQL Server 2000. The links below show you several options including the Dataset.ReadXml method. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

http://forums.asp.net/thread/1024186.aspx

|||

after i run the below code:

DECLARE @.hdoc int
DECLARE @.doc varchar(2000)
SET @.doc = '
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE root
[<!ELEMENT root (Customers)*>
<!ELEMENT Customers EMPTY>
<!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]>
<root>
<Customers CustomerID="ALFKI" ContactName="Maria Anders"/>
</root>'

EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc

what do i do after that?

|||The second page of the thread I gave include a sample using repeater and the ReadXml method of the dataset. Hope this helps.

Generate 2 decimal points?? how?

HI All,

I use MSSQL as my database and ASP.NET as my front application. I want to display Price value S$23.68.

The dayatype I used is smallmoney, but it display: S$23.6800. HOw do I control the number of decimals point in the column of MSSQL??

Thanks a lot

Suigion

Well for monetry values i use datatype Decimal(9,2)

where 9 is the number of digits before Decimal and 2 is the number of digits that u can use after decimal Point

hope that might helps....!!