How to se TempTables dat in Query Analazer

Hi all,
i am very interested on sqlprogramming, i want to see the #temptables
data in Query analazer, and i want to create a log files that how many
#tables created at instalation time , later i want to display the data
in form or grid, any one can please help me..
Thanks in advance
SureshSuresh wrote:
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>
I'm not sure that I fully understand what you are looking for but if you
want to view data in a temp table you just run a select like on every
other regular table - e.g. SELECT column1, column2... FROM #YourTempTable
Regards
Steen Schlter Persson
DBA|||Read up on temp tables within Books Online.
If you create a temp table (denoted by a single # sign) you can query the
data within the table ONLY if you query it on the same connection that
created the table (and the thing that created the table was not a stored
procedure that you just ececuted).
I am not sure what you mean when you say you want to see how many # tables
were created "at instalation time." Installation of what?
Keith Kratochvil
"Suresh" <suresh_yalla@.hotmail.com> wrote in message
news:1149678535.349104.296080@.y43g2000cwc.googlegroups.com...
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>

How to se TempTables dat in Query Analazer

Hi all,
i am very interested on sqlprogramming, i want to see the #temptables
data in Query analazer, and i want to create a log files that how many
#tables created at instalation time , later i want to display the data
in form or grid, any one can please help me..
Thanks in advance
SureshSuresh wrote:
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>
I'm not sure that I fully understand what you are looking for but if you
want to view data in a temp table you just run a select like on every
other regular table - e.g. SELECT column1, column2... FROM #YourTempTable
--
Regards
Steen Schlüter Persson
DBA|||Read up on temp tables within Books Online.
If you create a temp table (denoted by a single # sign) you can query the
data within the table ONLY if you query it on the same connection that
created the table (and the thing that created the table was not a stored
procedure that you just ececuted).
I am not sure what you mean when you say you want to see how many # tables
were created "at instalation time." Installation of what?
--
Keith Kratochvil
"Suresh" <suresh_yalla@.hotmail.com> wrote in message
news:1149678535.349104.296080@.y43g2000cwc.googlegroups.com...
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>

how to script security permissions?

I have a very large table that is refreshed periodically. Since it's
so big, I do a 'drop table', 'create table', 'create index' then a bulk
load. It's much faster than doing a 'delete from'. I also do a
'shrinkdb' as part of this process.

The problem, however, is that the user permissions are also dropped in
this process. So, how can I script the user permissions? For example,
how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
thanks!!

Eben Yong
yonglove@.yahoo.comGRANT SELECT ON MyTable TO MyUser|||Eben (yonglove@.yahoo.com) writes:
> I have a very large table that is refreshed periodically. Since it's
> so big, I do a 'drop table', 'create table', 'create index' then a bulk
> load. It's much faster than doing a 'delete from'. I also do a
> 'shrinkdb' as part of this process.

So why not do a TRUNCATE TABLE instead? This is a minimally logged
operation, and you maintain indexes, permissions etc.

> The problem, however, is that the user permissions are also dropped in
> this process. So, how can I script the user permissions? For example,
> how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
> thanks!!

I guess you can do this with DMO, if you want to do this programmatically.
However, I have not used DMO myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Eben (yonglove@.yahoo.com) writes:
> > I have a very large table that is refreshed periodically. Since it's
> > so big, I do a 'drop table', 'create table', 'create index' then a bulk
> > load. It's much faster than doing a 'delete from'. I also do a
> > 'shrinkdb' as part of this process.
> So why not do a TRUNCATE TABLE instead? This is a minimally logged
> operation, and you maintain indexes, permissions etc.
Although keeping indexes is sometimes a good thing, it's also sometimes
beneficial to drop all indexes before the truncate and only add them
back when all the data loading is complete.

Of course, it depends on how the data loading is being managed (in my
case, I happen to be loading 3 1/2 million records from a non-R DBMS,
and having to do it one row at a time - I'd rather not have the indexes
rebuilt for every insert)

Damien|||Thank you, everyone. I did not know about the TRUNCATE TABLE option.
But accomplishing this objective using DROP TABLE, CREATE TABLE, and so
on, has required that I learn many other SQL Server methods, so it's
good for me. I spent more time developing the solution but SQL Server
doesn't care one way or the other and both methods still get the job
done in the same amount of time. So, once again, thanks everyone for
your input!

How To Script Jobs In SQL Server ?

How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...

msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX

soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chris
Hi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegrou ps.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chrisHi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegroups.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

how to save trigger into table in sql 2005

I have found the node for Triggers in SQL Server 2005's Management Studio and tried to create a 'New Trigger...' but when I save, it saves it to an .sql file but do not attach it to the table.

What am I doing wrong here? I can't seem to attach it. And where is this Assisted Editor ...i can't seem to find it anywhere

creating a trigger is like any other sql statement. You can execute them against a database or you can save the script for later use. You want to execute the statement so instead of hitting save try the execute button or hitting F5

|||

THANK YOU SO MUCH, I MADE IT.

sql

how to save query to file in Query Designer

Using SQL Server 2000's Query Designer (not analyzer), I can create a
query, look at the query in SQL pane. On a right click, if I select
save as, there is no file dialog to inquire where to save file. If I
select save, I can't find file.
Is the save-as a bug?
Where should I look for the file; what naming convention is used?
RitaRita,
You are correct that Save As and Save do not work here. See
http://groups.google.com/groups?q=hoegemeier%2Bsigouin
for an answer to this question.
SK
"Rita W." <riwillia@.telus.net> wrote in message
news:40130D48.2050708@.telus.net...

quote:

---

> Using SQL Server 2000's Query Designer (not analyzer), I can create a
> query, look at the query in SQL pane. On a right click, if I select
> save as, there is no file dialog to inquire where to save file. If I
> select save, I can't find file.
> Is the save-as a bug?
> Where should I look for the file; what naming convention is used?
> Rita
>

---

|||Thanks, Steve.
R
Steve Kass wrote:

quote:

---

>Rita,
> You are correct that Save As and Save do not work here. See
>http://groups.google.com/groups?q=hoegemeier%2Bsigouin
>for an answer to this question.
>SK
>"Rita W." <riwillia@.telus.net> wrote in message
>news:40130D48.2050708@.telus.net...
>
>
>

---

how to save query to file in Query Designer

Using SQL Server 2000's Query Designer (not analyzer), I can create a
query, look at the query in SQL pane. On a right click, if I select
save as, there is no file dialog to inquire where to save file. If I
select save, I can't find file.
Is the save-as a bug?
Where should I look for the file; what naming convention is used?
RitaRita,
You are correct that Save As and Save do not work here. See
http://groups.google.com/groups?q=hoegemeier%2Bsigouin
for an answer to this question.
SK
"Rita W." <riwillia@.telus.net> wrote in message
news:40130D48.2050708@.telus.net...
> Using SQL Server 2000's Query Designer (not analyzer), I can create a
> query, look at the query in SQL pane. On a right click, if I select
> save as, there is no file dialog to inquire where to save file. If I
> select save, I can't find file.
> Is the save-as a bug?
> Where should I look for the file; what naming convention is used?
> Rita
>|||Thanks, Steve.
R
Steve Kass wrote:
>Rita,
> You are correct that Save As and Save do not work here. See
>http://groups.google.com/groups?q=hoegemeier%2Bsigouin
>for an answer to this question.
>SK
>"Rita W." <riwillia@.telus.net> wrote in message
>news:40130D48.2050708@.telus.net...
>
>>Using SQL Server 2000's Query Designer (not analyzer), I can create a
>>query, look at the query in SQL pane. On a right click, if I select
>>save as, there is no file dialog to inquire where to save file. If I
>>select save, I can't find file.
>>Is the save-as a bug?
>>Where should I look for the file; what naming convention is used?
>>Rita
>>
>
>

how to save expression

Hi,
I am new to RS. in Crystal Report, i can create a formula then save it. So
it can be used later. In RS, how can I save an expression and use it later?
Thx.You can create an embedded VB.NET function which will be visible in the
report it is saved (see Report Properties - > Code tab) or you can create an
external .NET assembly which can be shared by all reports.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Mia" <Mia@.discussions.microsoft.com> wrote in message
news:F4516C1C-0F54-42CD-BEF8-5115097DBFB1@.microsoft.com...
> Hi,
> I am new to RS. in Crystal Report, i can create a formula then save it. So
> it can be used later. In RS, how can I save an expression and use it
> later?
> Thx.

How to save DTS package as an application

Hi,

I want create stand alone application from DTS package. I remember I
did one time ago, I couldnt recall how I did it.

Thanks.If you want to run DTS as an executable then the two main options are
to use the DTSRUN utility (see Books Online) or to invoke the package
using DTS's COM object model (see "DTS Programming" in Books Online)

--
David Portas
SQL Server MVP
--|||more importantly..

you can save a dts package as a vb bas module and then compile it into
a vb6 app right?

hth

aaron|||Yes, I forgot abvout that. Select Package, Save As and select "Visual
Basic File" from the location dropdown.

--
David Portas
SQL Server MVP
--|||that, single-handedly is the reason i haven't gotten into this .NET
crap yet

you can't save it as VB.net can you?

-aaron

How to save contents of Text box to database?

Hi,

For some reason I can't use the edit, update or insert features on my remote shared server, so I am looking to create a web page that has text boxes on it, that I can enter data into, that will be saved into my database.

This is opposed to entering the data directly into the database itself. I want to be able to use a webpage, for simply adding new data, and saving it so that the new data updates and saves over the top of the old data.

What are the steps involved in doing this?

Any example code for just one text box would be appreciated, I could then extend it to suit my needs. Tia.

As I understand you want to get data in text box in you website and then update or insert to the database table. Here I wrote a very simple sample in C#:

protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute\SQL2000;Initial Catalog=tempdb;Integrated Security=SSPI;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
// Connect to the database then retrieve the schema information.

SqlCommand cmd = new SqlCommand("sp_UpdateMytable", connection);
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.id", txtBox_ID.Text);
cmd.Parameters.Add("@.name", txtBox_Name.Text);
int i = cmd.ExecuteNonQuery();

}

And the storedprocedure sp_UpdateMytable will update a table t1(id int, name varchar(30)) in this way:

create proc sp_UpdateMyTable @.id int,@.name varchar(30)
as
if exists (select * from t1 whereid=@.id)
update t1 setname=@.namewhere id= @.id
else insert into t1 select @.id,@.name
go

|||

Thanks Lori_Jay,

I actually resolved the issue and did switch the answered tag on this thread. I am sure your solution would work, however mine was a more simple issue, actually I will mention it here for other poor souls who struggle with the same issue I did.

Basically, I did not have a Primary Key set in my table of my database. Firstly, I was taught that although its a good idea to have a PK, it's not absolutely necessary. Because I had one table, with one column and one row, Idecided not to have one.

If you don't have one, then in Visual Studio 2005, you cannot access the Advanced SQL options, which are INSERT, UPDATE & DELETE, this seems to be a major fault if you ask me because there is ZERO error reporting and ZERO documentation about it.

I got lucky when I did a Google for it (after a week of endless suffering) to find one site in the entire world, written in Russian (which I had to translate very poorily), which stated that you need a PK. I quickly added a PK to my table and it worked instantly.

Perhaps the powers that be whom monitor these forums, can look into this and document it so that others are spared the same distress.

Regards.

how to run this proc in SQL Analyzer?

hi, guys

I have a stored procedure, like that:

CREATE PROCEDURE [dbo].[ViewTitles]
@.UID int,
@.DateStart datetime,
@.DateEnd datetime,
........

When I run this query in SQL Analyzer like this:
ViewTitles 6165, '2006-01-29 10:00:00', '2006-02-29 10:00:00'

It alwasy shows:
Error converting data type varchar to datetime.

If I run it in code, asp.net, or report, no problem at all.
What is wrong with that?

Thanks.

What is the dateformat of the session when logged in through ISQLW? You can verify this by looking at output of DBCC USEROPTIONS. Look for set option "dateformat". In addition to this, you should one of the ISO 8601 formats for datetime literals that should be interpreted correctly irrespective of the language or dateformat settings. Specify the value instead like '2006-01-29T10:00:00'.

How to run SSIS package

How can I:

1) Create a Job in 2005 to run my SSIS project? Specifically syntax and where I would place the all in the job's properties

2) Can I run an SSIS package from the command prompt? what would be the syntax

You should read BOL as it has extensive information on these topics.

1) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/541ee5ac-2c9f-4b74-b4f0-13b7bd5920b0.htm

This is general info on agent and you would use the SSIS subsystem for running SSIS packages.

2) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

Matt

|||this is a dumb question but how do I run those paths to get to your URLs above?|||Open BOL and put the URL into the URL box.

How To Run SQL Script

Hi,

I have a SQL script which create few tables.
Currently, I have to paste the scripts to the SQL Enterprise Manager
and highlight one by one to run it. It can't seem to run it as "one
unit".

My script looks like below:

CREATE TABLE T1 AS ...;
CREATE TABLE T2 AS ...;

Any comments?

Thanks in advance.You can run them as a single unit, if these are just CREATE TABLE
statements. Are you getting any errors?

However if you are creating objects with cyclic dependencies you may have to
make sure the dependant objects must be created before the parent object.
One alternative to this is to use CREATE SCHEMA DDL & details can be found
in SQL Server Books Online.

--
- Anith
( Please reply to newsgroups only )

How to Run Script in SQL 2000 EM

I'm sure this is a simple problem, but my experience is limited.
I have script to create a large table.
I'm told that I should open Enterprise Manager and Run the Script, and it will create the table.
How do I run the script? :o
Thank you.Sounds like a miscommunication, actually. Query Analyzer is the tool fo choice for scripts. You should be able to access QA from Enterprise Mangler via the tools menu, or one of the buttons on the tool bars. Been a while since I looked for those, though.|||Your advise proved to be correct.
Enterprise Manager/Tools/Query Analyzer
Paste the script, Press F5

How to run multiple script files

I have a number of script files that create various objects in my database, and to create a new DB from scratch I need to run them all in a certain sequence.

Is it possible to create a master script file that calls the others in the desired sequence?

P.S. I am using SQL Server Management Studio Express to edit and run the scripts.

There is no need to separate all your objects into separate script files, you can include them all in a single script and just separate them into Batches as appropriate. This is how we create our sample database in many cases and how most people deploy databases as part of an application.

Mike

|||

Thanks for the advice, but I use a code generator and therefore need to have them in separate files.

Anyway, I worked out how to do it. First you have to click the Query menu and choose SQLCMD Mode. Then you can enter the files like this:

r: c:\myfiles\file1.sql
r: c:\myfiles\file2.sql
etc...

My path is quite long so I use a variable as follows (I also added the server connection):

:Connect MyServer\SQLExpress
:setvar SourceFolder "K:\Product Development\Programming\Current\Database\Stored Procedures"
:r $(SourceFolder)\SProc1.sql
:r $(SourceFolder)\SProc2.sql

How to run Microsoft full-text Search service on a separate machine(otherthanSQLServer)

How to run Microsoft full-text Search service on a separate
machine(otherthanSQLServer)
Is it possible to create Microsoft Search Service for full-text search
on separate machine instead of running it on SQL Server machine? If so,
how? Can you point me to any best practice to architect it?
I am looking for possible architectures to improve performance of MS
full-text Search Services by taking MS Search services out from SQL
Server machine and implement it on separate machine.
I'd appreciate any help you could provide on it.
Brian,
No, it is not possible to the "Microsoft Search" (mssearch.exe) service
running on another machine without SQL Server 2000. However, you can use the
procedures documented in the following Kb article 240867 (Q240867) "INF: How
to Move, Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867 to come close
to "propagating" the Full-text Indexing of the FT Catalogs on another
server. Note, this is also not possible with SQL Server 2005.
If you're trying to properly tune a server for Full-text Search, I can help,
but I need you to provide more info on your server's configuration as well
as software and database configurations. Below are SQL FTS resources, you
should review:
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<brianshaw03@.yahoo.com> wrote in message
news:1129605910.538413.210580@.g14g2000cwa.googlegr oups.com...
> How to run Microsoft full-text Search service on a separate
> machine(otherthanSQLServer)
> Is it possible to create Microsoft Search Service for full-text search
> on separate machine instead of running it on SQL Server machine? If so,
> how? Can you point me to any best practice to architect it?
> I am looking for possible architectures to improve performance of MS
> full-text Search Services by taking MS Search services out from SQL
> Server machine and implement it on separate machine.
> I'd appreciate any help you could provide on it.
>
|||Hi John/All
I am trying to understand the best configuration for Full text search.
We are running full text search with 4GB RAM on SQL server 2000 on a
windows 2003 enterprise server. I have the
/3G option on in boot.ini. The VM setting is set at 2048 to 4095.
Typically SQL server acquires close to 3 GB RAM (about 2.87 GB) and the
rest "appears" available with task manager. While running full text
search queries we see mssearch.exe taking about 20MB RAM and it page
faults heavily.
I am not sure if this is an indicator of MSSearch running out of
memory. Also I am not sure as about 1GB memory is "available", why
MSSearch does not consume more memory?
I read it in the manual that the optimal configuration for full text
search
is
1. The virtual memory size to at least 3 times the physical memory
installed
in the computer.
2. The SQL Server max server memory server configuration option to 1.5
times
the physical memory (half the virtual memory size setting).
Do I have to set the above settings? If I had more virtual memory , I
am likely to have more page faults by my understanding. Is that true?
What are the right settings and what am I doing wrong?
Matt
John Kane wrote:[vbcol=seagreen]
> Brian,
> No, it is not possible to the "Microsoft Search" (mssearch.exe) service
> running on another machine without SQL Server 2000. However, you can use the
> procedures documented in the following Kb article 240867 (Q240867) "INF: How
> to Move, Copy, and Backup Full-Text Catalog Folders and Files" at
> http://support.microsoft.com/default...b;EN-US;240867 to come close
> to "propagating" the Full-text Indexing of the FT Catalogs on another
> server. Note, this is also not possible with SQL Server 2005.
> If you're trying to properly tune a server for Full-text Search, I can help,
> but I need you to provide more info on your server's configuration as well
> as software and database configurations. Below are SQL FTS resources, you
> should review:
> SQL Server 2000 Full-Text Search Resources and Links
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
>
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <brianshaw03@.yahoo.com> wrote in message
> news:1129605910.538413.210580@.g14g2000cwa.googlegr oups.com...
sql

How to run MDX query as scheduled job?

Hi,

Is there any way to run MDX

query as scheduled job on SQL Server 2005?

We need create local cubes by

weekly or monthly. How can we put “Create global cube” MDX query into scheduled

job?

Any info would be greatly

appreciated.

Thanks.

Yes. Create a new SQL Server Agent job and add a step with the step type of SQL Server Analysis Services Query. For this type of step, you need to specify the SSAS server to connect to, the database to run the query within, and the text for the query itself.

Should do exactly what you want...

HTH,

Dave Fackler

|||

Hi Dave,

Thanks for the quick replay.

Could you recommend any article or books that I can take look?

Thanks.

|||

I'm assuming you are asking about references for SSIS? If that is the case, check out Microsoft SQL Server 2005 Integration Services (SQL Server Series) by Kirk Haselden, Professional SQL Server 2005 Integration Services (Programmer to Programmer) by Brian Knight et al, and The Rational Guide to Extending SSIS 2005 with Script (Rational Guides) (Rational Guides) by Donald Farmer. All great books related to SSIS that should get you started...

HTH,

Dave Fackler

|||Thank you very much Dave for answered my question. It's helpful.

How to run a DTS as a trusted user in a stored proc

I get an error when i try to run a dts
but when i create a new dts and select the right server and user
everything went well.
Can someone help me?What is the error you receive ?

This is a cut from a microsoft article which may address your problem:
"Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run. "

Here is the article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;q269074)sql