(Reposting, reworded)
I am writing a C# Web application with a web form that displays a report
using URL access method. The URL displays the report for the user, and then
they can pick their parameters and run the report.
I want to know: how can I display the report to the user the next time with
all of the same parameters that they chose? I don't want a snapshot, I want
to refresh the data for the same parameters the user chose.
Do I need to use form post method? or SOAP? Or is there a way to do this
with URL access.
The problem is that when the user changes the value of a parameter, the URL
does not change, so I cannot access that information from my C# Web
application.
MalikFor the level of control you want you need to use web services (soap). You
can definitely get the information you want with that. The URL access will
not provide what you want.
Bruce L-C
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:%23OdayAThEHA.3612@.TK2MSFTNGP12.phx.gbl...
> (Reposting, reworded)
> I am writing a C# Web application with a web form that displays a report
> using URL access method. The URL displays the report for the user, and
then
> they can pick their parameters and run the report.
> I want to know: how can I display the report to the user the next time
with
> all of the same parameters that they chose? I don't want a snapshot, I
want
> to refresh the data for the same parameters the user chose.
> Do I need to use form post method? or SOAP? Or is there a way to do this
> with URL access.
> The problem is that when the user changes the value of a parameter, the
URL
> does not change, so I cannot access that information from my C# Web
> application.
> Malik
>|||While there's no easy way to do this, it is possible by making the parameter default populate their
values from a database table which looks up the last parameter values used by that user (or a
default if no row exists). You would then also need a set of queries which stored the current
parameter values in the database when the report is run.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e7sShOThEHA.3148@.TK2MSFTNGP10.phx.gbl...
> For the level of control you want you need to use web services (soap). You
> can definitely get the information you want with that. The URL access will
> not provide what you want.
> Bruce L-C
> "Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
> news:%23OdayAThEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > (Reposting, reworded)
> >
> > I am writing a C# Web application with a web form that displays a report
> > using URL access method. The URL displays the report for the user, and
> then
> > they can pick their parameters and run the report.
> >
> > I want to know: how can I display the report to the user the next time
> with
> > all of the same parameters that they chose? I don't want a snapshot, I
> want
> > to refresh the data for the same parameters the user chose.
> >
> > Do I need to use form post method? or SOAP? Or is there a way to do this
> > with URL access.
> >
> > The problem is that when the user changes the value of a parameter, the
> URL
> > does not change, so I cannot access that information from my C# Web
> > application.
> >
> > Malik
> >
> >
>|||Thank you very much for both of your responses.
I did assume that I would have to store parameter values in my own database
table to be able to save report parameters, so that is not really a problem.
In fact, my application already has a table to store parameters, I just have
to define a new parameter type, then put parameter names and values in the
existing table.
But I wouldn't get anywhere without SOAP. My gap in knowledge here was how
to do SOAP integration. I am now hurriedly learning how to do this, but it
doesn't seem overly complicated. It seems like there is a reporting services
object model, and I can somehow get the current parameter values from an
object, so I am content to keep hitting the books until the solution comes
together.
Thanks again for pointing me in the right direction.
Malik
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:eKhQrFUhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> While there's no easy way to do this, it is possible by making the
parameter default populate their
> values from a database table which looks up the last parameter values used
by that user (or a
> default if no row exists). You would then also need a set of queries
which stored the current
> parameter values in the database when the report is run.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e7sShOThEHA.3148@.TK2MSFTNGP10.phx.gbl...
> > For the level of control you want you need to use web services (soap).
You
> > can definitely get the information you want with that. The URL access
will
> > not provide what you want.
> >
> > Bruce L-C
> >
> > "Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
> > news:%23OdayAThEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > (Reposting, reworded)
> > >
> > > I am writing a C# Web application with a web form that displays a
report
> > > using URL access method. The URL displays the report for the user, and
> > then
> > > they can pick their parameters and run the report.
> > >
> > > I want to know: how can I display the report to the user the next time
> > with
> > > all of the same parameters that they chose? I don't want a snapshot, I
> > want
> > > to refresh the data for the same parameters the user chose.
> > >
> > > Do I need to use form post method? or SOAP? Or is there a way to do
this
> > > with URL access.
> > >
> > > The problem is that when the user changes the value of a parameter,
the
> > URL
> > > does not change, so I cannot access that information from my C# Web
> > > application.
> > >
> > > Malik
> > >
> > >
> >
> >
>sql
Friday, March 30, 2012
Saving uploaded picture filepath to sql database
I am trying to save an uploaded picture filepath to a sql database record. The record that the filepath will be saved to will be the record determined by the logged in userid. I get an error stating Object not set to an instance of an object. This is my code for when the upload button is hit
ProtectedSub uploadbutton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles uploadbutton.ClickIf UploadTest.HasFile =FalseThen
' No file uploaded!
UploadDetails.Text ="Please first select a file to upload..."
Else
' Display the uploaded file's details
UploadDetails.Text =String.Format( _"Uploaded file: {0}<br />" & _
"File size (in bytes): {1:N0}<br />" & _"Content-type: {2}", _UploadTest.FileName, _
UploadTest.FileBytes.Length, _
UploadTest.PostedFile.ContentType)
' Save the file
Dim filePathAsString = Server.MapPath("~/ProfilePictures/" & UploadTest.FileName)UploadTest.SaveAs(filePath)
Dim datasources1AsNew SqlDataSourcedatasources1.ConnectionString = ConfigurationManager.ConnectionStrings("aspnetdb_connection").ToString()datasources1.UpdateCommandType = SqlDataSourceCommandType.Text
Dim myUserIDAsString = Membership.GetUser().ProviderUserKey.ToString
datasources1.UpdateParameters("userid").DefaultValue = myUserIDdatasources1.UpdateCommand ="UPDATE into profile_details (pic) VALUES (@.filepath) WHERE ([UserId] = @.UserId)"
EndIf
EndSub
Any ideas on what i need to do to fix this?
i save image path into my database too
1: i take the file name and i create a dirrectory with the name of loged in user
string fileName = FileUpload1.FileName.ToString(); //i don't know if is FileName() or FileName.ToString()...
string userName = Profile.UserName;string uploadFolderPath ="~/upload/"; // the folder where you save uploads
string filePath =HttpContext.Current.Server.MapPath(uploadFolderPath);Directory.CreateDirectory(filePath +"\\" + userName);
2: i save the picture
FileUpload1.SaveAs("F:\\Documents and Settings\\Administrator\\Desktop\\WebSite2\\upload\\" + userName +"\\" + fileName);
3: i save the filepath into a hiddenField
HiddenField1.Value = userName +"\\" + fileName; // would be like "user1\picture.jpg"
4: i insert the HiddenField1 Value into my database
this is what i did and i am pleased...hope i helped you
|||The problem I am having is the actual code to imput the string into the database. It seems if i use the hiddenfield way your talking about their has to be another step to actually input it.
|||i don't understand very clear what you mean, but i use a stored procedure to insert the filepath into database like this one
CREATE PROCEDURE InsertPicture@.Image1varchar(200)ASINSERT INTO Pictures (Image1)VALUES (@.Image1)
you can use an update procedure instead of iseart if you want to change the filepath for a specific picture with PictureID = x
and in the code behind you will have something like this
SqlConnection conn =new SqlConnection(ConnectionString);// wite your conn string in bracketsSqlCommand command =new SqlCommand("InsertPicture", conn);command.CommandType = CommandType.StoredProcedure;SqlParameter Image1_param =new SqlParameter();Image1_param.ParameterName ="@.Image1";Image1_param.SqlDbType = SqlDbType.VarChar;Image1_param.Value = HiddenField1.Value.ToString();command.Parameters.Add(Image1_param);conn.Open();SqlDataReader reader = command.ExecuteNonQuery();reader.Close();command.Connection.Close();conn.Close();|||in your example what column in the database are you saving the imagefile path to? Image1 right?
|||yes, i have many columns like PictureID, Name, Description and so on, but in Image1 i store the filepath
Saving updated row
Is there a way to save a record(s) prior to modifying values in it via trigger? If not, what options do I have to maintain something like a snapshot of the record(s) besides backups?
BTW, I use SQL 7.
ThanksRefer to Using the inserted and deleted Tables under books online.
BTW, I use SQL 7.
ThanksRefer to Using the inserted and deleted Tables under books online.
Saving to SQL
I am currently migrating my application from Access to SQL. It looks like it's working good but one thing I cannot understand is why when I have created a varchar(40) field and after I insert a field that is less then 40 characters long, the rest of the string is filled with the leading spaces. I have also create an nchar(40) that did not solve it ether. For example: the text is "Hello World". The text is 11 characters long but the data saved to the table is: "Hello Wolrd" + 29 spaces.
Am I doing something wrong?
Thanks for your help in advance.
Use nvarchar(40) instead. The nchar(40) is for a fixed length column.
|||I have used nvarchar(40) and varchar(40) and both save with the trailing spaces. I have changed one of the tables and it seems it took effect but then the other table still does the same thing. I wonder what I did to get it working. I don't remember the steps I took. Is there something else besides changing the datafields?
You can update your varchar(nvarchar) column with TRIM functions to remove trailing spaces from both ends(just in case).
UPDATE yourTableSET colVarChar40=LTRIM(RTRIM(colChar40))
|||I am actually Inserting the new value not updating. How can I accomplish this?
|||please show your insert code.
|||the update is for after you have done all your inserting just update it.... or in your sproc before you insert the value do the trim
Saving to a Web Folder i.e WSS
Is it possible for reporting services to save a report to a web folder or
more specifically WSS/SharePoint when you define a subscription? We save all
our reports on a WSS site and we would like to remove this as a manual step.
Oh and is it possible to dynamically change the path based on the month and
year, for example create a new folder for the month of december to put all
the reports for that month in.
thanks for any help!This is not currently supported. You would need to write your own delivery
extension to do this. Delivery extensions are fairly easy to write, you
should look at the Printer Delivery Sample for how to get started.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:EA07C8FB-287E-48B6-94E5-ABA172763330@.microsoft.com...
> Is it possible for reporting services to save a report to a web folder or
> more specifically WSS/SharePoint when you define a subscription? We save
> all
> our reports on a WSS site and we would like to remove this as a manual
> step.
> Oh and is it possible to dynamically change the path based on the month
> and
> year, for example create a new folder for the month of december to put all
> the reports for that month in.
> thanks for any help!|||Where would I find the printer delivery sample?
Thanks for the help!!
"Daniel Reib [MSFT]" wrote:
> This is not currently supported. You would need to write your own delivery
> extension to do this. Delivery extensions are fairly easy to write, you
> should look at the Printer Delivery Sample for how to get started.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:EA07C8FB-287E-48B6-94E5-ABA172763330@.microsoft.com...
> > Is it possible for reporting services to save a report to a web folder or
> > more specifically WSS/SharePoint when you define a subscription? We save
> > all
> > our reports on a WSS site and we would like to remove this as a manual
> > step.
> >
> > Oh and is it possible to dynamically change the path based on the month
> > and
> > year, for example create a new folder for the month of december to put all
> > the reports for that month in.
> >
> > thanks for any help!
>
>|||That depends on the version of RS you are using. Both are included in setup.
You can find more info here:
RS 2005:
http://msdn2.microsoft.com/library/ms160954(en-us,SQL.90).aspx
RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_3t69.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:92B40DFD-55CB-4821-9B75-BE35E8C577E5@.microsoft.com...
> Where would I find the printer delivery sample?
> Thanks for the help!!
> "Daniel Reib [MSFT]" wrote:
>> This is not currently supported. You would need to write your own
>> delivery
>> extension to do this. Delivery extensions are fairly easy to write, you
>> should look at the Printer Delivery Sample for how to get started.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:EA07C8FB-287E-48B6-94E5-ABA172763330@.microsoft.com...
>> > Is it possible for reporting services to save a report to a web folder
>> > or
>> > more specifically WSS/SharePoint when you define a subscription? We
>> > save
>> > all
>> > our reports on a WSS site and we would like to remove this as a manual
>> > step.
>> >
>> > Oh and is it possible to dynamically change the path based on the month
>> > and
>> > year, for example create a new folder for the month of december to put
>> > all
>> > the reports for that month in.
>> >
>> > thanks for any help!
>>
more specifically WSS/SharePoint when you define a subscription? We save all
our reports on a WSS site and we would like to remove this as a manual step.
Oh and is it possible to dynamically change the path based on the month and
year, for example create a new folder for the month of december to put all
the reports for that month in.
thanks for any help!This is not currently supported. You would need to write your own delivery
extension to do this. Delivery extensions are fairly easy to write, you
should look at the Printer Delivery Sample for how to get started.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:EA07C8FB-287E-48B6-94E5-ABA172763330@.microsoft.com...
> Is it possible for reporting services to save a report to a web folder or
> more specifically WSS/SharePoint when you define a subscription? We save
> all
> our reports on a WSS site and we would like to remove this as a manual
> step.
> Oh and is it possible to dynamically change the path based on the month
> and
> year, for example create a new folder for the month of december to put all
> the reports for that month in.
> thanks for any help!|||Where would I find the printer delivery sample?
Thanks for the help!!
"Daniel Reib [MSFT]" wrote:
> This is not currently supported. You would need to write your own delivery
> extension to do this. Delivery extensions are fairly easy to write, you
> should look at the Printer Delivery Sample for how to get started.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:EA07C8FB-287E-48B6-94E5-ABA172763330@.microsoft.com...
> > Is it possible for reporting services to save a report to a web folder or
> > more specifically WSS/SharePoint when you define a subscription? We save
> > all
> > our reports on a WSS site and we would like to remove this as a manual
> > step.
> >
> > Oh and is it possible to dynamically change the path based on the month
> > and
> > year, for example create a new folder for the month of december to put all
> > the reports for that month in.
> >
> > thanks for any help!
>
>|||That depends on the version of RS you are using. Both are included in setup.
You can find more info here:
RS 2005:
http://msdn2.microsoft.com/library/ms160954(en-us,SQL.90).aspx
RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_3t69.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:92B40DFD-55CB-4821-9B75-BE35E8C577E5@.microsoft.com...
> Where would I find the printer delivery sample?
> Thanks for the help!!
> "Daniel Reib [MSFT]" wrote:
>> This is not currently supported. You would need to write your own
>> delivery
>> extension to do this. Delivery extensions are fairly easy to write, you
>> should look at the Printer Delivery Sample for how to get started.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:EA07C8FB-287E-48B6-94E5-ABA172763330@.microsoft.com...
>> > Is it possible for reporting services to save a report to a web folder
>> > or
>> > more specifically WSS/SharePoint when you define a subscription? We
>> > save
>> > all
>> > our reports on a WSS site and we would like to remove this as a manual
>> > step.
>> >
>> > Oh and is it possible to dynamically change the path based on the month
>> > and
>> > year, for example create a new folder for the month of december to put
>> > all
>> > the reports for that month in.
>> >
>> > thanks for any help!
>>
saving time in snapshot?
We are using SQL 2K with sp4 and using push replication.
if I set up the push replication for the very first time, is there a way to
save the time for the initial snapshot generation of all tables by having the
subcriber database popuated with tables using backup and restore method from
the publishing dataabase? Then, can I make replication think that two
databases are in syn already (which it is) and start replicate changes from
this point on via the log reader?
wingman
Yes - this is referred to as a nosync initialization. Please see this
article for more info:
http://www.replicationanswers.com/No...alizations.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
if I set up the push replication for the very first time, is there a way to
save the time for the initial snapshot generation of all tables by having the
subcriber database popuated with tables using backup and restore method from
the publishing dataabase? Then, can I make replication think that two
databases are in syn already (which it is) and start replicate changes from
this point on via the log reader?
wingman
Yes - this is referred to as a nosync initialization. Please see this
article for more info:
http://www.replicationanswers.com/No...alizations.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Saving Time in 12 hour format
Hi,
By default in SQL Server, when I store a time in an Datetime field, it
stores in 24 hour format. but I want to store in 12 hours format with AM/PM
also being stored.
Can anyone help me in this.
Thanks & Regards
Sudhakara.T.P.Its is not important how the data is stored in the database. all that
metters is how u retreive the data.
if u want to retrive with AM and PM format: u can use
CONVERT(varchar(20), getdate(), 100)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi,
> By default in SQL Server, when I store a time in an Datetime field, it
> stores in 24 hour format. but I want to store in 12 hours format with AM/P
M
> also being stored.
> Can anyone help me in this.
> Thanks & Regards
> Sudhakara.T.P.|||Hi,
I am very much aware of the fact of retreiving the records in the format
that I want, but I am very much interested in storing the time data in 12
hour format itself as this is one of my prime requirement.
"Chandra" wrote:
> Its is not important how the data is stored in the database. all that
> metters is how u retreive the data.
> if u want to retrive with AM and PM format: u can use
> CONVERT(varchar(20), getdate(), 100)
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sudhakara.T.P." wrote:
>|||SQL Server does not store datetime values in any user specified format,
but in an internal format.
So showing the AM/PM is basically a formatting issue.
You can use the CONVERT function to do the same.
Have a look at
http://www.karaszi.com/sqlserver/info_datetime.asp
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:BB0D056C-CA8C-4D59-9B97-EDB5B3CBE88E@.microsoft.com...
> Hi,
> By default in SQL Server, when I store a time in an Datetime field, it
> stores in 24 hour format. but I want to store in 12 hours format with
> AM/PM
> also being stored.
> Can anyone help me in this.
> Thanks & Regards
> Sudhakara.T.P.|||then create a varchar field for this instead of datetime field. hope this
can solve the purpose.
may i know the prime reason behind storing as that format. probably i can
give u a suggestion
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi,
> I am very much aware of the fact of retreiving the records in the format
> that I want, but I am very much interested in storing the time data in 12
> hour format itself as this is one of my prime requirement.
> "Chandra" wrote:
>|||Hi Chandra,
thanks for the reply.
Well for one of the project that we are developing for the client, it is his
prime requirement to see the datetime record saved in the database in 12 hou
r
format as the db administrator is the client himself and he needs to open th
e
database and check the records frequently for some of his purposes[which he
has not revealed the reason].
I am freelance programmer and this is his first requirement/condition in the
project.
Regards
Sudhakara.T.P.|||then for this purpose, i prefer using a view and those who want to check can
use a view instead of referrig directly to a table
pleas let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi Chandra,
> thanks for the reply.
> Well for one of the project that we are developing for the client, it is h
is
> prime requirement to see the datetime record saved in the database in 12 h
our
> format as the db administrator is the client himself and he needs to open
the
> database and check the records frequently for some of his purposes[which h
e
> has not revealed the reason].
> I am freelance programmer and this is his first requirement/condition in t
he
> project.
> Regards
> Sudhakara.T.P.|||>> By default in SQL Server, when I store a time in an Datetime field [sic], it s
tores in 24 hour format. but I want to store in 12 hours format with AM/PM a
lso being stored. <<
Take your hands off the keyboard. Step away from the database. You
are dangerously ignorant.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the *many* differences in
a field and column is that a column has an abstract datatype and
meaning in and of itself. A field gets its meaning from the program
that reads it. This kind of question is the result of not knowing
these differences.
How a column is displayed in done in the host program and has nothing
WHATSOEVER to do with the internal format in the RDBMS. In the case of
T-SQL, it is a "floating point number" that counts clock ticks. To
display it as a string, you use CONVERT() and a format parameter.
Now, if you are a good SQL programmer who respects ISO standards you
use ISO-8601 formats only to avoid data exchange problems, ambigous
dates and other problems so you would never have AM/PM times in your
front end.
By default in SQL Server, when I store a time in an Datetime field, it
stores in 24 hour format. but I want to store in 12 hours format with AM/PM
also being stored.
Can anyone help me in this.
Thanks & Regards
Sudhakara.T.P.Its is not important how the data is stored in the database. all that
metters is how u retreive the data.
if u want to retrive with AM and PM format: u can use
CONVERT(varchar(20), getdate(), 100)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi,
> By default in SQL Server, when I store a time in an Datetime field, it
> stores in 24 hour format. but I want to store in 12 hours format with AM/P
M
> also being stored.
> Can anyone help me in this.
> Thanks & Regards
> Sudhakara.T.P.|||Hi,
I am very much aware of the fact of retreiving the records in the format
that I want, but I am very much interested in storing the time data in 12
hour format itself as this is one of my prime requirement.
"Chandra" wrote:
> Its is not important how the data is stored in the database. all that
> metters is how u retreive the data.
> if u want to retrive with AM and PM format: u can use
> CONVERT(varchar(20), getdate(), 100)
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sudhakara.T.P." wrote:
>|||SQL Server does not store datetime values in any user specified format,
but in an internal format.
So showing the AM/PM is basically a formatting issue.
You can use the CONVERT function to do the same.
Have a look at
http://www.karaszi.com/sqlserver/info_datetime.asp
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:BB0D056C-CA8C-4D59-9B97-EDB5B3CBE88E@.microsoft.com...
> Hi,
> By default in SQL Server, when I store a time in an Datetime field, it
> stores in 24 hour format. but I want to store in 12 hours format with
> AM/PM
> also being stored.
> Can anyone help me in this.
> Thanks & Regards
> Sudhakara.T.P.|||then create a varchar field for this instead of datetime field. hope this
can solve the purpose.
may i know the prime reason behind storing as that format. probably i can
give u a suggestion
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi,
> I am very much aware of the fact of retreiving the records in the format
> that I want, but I am very much interested in storing the time data in 12
> hour format itself as this is one of my prime requirement.
> "Chandra" wrote:
>|||Hi Chandra,
thanks for the reply.
Well for one of the project that we are developing for the client, it is his
prime requirement to see the datetime record saved in the database in 12 hou
r
format as the db administrator is the client himself and he needs to open th
e
database and check the records frequently for some of his purposes[which he
has not revealed the reason].
I am freelance programmer and this is his first requirement/condition in the
project.
Regards
Sudhakara.T.P.|||then for this purpose, i prefer using a view and those who want to check can
use a view instead of referrig directly to a table
pleas let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi Chandra,
> thanks for the reply.
> Well for one of the project that we are developing for the client, it is h
is
> prime requirement to see the datetime record saved in the database in 12 h
our
> format as the db administrator is the client himself and he needs to open
the
> database and check the records frequently for some of his purposes[which h
e
> has not revealed the reason].
> I am freelance programmer and this is his first requirement/condition in t
he
> project.
> Regards
> Sudhakara.T.P.|||>> By default in SQL Server, when I store a time in an Datetime field [sic], it s
tores in 24 hour format. but I want to store in 12 hours format with AM/PM a
lso being stored. <<
Take your hands off the keyboard. Step away from the database. You
are dangerously ignorant.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the *many* differences in
a field and column is that a column has an abstract datatype and
meaning in and of itself. A field gets its meaning from the program
that reads it. This kind of question is the result of not knowing
these differences.
How a column is displayed in done in the host program and has nothing
WHATSOEVER to do with the internal format in the RDBMS. In the case of
T-SQL, it is a "floating point number" that counts clock ticks. To
display it as a string, you use CONVERT() and a format parameter.
Now, if you are a good SQL programmer who respects ISO standards you
use ISO-8601 formats only to avoid data exchange problems, ambigous
dates and other problems so you would never have AM/PM times in your
front end.
Saving Time Format in SQL
Dear all,
I have a table in which I have a field of type datetime.
If I insert a time value through a SP, SQL adds automatically a default
date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
and if I manually edit/insert a value from the result pane, the data is
'8:30 AM' as I want it.
My question is: How to save only the time in the table via SP?
Thanks in advance.
jouj.> My question is: How to save only the time in the table via SP?
Impossible in the datetime datatype. EM is fooling you and will put in date
1899-12-31 (I think) and
for some curious reason not display that date. Do a SELECT from Query Analyz
er and you will see. I
suggest you read this article to de-mystify the datetime datatypes in SQL Se
rver:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> Dear all,
> I have a table in which I have a field of type datetime.
> If I insert a time value through a SP, SQL adds automatically a default
> date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> and if I manually edit/insert a value from the result pane, the data is
> '8:30 AM' as I want it.
> My question is: How to save only the time in the table via SP?
> Thanks in advance.
> jouj.
>|||Thank you Mr. Karaszi.
jouj
"Tibor Karaszi" wrote:
> Impossible in the datetime datatype. EM is fooling you and will put in dat
e 1899-12-31 (I think) and
> for some curious reason not display that date. Do a SELECT from Query Anal
yzer and you will see. I
> suggest you read this article to de-mystify the datetime datatypes in SQL
Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
>
I have a table in which I have a field of type datetime.
If I insert a time value through a SP, SQL adds automatically a default
date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
and if I manually edit/insert a value from the result pane, the data is
'8:30 AM' as I want it.
My question is: How to save only the time in the table via SP?
Thanks in advance.
jouj.> My question is: How to save only the time in the table via SP?
Impossible in the datetime datatype. EM is fooling you and will put in date
1899-12-31 (I think) and
for some curious reason not display that date. Do a SELECT from Query Analyz
er and you will see. I
suggest you read this article to de-mystify the datetime datatypes in SQL Se
rver:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> Dear all,
> I have a table in which I have a field of type datetime.
> If I insert a time value through a SP, SQL adds automatically a default
> date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> and if I manually edit/insert a value from the result pane, the data is
> '8:30 AM' as I want it.
> My question is: How to save only the time in the table via SP?
> Thanks in advance.
> jouj.
>|||Thank you Mr. Karaszi.
jouj
"Tibor Karaszi" wrote:
> Impossible in the datetime datatype. EM is fooling you and will put in dat
e 1899-12-31 (I think) and
> for some curious reason not display that date. Do a SELECT from Query Anal
yzer and you will see. I
> suggest you read this article to de-mystify the datetime datatypes in SQL
Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
>
Saving Time Format in SQL
Dear all,
I have a table in which I have a field of type datetime.
If I insert a time value through a SP, SQL adds automatically a default
date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
and if I manually edit/insert a value from the result pane, the data is
'8:30 AM' as I want it.
My question is: How to save only the time in the table via SP?
Thanks in advance.
jouj.> My question is: How to save only the time in the table via SP?
Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
suggest you read this article to de-mystify the datetime datatypes in SQL Server:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> Dear all,
> I have a table in which I have a field of type datetime.
> If I insert a time value through a SP, SQL adds automatically a default
> date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> and if I manually edit/insert a value from the result pane, the data is
> '8:30 AM' as I want it.
> My question is: How to save only the time in the table via SP?
> Thanks in advance.
> jouj.
>|||Thank you Mr. Karaszi.
jouj
"Tibor Karaszi" wrote:
> > My question is: How to save only the time in the table via SP?
> Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
> for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
> suggest you read this article to de-mystify the datetime datatypes in SQL Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> > Dear all,
> > I have a table in which I have a field of type datetime.
> > If I insert a time value through a SP, SQL adds automatically a default
> > date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> > and if I manually edit/insert a value from the result pane, the data is
> > '8:30 AM' as I want it.
> > My question is: How to save only the time in the table via SP?
> >
> > Thanks in advance.
> > jouj.
> >
>
I have a table in which I have a field of type datetime.
If I insert a time value through a SP, SQL adds automatically a default
date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
and if I manually edit/insert a value from the result pane, the data is
'8:30 AM' as I want it.
My question is: How to save only the time in the table via SP?
Thanks in advance.
jouj.> My question is: How to save only the time in the table via SP?
Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
suggest you read this article to de-mystify the datetime datatypes in SQL Server:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> Dear all,
> I have a table in which I have a field of type datetime.
> If I insert a time value through a SP, SQL adds automatically a default
> date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> and if I manually edit/insert a value from the result pane, the data is
> '8:30 AM' as I want it.
> My question is: How to save only the time in the table via SP?
> Thanks in advance.
> jouj.
>|||Thank you Mr. Karaszi.
jouj
"Tibor Karaszi" wrote:
> > My question is: How to save only the time in the table via SP?
> Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
> for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
> suggest you read this article to de-mystify the datetime datatypes in SQL Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> > Dear all,
> > I have a table in which I have a field of type datetime.
> > If I insert a time value through a SP, SQL adds automatically a default
> > date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> > and if I manually edit/insert a value from the result pane, the data is
> > '8:30 AM' as I want it.
> > My question is: How to save only the time in the table via SP?
> >
> > Thanks in advance.
> > jouj.
> >
>
Saving Time Format in SQL
Dear all,
I have a table in which I have a field of type datetime.
If I insert a time value through a SP, SQL adds automatically a default
date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
and if I manually edit/insert a value from the result pane, the data is
'8:30 AM' as I want it.
My question is: How to save only the time in the table via SP?
Thanks in advance.
jouj.
> My question is: How to save only the time in the table via SP?
Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
suggest you read this article to de-mystify the datetime datatypes in SQL Server:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> Dear all,
> I have a table in which I have a field of type datetime.
> If I insert a time value through a SP, SQL adds automatically a default
> date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> and if I manually edit/insert a value from the result pane, the data is
> '8:30 AM' as I want it.
> My question is: How to save only the time in the table via SP?
> Thanks in advance.
> jouj.
>
|||Thank you Mr. Karaszi.
jouj
"Tibor Karaszi" wrote:
> Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
> for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
> suggest you read this article to de-mystify the datetime datatypes in SQL Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
>
I have a table in which I have a field of type datetime.
If I insert a time value through a SP, SQL adds automatically a default
date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
and if I manually edit/insert a value from the result pane, the data is
'8:30 AM' as I want it.
My question is: How to save only the time in the table via SP?
Thanks in advance.
jouj.
> My question is: How to save only the time in the table via SP?
Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
suggest you read this article to de-mystify the datetime datatypes in SQL Server:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
> Dear all,
> I have a table in which I have a field of type datetime.
> If I insert a time value through a SP, SQL adds automatically a default
> date.(instead of '8:30 AM' it inserts '1/1/1900 8:30 AM')
> and if I manually edit/insert a value from the result pane, the data is
> '8:30 AM' as I want it.
> My question is: How to save only the time in the table via SP?
> Thanks in advance.
> jouj.
>
|||Thank you Mr. Karaszi.
jouj
"Tibor Karaszi" wrote:
> Impossible in the datetime datatype. EM is fooling you and will put in date 1899-12-31 (I think) and
> for some curious reason not display that date. Do a SELECT from Query Analyzer and you will see. I
> suggest you read this article to de-mystify the datetime datatypes in SQL Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:E8F456B7-C962-4B6F-A321-43852876E0F3@.microsoft.com...
>
Saving the username and password in the DSN
I am wrting a vb application that connects to an MSDE database. I am using
ADO for the data objects and an OBDC DSN entry for connecting to the
database. Is there a way to store the username and password in the DSN entry
so that I do not need to supply this in code?
Alternatively, is there a simple, fast and efficient way to encrypt a
connection string if I cannot store this in the database. I do not want to
store the connection string in the code. It is too limiting.
Thanks.
For security reasons, the user name and password are not stored in DSN's.
Anyone can access this information via the registry (for example,
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\DSN_Test1 23)
Copying the user name and password to the DSN would allow anyone to see
this information.
I don't think drivers even try to read this information from the DSN's even
if you did store it there.
I'm pretty sure you need to enter this information every time you connect.
sql
ADO for the data objects and an OBDC DSN entry for connecting to the
database. Is there a way to store the username and password in the DSN entry
so that I do not need to supply this in code?
Alternatively, is there a simple, fast and efficient way to encrypt a
connection string if I cannot store this in the database. I do not want to
store the connection string in the code. It is too limiting.
Thanks.
For security reasons, the user name and password are not stored in DSN's.
Anyone can access this information via the registry (for example,
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\DSN_Test1 23)
Copying the user name and password to the DSN would allow anyone to see
this information.
I don't think drivers even try to read this information from the DSN's even
if you did store it there.
I'm pretty sure you need to enter this information every time you connect.
sql
Saving the username and password in the DSN
I am wrting a vb application that connects to an MSDE database. I am using
ADO for the data objects and an OBDC DSN entry for connecting to the
database. Is there a way to store the username and password in the DSN entry
so that I do not need to supply this in code?
Alternatively, is there a simple, fast and efficient way to encrypt a
connection string if I cannot store this in the database. I do not want to
store the connection string in the code. It is too limiting.
Thanks.For security reasons, the user name and password are not stored in DSN's.
Anyone can access this information via the registry (for example,
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\DSN_Test123)
Copying the user name and password to the DSN would allow anyone to see
this information.
I don't think drivers even try to read this information from the DSN's even
if you did store it there.
I'm pretty sure you need to enter this information every time you connect.
ADO for the data objects and an OBDC DSN entry for connecting to the
database. Is there a way to store the username and password in the DSN entry
so that I do not need to supply this in code?
Alternatively, is there a simple, fast and efficient way to encrypt a
connection string if I cannot store this in the database. I do not want to
store the connection string in the code. It is too limiting.
Thanks.For security reasons, the user name and password are not stored in DSN's.
Anyone can access this information via the registry (for example,
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\DSN_Test123)
Copying the user name and password to the DSN would allow anyone to see
this information.
I don't think drivers even try to read this information from the DSN's even
if you did store it there.
I'm pretty sure you need to enter this information every time you connect.
Saving the time with the date
I have a calendar control on a page that is saving the date to a SQL Server 2005 table and this works fine; but how do I also save it with the current time? It is always saving as 12:00:00 AM.
Thanks for the help.
Check your calendar control properties because it probably is setup to display/send only date. In this case SQL server add dfault time for date which is 12 AM.
It should have options like date and time, date only, time only.
Thanks
|||
It seems odd you would use a date from a calendar and want the current time but...
DateTime DateOnly = new DateTime(2007, 5, 3);
DateTime RightNow = DateTime.Now;
// add current time to date
DateOnly = DateOnly.AddHours(RightNow.Hour);
DateOnly = DateOnly.AddMinutes(RightNow.Minute);
DateOnly = DateOnly.AddSeconds(RightNow.Second);
This is an intranet application where our CSRs record incoming items and the time stamp needs to be with the record. I solved it by putting System.DateTime.Now() into a Session variable.
Thanks for the responses. Now if I can figure out my "Access is Denied" problem with ajax...
Saving the server report at a default location automatically
I have created a server report that has been deployed on a server. I have written some code in VS 2005 that displays the report in a report viewer and automatically prints the report.
Now, instead of printing the report, I want to save it at some default location. However, it shows me a Save dialog box for which the manual interference is required. I want to run the report with several parameters, one by one, and want to automate it.
Can anyone tell how to give the default location or get the handle for save dialog box?
Thanks in advance!!!
Have you looked into setting up a schedule for the report and utilizing the delivery functionality that is already built into the product?
If that is not an option for you, and you just want to export the report programmatically, you can use the SOAP APIs to render the report and save it to disk.
Check out this article from SQL Books Online. It details the approaches you can use from Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms157214.aspx
Saving the ReportClientDocument
Hi,
I am not able to save a port which is created using ReportClientDocument. I am using the following Code.
m_crReportDocument = new ReportClientDocument();
Object path = new object();
path = "D:\\";
m_crReportDocument.SaveAs("Temp.rpt", ref path, 1);
It gives the following error
Access denied. Please check directory setting for files you can access.
System.Runtime.InteropServices.COMException: Access denied. Please check directory setting for files you can access.
I gave permissions to D: drive for LocalComputer\ASPNET also. Even I Tried giving "D:\\temp.rpt" for Path
Any Ideas?
Thanks
RameshI always access paths using a single backslash
'D:\temp.rpt' instead of 'D:\\temp.rpt'|||I am using C# not VB.NET. So I have to use D:\\
I am not able to save a port which is created using ReportClientDocument. I am using the following Code.
m_crReportDocument = new ReportClientDocument();
Object path = new object();
path = "D:\\";
m_crReportDocument.SaveAs("Temp.rpt", ref path, 1);
It gives the following error
Access denied. Please check directory setting for files you can access.
System.Runtime.InteropServices.COMException: Access denied. Please check directory setting for files you can access.
I gave permissions to D: drive for LocalComputer\ASPNET also. Even I Tried giving "D:\\temp.rpt" for Path
Any Ideas?
Thanks
RameshI always access paths using a single backslash
'D:\temp.rpt' instead of 'D:\\temp.rpt'|||I am using C# not VB.NET. So I have to use D:\\
Saving the Full Resultset of Execute Sql Task directly into Sql Server 2005 table
Hi friends,
I couldn't find links for this issue.
1) How to write the contents of a dataset or a full resultset (from execute sql task) directly into a Sql Server 2005 table.
2) Since I have hundreds of Resulting columns, I want to create the Destination table based on the structure of the dataset.
How can we achieve this?
Thanks
Subhash Subramanyam
Seems like it would be easiest to do it within SQL Server instead of SSIS. Probably by using a SELECT INTO statement in your Execute SQL Task. If you needed the resultset in SSIS, you could read it from your newly created table.|||
Thanks for your reply JayH. I think you did not get my entire scenario.
1) I am using Oracle Source and Sql Server Destination
2) After fetching results of a query from oracle in the form of Full result Set, I use a Script task in which I add few computed columns thus finally storing everything into a DataTable
3) Now this DataTable has to be written into a Sql Server table which has to be created if not present, and it should have the structure of the Datatable.
Thanks
Subhash Subramanyam
|||
Subhash512525 wrote:
Thanks for your reply JayH. I think you did not get my entire scenario.
1) I am using Oracle Source and Sql Server Destination
2) After fetching results of a query from oracle in the form of Full result Set, I use a Script task in which I add few computed columns thus finally storing everything into a DataTable
3) Now this DataTable has to be written into a Sql Server table which has to be created if not present, and it should have the structure of the Datatable.
Thanks
Subhash Subramanyam
In that case you'll have to do it all in a script task. SSIS does not provide any mechanism to do what you want, and I don't think ADO.NET does either, though this is really more of an ADO.NET programming question now than an SSIS question.
I think you'll need to iterate through your columns to create a CREATE TABLE statement to create the table. Then you can iterate through the rows to create INSERT INTO statements. All of which would be executed inside the script.
sql
saving the database
hi,
I am using SQLSERVER 2000. I had installed a trial version from microsoft. Now, it is about to expire. But i am not done yet with my project. So, I want to reinstall the trial version. But i don't know how can i save my current database, and transfer it to new database, that i will install.
Kindly, explain it in detail, as I am new to database stuff.
ThanksI am not sure if ms will allow you to uninstall/install the trial edition ... Anyway, you can detach and copy the database (then reattach when sql server is reinstalled) or backup/restore.
In Enterprise manager, right-click on the database -> All Tasks - In all tasks you will see Backup Database and Detach Database. For backup, follow the wizard, and back the db up to the hard disk. Later, right-click again and restore. For detach, once you have detached then copy the db to another name or directory. Then click on Databases and attach the database.
I am using SQLSERVER 2000. I had installed a trial version from microsoft. Now, it is about to expire. But i am not done yet with my project. So, I want to reinstall the trial version. But i don't know how can i save my current database, and transfer it to new database, that i will install.
Kindly, explain it in detail, as I am new to database stuff.
ThanksI am not sure if ms will allow you to uninstall/install the trial edition ... Anyway, you can detach and copy the database (then reattach when sql server is reinstalled) or backup/restore.
In Enterprise manager, right-click on the database -> All Tasks - In all tasks you will see Backup Database and Detach Database. For backup, follow the wizard, and back the db up to the hard disk. Later, right-click again and restore. For detach, once you have detached then copy the db to another name or directory. Then click on Databases and attach the database.
Saving Text with special character into SQL Express table
Hi, fellows!
When I need to save a text into a SQL Express table I use the character ' to encote the text. (ex: 'myText' )
How to procedure when the text has already the character ' ? (ex: how to enconte the text Color's car ) ?
Many thanks to any kind of help!
Using a INSERT / UPDATE Script you will have to double quote it:INSERT INTO SomeTable(SomeColumn)
VALUES('Jens''s')
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Many thanks, Jens!
Saving text to MS-SQL...
If you use the command UPDATE <tablename> SET <fieldname> = ' ... ' to
update a CHAR or TEXT field there are some characters that can cause
problems such as a single quote. Are there any other type of charaters like
that?
update a CHAR or TEXT field there are some characters that can cause
problems such as a single quote. Are there any other type of charaters like
that?
TIA"The Eeediot" <eeediot@.hotmail.com> wrote in message
news:S-ydnff_Qu2V6cncRVn-gw@.giganews.com...
> If you use the command UPDATE <tablename> SET <fieldname> = ' ... ' to
> update a CHAR or TEXT field there are some characters that can cause
> problems such as a single quote. Are there any other type of charaters
> like
> that?
>
> TIA
Check out "Using char and varchar Data" in Books Online.
The single quote is only an issue because it's the MSSQL string terminator,
so it needs to be doubled to escape it. Apart from that, there shouldn't be
a problem unless you're storing characters which aren't available in the
collation for your column, or non-ASCII characters such as Arabic or
Chinese. If this is a concern, you can use nchar or ntext to store the data
as Unicode.
Simon
Saving Tags on Uploaded Content
Hi.
I am wondering if somebody could give me some advice on saving
tags (such as "art", "picasso", "guernica", "spain", "spanish civil
war", "cubism", for an uploaded image of picasso's Guernica painting).
How do these terms get saved to the database? Do
they live together as single TEXT (or varchar)? Do they get parsed
before insertion? How do they get searched?
I am guessing that much has been said about this topic so, if
you don't feel like getting into it here (although it would be
appreciated), maybe somebody could suggest a well-known resource/
link?
I have come up with a few links after a google search, most
directly:
http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html
Thanks.
Peter> I am wondering if somebody could give me some advice on saving
> tags (such as "art", "picasso", "guernica", "spain", "spanish civil
> war", "cubism", for an uploaded image of picasso's Guernica painting).
I would make a Tag table, and a tag_image relations table.
If image 1 is uploaded with 2 tags, then the 2 tags are saved in the tag
table, and the image in the image table - and then two rows are inserted in
the tag_image table - indikating that image 1 is relatet to tag 1 and that
image 1 is related to tag 2.
Of course you should tjeck if the given tag already exists in the tag table,
an then reuse that id instead.
table: image
id,
imagedata
table: tag
id,
tagName
table: tag_image
imageId,
tagId
The above should show the idea.
/Sjang|||On Sep 11, 11:23 pm, "Henrik Davidsen" <n...@.none.dk> wrote:
> > I am wondering if somebody could give me some advice on saving
> > tags (such as "art", "picasso", "guernica", "spain", "spanish civil
> > war", "cubism", for an uploaded image of picasso's Guernica painting).
> I would make a Tag table, and a tag_image relations table.
> If image 1 is uploaded with 2 tags, then the 2 tags are saved in the tag
> table, and the image in the image table - and then two rows are inserted in
> the tag_image table - indikating that image 1 is relatet to tag 1 and that
> image 1 is related to tag 2.
> Of course you should tjeck if the given tag already exists in the tag table,
> an then reuse that id instead.
> table: image
> id,
> imagedata
> table: tag
> id,
> tagName
> table: tag_image
> imageId,
> tagId
> The above should show the idea.
> /Sjang
OK, thanks. I was a bit confused because
I read in places that the tag string should be inserted as a whole
which
seems confusing to me. Your suggestion makes perfect sense.
Thanks.
I am wondering if somebody could give me some advice on saving
tags (such as "art", "picasso", "guernica", "spain", "spanish civil
war", "cubism", for an uploaded image of picasso's Guernica painting).
How do these terms get saved to the database? Do
they live together as single TEXT (or varchar)? Do they get parsed
before insertion? How do they get searched?
I am guessing that much has been said about this topic so, if
you don't feel like getting into it here (although it would be
appreciated), maybe somebody could suggest a well-known resource/
link?
I have come up with a few links after a google search, most
directly:
http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html
Thanks.
Peter> I am wondering if somebody could give me some advice on saving
> tags (such as "art", "picasso", "guernica", "spain", "spanish civil
> war", "cubism", for an uploaded image of picasso's Guernica painting).
I would make a Tag table, and a tag_image relations table.
If image 1 is uploaded with 2 tags, then the 2 tags are saved in the tag
table, and the image in the image table - and then two rows are inserted in
the tag_image table - indikating that image 1 is relatet to tag 1 and that
image 1 is related to tag 2.
Of course you should tjeck if the given tag already exists in the tag table,
an then reuse that id instead.
table: image
id,
imagedata
table: tag
id,
tagName
table: tag_image
imageId,
tagId
The above should show the idea.
/Sjang|||On Sep 11, 11:23 pm, "Henrik Davidsen" <n...@.none.dk> wrote:
> > I am wondering if somebody could give me some advice on saving
> > tags (such as "art", "picasso", "guernica", "spain", "spanish civil
> > war", "cubism", for an uploaded image of picasso's Guernica painting).
> I would make a Tag table, and a tag_image relations table.
> If image 1 is uploaded with 2 tags, then the 2 tags are saved in the tag
> table, and the image in the image table - and then two rows are inserted in
> the tag_image table - indikating that image 1 is relatet to tag 1 and that
> image 1 is related to tag 2.
> Of course you should tjeck if the given tag already exists in the tag table,
> an then reuse that id instead.
> table: image
> id,
> imagedata
> table: tag
> id,
> tagName
> table: tag_image
> imageId,
> tagId
> The above should show the idea.
> /Sjang
OK, thanks. I was a bit confused because
I read in places that the tag string should be inserted as a whole
which
seems confusing to me. Your suggestion makes perfect sense.
Thanks.
Saving subscription reports in database
I was wondering if it was possible to have a Subscription report write back
into a table on the database'
Thanks in advance,
John Scott.it sure would be nice.. but I dont' think that anyone in Redmond has to
actually eat their own dogfood anymore
I mean.. that is a total no-brainer
I would use it ALL THE FRIGGIN TIME.
John Scott wrote:
> I was wondering if it was possible to have a Subscription report write back
> into a table on the database'
>
> Thanks in advance,
> John Scott.
into a table on the database'
Thanks in advance,
John Scott.it sure would be nice.. but I dont' think that anyone in Redmond has to
actually eat their own dogfood anymore
I mean.. that is a total no-brainer
I would use it ALL THE FRIGGIN TIME.
John Scott wrote:
> I was wondering if it was possible to have a Subscription report write back
> into a table on the database'
>
> Thanks in advance,
> John Scott.
Saving Stored Procedure
Hello,
I installed SQL Server 2005 on my PC and am using it to access a SQL
database running on a remote server. Using SQL Server Management Studio on
my PC, I connect to the remote database using SQL Server Authentication and
an account that has database owner permissions. At least I think it does -
when I go to the database, then Security / Roles / Database Roles / db_owner
,
it is listed as a role member there.
Once connected, I can change data in tables and view the stored procedures.
However, when I modify one of the stored procedures (by right clicking on it
in Object Explorer, then clicking Modify, then changing it), and then attemp
t
to save the changes, a window pops up on my screen asking me to identify the
location where I want to save the changed stored procedure. It defaults to
a
folder on my local hard drive: My Documents/SQL Server Management
Studio/Projects.
I just want to save the changes in the stored procedure on the remote
database (the master version). Shouldn't I be able to do that? If so, how?
By the way, when I click Modify to view the stored procedure, it's name, at
the top of the window where the T-SQL text is, reads: the address of the
server…SQLQuery1.sql (instead of it's actual name) - for example it reads:
100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
Thanks in advance for help.SSMS opens a Query Window for you when you select to modify the stored proce
dure. Same thing as if
you right-click the server and say "New Query". SSMS also pastes the source
code for the proc in
that query window. As with Word, for instance, a new document has some stand
ard name and when you
select "save" Word (or SSMS) ask you for how you want to name that file.
If you want to execute the ALTER PROCEDURE statement to actually change the
procedure, just execute
the code in the query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio o
n
> my PC, I connect to the remote database using SQL Server Authentication an
d
> an account that has database owner permissions. At least I think it does
-
> when I go to the database, then Security / Roles / Database Roles / db_own
er,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored procedures
.
> However, when I modify one of the stored procedures (by right clicking on
it
> in Object Explorer, then clicking Modify, then changing it), and then atte
mpt
> to save the changes, a window pops up on my screen asking me to identify t
he
> location where I want to save the changed stored procedure. It defaults t
o a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so, ho
w?
> By the way, when I click Modify to view the stored procedure, it's name, a
t
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it read
s:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||You don't use the Save button. Use the Execute button (or press F5). This
runs the ALTER PROCEDURE script you have modified. The Save button is for
saving scripts on your machine, not for saving changes to the database...
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio
> on
> my PC, I connect to the remote database using SQL Server Authentication
> and
> an account that has database owner permissions. At least I think it
> does -
> when I go to the database, then Security / Roles / Database Roles /
> db_owner,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored
> procedures.
> However, when I modify one of the stored procedures (by right clicking on
> it
> in Object Explorer, then clicking Modify, then changing it), and then
> attempt
> to save the changes, a window pops up on my screen asking me to identify
> the
> location where I want to save the changed stored procedure. It defaults
> to a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
> By the way, when I click Modify to view the stored procedure, it's name,
> at
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it
> reads:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||conmat (conmat@.discussions.microsoft.com) writes:
> However, when I modify one of the stored procedures (by right clicking
> on it in Object Explorer, then clicking Modify, then changing it), and
> then attempt to save the changes, a window pops up on my screen asking
> me to identify the location where I want to save the changed stored
> procedure. It defaults to a folder on my local hard drive: My
> Documents/SQL Server Management Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
But you should really save the code to disk and put it under version
control. The datbase should just be viewed as a binary repository.
Of course, as long as you are only playing around, the database may do,
but it's not suitable for real application development.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help.
I don't think I understand the difference between:
1) Modifying the script in a stored procedure on the database without
actually running the content / code of the procedure
versus
2) Running the actual content / code of the procedure.
If I right click on a stored procedure, choose modify, the script of the
procedure pops up in the Query Window(?), and it starts with:
ALTER PROCEDURE ____
AS
If I then modify the script, then Execute, does that just change the script
without actually running the content (I think the answer is yes, if I
understand you correctly)?
If that is so, how do I then actually execute the content - do I need to
remove the ALTER PROCEDURE___ AS, then Execute (but, how do I remove the
ALTER PROCEDURE without altering the procedure)?
I'm very new to SQL Sever, as you can tell - thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You don't use the Save button. Use the Execute button (or press F5). Thi
s
> runs the ALTER PROCEDURE script you have modified. The Save button is for
> saving scripts on your machine, not for saving changes to the database...
>
>
> "conmat" <conmat@.discussions.microsoft.com> wrote in message
> news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
>
>|||> 1) Modifying the script in a stored procedure on the database without
> actually running the content / code of the procedure
> versus
This changes the code that SQL Server has for the stored procedure. This
means it will change what is stored, and will change what is executed every
time anyone calls the stored procedure.
> 2) Running the actual content / code of the procedure.
This merely runs the code, once.
Consider:
CREATE PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 1;
END
GO
When you run this code, it creates a procedure. It does not run the SELECT
statement. When you *EXECUTE* the stored procedure:
EXEC dbo.foo;
You will see a single-column resultset with the value 1. Just like you
would have seen if you ran the code within the procedure, by itself:
SELECT a = 1;
Now, if you run this:
ALTER PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 2;
END
GO
Again, it does not run the SELECT statement, it changes the script that SQL
Server has stored for the procedure. So the next time someone executes the
stored procedure, they will get a single-column resultset with the value 2.
> If that is so, how do I then actually execute the content
Well, if you have already run the ALTER and you are happy with it, then like
above:
EXEC dbo.foo;
> I'm very new to SQL Sever, as you can tell - thanks again for your help.
I think there is plenty of material out there that you should familiarize
yourself with.
http://www.aspfaq.com/2423|||Hey Guys,
The Orginal question was how to save a modified stored procedure.
I read all your reponses but none addressed the issue.
I have a similar problem
Can anyone just simple show how to save a modified sp in sql server managem
ent studio.
Thanks|||> The Orginal question was how to save a modified stored procedure.
Save where? Do you want to create a file containing the TSQL code used to cr
eate that procedure? Or
do you want to execute the ALTER PROCEDURE ... command so you do modify that
procedure according to
the changes you have made?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sportrx" <sportrx.24qulq@.mail.codecomments.com> wrote in message
news:sportrx.24qulq@.mail.codecomments.com...
> Hey Guys,
> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
> I have a similar problem
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
> Thanks
>
> --
> sportrx
> ---
> Posted via http://www.codecomments.com
> ---
>|||> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
That's because the original question remains unclear.
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
What does "save a modified sp" mean? Do you want to apply the changes to
the database, save the script in a .PRC or .SQL file, check the changes into
source control, ...?
If you run an ALTER PROCEDURE script, that will apply the changes to the
stored procedure in the current database. If you want to save the script to
a file, click the Save icon. Those are two *very* different actions.
A
I installed SQL Server 2005 on my PC and am using it to access a SQL
database running on a remote server. Using SQL Server Management Studio on
my PC, I connect to the remote database using SQL Server Authentication and
an account that has database owner permissions. At least I think it does -
when I go to the database, then Security / Roles / Database Roles / db_owner
,
it is listed as a role member there.
Once connected, I can change data in tables and view the stored procedures.
However, when I modify one of the stored procedures (by right clicking on it
in Object Explorer, then clicking Modify, then changing it), and then attemp
t
to save the changes, a window pops up on my screen asking me to identify the
location where I want to save the changed stored procedure. It defaults to
a
folder on my local hard drive: My Documents/SQL Server Management
Studio/Projects.
I just want to save the changes in the stored procedure on the remote
database (the master version). Shouldn't I be able to do that? If so, how?
By the way, when I click Modify to view the stored procedure, it's name, at
the top of the window where the T-SQL text is, reads: the address of the
server…SQLQuery1.sql (instead of it's actual name) - for example it reads:
100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
Thanks in advance for help.SSMS opens a Query Window for you when you select to modify the stored proce
dure. Same thing as if
you right-click the server and say "New Query". SSMS also pastes the source
code for the proc in
that query window. As with Word, for instance, a new document has some stand
ard name and when you
select "save" Word (or SSMS) ask you for how you want to name that file.
If you want to execute the ALTER PROCEDURE statement to actually change the
procedure, just execute
the code in the query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio o
n
> my PC, I connect to the remote database using SQL Server Authentication an
d
> an account that has database owner permissions. At least I think it does
-
> when I go to the database, then Security / Roles / Database Roles / db_own
er,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored procedures
.
> However, when I modify one of the stored procedures (by right clicking on
it
> in Object Explorer, then clicking Modify, then changing it), and then atte
mpt
> to save the changes, a window pops up on my screen asking me to identify t
he
> location where I want to save the changed stored procedure. It defaults t
o a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so, ho
w?
> By the way, when I click Modify to view the stored procedure, it's name, a
t
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it read
s:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||You don't use the Save button. Use the Execute button (or press F5). This
runs the ALTER PROCEDURE script you have modified. The Save button is for
saving scripts on your machine, not for saving changes to the database...
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio
> on
> my PC, I connect to the remote database using SQL Server Authentication
> and
> an account that has database owner permissions. At least I think it
> does -
> when I go to the database, then Security / Roles / Database Roles /
> db_owner,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored
> procedures.
> However, when I modify one of the stored procedures (by right clicking on
> it
> in Object Explorer, then clicking Modify, then changing it), and then
> attempt
> to save the changes, a window pops up on my screen asking me to identify
> the
> location where I want to save the changed stored procedure. It defaults
> to a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
> By the way, when I click Modify to view the stored procedure, it's name,
> at
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it
> reads:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||conmat (conmat@.discussions.microsoft.com) writes:
> However, when I modify one of the stored procedures (by right clicking
> on it in Object Explorer, then clicking Modify, then changing it), and
> then attempt to save the changes, a window pops up on my screen asking
> me to identify the location where I want to save the changed stored
> procedure. It defaults to a folder on my local hard drive: My
> Documents/SQL Server Management Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
But you should really save the code to disk and put it under version
control. The datbase should just be viewed as a binary repository.
Of course, as long as you are only playing around, the database may do,
but it's not suitable for real application development.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help.
I don't think I understand the difference between:
1) Modifying the script in a stored procedure on the database without
actually running the content / code of the procedure
versus
2) Running the actual content / code of the procedure.
If I right click on a stored procedure, choose modify, the script of the
procedure pops up in the Query Window(?), and it starts with:
ALTER PROCEDURE ____
AS
If I then modify the script, then Execute, does that just change the script
without actually running the content (I think the answer is yes, if I
understand you correctly)?
If that is so, how do I then actually execute the content - do I need to
remove the ALTER PROCEDURE___ AS, then Execute (but, how do I remove the
ALTER PROCEDURE without altering the procedure)?
I'm very new to SQL Sever, as you can tell - thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You don't use the Save button. Use the Execute button (or press F5). Thi
s
> runs the ALTER PROCEDURE script you have modified. The Save button is for
> saving scripts on your machine, not for saving changes to the database...
>
>
> "conmat" <conmat@.discussions.microsoft.com> wrote in message
> news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
>
>|||> 1) Modifying the script in a stored procedure on the database without
> actually running the content / code of the procedure
> versus
This changes the code that SQL Server has for the stored procedure. This
means it will change what is stored, and will change what is executed every
time anyone calls the stored procedure.
> 2) Running the actual content / code of the procedure.
This merely runs the code, once.
Consider:
CREATE PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 1;
END
GO
When you run this code, it creates a procedure. It does not run the SELECT
statement. When you *EXECUTE* the stored procedure:
EXEC dbo.foo;
You will see a single-column resultset with the value 1. Just like you
would have seen if you ran the code within the procedure, by itself:
SELECT a = 1;
Now, if you run this:
ALTER PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 2;
END
GO
Again, it does not run the SELECT statement, it changes the script that SQL
Server has stored for the procedure. So the next time someone executes the
stored procedure, they will get a single-column resultset with the value 2.
> If that is so, how do I then actually execute the content
Well, if you have already run the ALTER and you are happy with it, then like
above:
EXEC dbo.foo;
> I'm very new to SQL Sever, as you can tell - thanks again for your help.
I think there is plenty of material out there that you should familiarize
yourself with.
http://www.aspfaq.com/2423|||Hey Guys,
The Orginal question was how to save a modified stored procedure.
I read all your reponses but none addressed the issue.
I have a similar problem
Can anyone just simple show how to save a modified sp in sql server managem
ent studio.
Thanks|||> The Orginal question was how to save a modified stored procedure.
Save where? Do you want to create a file containing the TSQL code used to cr
eate that procedure? Or
do you want to execute the ALTER PROCEDURE ... command so you do modify that
procedure according to
the changes you have made?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sportrx" <sportrx.24qulq@.mail.codecomments.com> wrote in message
news:sportrx.24qulq@.mail.codecomments.com...
> Hey Guys,
> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
> I have a similar problem
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
> Thanks
>
> --
> sportrx
> ---
> Posted via http://www.codecomments.com
> ---
>|||> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
That's because the original question remains unclear.
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
What does "save a modified sp" mean? Do you want to apply the changes to
the database, save the script in a .PRC or .SQL file, check the changes into
source control, ...?
If you run an ALTER PROCEDURE script, that will apply the changes to the
stored procedure in the current database. If you want to save the script to
a file, click the Save icon. Those are two *very* different actions.
A
Saving Store procedure on table
Hello there
I have some store procedure that return query as result.
Is there a way to store the result in table?"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
Maybe this will give you the information you need:
http://www.sommarskog.se/share_data.html|||INSERT INTO YourTable (Col1, Col2...) EXEC YourSp
Andrew J. Kelly SQL MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
>
>|||You can create the table upfront & use INSERT .. EXEC statement.
INSERT tbl ( ... ) EXEC usp @.param1, @.param2, ...
Alternatively, you can create a loopback and use OPENQUERY like:
EXEC sp_serveroption 'data access', true
GO
SELECT * INTO tbl
FROM OPENQUERY ( Server, 'SET FMTONLY OFF; EXEC.. ' )
Anith
I have some store procedure that return query as result.
Is there a way to store the result in table?"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
Maybe this will give you the information you need:
http://www.sommarskog.se/share_data.html|||INSERT INTO YourTable (Col1, Col2...) EXEC YourSp
Andrew J. Kelly SQL MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
>
>|||You can create the table upfront & use INSERT .. EXEC statement.
INSERT tbl ( ... ) EXEC usp @.param1, @.param2, ...
Alternatively, you can create a loopback and use OPENQUERY like:
EXEC sp_serveroption 'data access', true
GO
SELECT * INTO tbl
FROM OPENQUERY ( Server, 'SET FMTONLY OFF; EXEC.. ' )
Anith
Saving SSIS package connection passwords
Hello All,
I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.
If you search this forum for "connection password" you'll get plenty of results. Basically, SSIS does not save passwords in connection strings.Verify that your package property, ProtectionLevel, is set to EncryptSensitiveWithUserKey. If that doesn't work, then you can put the connection string for the Oracle database in a configuration file, of which you can manually update to contain the password. All of this is posted throughout this forum.|||
Hi,
Thanks for your reply. The thing about that is, that the package is owned by the OS user I use, but the Server Agent runs on another profile (System), so encrypting with a user key will make the Agent unable to execute de package. I wil check out the configuratio file thing, though.
|||Then you can do EncryptSensitiveWithPassword and specify the package password in SQL Agent by altering the dtexec command line parameters.|||I ran into your problem and my solution was to change the CreatorName property of the package to the account that SQL Agent runs under, and I also changed the CreatorComputerName to the name of the production server, then it finally workedsqlsaving SSIS package
Hi,
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!
SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!
SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>
saving SSIS package
Hi,
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>
saving SSIS package
Hi,
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>
Saving SQL Express Databases?
Hello,
I have Visual Studio 2005 Professional.
I was wondering if there were any way to save the .dbo databases created in it to upload to my website? Do the databases must be save as a specific filetype according to the website configuration?
Do most people actually connect to their site's database to modify it or something? Or do they usually connect to an external database and then upload it to their site?
Sorry, but I am kind of new to all of this.
Thanks,
Eng
I'm not very clear about your question. What do you mean by '.dbo databases'? Do you mean something like this: 'mySQLInstance.master.dbo'? This is database reference--not real database file; it is used to connect to database. Databases are always seperated from site, and you should connect to database to retrieve data that used in your site.Saving SQL 2000 Databases in SQL 7 format
Hi
I understand 2000 databases are not backward compatible
but is there any way to save 2000 databases in 7 format?
Is there an option?
Thanks
Tony
Hi,
You can set the user databases to SQL 7.0 compatible in SQL2000. You can do
that using Enterprise manager as well as Query Analyzer.
Enterprise manager:
1.Expand databases -- Right click and select properties
2. Choose option tab -- In the compatibility combo box select "Database
compatibilty level 70"
3. Click ok
Query ANalyzer:
1. Execute the below script.
EXEC sp_dbcmptlevel 'pubs', 70
Thanks
Hari
MCDBA
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony
|||What exactly do you mean by "Save"? Do you want to know how to get it into
a 7.0 db or did Hari address your issue?
Andrew J. Kelly SQL MVP
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony
|||Tony,
To add to Andrew's post, if you want to export data from 2000 to 7, then
you can use DTS.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Andrew J. Kelly wrote:
> What exactly do you mean by "Save"? Do you want to know how to get it into
> a 7.0 db or did Hari address your issue?
>
sql
I understand 2000 databases are not backward compatible
but is there any way to save 2000 databases in 7 format?
Is there an option?
Thanks
Tony
Hi,
You can set the user databases to SQL 7.0 compatible in SQL2000. You can do
that using Enterprise manager as well as Query Analyzer.
Enterprise manager:
1.Expand databases -- Right click and select properties
2. Choose option tab -- In the compatibility combo box select "Database
compatibilty level 70"
3. Click ok
Query ANalyzer:
1. Execute the below script.
EXEC sp_dbcmptlevel 'pubs', 70
Thanks
Hari
MCDBA
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony
|||What exactly do you mean by "Save"? Do you want to know how to get it into
a 7.0 db or did Hari address your issue?
Andrew J. Kelly SQL MVP
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony
|||Tony,
To add to Andrew's post, if you want to export data from 2000 to 7, then
you can use DTS.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Andrew J. Kelly wrote:
> What exactly do you mean by "Save"? Do you want to know how to get it into
> a 7.0 db or did Hari address your issue?
>
sql
Saving SQL 2000 Databases in SQL 7 format
Hi
I understand 2000 databases are not backward compatible
but is there any way to save 2000 databases in 7 format?
Is there an option?
Thanks
TonyHi,
You can set the user databases to SQL 7.0 compatible in SQL2000. You can do
that using Enterprise manager as well as Query Analyzer.
Enterprise manager:
1.Expand databases -- Right click and select properties
2. Choose option tab -- In the compatibility combo box select "Database
compatibilty level 70"
3. Click ok
Query ANalyzer:
1. Execute the below script.
EXEC sp_dbcmptlevel 'pubs', 70
Thanks
Hari
MCDBA
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||What exactly do you mean by "Save"? Do you want to know how to get it into
a 7.0 db or did Hari address your issue?
--
Andrew J. Kelly SQL MVP
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||Tony,
To add to Andrew's post, if you want to export data from 2000 to 7, then
you can use DTS.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Andrew J. Kelly wrote:
> What exactly do you mean by "Save"? Do you want to know how to get it into
> a 7.0 db or did Hari address your issue?
>
I understand 2000 databases are not backward compatible
but is there any way to save 2000 databases in 7 format?
Is there an option?
Thanks
TonyHi,
You can set the user databases to SQL 7.0 compatible in SQL2000. You can do
that using Enterprise manager as well as Query Analyzer.
Enterprise manager:
1.Expand databases -- Right click and select properties
2. Choose option tab -- In the compatibility combo box select "Database
compatibilty level 70"
3. Click ok
Query ANalyzer:
1. Execute the below script.
EXEC sp_dbcmptlevel 'pubs', 70
Thanks
Hari
MCDBA
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||What exactly do you mean by "Save"? Do you want to know how to get it into
a 7.0 db or did Hari address your issue?
--
Andrew J. Kelly SQL MVP
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||Tony,
To add to Andrew's post, if you want to export data from 2000 to 7, then
you can use DTS.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Andrew J. Kelly wrote:
> What exactly do you mean by "Save"? Do you want to know how to get it into
> a 7.0 db or did Hari address your issue?
>
Saving SQL 2000 Databases in SQL 7 format
Hi
I understand 2000 databases are not backward compatible
but is there any way to save 2000 databases in 7 format?
Is there an option?
Thanks
TonyHi,
You can set the user databases to SQL 7.0 compatible in SQL2000. You can do
that using Enterprise manager as well as Query Analyzer.
Enterprise manager:
1.Expand databases -- Right click and select properties
2. Choose option tab -- In the compatibility combo box select "Database
compatibilty level 70"
3. Click ok
Query ANalyzer:
1. Execute the below script.
EXEC sp_dbcmptlevel 'pubs', 70
Thanks
Hari
MCDBA
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||What exactly do you mean by "Save"? Do you want to know how to get it into
a 7.0 db or did Hari address your issue?
Andrew J. Kelly SQL MVP
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||Tony,
To add to Andrew's post, if you want to export data from 2000 to 7, then
you can use DTS.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Andrew J. Kelly wrote:
> What exactly do you mean by "Save"? Do you want to know how to get it int
o
> a 7.0 db or did Hari address your issue?
>
I understand 2000 databases are not backward compatible
but is there any way to save 2000 databases in 7 format?
Is there an option?
Thanks
TonyHi,
You can set the user databases to SQL 7.0 compatible in SQL2000. You can do
that using Enterprise manager as well as Query Analyzer.
Enterprise manager:
1.Expand databases -- Right click and select properties
2. Choose option tab -- In the compatibility combo box select "Database
compatibilty level 70"
3. Click ok
Query ANalyzer:
1. Execute the below script.
EXEC sp_dbcmptlevel 'pubs', 70
Thanks
Hari
MCDBA
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||What exactly do you mean by "Save"? Do you want to know how to get it into
a 7.0 db or did Hari address your issue?
Andrew J. Kelly SQL MVP
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:298601c4709c$5dc1af40$a401280a@.phx.gbl...
> Hi
> I understand 2000 databases are not backward compatible
> but is there any way to save 2000 databases in 7 format?
> Is there an option?
> Thanks
> Tony|||Tony,
To add to Andrew's post, if you want to export data from 2000 to 7, then
you can use DTS.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Andrew J. Kelly wrote:
> What exactly do you mean by "Save"? Do you want to know how to get it int
o
> a 7.0 db or did Hari address your issue?
>
Saving space by using nulls?
Say that I have 2 identical tables with the only difference that one table
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
Thanks
Same. As of 7.0, space requirement for fixed length column is the same regardless if a column of a
row has null or an actual value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Say that I have 2 identical tables with the only difference that one table allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only difference that on the
> table that does not allow nulls in its numeric columns I insert numeric values while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>
|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
Thanks
Same. As of 7.0, space requirement for fixed length column is the same regardless if a column of a
row has null or an actual value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Say that I have 2 identical tables with the only difference that one table allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only difference that on the
> table that does not allow nulls in its numeric columns I insert numeric values while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>
|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>
Saving space by using nulls?
Say that I have 2 identical tables with the only difference that one table
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
ThanksSame. As of 7.0, space requirement for fixed length column is the same regardless if a column of a
row has null or an actual value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Say that I have 2 identical tables with the only difference that one table allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only difference that on the
> table that does not allow nulls in its numeric columns I insert numeric values while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Say that I have 2 identical tables with the only difference that one
>> table allows nulls on all of its *numeric* columns and the other one does
>> not.
>> Now say that I add the same amount of rows to both tables with the only
>> difference that on the table that does not allow nulls in its numeric
>> columns I insert numeric values while in the one that does allow nulls I
>> insert <<Null>> value.
>> Will both tables use the same storage space or would the table that
>> allows nulls use less space because I am insetting Null values on it?
>> Thanks
>>
>
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
ThanksSame. As of 7.0, space requirement for fixed length column is the same regardless if a column of a
row has null or an actual value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Say that I have 2 identical tables with the only difference that one table allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only difference that on the
> table that does not allow nulls in its numeric columns I insert numeric values while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Say that I have 2 identical tables with the only difference that one
>> table allows nulls on all of its *numeric* columns and the other one does
>> not.
>> Now say that I add the same amount of rows to both tables with the only
>> difference that on the table that does not allow nulls in its numeric
>> columns I insert numeric values while in the one that does allow nulls I
>> insert <<Null>> value.
>> Will both tables use the same storage space or would the table that
>> allows nulls use less space because I am insetting Null values on it?
>> Thanks
>>
>
Saving space by using nulls?
Say that I have 2 identical tables with the only difference that one table
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
ThanksSame. As of 7.0, space requirement for fixed length column is the same regar
dless if a column of a
row has null or an actual value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> Say that I have 2 identical tables with the only difference that one table
allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only di
fference that on the
> table that does not allow nulls in its numeric columns I insert numeric va
lues while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows
nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>[/vbcol]|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>sql
allows nulls on all of its *numeric* columns and the other one does not.
Now say that I add the same amount of rows to both tables with the only
difference that on the table that does not allow nulls in its numeric
columns I insert numeric values while in the one that does allow nulls I
insert <<Null>> value.
Will both tables use the same storage space or would the table that allows
nulls use less space because I am insetting Null values on it?
ThanksSame. As of 7.0, space requirement for fixed length column is the same regar
dless if a column of a
row has null or an actual value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rene" <nospam@.nospam.com> wrote in message news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> Say that I have 2 identical tables with the only difference that one table
allows nulls on all of
> its *numeric* columns and the other one does not.
> Now say that I add the same amount of rows to both tables with the only di
fference that on the
> table that does not allow nulls in its numeric columns I insert numeric va
lues while in the one
> that does allow nulls I insert <<Null>> value.
> Will both tables use the same storage space or would the table that allows
nulls use less space
> because I am insetting Null values on it?
> Thanks
>
>[/vbcol]|||Thanks Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eemuGL8zFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Same. As of 7.0, space requirement for fixed length column is the same
> regardless if a column of a row has null or an actual value.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23m6dfC8zFHA.1032@.TK2MSFTNGP12.phx.gbl...
>sql
saving sp results
Hi all
I have a heavy sp which i need to run out of hours.
The sp will be run a number of times but with different parameters, i can do this as a job but i need the results of each time it is run saved to a different file (.csv)
Please help!!!
Rich
Hi Rich.
I think DTS would be the best tool to do it. You can run you procedure with different parameters as separate steps of the DTS package, then dinamically generating Name of your output file using Dynamic properties of DTS. Your package can be scheduled to run outside of working hours and can be design to inform you if any unexpected problems.
NB.
|||NB
I have just tried that and it works find, thanks for the advice
Rich
Saving selections for next report run?
Hi All,
Don't think this can be done, but a user request, so thought I should ask anyway...
I have report with 11 filters (yes, I know thats overkill, but we aren't using analysis services) and the user has asked, if he wants to run it with the same selection criteria every month, can he do that without having to re-select them all?
Using RS 2000
thanx,
99
If the user is running this once a month and with specific selections, the user can create a subscription with specific parameter values to the report and have the report delivered via email or file share as needed. The user can create as many different subscriptions as needed if there are different parameters needed at different times of the month.
Or, the user can create a linked report and set all of the parameter values as desired.
|||Many thanks!!!|||I have the same question, if filter can be saved upon selection and every time user access report, same filters should apply to generate report.
Saving selections for next report run?
Hi All,
Don't think this can be done, but a user request, so thought I should ask anyway...
I have report with 11 filters (yes, I know thats overkill, but we aren't using analysis services) and the user has asked, if he wants to run it with the same selection criteria every month, can he do that without having to re-select them all?
Using RS 2000
thanx,
99
If the user is running this once a month and with specific selections, the user can create a subscription with specific parameter values to the report and have the report delivered via email or file share as needed. The user can create as many different subscriptions as needed if there are different parameters needed at different times of the month.
Or, the user can create a linked report and set all of the parameter values as desired.
|||Many thanks!!!|||
I have the same question, if filter can be saved upon selection and every time user access report, same filters should apply to generate report.
Saving results of query into text file
I need to save a results of query in a text file.
Ex.: select id, name from teacher into c:\teacher.txt
Marcos,
Check the Osql.Exe command prompt utility in Books OnLine. It can save
output to a file.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
|||http://www.aspfaq.com/2482
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
|||Hi,
There are lots of options:
1. Execute the Query analyzer and use File - save option to save the file as
text.
2. Use OSQL or ISQL utilities to run the query from command prompt and
oue -o option save it in hard disk with text extension
3. Use BCP OUT utility with Queryout option to save the output to a text
file.
Note:
Refer books online for the details of each command.
Thanks
Hari
MCDBA
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
sql
Ex.: select id, name from teacher into c:\teacher.txt
Marcos,
Check the Osql.Exe command prompt utility in Books OnLine. It can save
output to a file.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
|||http://www.aspfaq.com/2482
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
|||Hi,
There are lots of options:
1. Execute the Query analyzer and use File - save option to save the file as
text.
2. Use OSQL or ISQL utilities to run the query from command prompt and
oue -o option save it in hard disk with text extension
3. Use BCP OUT utility with Queryout option to save the output to a text
file.
Note:
Refer books online for the details of each command.
Thanks
Hari
MCDBA
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
sql
Saving results of query into text file
I need to save a results of query in a text file.
Ex.: select id, name from teacher into c:\teacher.txtMarcos,
Check the Osql.Exe command prompt utility in Books OnLine. It can save
output to a file.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt|||http://www.aspfaq.com/2482
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt|||Hi,
There are lots of options:
1. Execute the Query analyzer and use File - save option to save the file as
text.
2. Use OSQL or ISQL utilities to run the query from command prompt and
oue -o option save it in hard disk with text extension
3. Use BCP OUT utility with Queryout option to save the output to a text
file.
Note:
Refer books online for the details of each command.
Thanks
Hari
MCDBA
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
Ex.: select id, name from teacher into c:\teacher.txtMarcos,
Check the Osql.Exe command prompt utility in Books OnLine. It can save
output to a file.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt|||http://www.aspfaq.com/2482
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt|||Hi,
There are lots of options:
1. Execute the Query analyzer and use File - save option to save the file as
text.
2. Use OSQL or ISQL utilities to run the query from command prompt and
oue -o option save it in hard disk with text extension
3. Use BCP OUT utility with Queryout option to save the output to a text
file.
Note:
Refer books online for the details of each command.
Thanks
Hari
MCDBA
"Marcos Vieira" <anonymous@.discussions.microsoft.com> wrote in message
news:318EF59A-0057-420A-86CA-1F4F1F6BA8F8@.microsoft.com...
> I need to save a results of query in a text file.
> Ex.: select id, name from teacher into c:\teacher.txt
Saving results of job to a text file
OK. I figured out a way how to do this and in case anyone
else wants to do it...
Call osql using Operating System Command from the job step.
osql -Ssrever -Uuser -Ppassword -Q"DB.dbo.stored proc"
I'd still be interested in know how other people do it.
Still need to play around a bit with the file format.
Amelia.
>--Original Message--
>Using the SQLServer job scheduler, just wondering if it
is
>possible to call a stored proc in a job step using Type
>Transact-SQL and then save the results out to a text file?
>At the moment, I am using Operating System Command Type
to
>call a command file which calls a windows script file
>using javascript to call the stored proc using osql and
>then specifiying the file name for output here.
>This must be a common thing in Prod Support environments
>to schedule daily jobs to run taht produce result files
>saved somewhere. Is there a simpler way?
>Thanks for any help.
>.
>
Another way is to use the T-SQL job type step. When viewing
the job step properties, click the advanced tab. From there,
you can specify an output file for the results of the t-sql
command for the job step.
-Sue
On Tue, 29 Jun 2004 18:28:42 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>OK. I figured out a way how to do this and in case anyone
>else wants to do it...
>Call osql using Operating System Command from the job step.
>osql -Ssrever -Uuser -Ppassword -Q"DB.dbo.stored proc"
>I'd still be interested in know how other people do it.
>Still need to play around a bit with the file format.
>Amelia.
>is
>to
|||Thanks Sue. I didn't know this method either.
:0)
Amelia
>--Original Message--
>Another way is to use the T-SQL job type step. When
viewing
>the job step properties, click the advanced tab. From
there,
>you can specify an output file for the results of the t-
sql[vbcol=seagreen]
>command for the job step.
>-Sue
>On Tue, 29 Jun 2004 18:28:42 -0700,
><anonymous@.discussions.microsoft.com> wrote:
anyone[vbcol=seagreen]
step.[vbcol=seagreen]
file?[vbcol=seagreen]
environments
>.
>
|||This is exactly what I was after. I feel so silly that I
did not see this before.
Thanks again.
Amelia
[vbcol=seagreen]
>--Original Message--
>Thanks Sue. I didn't know this method either.
>:0)
>Amelia
>viewing
>there,
>sql
>anyone
>step.
it[vbcol=seagreen]
Type[vbcol=seagreen]
>file?
Type[vbcol=seagreen]
and[vbcol=seagreen]
>environments
files
>.
>
|||You have plenty of company - a lot of people miss that this
functionality is available.
Glad it helped -
-Sue
On Sun, 4 Jul 2004 21:02:26 -0700, "Amelia"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>This is exactly what I was after. I feel so silly that I
>did not see this before.
>Thanks again.
>Amelia
>it
>Type
>Type
>and
>files
else wants to do it...
Call osql using Operating System Command from the job step.
osql -Ssrever -Uuser -Ppassword -Q"DB.dbo.stored proc"
I'd still be interested in know how other people do it.
Still need to play around a bit with the file format.
Amelia.
>--Original Message--
>Using the SQLServer job scheduler, just wondering if it
is
>possible to call a stored proc in a job step using Type
>Transact-SQL and then save the results out to a text file?
>At the moment, I am using Operating System Command Type
to
>call a command file which calls a windows script file
>using javascript to call the stored proc using osql and
>then specifiying the file name for output here.
>This must be a common thing in Prod Support environments
>to schedule daily jobs to run taht produce result files
>saved somewhere. Is there a simpler way?
>Thanks for any help.
>.
>
Another way is to use the T-SQL job type step. When viewing
the job step properties, click the advanced tab. From there,
you can specify an output file for the results of the t-sql
command for the job step.
-Sue
On Tue, 29 Jun 2004 18:28:42 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>OK. I figured out a way how to do this and in case anyone
>else wants to do it...
>Call osql using Operating System Command from the job step.
>osql -Ssrever -Uuser -Ppassword -Q"DB.dbo.stored proc"
>I'd still be interested in know how other people do it.
>Still need to play around a bit with the file format.
>Amelia.
>is
>to
|||Thanks Sue. I didn't know this method either.
:0)
Amelia
>--Original Message--
>Another way is to use the T-SQL job type step. When
viewing
>the job step properties, click the advanced tab. From
there,
>you can specify an output file for the results of the t-
sql[vbcol=seagreen]
>command for the job step.
>-Sue
>On Tue, 29 Jun 2004 18:28:42 -0700,
><anonymous@.discussions.microsoft.com> wrote:
anyone[vbcol=seagreen]
step.[vbcol=seagreen]
file?[vbcol=seagreen]
environments
>.
>
|||This is exactly what I was after. I feel so silly that I
did not see this before.
Thanks again.
Amelia
[vbcol=seagreen]
>--Original Message--
>Thanks Sue. I didn't know this method either.
>:0)
>Amelia
>viewing
>there,
>sql
>anyone
>step.
it[vbcol=seagreen]
Type[vbcol=seagreen]
>file?
Type[vbcol=seagreen]
and[vbcol=seagreen]
>environments
files
>.
>
|||You have plenty of company - a lot of people miss that this
functionality is available.
Glad it helped -
-Sue
On Sun, 4 Jul 2004 21:02:26 -0700, "Amelia"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>This is exactly what I was after. I feel so silly that I
>did not see this before.
>Thanks again.
>Amelia
>it
>Type
>Type
>and
>files
Saving reports in previous formats
Well, the question is not really VB-related, but probebly someone will give a hint on how to save Crystal Reports 9 report, so that I can open it in Crystal Reports 8 ? Seems that the native "save as" can only save in v.9. :(From the Crystal Reports support site:
Since Crystal Reports 9 and later uses unicode, if you create or save a report in Crystal Reports 9 or later, then you cannot open this report in previous versions of Crystal Reports.
If you save a report in Crystal Reports 9 and later, there is no method of re-saving this report in a previous version of Crystal Reports. That is, it is not possible to save a version 9 or later report as a version 8.5 report. Therefore, it is advisable to have back up copies of your existing reports before saving them in Crystal Reports 9 or later format.
You are able to open a report created with Crystal Reports XI in some previous versions of Crystal Reports. For example:
Crystal Reports 10 and 9 can open a report created with Crystal Reports XI. However, new functionality from Crystal Reports XI may not be available in Crystal Reports 10 and 9.
Crystal Reports 8.5 and lower versions cannot open a report created with Crystal Reports XI. Attempting to do so results in the following error message:
"Failed to open document"
Since Crystal Reports 9 and later uses unicode, if you create or save a report in Crystal Reports 9 or later, then you cannot open this report in previous versions of Crystal Reports.
If you save a report in Crystal Reports 9 and later, there is no method of re-saving this report in a previous version of Crystal Reports. That is, it is not possible to save a version 9 or later report as a version 8.5 report. Therefore, it is advisable to have back up copies of your existing reports before saving them in Crystal Reports 9 or later format.
You are able to open a report created with Crystal Reports XI in some previous versions of Crystal Reports. For example:
Crystal Reports 10 and 9 can open a report created with Crystal Reports XI. However, new functionality from Crystal Reports XI may not be available in Crystal Reports 10 and 9.
Crystal Reports 8.5 and lower versions cannot open a report created with Crystal Reports XI. Attempting to do so results in the following error message:
"Failed to open document"
Saving Report without report export pop-up window
What I want to do is to save a report in PDF format to a pre-defined location in the web server when I click a button in a webpage. I don't want the pop-up windows appear to ask for save or open the file.
Anyone can help ?
if u save your report in a temp place you can use the acrobt activex control and give it the pdf file path
it will be opened without asking
Saving Report Viewer rending as an HTML file
I'm trying to get an HTML rendering to an html file instead of being displayed in Internet Explorer. My idea is that maybe I could launch an Internet Explorer with an .aspx page that contains a report viewer. I want to be able to capture the output of the Report Viewer control and save off that HTML to a file and immediately close the IE window.
I have read a post or two on other messageboards from people who said that they have done it, and I also heard from a Microsoft source that this is a perfectly acceptable way of capturing an HTML output since MS removed that export capability before they released the control.
What I'm asking to be done could probably be done on any web control that renders HTML, I just don't know how to do it in the general sense (steal the rendering stream from a web control and save the stream off to a file.)
Hopefully what I said makes sense.
If anyone has any suggestions on how to handle that, I would greatly appreciate it.
Thanks,
Ken
Ken, this gets the report output in a stream, I assume you can take it from there. I found this as an example of someone converting to excel(note the commented out line). So I have not really tested this, I just wanted to help.
I have read a post or two on other messageboards from people who said that they have done it, and I also heard from a Microsoft source that this is a perfectly acceptable way of capturing an HTML output since MS removed that export capability before they released the control.
What I'm asking to be done could probably be done on any web control that renders HTML, I just don't know how to do it in the general sense (steal the rendering stream from a web control and save the stream off to a file.)
Hopefully what I said makes sense.
If anyone has any suggestions on how to handle that, I would greatly appreciate it.
Thanks,
Ken
Ken, this gets the report output in a stream, I assume you can take it from there. I found this as an example of someone converting to excel(note the commented out line). So I have not really tested this, I just wanted to help.
Response.Clear()
Response.Buffer =True
' Response.ContentType = "application/vnd.ms-excel"
Response.ContentType = "text/html"
Response.Charset = ""
Dim oStringWriterAsNew System.IO.StringWriter
Dim oHtmlTextWriterAsNew System.Web.UI.HtmlTextWriter(oStringWriter)
oHtmlTextWriter.Write("<html><head>")
oHtmlTextWriter.Write("<style>")
oHtmlTextWriter.Write(".dataGridHeader{font-weight: bold;font-size:8pt;color: black;font-family: Verdana;background-color: #ddddee;text-align:left;}")
oHtmlTextWriter.Write(".dataGridItemStyle{font-size: 8pt;color: black;font-family: Verdana;text-align: left;}")
oHtmlTextWriter.Write("</style>")
oHtmlTextWriter.Write("</head><body>")
oHtmlTextWriter.WriteBeginTag("form runat=server ")
oHtmlTextWriter.WriteAttribute("target", "_blank")
oHtmlTextWriter.Write(">")
ReportViewer1.RenderControl(oHtmlTextWriter)
oHtmlTextWriter.Write("</form></body></html>")
Response.Write(oStringWriter.ToString())
Response.End()
sqlSaving Report Parameters
What is the best way to let the user choose report parameters and then save
them?
When the user looks at the report with saved parameters again, it should
show refreshed data for the same parameter selection.
I know how to pass parameters to a report on the URL, but how can I find out
what parameters the user has selected on my report?You can access the parameter values using parameter!
<ParameterName>.Value
Hope that answers your query.
>--Original Message--
>What is the best way to let the user choose report
parameters and then save
>them?
>When the user looks at the report with saved parameters
again, it should
>show refreshed data for the same parameter selection.
>I know how to pass parameters to a report on the URL, but
how can I find out
>what parameters the user has selected on my report?
>
>.
>|||That is how you get the value of a parameter from within the report
designer. I knew how to do that. But I am really asking how to do it using a
web application in C#.NET. I should have been more clear.
<anonymous@.discussions.microsoft.com> wrote in message
news:10db01c4851e$2b389ca0$a301280a@.phx.gbl...
> You can access the parameter values using parameter!
> <ParameterName>.Value
> Hope that answers your query.
>
> >--Original Message--
> >What is the best way to let the user choose report
> parameters and then save
> >them?
> >
> >When the user looks at the report with saved parameters
> again, it should
> >show refreshed data for the same parameter selection.
> >
> >I know how to pass parameters to a report on the URL, but
> how can I find out
> >what parameters the user has selected on my report?
> >
> >
> >.
> >
them?
When the user looks at the report with saved parameters again, it should
show refreshed data for the same parameter selection.
I know how to pass parameters to a report on the URL, but how can I find out
what parameters the user has selected on my report?You can access the parameter values using parameter!
<ParameterName>.Value
Hope that answers your query.
>--Original Message--
>What is the best way to let the user choose report
parameters and then save
>them?
>When the user looks at the report with saved parameters
again, it should
>show refreshed data for the same parameter selection.
>I know how to pass parameters to a report on the URL, but
how can I find out
>what parameters the user has selected on my report?
>
>.
>|||That is how you get the value of a parameter from within the report
designer. I knew how to do that. But I am really asking how to do it using a
web application in C#.NET. I should have been more clear.
<anonymous@.discussions.microsoft.com> wrote in message
news:10db01c4851e$2b389ca0$a301280a@.phx.gbl...
> You can access the parameter values using parameter!
> <ParameterName>.Value
> Hope that answers your query.
>
> >--Original Message--
> >What is the best way to let the user choose report
> parameters and then save
> >them?
> >
> >When the user looks at the report with saved parameters
> again, it should
> >show refreshed data for the same parameter selection.
> >
> >I know how to pass parameters to a report on the URL, but
> how can I find out
> >what parameters the user has selected on my report?
> >
> >
> >.
> >
Wednesday, March 28, 2012
Saving Report History
Hi all.
Im struggling with the following problem.
My company produces a report for customers on both an adhoc and a scheduled basis. This report is the same for each customer but the data is obviously different. (filtered by job number)
It is a legal requirement that we keep a copy of each report that is sent to the customer.
It is a company requirement that the data from this report can be rendered into another format.
I am limited to using RS 2000
I have found that I can satisfy the scheduled reports requirement using a data driven subscription and adhoc reporting on job number are no problem either, the problem is saving the reports data so that it can be rendered into another format.
The report history seems to be what I am looking for, as it saves reports in IF that can be rerendered but I am struggling to find a way to generate a report history both on a schedule and an adhoc basis. Am I barking up the wrong tree, is there another way of doing this?
I am not adverse to writing custom data extensions or whatever it takes. :/
Your help would be very much appreciated.
Daren Fox
You can't subscribe to history snapshots, but you can set things up so report subscriptions and history snapshots are generated simultaneously -- set the report execution properties such that a report execution snapshot is generated on the same schedule that you wanted for your subscriptions, then have all execution snapshots also be stored as history snapshots.
Subscribe to:
Posts (Atom)