Enterprise Manager question

When I click on a server node in EM, I am prompted to
enter the sa password in hte usual dialog box. However for
one particular server I get this dialog box twice - why is
this different to the others?
TIA,
BBSounds to me like something introduced by installing a security hotfix. Chan you check if that
differ on the server or client level?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:01d601c4a1a5$83884c40$a501280a@.phx.gbl...
> When I click on a server node in EM, I am prompted to
> enter the sa password in hte usual dialog box. However for
> one particular server I get this dialog box twice - why is
> this different to the others?
> TIA,
> BB

Enterprise Manager data entry limit

I have a table that has several fields of data type varchar
(4000). I cannot enter more that 1024 characters using
Enterprise Manager. I can create a table in Access and
import it or use VB to populate the table without any
problem. Why doesn't EM allow data to be entered up to the
field length in the table design?
Hi,
Sorry to tell this.
Enterprise manager is a GUI tool to do database administration, it is not a
tool to do data entry. You can very well
use Query Analyzer to insert the text data into a table using INSERT
statement.
Insert into table_name values('jhsajdhaskdlksadhsalkdjsaldjlsadjlsajdjl.. .n
characters)
Thanks
Hari
MCDBA
"Colin" <anonymous@.discussions.microsoft.com> wrote in message
news:1db6a01c4546c$17824130$a101280a@.phx.gbl...
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?
|||Colin,
I don't know the answer to your question, perhaps its a limit of the
grid control that is used? I have a question for you. Why do you want to
insert such huge values through Enterprise Manager? Why not just write a
quick script in Query Analyzer to insert the data?
Also beware that you could run into problems if all your field lengths
exceed the size of a data page (8k).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Colin wrote:
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?
|||Thanks for the replies.
I understand EM is not the tool to use for data
entry...just curious why it limited data entry in this
way.
|||Hi Colin,
I've been chatting with the SQL MVPs and some people at Microsoft about
this issue on your behalf, and I was told to RTFM. :-) SQL MVP, Steve
Kass pointed this out to me:
"Some of the limitations of Enterprise Manager are documented in the
following Books Online article:
Editing Rows in the Results Pane
In particular, that article says:
You can edit a memo-type column (such as a text, memo, or long character
column) if the column does not display <Long Text>. The Results pane can
accept up to 900 characters of text in memo-type columns. If you are
typing in a cell and exceed the amount of text that the Results pane can
accept, the Query Designer will beep to indicate that you have exceeded
this limit."
I hope this helps further.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Colin wrote:
> Thanks for the replies.
> I understand EM is not the tool to use for data
> entry...just curious why it limited data entry in this
> way.

Enterprise Manager data entry limit

I have a table that has several fields of data type varchar
(4000). I cannot enter more that 1024 characters using
Enterprise Manager. I can create a table in Access and
import it or use VB to populate the table without any
problem. Why doesn't EM allow data to be entered up to the
field length in the table design?Hi,
Sorry to tell this.
Enterprise manager is a GUI tool to do database administration, it is not a
tool to do data entry. You can very well
use Query Analyzer to insert the text data into a table using INSERT
statement.
Insert into table_name values('jhsajdhaskdlksadhsalkdjsaldjlsad
jlsajdjl...n
characters)
Thanks
Hari
MCDBA
"Colin" <anonymous@.discussions.microsoft.com> wrote in message
news:1db6a01c4546c$17824130$a101280a@.phx
.gbl...
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?|||Colin,
I don't know the answer to your question, perhaps its a limit of the
grid control that is used? I have a question for you. Why do you want to
insert such huge values through Enterprise Manager? Why not just write a
quick script in Query Analyzer to insert the data?
Also beware that you could run into problems if all your field lengths
exceed the size of a data page (8k).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Colin wrote:
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?|||Thanks for the replies.
I understand EM is not the tool to use for data
entry...just curious why it limited data entry in this
way.|||Hi Colin,
I've been chatting with the SQL MVPs and some people at Microsoft about
this issue on your behalf, and I was told to RTFM. :-) SQL MVP, Steve
Kass pointed this out to me:
"Some of the limitations of Enterprise Manager are documented in the
following Books Online article:
Editing Rows in the Results Pane
In particular, that article says:
You can edit a memo-type column (such as a text, memo, or long character
column) if the column does not display <Long Text>. The Results pane can
accept up to 900 characters of text in memo-type columns. If you are
typing in a cell and exceed the amount of text that the Results pane can
accept, the Query Designer will beep to indicate that you have exceeded
this limit."
I hope this helps further.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Colin wrote:
> Thanks for the replies.
> I understand EM is not the tool to use for data
> entry...just curious why it limited data entry in this
> way.

Enterprise Manager data entry limit

I have a table that has several fields of data type varchar
(4000). I cannot enter more that 1024 characters using
Enterprise Manager. I can create a table in Access and
import it or use VB to populate the table without any
problem. Why doesn't EM allow data to be entered up to the
field length in the table design?Hi,
Sorry to tell this.
Enterprise manager is a GUI tool to do database administration, it is not a
tool to do data entry. You can very well
use Query Analyzer to insert the text data into a table using INSERT
statement.
Insert into table_name values('jhsajdhaskdlksadhsalkdjsaldjlsadjlsajdjl...n
characters)
Thanks
Hari
MCDBA
"Colin" <anonymous@.discussions.microsoft.com> wrote in message
news:1db6a01c4546c$17824130$a101280a@.phx.gbl...
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?|||Colin,
I don't know the answer to your question, perhaps its a limit of the
grid control that is used? I have a question for you. Why do you want to
insert such huge values through Enterprise Manager? Why not just write a
quick script in Query Analyzer to insert the data?
Also beware that you could run into problems if all your field lengths
exceed the size of a data page (8k).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Colin wrote:
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?|||Thanks for the replies.
I understand EM is not the tool to use for data
entry...just curious why it limited data entry in this
way.|||Hi Colin,
I've been chatting with the SQL MVPs and some people at Microsoft about
this issue on your behalf, and I was told to RTFM. :-) SQL MVP, Steve
Kass pointed this out to me:
"Some of the limitations of Enterprise Manager are documented in the
following Books Online article:
Editing Rows in the Results Pane
In particular, that article says:
You can edit a memo-type column (such as a text, memo, or long character
column) if the column does not display <Long Text>. The Results pane can
accept up to 900 characters of text in memo-type columns. If you are
typing in a cell and exceed the amount of text that the Results pane can
accept, the Query Designer will beep to indicate that you have exceeded
this limit."
I hope this helps further.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Colin wrote:
> Thanks for the replies.
> I understand EM is not the tool to use for data
> entry...just curious why it limited data entry in this
> way.|||Can one of you experts give me some solutions?
We have small web-based sale order system linked to SQL server. One of the web page fields: "Remark" field only accepts 250 characters.
On the web page, the Remark field will let you type more than 250 characters. However, it only display maximum 250 characters in print preview. Any solutions?
Thank you in advance for any sugguestions.
"Colin" wrote:
> I have a table that has several fields of data type varchar
> (4000). I cannot enter more that 1024 characters using
> Enterprise Manager. I can create a table in Access and
> import it or use VB to populate the table without any
> problem. Why doesn't EM allow data to be entered up to the
> field length in the table design?
>

Enterprice manager.

Hi there.
When i allow remote connection to my sql with Enterprice Manager .. users
see
the list of all databases but can only enter there own .
How can i make it so, so they only see there own database witch there user
has
access to ?
Regards.
Steinarr.Hi,
This is not possible in SQL 7.0 and SQL 2000.
Thanks
Hari
SQL Server MVP
"Steinarr G." <steinki@.dynamicsystems.dk> wrote in message
news:eH$JN3JrFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Hi there.
> When i allow remote connection to my sql with Enterprice Manager .. users
> see
> the list of all databases but can only enter there own .
> How can i make it so, so they only see there own database witch there user
> has
> access to ?
> Regards.
> Steinarr.
>|||Thanks Hari.
Now at least i know it for sure.
Regards.
Steinarr.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ueE%2364JrFHA.716@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is not possible in SQL 7.0 and SQL 2000.
> Thanks
> Hari
> SQL Server MVP
> "Steinarr G." <steinki@.dynamicsystems.dk> wrote in message
> news:eH$JN3JrFHA.3264@.TK2MSFTNGP12.phx.gbl...
>

entering store procedure result to table

Hello there
is there a way to enter store procedure result to table?insert into tblname
exec stored_proc1
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Roy Goldhammer" wrote:

> Hello there
> is there a way to enter store procedure result to table?
>
>|||Thankes A lot
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:DB2F9A89-FFC1-4B17-ABBD-3A45C8B24867@.microsoft.com...
> insert into tblname
> exec stored_proc1
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Roy Goldhammer" wrote:
>|||Whell Omnibuzz.
Now i need to enter it to function in order to use it as generic function
My function looks like this:
create function fnDir(@.Path as varchar(8000))
returns @.ret table(FileName varchar(1000))
as
begin
DECLARE @.dir varchar(1000)
set @.dir = 'dir ' + @.Path + '/b'
insert @.ret
exec master..xp_cmdshell @.DIR
end
and it gives me error:
EXECUTE cannot be used as a source when inserting into a table variable.
how can i solve it?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:DB2F9A89-FFC1-4B17-ABBD-3A45C8B24867@.microsoft.com...
> insert into tblname
> exec stored_proc1
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Roy Goldhammer" wrote:
>|||Roy,shalom
The error message is pretty clear. You cannot use EXEC command within a UDF.
Instead , create a stored procedure
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ehGpAc5kGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Whell Omnibuzz.
> Now i need to enter it to function in order to use it as generic function
> My function looks like this:
> create function fnDir(@.Path as varchar(8000))
> returns @.ret table(FileName varchar(1000))
> as
> begin
> DECLARE @.dir varchar(1000)
> set @.dir = 'dir ' + @.Path + '/b'
> insert @.ret
> exec master..xp_cmdshell @.DIR
> end
> and it gives me error:
> EXECUTE cannot be used as a source when inserting into a table variable.
> how can i solve it?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:DB2F9A89-FFC1-4B17-ABBD-3A45C8B24867@.microsoft.com...
>|||Whell Uri.
I need it because other wants to use it as select *
from [process]
the only way i know to use it is by that
do you have any idea?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ui9Sbh5kGHA.896@.TK2MSFTNGP04.phx.gbl...
> Roy,shalom
> The error message is pretty clear. You cannot use EXEC command within a
> UDF. Instead , create a stored procedure
>
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ehGpAc5kGHA.3816@.TK2MSFTNGP02.phx.gbl...
>|||> Whell Uri.
> I need it because other wants to use it as select *
> from [process]
> the only way i know to use it is by that
> do you have any idea?
Yes, teach them to use EXEC [process];|||Roy
I'm a little bit . What is prevent you from using this code inside
the SP, ahhh I see because someone wants to run SELECT *, so
you can run EXEC sp and that will returt the same data , does it matter to
him?
And as you probably know that using SELECT * in the production is considered
a bad practice
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23yHLBo5kGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Whell Uri.
> I need it because other wants to use it as select *
> from [process]
> the only way i know to use it is by that
> do you have any idea?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ui9Sbh5kGHA.896@.TK2MSFTNGP04.phx.gbl...
>|||In SQL 2005 you could use a CLR function in SQL 2000, however, you could use
sp_OA* system procedures to instantiate a file system object
("Scripting.FileSystemObject") then traverse the selected folder to get a
list of file names to return as the result of the function.
What's your version? Anyway, there are many good examples in Books Online
(and elsewhere on MSDN).
ML
http://milambda.blogspot.com/|||Whell Aaron
they need it for anoter sql in order to add it to join.
how can i do it with store procedue?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2354Ptv5kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>
> Yes, teach them to use EXEC [process];
>
>

Entering null into blank fields in the database

How can I enter NULL manually in one of the rows in Sql Server database. Those rows does not contain any data.
you can use DbNull.Value. If its a datetime datatype you need to use SqlDateTime.Null and import Sqltypes namespace.
|||well the problem is bit different. I have 4 rows with 4 fields each. Like Name, Age, Address, Phone number.
Now Name, Age , Address is filled with some data. But I forgot to adddata for the phone and now its just blank. How can I make this nullinstead of blank.

|||UPDATE
<table>
SET
[phone]= NULL
WHERe
[phone] = ' '

Entering Notes/Comments/Remarks to Report

Hi, there,

We intended to create a report that allows users to enter remarks, comments or additional notes in supporting the report.

Can it be done in SSRS? FYI, we are using SQL Server 2005 Enterprise.

Thank you.

Regards,

Yong Hwee

You want to annotate within the report, or you want to add extended properties to the report? If it is the first, then there is no way without changing the RDL or annotating the exported format. If it is the second, then you can use SetProperties/GetProperties to write/retrieve extended properties (including your annotations) programmatically.|||

Hi, John,

Thank you for your reply. Sorry for my ignorance. In fact, we are still thinking the way to cater this requirement. Currently, the report is in Excel workbook. Annotations are entered directly to the workbook. Users are producing this report every mth. If possible, we would like users to enter the annotations to the report and saved. It seems like the second method using SetProperties/GetProperties is the better choice but what are the possible ways of implemeting it?

We are looking for a intuitive solution that allows monthly reports to be saved and retrieved later. The results in the saved reports should not change irregardless of the underlying source. I think this is just like the 'report snapshot' in SSRS.

Thank you.

Regards,

Yong Hwee

|||You can use history snapshots for archiving previously generated reports, but we don't support setting/getting properties on individual snapshots, only the report item itself.|||

Hi, John,

Thank you for the reply.

Regards,

Yong Hwee

Entering Notes/Comments/Remarks to Report

Hi, there,

We intended to create a report that allows users to enter remarks, comments or additional notes in supporting the report.

Can it be done in SSRS? FYI, we are using SQL Server 2005 Enterprise.

Thank you.

Regards,

Yong Hwee

You want to annotate within the report, or you want to add extended properties to the report? If it is the first, then there is no way without changing the RDL or annotating the exported format. If it is the second, then you can use SetProperties/GetProperties to write/retrieve extended properties (including your annotations) programmatically.|||

Hi, John,

Thank you for your reply. Sorry for my ignorance. In fact, we are still thinking the way to cater this requirement. Currently, the report is in Excel workbook. Annotations are entered directly to the workbook. Users are producing this report every mth. If possible, we would like users to enter the annotations to the report and saved. It seems like the second method using SetProperties/GetProperties is the better choice but what are the possible ways of implemeting it?

We are looking for a intuitive solution that allows monthly reports to be saved and retrieved later. The results in the saved reports should not change irregardless of the underlying source. I think this is just like the 'report snapshot' in SSRS.

Thank you.

Regards,

Yong Hwee

|||You can use history snapshots for archiving previously generated reports, but we don't support setting/getting properties on individual snapshots, only the report item itself.|||

Hi, John,

Thank you for the reply.

Regards,

Yong Hwee

Entering Empty Node Using An Updategram

Hi there,
I was wondering if anyone has found how to add an empty (not NULL) node
to an update. I found if you create a node you can get it to enter an
empty space by setting the default value to ('') on the database so
that when you query the data you have an empty node returned. The
problem I'm having is when I want to "blank" a node so I have something
like:
<before>
<node>Some text</node>
</before>
<after>
<node/>
</after>
which will result in a NULL value. Is there something I can put in the
after node that will result in it producing the same as ('') or
something to that effect?
Thanks,
GaryGary,
I'm assuming you are using the XML datatype and want to perform this DML via
the .modify() method.
In that case, you can use the replace value of DML statement. The new value
you want is "empty" or (). Here is an example:
declare @.x xml
set @.x = '<foo>bar</foo>'
set @.x.modify('replace value of /foo[1]/text()[1] with ()')
select @.x
You will most likely need to modify this DML if you are using a typed xml
column.
Regards,
Galex Yen
"Gary" wrote:

> Hi there,
> I was wondering if anyone has found how to add an empty (not NULL) node
> to an update. I found if you create a node you can get it to enter an
> empty space by setting the default value to ('') on the database so
> that when you query the data you have an empty node returned. The
> problem I'm having is when I want to "blank" a node so I have something
> like:
> <before>
> <node>Some text</node>
> </before>
> <after>
> <node/>
> </after>
> which will result in a NULL value. Is there something I can put in the
> after node that will result in it producing the same as ('') or
> something to that effect?
> Thanks,
> Gary
>|||you need to use xsi:nil. Here is the documentation and an example:
http://msdn2.microsoft.com/en-us/library/ms171764.aspx
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<updg:sync mapping-schema='StudentSchema.xml'>
<updg:before/>
<updg:after>
<Student SID="S00004" lname="Elmaci" minitial="" years="2">
<fname xsi:nil="true">
</fname>
</Student>
</updg:after>
</updg:sync>
</ROOT>
"Gary" wrote:

> Hi there,
> I was wondering if anyone has found how to add an empty (not NULL) node
> to an update. I found if you create a node you can get it to enter an
> empty space by setting the default value to ('') on the database so
> that when you query the data you have an empty node returned. The
> problem I'm having is when I want to "blank" a node so I have something
> like:
> <before>
> <node>Some text</node>
> </before>
> <after>
> <node/>
> </after>
> which will result in a NULL value. Is there something I can put in the
> after node that will result in it producing the same as ('') or
> something to that effect?
> Thanks,
> Gary
>

Entering Empty Node Using An Updategram

Hi there,
I was wondering if anyone has found how to add an empty (not NULL) node
to an update. I found if you create a node you can get it to enter an
empty space by setting the default value to ('') on the database so
that when you query the data you have an empty node returned. The
problem I'm having is when I want to "blank" a node so I have something
like:
<before>
<node>Some text</node>
</before>
<after>
<node/>
</after>
which will result in a NULL value. Is there something I can put in the
after node that will result in it producing the same as ('') or
something to that effect?
Thanks,
Gary
Gary,
I'm assuming you are using the XML datatype and want to perform this DML via
the .modify() method.
In that case, you can use the replace value of DML statement. The new value
you want is "empty" or (). Here is an example:
declare @.x xml
set @.x = '<foo>bar</foo>'
set @.x.modify('replace value of /foo[1]/text()[1] with ()')
select @.x
You will most likely need to modify this DML if you are using a typed xml
column.
Regards,
Galex Yen
"Gary" wrote:

> Hi there,
> I was wondering if anyone has found how to add an empty (not NULL) node
> to an update. I found if you create a node you can get it to enter an
> empty space by setting the default value to ('') on the database so
> that when you query the data you have an empty node returned. The
> problem I'm having is when I want to "blank" a node so I have something
> like:
> <before>
> <node>Some text</node>
> </before>
> <after>
> <node/>
> </after>
> which will result in a NULL value. Is there something I can put in the
> after node that will result in it producing the same as ('') or
> something to that effect?
> Thanks,
> Gary
>
|||you need to use xsi:nil. Here is the documentation and an example:
http://msdn2.microsoft.com/en-us/library/ms171764.aspx
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<updg:sync mapping-schema='StudentSchema.xml'>
<updg:before/>
<updg:after>
<Student SID="S00004" lname="Elmaci" minitial="" years="2">
<fname xsi:nil="true">
</fname>
</Student>
</updg:after>
</updg:sync>
</ROOT>
"Gary" wrote:

> Hi there,
> I was wondering if anyone has found how to add an empty (not NULL) node
> to an update. I found if you create a node you can get it to enter an
> empty space by setting the default value to ('') on the database so
> that when you query the data you have an empty node returned. The
> problem I'm having is when I want to "blank" a node so I have something
> like:
> <before>
> <node>Some text</node>
> </before>
> <after>
> <node/>
> </after>
> which will result in a NULL value. Is there something I can put in the
> after node that will result in it producing the same as ('') or
> something to that effect?
> Thanks,
> Gary
>

Entering data into tables

How does one enter data into the tables of a new database in the first place? I'm using SQL Server Management Studio Express. I can name the columns, but that seems to be as far as I get. Thanks for any Help.

I would recommend you use the T-SQL INSERT statement for adding data to the table.

Please take a look at the following tutorial titled "Inserting and Updating Data In a Table"

http://msdn2.microsoft.com/en-us/library/ms365309.aspx.

Kind Regards,
Jaaved

|||

Thanks a lot, as you can tell, I'm brand new to this SQL. Just trying to learn the ropes.

Thanks again.

Entering a row from a table to a column of other table

Is it possible to enter a complete record into a column say varchar(max) and then be able to retreive the record from the column. I am using sql 2005.

Thanks very much in advance,

you can use a separator for columns and insert it into a table.

insert into tablename (varcharnmaxcolumn)

select col1 +'|' + col2+'|' +col3

from table1

and during retrieval you can split the output as per separator as '|' in this case

Entering a Guid in Enterprise Manager

A very simple question...
When I edit the data in a table how can I enter a Guid value in Enterprise
Manager?
I've tried:
{AAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE}
AAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE
'AAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'
nothing works!
Hi
{E5215E35-1D3B-40B1-9F8E-A0B7BEF39F03} works perfectly for me
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sm" <sm@.smith.com.invalid> wrote in message
news:%23PkcSZpSFHA.2124@.TK2MSFTNGP14.phx.gbl...
>A very simple question...
> When I edit the data in a table how can I enter a Guid value in Enterprise
> Manager?
> I've tried:
> {AAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE}
> AAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE
> 'AAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'
> nothing works!
>
|||Thanks.
Actually generating a Guid in registry format with guidgen.exe (Visual
Studio Tools menu) and then pasting it in the column doesn't seem to work.
It's probably because of a carriage return.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ecp1cBqSFHA.2872@.TK2MSFTNGP14.phx.gbl...
> Hi
> {E5215E35-1D3B-40B1-9F8E-A0B7BEF39F03} works perfectly for me
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "sm" <sm@.smith.com.invalid> wrote in message
> news:%23PkcSZpSFHA.2124@.TK2MSFTNGP14.phx.gbl...
>

Entering <NULL> into column via EM

How do I enter <NULL> into table column via EM?
I thought it was Shift + Enter or CTRL+Shift + Enter - but no dice.
Thanks.Its CTRL+0
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Guadala Harry" <GMan@.NoSpam.net> schrieb im Newsbeitrag
news:O9rXxMdSFHA.248@.TK2MSFTNGP15.phx.gbl...
> How do I enter <NULL> into table column via EM?
> I thought it was Shift + Enter or CTRL+Shift + Enter - but no dice.
> Thanks.
>|||CTRL + 0

Enter userid and password for the databse (Crystal Reports 10)

Hi,

I have updated crystal reports from version 8 to 10. Updated the datasource, using odbc connection to connect to SQL Server. Reports run fine on my machine. When I run reports on the server I get enter userid and password for the database. Looks like, when I run reports on the server it's caching Server name from my machine. I have clear cache on the server which did not help. I am sending userid and pwd in the URL to Crystal reports. Please let me know, if there is a work around. FYI, reports work fine in production.

Thanks in Advance,
BhavnaDon't check Trusted connection checkbox when setting Datasource location. Unchecking this check box resolved the issue.

-Bhavna

Enter Today's Date in SSMS Open Table Grid View

What date constants are available for entering today's date in a
datetime column after using the Open Table command within SSMS? I
have tried GETDATE() and CURRENT_TIMESTAMP, but neither worked.
Another question - if I open the samed named table in two databases to
compare the data contents using Open Table, the tab labels only
display the table name and are not fully qualified. Is there an SSMS
option to show the table labels fully qualified.
Thanks in advance - Peter
pwc (pcrickman@.verizon.net) writes:
> What date constants are available for entering today's date in a
> datetime column after using the Open Table command within SSMS? I
> have tried GETDATE() and CURRENT_TIMESTAMP, but neither worked.
You look at your watch, and the type what you see. Open Table is a very
simple data-entry tool.
If you don't want to consult your watch, you will have to write an INSERT
or an UPDATE statement instead.

> Another question - if I open the samed named table in two databases to
> compare the data contents using Open Table, the tab labels only
> display the table name and are not fully qualified. Is there an SSMS
> option to show the table labels fully qualified.
Yes, it's called SP2. That is, they've changed this in SP2 so that the
database name is included.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Enter Parameter Value

Hey in Access MDB you can create a query that when you run it, it will ask you to Enter the Paramer Value, User could enter in a year or Team Member Number or however you have the query set up. is this possible in SQL ADP?No, not in SQL. Prompting the user for input is the responsibility of the interface, not the database engine. Access has an interface packaged with the engine. SQL Server is purely a database engine.

Your ASP page will need to check the stored procedure to determine which parameters are required and provide a means for the user to enter them.|||Thank you Blindman, wasnt sure|||How have you been??|||I actually think that an ADP (Access Data Project) can do interactive prompting, but I'm not sure how to make that happen. Since all of the ADP code actually runs on the client (either via the web page or within the project itself), I don't see any problem...

I'll have to experiment and see if I can finger out how to make this fly, but it won't be today.

-PatP|||Yeah I was hoping there would be a way of doing that, I'll have to do some investigating. It would make things a little easier for me though|||Even if you could find a way to automate this at the ASP layer, wouldn't it be limited to prompting for the often cryptic parameter names specified by the procedure developer?

Not very user friendly...|||I'm not sure what ADP projects to ASP. I think that Desiree is looking for a pure ADP configuration at least for now. I'll investigate both ADP and how it projects into ASP though.

-PatP|||ADP ASP ADP ASP ADP ASP ADP ASP...

Oh. aDp!

<Mild embarrasment./>|||I've never had that happen, but I read about it in this book once... ;)

-PatP|||When I open up a stored procedure in an Access Data Project it does prompt me for the parameter.

Enter Month and year option

Hi everyone me again, I have an MDB that I need to Change into an ADP, SQL server is giving me fits on this and I need to make it so that when the users open this report it gives them the option to enter in the month and year in this format "January 2007" and then get the results on the report. The person who created the MDB gave that option with the code below how do I interpret that from Jet SQL to SQL Server?

Code Snippet

SELECT DISTINCTROW Inspectors.[Last Name], Inspectors.[First Name], Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], Count(*) AS [Count Of Main Table]
FROM Inspectors INNER JOIN [Main Table] ON Inspectors.ID = [Main Table].Inspector
GROUP BY Inspectors.[Last Name], Inspectors.[First Name], Format$([Main Table].Date,'mmmm yyyy'), Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and Year]));

This is what I got so far but its giving me fits

Code Snippet

SELECT Inspector, 'Date: Year([Main Table]).Date,mm yyyy)' AS [Date by Month], COUNT('Count of [Main Table]:Count(*)') AS [Count], YEAR(Date)
* 12 + DATEPART('m', Date) - 1 AS Year, 'Date:([Main Table].[Date],mmmm yyyy)' AS [Enter Month and Year]
FROM dbo.[Main Table]
GROUP BY Inspector, 'Date: Year([Main Table]).Date,mm yyyy)', YEAR(Date) * 12 + DATEPART('m', Date) - 1
HAVING ('Date:([Main Table].[Date],mmmm yyyy)' = @.Enter_Month AND 'Date:([Main Table].[Date],mmmm yyyy)' = @.Enter_Year)

You need to use the DATEPART function to get different pieces from your dates.

This documentation on MSDN should get you through it.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx

The way you are calling your functions is syntactically incorrect. Simply use the function name and a column alias if neccessary.

Code Snippet

SELECT DATEPART(yyyy, t.date) AS Year

,COUNT(*) AS Count

FROM table t

GROUP BY t.Date

|||

Is this what you want the output to look like:

Inspector Date by Month Year Enter Month and Year
-- - -- --
1 3 2007 3 2007

or maybe something like:

declare @.enter_year integer set @.enter_year = 2007
declare @.enter_month integer set @.enter_month = 3

insert into dbo.[main table]
select 1, '3/15/7'

SELECT Inspector,
convert(varchar(2), datepart(mm, date)) as [Date by Month],
YEAR(Date) AS Year,
count(*) as [count],
convert(varchar(2), datepart(mm, date)) + ' '
+ convert (varchar(4), year(date)) as [Enter Month and Year]
FROM dbo.[Main Table]
where year(date) = @.enter_year
and month(date) = @.enter_month
group by inspector,
convert(varchar(2), datepart(mm, date)),
YEAR(Date),
convert(varchar(2), datepart(mm, date)) + ' '
+ convert (varchar(4), year(date))

-- Inspector Date by Month Year count Enter Month and Year
-- -- - -- -- --
-- 1 3 2007 1 3 2007

|||Well not exaclty see when they execute the query it asks them to enter in a date [Month and Year] then you get the results. It basically tells the users how many reports each inspector did for that month and year and they it gives the over all total|||

I think that this is basically the query you want.

Code Snippet

SELECT I.[Last Name], I.[First Name],
[Date by Month] = DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]),
[Count Of Main Table] = count(*)
FROM Inspectors I
INNER JOIN [Main Table] MT
ON (I.ID = MT.Inspector)
GROUP BY I.[Last Name], I.[First Name],
DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE])
WHERE (MT.[Date] >= CONVERT(datetime, @.EnterMonthYear)) AND
(MT.[Date] < DATEADD(month, 1, CONVERT(datetime, @.EnterMonthYear)))

@.EnterMonthYear is a varchar variable containing the input date in the form 'mmmm yyyy'. This SQL will error if the text in the variable cannot be converted to a date.

I removed the "DATEPART(year, MT.[Date]) * 12 + DATEPART(month, MT.[Date]) - 1" expression as it simply seemed to have a 1:1 correspondance with the Date by Month expression (so not changing the records generated by the GROUP BY) and was not used anywhere else. It seems to be generating some kind of serial month number.

I simplified the selection condition to work before the grouping and use the datetime fields directly. The one effect of this is that if they input a date with a day number on it (5 January 2007 for instance) they will get the data from 5 January 2007 to 4 February 2007 and will get up to 2 rows per inspector labelled January 2007 and February 2007 grouping this data appropriately.

Rather than using this SQL directly you might like to consider putting it in a stored procedure and calling that if possible. This will allow handling of the bad date problems (and the forcing of the input date to first of the month etc.). Also it will allow any later maintenance to be performed in the datebase rather than application (as long as the stored procedure calling convention/arguments do not change).

Enter Default Values for all columns in all tables except Primary Keys

How can i enter Default Values of " " to all the columns of type character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank youOn Sun, 3 Apr 2005 18:05:46 -0400, serge wrote:

>How can i enter Default Values of " " to all the columns of type character
>of all the tables (excluding system tables) and Default Values of 0
>of all columns of type numbers. Excluding all primary key columns.
>Thank you

Hi Serge,

CREATE TABLE Example (PKCol int NOT NULL PRIMARY KEY,
NumCol int NOT NULL DEFAULT 0,
CharCol varchar(20) NOT NULL DEFAULT '')
go
INSERT Example (PKCol)
VALUES (1)
SELECT * FROM Example
go
DROP TABLE Example
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)