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 adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

how to save result of sql query to .csv file "using command in sql query itself"

Hello there

I m new in sql..i want to know wether above said task is possible in sql?

if yes then what is exact format of sql query command..

Thanks

AVD

YOu can have a look in the BOL for OPENDATASOURCE, there should a sample for an output to a propetary format.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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 query in Query Designer

Just learning SQL Server, using MS Step by Step book. I think I should be able to right click in query designer pane and save-as query. I see this option in context menu. When I try to save file, I don't get file dialog to specify file name. I also don't get error. Any ideas?

Thanks.Are you using Query Analyzer?|||Originally posted by blindman
Are you using Query Analyzer?

Sorry, I should have been more precise. Using SQL Server2000 (on Windows2000 Server) Enterprise Manager to create query in its Query Designer.

How to save query execution plan?

Hello,
Does anyone know how to save a query plan into word doc?
Thanks,
Lianne> Does anyone know how to save a query plan into word doc?
There are a variety of ways.
You can click that pane of QA, print it...to file and save as text (which
works for the textual query plan, or you could Alt+PrntScrn it like I do,
and then crop off the query plan part of the screen shot, save it as a jpg
and then insert that to your word doc.
I bet other people will suggest other things. I like the JPG -> Word
option, because it lets you keep an original of your Q-Plan.
Unfortunately, my way does not capture the information that QA puts in the
tooltips when you wave your mouse over the steps.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei
"Lianne Kwock" <LianneKwock@.discussions.microsoft.com> wrote in message
news:658E97FA-289D-40A7-B998-7F944CD7D472@.microsoft.com...
> Hello,
>
> Thanks,
> Lianne|||Hi Mike,
Thank very much for your suggestion. I appreicate it.
"Mike Labosh" wrote:

> There are a variety of ways.
> You can click that pane of QA, print it...to file and save as text (which
> works for the textual query plan, or you could Alt+PrntScrn it like I do,
> and then crop off the query plan part of the screen shot, save it as a jpg
> and then insert that to your word doc.
> I bet other people will suggest other things. I like the JPG -> Word
> option, because it lets you keep an original of your Q-Plan.
> Unfortunately, my way does not capture the information that QA puts in the
> tooltips when you wave your mouse over the steps.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
> "Lianne Kwock" <LianneKwock@.discussions.microsoft.com> wrote in message
> news:658E97FA-289D-40A7-B998-7F944CD7D472@.microsoft.com...
>
>|||Lianne Kwock wrote:
> Hi Mike,
> Thank very much for your suggestion. I appreicate it.
> "Mike Labosh" wrote:
>
If you are using SQL Server SQL Server 2005 you can use
set showplan_xml on
which will show exeution plan in XML
which you can use to force query execution plan using USE PLAN query
hint.
You can also create plan guide for query which will guide query for
execution when no plan in cache.
Look at sp_create_plan_guide in BOL.
Regards
Amish Shah

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK
Consider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK
sql

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KKConsider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK

How to Save a query result in ANSI in SQL 2005?

Hi

When I was using Enterprise Manager (SQL 2000) and stored my query result (to file), it was stored in Ansi Encoding.

After I upgrade to SQL Server Management Studio (2005), it seams that when I store the query result to file it's Unicode Encoded. This give me a lot of trouble, when I use other program who I must read this file. For small result set, I can open it in Notepad, save it as ANSI, and then use it in my other program. When the result set is so big that Notepad not can help me, I can't use the stored result set.

There must be a way to store my Query result in Ansi encoding, but I don't know how I can do it. Hope some one know, and can help me solve this big problem.

JF

When the Save Results dialog box Open click the drop down next to the Save button and click Save with Encoding..."

Another dialog box Save With Encoding gives you Available Encodings to choose .

How to run replace on all columns

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.

Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')

Thanks,
JPJackpipE (pipe.jack@.gmail.com) writes:

Quote:

Originally Posted by

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.
>
Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')

There is no way to loop through the columns in a table in a simple
fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.

For a thing like this I would do:

SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'

and the copy, paste and run result.

--
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|||There is no way to loop through the columns in a table in a simple

Quote:

Originally Posted by

fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.
>
For a thing like this I would do:
>
SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'
>
and the copy, paste and run result.

Well that simplify my job but still does not automate the process to a
point where one query execution will take care of entire table.

Thanks.|||I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @.tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.

declare @.tblname nvarchar(60)
set @.tblname = 'PROFILE'

SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid

Output from one test exeuction:

UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 16:01:40 -0800, "JackpipE" <pipe.jack@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>There is no way to loop through the columns in a table in a simple
>fashion. This is because that it would rarely make any sense; columns
>in a table are supposed to described distinct attribuets.
>>
>For a thing like this I would do:
>>
> SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
> name + ', 1, 255), char(13) + char(10), '':'')'
> FROM syscolumns
> WHERE id = object_id('PROFILE')
> and type_name(xtype) like '%char'
>>
>and the copy, paste and run result.

>
>
>Well that simplify my job but still does not automate the process to a
>point where one query execution will take care of entire table.
>
>Thanks.

|||On Feb 22, 8:52 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @.tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.
>
declare @.tblname nvarchar(60)
set @.tblname = 'PROFILE'
>
SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid
>
Output from one test exeuction:
>
UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');
>
Roy Harvey
Beacon Falls, CT
>

Roy,

I had different output when I ran your query:
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');

It looked like select statement output with 17 rows (17 columns in the
table) like the above. No UPDATE or SET function.|||On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pipe.jack@.gmail.comwrote:

Quote:

Originally Posted by

Roy,
>
>I had different output when I ran your query:
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');
>
>It looked like select statement output with 17 rows (17 columns in the
>table) like the above. No UPDATE or SET function.

I assume you changed the column names to all be _NAME, rather than the
query actually returning that.

The missing UPDATE and SET is what I would expect if the query was
written to filter out the first column. The specification said every
column, so I did not write the query to allow for that. In the first
CASE the first WHEN test would have to change from the simple:

WHEN C.colid = 1

To something like:

WHEN C.colid = (SELECT MIN(x.colid) FROM syscolumns as X WHERE X.id =
O.id AND <whatever filtering was used in the outer WHERE clause>)

Likewise the subquery in the last CASE would have to add the same
tests to match the WHERE clause.

Roy Harvey
Beacon Falls, CT|||Please post responses to the newsgroup, not to email. This promotes
the basic function of newsgroups, sharing information. It also means
that more than one person is reading and thinking about your problem.

If you did not add a WHERE clause test to limit the columns then I am
quite surprised that the UPDATE line did not appear in the output.
That would seem to indicate that there is no colid = 1 for the table.
I was able to create that condition by doing an ALTER TABLE to drop
the first column, but it is a condition my original query did not
allow for.

Did you try the alternate syntax I provided? What do you get from
this query?

SELECT MIN(colid), MAX(colod), count(colid), count(distinct colid)
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname

As for executing the code from inside the stored procedure, it is
possible. The command is spread over many rows, so the first step is
to turn that query into a cursor and step through the rows
concatenating all of them into a single string. Then you would have
to use dynamic SQL to execute it. Before doing that I suggest reading
this article very carefully: http://www.sommarskog.se/dynamic_sql.html
Roy Harvey
Beacon Falls, CT

Quote:

Originally Posted by

>Roy,
>
>Thank you for your time and helping me out. In my last reply the
>output I copied was wrong. Here is the code and output I get from your
>script:
>declare @.tblname nvarchar(60)
>set @.tblname = '_PHYSICIAN_PROFILE'
>
>SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid
>
>======= output 42 rows =========
>
_NAME= replace(cast(_NAME as
>nvarchar(255)),char(13)+char(10),':'),
_SPECIALTY= replace(cast(_SPECIALTY as
>nvarchar(255)),char(13)+char(10),':'),
_GENDER= replace(cast(_GENDER as
>nvarchar(255)),char(13)+char(10),':'),
_SPECIAL_INTERESTS= replace(cast(_SPECIAL_INTERESTS as
>nvarchar(255)),char(13)+char(10),':'),
_PRACTICE_HIGHLIGHTS= replace(cast(_PRACTICE_HIGHLIGHTS as
>nvarchar(255)),char(13)+char(10),':'),
_TRAINING_POST_GRADUATE_EDUCATION=
>replace(cast(_TRAINING_POST_GRADUATE_EDUCATION as
>nvarchar(255)),char(13)+char(10),':'),
_BOARD_CERTIFICATION= replace(cast(_BOARD_CERTIFICATION as
>nvarchar(255)),char(13)+char(10),':'),
_LANGUAGES_SPOKEN= replace(cast(_LANGUAGES_SPOKEN as
>nvarchar(255)),char(13)+char(10),':'),
_INSURANCE_ACCEPTED= replace(cast(_INSURANCE_ACCEPTED as
>nvarchar(255)),char(13)+char(10),':'),
_PERSONAL_INFORMATION= replace(cast(_PERSONAL_INFORMATION as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_1= replace(cast(_ADDRESS1_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_2= replace(cast(_ADDRESS1_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_3= replace(cast(_ADDRESS1_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE1= replace(cast(_PHONE1 as
>nvarchar(255)),char(13)+char(10),':'),
_FAX1= replace(cast(_FAX1 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK1= replace(cast(_IN_NETWORK1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_1= replace(cast(_ADDRESS2_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_2= replace(cast(_ADDRESS2_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_3= replace(cast(_ADDRESS2_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE2= replace(cast(_PHONE2 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK2= replace(cast(_IN_NETWORK2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_1= replace(cast(_ADDRESS3_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_2= replace(cast(_ADDRESS3_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_3= replace(cast(_ADDRESS3_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE3= replace(cast(_PHONE3 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK3= replace(cast(_IN_NETWORK3 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_1= replace(cast(_ADDRESS4_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_2= replace(cast(_ADDRESS4_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_3= replace(cast(_ADDRESS4_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE4= replace(cast(_PHONE4 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK4= replace(cast(_IN_NETWORK4 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_1= replace(cast(_ADDRESS5_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_2= replace(cast(_ADDRESS5_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_3= replace(cast(_ADDRESS5_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE5= replace(cast(_PHONE5 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK5= replace(cast(_IN_NETWORK5 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_1= replace(cast(_ADDRESS6_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_2= replace(cast(_ADDRESS6_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_3= replace(cast(_ADDRESS6_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE6= replace(cast(_PHONE6 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK6= replace(cast(_IN_NETWORK6 as
>nvarchar(255)),char(13)+char(10),':'),
META_SRC_URI= replace(cast(META_SRC_URI as
>nvarchar(255)),char(13)+char(10),':');
>
>I don't think I filter out anything yet I don't have UPDATE or SET
>function and the output from this query is just like a select
>statement that does not execute the replacement.
>
>Is there a way actually execute the replace from this stored procedure?

How to run query on system tables of MS Jet

I am new to use Microsoft.Jet.OLEDB.4.0.

I need to run query (Select * from TABLE...) on system tables of my DB.
I can see data in those table from MS Access. But anytime I run query programatically, I got error saying

exception 80040E09, Record(s) cannot be read; no read permission on 'MSysRelationships'. (80040E09)

Can anyone tell me how to run query on sytem tables?

I can do this type of things in MS SQL Server.

Really appreciate your help!

/HannaI have exactly same problem. PLease help !

How to run query between two databases

Hi i have query which i need to join it
like i am doing some thing like
Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN
[B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] =
[b].[dbo].[tbla].[ID]
and i am running that query in database A but the only problem is it saying
that b.dbo.tblb.ID is invalid colum name i dont understand that i am giving
full address of both databases but cannt do it ..... any help thanksYour query seem to be syntactically wrong.
Anyways, correct it and try to add table alias for the tables and use them
to refer to the columns.
Or post the actual query that gave the error you mentioned.
--
"amjad" wrote:

> Hi i have query which i need to join it
> like i am doing some thing like
> Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN
> [B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] =
> [b].[dbo].[tbla].[ID]
> and i am running that query in database A but the only problem is it sayin
g
> that b.dbo.tblb.ID is invalid colum name i dont understand that i am givin
g
> full address of both databases but cannt do it ..... any help thanks|||could you post what your actually doing, rather than something like
what you're doing?
I mean what's an INNER INNER JOIN? is it something that's really
seriously inner on the join? and is it a typo or have you missed out a
"]" in your actual code?|||thanks for help i solved thanks
"Will" wrote:

> could you post what your actually doing, rather than something like
> what you're doing?
> I mean what's an INNER INNER JOIN? is it something that's really
> seriously inner on the join? and is it a typo or have you missed out a
> "]" in your actual code?
>

how to run queries??

Hi,

I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.

i want to write my own querywithout help ofsqlDataSource control from start something like

sqldatasource con=new sqldatasource;

con.connection String=""

then what all things will come...... ?

and please give me some poitners to some articles which help one to do the requested.

Regards,

Please checkheresql

How to run multiple store procedures in SQL Reporting Service

Can we run two store procedures in the Query String under the Dataset section
of SQL Reporting Service 2005? It seems like the Query String only allow one
store procedure. Ww want to use a separate store procedure to open a
symmetric key before we run the second store procedure to retrieve the
sensitive data.What about writing a thrid procedure calling the two others ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--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 query file

I have a query file, test.sql, in the root of my C drive. It is
400,000 lines worth of insert into statements and is so big i cannot
load in into management studio. How can I run this?On Wed, 31 Oct 2007 17:02:36 -0700, "mitchman10@.gmail.com"
<mitchman10@.gmail.comwrote:

Quote:

Originally Posted by

>I have a query file, test.sql, in the root of my C drive. It is
>400,000 lines worth of insert into statements and is so big i cannot
>load in into management studio. How can I run this?

Add a line with nothing but a GO every, oh say after every 100 INSERT
commands. Then you could TRY to execute the file using the command
line utilities OSQL or SQLCMD. I'm not sure if they will choke on a
file that size as I've never had reason to test them in that way.

Roy Harvey
Beacon Falls, CT|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

On Wed, 31 Oct 2007 17:02:36 -0700, "mitchman10@.gmail.com"
<mitchman10@.gmail.comwrote:
>

Quote:

Originally Posted by

>I have a query file, test.sql, in the root of my C drive. It is
>400,000 lines worth of insert into statements and is so big i cannot
>load in into management studio. How can I run this?

>
Add a line with nothing but a GO every, oh say after every 100 INSERT
commands. Then you could TRY to execute the file using the command
line utilities OSQL or SQLCMD. I'm not sure if they will choke on a
file that size as I've never had reason to test them in that way.

Failing that, split it up into multiple files (by whatever means you
like) and execute them individually.|||On Wed, 31 Oct 2007 18:38:12 -0700, Ed Murphy <emurphy42@.socal.rr.com>
wrote:

Quote:

Originally Posted by

>Roy Harvey (SQL Server MVP) wrote:
>

Quote:

Originally Posted by

>On Wed, 31 Oct 2007 17:02:36 -0700, "mitchman10@.gmail.com"
><mitchman10@.gmail.comwrote:
>>

Quote:

Originally Posted by

>>I have a query file, test.sql, in the root of my C drive. It is
>>400,000 lines worth of insert into statements and is so big i cannot
>>load in into management studio. How can I run this?

>>
>Add a line with nothing but a GO every, oh say after every 100 INSERT
>commands. Then you could TRY to execute the file using the command
>line utilities OSQL or SQLCMD. I'm not sure if they will choke on a
>file that size as I've never had reason to test them in that way.

>
>Failing that, split it up into multiple files (by whatever means you
>like) and execute them individually.

Which might be no more work than adding the GO lines.

Roy Harvey
Beacon Falls, CT

how to roll back

is this the only way to do a rollback in SQL2000 ?
Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
undo the changes. And only applicable for UPDATE & DELETE queries within the
transaction.
just want to understand more how to use rollback and how it works.
tks
pkHi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>|||HI Greg,
the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script ne
ver make rollback.
Use the "if @.err != 0" command!
JBandi
-- Greg Linwood wrote: --
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> HI Greg,
> the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script
never make rollback.
> Use the "if @.err != 0" command!
> JBandi
> -- Greg Linwood wrote: --
> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation
(locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message
news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
TRAN to
within
> the|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning
of a sproc, but appearently it did not skip the t-sql errors happended with
in the sproc and rollback. I'm hoping to find a method to skip all error me
ssages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"Greg Linwood" wrote:

> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation (locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> the
>
>

how to roll back

is this the only way to do a rollback in SQL2000 ?
Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
undo the changes. And only applicable for UPDATE & DELETE queries within the
transaction.
just want to understand more how to use rollback and how it works.
tks
pk
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>
|||HI Greg,
the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script never make rollback.
Use the "if @.err != 0" command!
JBandi
-- Greg Linwood wrote: --
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk
|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> HI Greg,
> the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script
never make rollback.
> Use the "if @.err != 0" command!
> JBandi
> -- Greg Linwood wrote: --
> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation
(locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message
news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
TRAN to
within
> the
|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>
|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning of a sproc, but appearently it did not skip the t-sql errors happended within the sproc and rollback. I'm hoping to find a method to skip all error messages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"Greg Linwood" wrote:

> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation (locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> the
>
>