This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps this is just behavior in the dev environment, but if I provide = default values for all of my paramters, the report runs automatically = when I switch to preview.
Is there a way to provide defaults, but still require the user to click = view before the report runs?
Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Perhaps this is just behavior in the = dev environment, but if I provide default values for all of my paramters, = the report runs automatically when I switch to preview.
Is there a way to provide defaults, but = still require the user to click view before the report runs?
Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0--On Mar 1, 5:23 pm, <rlrc...@.newsgroups.nospam> wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks.
As far as I know, there is not a way to keep the report from
automatically running without removing at least one of the parameter's
default values.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||You can probably create a parameter and hide it...and then not provide it
default value.
-
HelpSeeker
"rlrcstr@.newsgroups.nospam" wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks|||Hi rlrcstr,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
How do I insert text into empty xml element in sql server 2005?
Hello
http://groups.google.com/group/microsoft.public.sqlserver.xml/browse_thread/thread/424e62d11c396b3e/bab10449576dccd8#bab10449576dccd8
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
For those interested, this has now been answered via a newsgroup here:http://groups.google.com/group/microsoft.public.sqlserver.xml/browse_thread/thread/424e62d11c396b3e/bab10449576dccd8#bab10449576dccd8
How do I insert text into empty xml element in sql server 2005?
Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
PeterYou would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegroups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>|||Thanks Michael - I appreciate your help.sql
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
PeterYou would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegroups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>|||Thanks Michael - I appreciate your help.sql
How do I insert text into empty xml element in sql server 2005?
Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
Peter
You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegro ups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>
|||Thanks Michael - I appreciate your help.
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
Peter
You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegro ups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>
|||Thanks Michael - I appreciate your help.
How do I insert CR/LF - CHAR(13) does not work
I need to send some long emails, problem is when I send a long body text CDO breaks up the message with spaces in the middle of words. I read on this forum the solution would be to insert CR/LF into the message so that CDO would not have to break it up at every 1k of text. I have tried using CHAR(13) as BOL suggests, but this is not doing as intended, it inserts a space and not the CR/LF I need.
Any suggestions how to insert CR/LF or why my installation is not responding to CHAR(13) is there some other escape code I could use?Char(10) ?|||An ASCII cr/lf is hexidecimal 0D0A. You could declare a char(2) variable and set it to 0x0D0A, then concatinate it to the string at your desired break points.
<code>
declare @.crlf char(2)
select @.crlf = 0x0D0A
</code>
HTH
Tom|||Hmmm, seems to work, my bad - I guess the results in QA are not accurate, but in the email the CR/LF exists.
Any suggestions how to insert CR/LF or why my installation is not responding to CHAR(13) is there some other escape code I could use?Char(10) ?|||An ASCII cr/lf is hexidecimal 0D0A. You could declare a char(2) variable and set it to 0x0D0A, then concatinate it to the string at your desired break points.
<code>
declare @.crlf char(2)
select @.crlf = 0x0D0A
</code>
HTH
Tom|||Hmmm, seems to work, my bad - I guess the results in QA are not accurate, but in the email the CR/LF exists.
How do I Import textfile into SQL table using schema
I've been importing delimited textfile data into Access db using a Schema.in
i
file
to describe the text like this
[Import.TXT] ( where the raw text is )
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1 = Field1 text width 255
Col2 = Field2 text width 255
Col3 = Field3 text width 255
Col4 = Field4 text width 255
With the following code ( VB )
'{Microsoft Text Driver (*.txt; *.csv)}
' paths for the jet engine to find schema & files + text driver for
importation
strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
"DBQ=" & filepath & ";" & _
"DefaultDir=" & filepath & ";" & _
"Uid=Admin;Pwd=;"
' use ADO - init connection
Set adoCn = New ADODB.Connection
adoCn.Open strCn
then
strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
strSQL = strSQL & "FROM " & txtfile
adoCn.Execute strSQL
to import the text into the appropriate fields in the table.
How do I do this with SQL Server 200?
thanks -- jackTry using DTS Wizard to do the job !
run at shell command "dtswiz"
Pollus Brodeur|||Hi
Along with DTS, you can use BCP or the BCP API. You may also want to look at
the BULK INSERT statement.
John
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||Jack,
If the import file will always be in the same directory, you could
set up a linked server to that directory. This will automatically
use the specifications in the schema.ini file. Here is an example
for exporting, but it should be simple to adapt for importing.
1) Create a directory for the text files. I chose E:\txtsrv
2) Add this as a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'e:\txtsrv',
NULL,
'Text'
3) Put an empty text file in this directory (I used empty.txt).
You can do this with master..xp_cmdshell, but I didn't. You
can't do CREATE TABLE on the linked server.
4) Create a schema.ini file to describe how this file should
represent your table. One schema.ini file is used for all the
tables of the linked server.
This is my schema.ini:
[empty.txt]
Format=FixedLength
ColNameHeader=True
Col1=CustomerNumber Long Width 11
Col2=CustomerName Text Width 30
Col3=CustomerDate DateTime Width 25
5) Populate the text-table, replacing the filename's . with # for the
table name.
insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
CustomerDate)
select orderid, customerid, orderdate
from northwind..orders
order by customerid
Steve Kass
Drew University
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||PollusB,
I was able to use the DTS Wizard to get a CSV file into my Table, but I
need to do this programatically on many files every day so a manual operatio
n
is not practical. I have seen some information that indicates DTS can be
used as an object etc., but I have no clue at this point about how that migh
t
be done. If you know how the task can be done using DTS internal to a VB ap
p
I'd sure appreciate it.
thanks for your help -- jack
"PollusB" wrote:
> Try using DTS Wizard to do the job !
> run at shell command "dtswiz"
> Pollus Brodeur
>|||John,
I tried to use a BCP format file, but since my input file has varying
number of columns per row, I wasn't able to get it to work. I've tried the
Bulk Insert approach
and it has the same problem. It wants to have the input 'line' contain the
exact
number of fields as the table, otherwise it gives an unexpected end of line
errror.
I've pretty much given up on the Bulk Insert method because I couldn't find
a method to get around it's limitations. I'm currently looking at how to us
e
DTS in
my application to get the files imported.
Thanks for your suggestions -- jack
"John Bell" wrote:
> Hi
> Along with DTS, you can use BCP or the BCP API. You may also want to look
at
> the BULK INSERT statement.
> John
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>|||Steve,
The technique you've suggested is almost verbatim the method I used for
Access
databases. It worked very well in that environment. I couldn't get the
'linked server' to work; I'm actually not familiar with that technique.
Thanks for your suggested solution and code -- jack
"Steve Kass" wrote:
> Jack,
> If the import file will always be in the same directory, you could
> set up a linked server to that directory. This will automatically
> use the specifications in the schema.ini file. Here is an example
> for exporting, but it should be simple to adapt for importing.
> 1) Create a directory for the text files. I chose E:\txtsrv
> 2) Add this as a linked server
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'e:\txtsrv',
> NULL,
> 'Text'
> 3) Put an empty text file in this directory (I used empty.txt).
> You can do this with master..xp_cmdshell, but I didn't. You
> can't do CREATE TABLE on the linked server.
> 4) Create a schema.ini file to describe how this file should
> represent your table. One schema.ini file is used for all the
> tables of the linked server.
> This is my schema.ini:
> [empty.txt]
> Format=FixedLength
> ColNameHeader=True
> Col1=CustomerNumber Long Width 11
> Col2=CustomerName Text Width 30
> Col3=CustomerDate DateTime Width 25
> 5) Populate the text-table, replacing the filename's . with # for the
> table name.
> insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
> CustomerDate)
> select orderid, customerid, orderdate
> from northwind..orders
> order by customerid
> Steve Kass
> Drew University
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>
i
file
to describe the text like this
[Import.TXT] ( where the raw text is )
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1 = Field1 text width 255
Col2 = Field2 text width 255
Col3 = Field3 text width 255
Col4 = Field4 text width 255
With the following code ( VB )
'{Microsoft Text Driver (*.txt; *.csv)}
' paths for the jet engine to find schema & files + text driver for
importation
strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
"DBQ=" & filepath & ";" & _
"DefaultDir=" & filepath & ";" & _
"Uid=Admin;Pwd=;"
' use ADO - init connection
Set adoCn = New ADODB.Connection
adoCn.Open strCn
then
strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
strSQL = strSQL & "FROM " & txtfile
adoCn.Execute strSQL
to import the text into the appropriate fields in the table.
How do I do this with SQL Server 200?
thanks -- jackTry using DTS Wizard to do the job !
run at shell command "dtswiz"
Pollus Brodeur|||Hi
Along with DTS, you can use BCP or the BCP API. You may also want to look at
the BULK INSERT statement.
John
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||Jack,
If the import file will always be in the same directory, you could
set up a linked server to that directory. This will automatically
use the specifications in the schema.ini file. Here is an example
for exporting, but it should be simple to adapt for importing.
1) Create a directory for the text files. I chose E:\txtsrv
2) Add this as a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'e:\txtsrv',
NULL,
'Text'
3) Put an empty text file in this directory (I used empty.txt).
You can do this with master..xp_cmdshell, but I didn't. You
can't do CREATE TABLE on the linked server.
4) Create a schema.ini file to describe how this file should
represent your table. One schema.ini file is used for all the
tables of the linked server.
This is my schema.ini:
[empty.txt]
Format=FixedLength
ColNameHeader=True
Col1=CustomerNumber Long Width 11
Col2=CustomerName Text Width 30
Col3=CustomerDate DateTime Width 25
5) Populate the text-table, replacing the filename's . with # for the
table name.
insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
CustomerDate)
select orderid, customerid, orderdate
from northwind..orders
order by customerid
Steve Kass
Drew University
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||PollusB,
I was able to use the DTS Wizard to get a CSV file into my Table, but I
need to do this programatically on many files every day so a manual operatio
n
is not practical. I have seen some information that indicates DTS can be
used as an object etc., but I have no clue at this point about how that migh
t
be done. If you know how the task can be done using DTS internal to a VB ap
p
I'd sure appreciate it.
thanks for your help -- jack
"PollusB" wrote:
> Try using DTS Wizard to do the job !
> run at shell command "dtswiz"
> Pollus Brodeur
>|||John,
I tried to use a BCP format file, but since my input file has varying
number of columns per row, I wasn't able to get it to work. I've tried the
Bulk Insert approach
and it has the same problem. It wants to have the input 'line' contain the
exact
number of fields as the table, otherwise it gives an unexpected end of line
errror.
I've pretty much given up on the Bulk Insert method because I couldn't find
a method to get around it's limitations. I'm currently looking at how to us
e
DTS in
my application to get the files imported.
Thanks for your suggestions -- jack
"John Bell" wrote:
> Hi
> Along with DTS, you can use BCP or the BCP API. You may also want to look
at
> the BULK INSERT statement.
> John
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>|||Steve,
The technique you've suggested is almost verbatim the method I used for
Access
databases. It worked very well in that environment. I couldn't get the
'linked server' to work; I'm actually not familiar with that technique.
Thanks for your suggested solution and code -- jack
"Steve Kass" wrote:
> Jack,
> If the import file will always be in the same directory, you could
> set up a linked server to that directory. This will automatically
> use the specifications in the schema.ini file. Here is an example
> for exporting, but it should be simple to adapt for importing.
> 1) Create a directory for the text files. I chose E:\txtsrv
> 2) Add this as a linked server
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'e:\txtsrv',
> NULL,
> 'Text'
> 3) Put an empty text file in this directory (I used empty.txt).
> You can do this with master..xp_cmdshell, but I didn't. You
> can't do CREATE TABLE on the linked server.
> 4) Create a schema.ini file to describe how this file should
> represent your table. One schema.ini file is used for all the
> tables of the linked server.
> This is my schema.ini:
> [empty.txt]
> Format=FixedLength
> ColNameHeader=True
> Col1=CustomerNumber Long Width 11
> Col2=CustomerName Text Width 30
> Col3=CustomerDate DateTime Width 25
> 5) Populate the text-table, replacing the filename's . with # for the
> table name.
> insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
> CustomerDate)
> select orderid, customerid, orderdate
> from northwind..orders
> order by customerid
> Steve Kass
> Drew University
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>
Monday, March 12, 2012
How do i get a count for one value and a different count from the same value?
I have a field, that display either Yes or no. I want to be able to put in a text box or in a table the count for the yes's and the count for the No's for that one column or various columns. Is this possible?
use a matrix or table
group by the yes/no field
in the value cell put Count(Fields!yes_no.Value)
How do I format data in the SQL statement
It has been a couple of years but I remember being able to format numbers,
dates and text in SQL Server statements, can't find much in online books ,
Where can I relearn this, thanks
JimThat's a little vague but I think you are after CONVERT(). See BooksOnLine
for more details.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
> It has been a couple of years but I remember being able to format numbers,
> dates and text in SQL Server statements, can't find much in online books
> ,
> Where can I relearn this, thanks
> Jim
>|||Sorry
How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do I
convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement of
some kind
Thanks
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That's a little vague but I think you are after CONVERT(). See
> BooksOnLine for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Warren" <jmwarren@.msu.edu> wrote in message
> news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
>|||Yes. It's called CONVERT().
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_2f3o.htm
However, CONVERT will not handle your first requirement. You will need to do
that in your client application.
PS. dates are not stored with any format. They are stored as pairs of
integers. You really need to start reading in Books Online (BOL)
Jim Warren wrote:
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2.
> How do I convert dates from 2005-1-14 to Jan 1, 2005. Is there a
> format statement of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||As I stated and Bob confirmed you would want to look at CONVERT() in
BooksOnLine. You can convert the decimal into an INT and that will loos the
decimal places.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:OI$46h3CFHA.1836@.tk2msftngp13.phx.gbl...
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do
> I convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement
> of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>
dates and text in SQL Server statements, can't find much in online books ,
Where can I relearn this, thanks
JimThat's a little vague but I think you are after CONVERT(). See BooksOnLine
for more details.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
> It has been a couple of years but I remember being able to format numbers,
> dates and text in SQL Server statements, can't find much in online books
> ,
> Where can I relearn this, thanks
> Jim
>|||Sorry
How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do I
convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement of
some kind
Thanks
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That's a little vague but I think you are after CONVERT(). See
> BooksOnLine for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Warren" <jmwarren@.msu.edu> wrote in message
> news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
>|||Yes. It's called CONVERT().
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_2f3o.htm
However, CONVERT will not handle your first requirement. You will need to do
that in your client application.
PS. dates are not stored with any format. They are stored as pairs of
integers. You really need to start reading in Books Online (BOL)
Jim Warren wrote:
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2.
> How do I convert dates from 2005-1-14 to Jan 1, 2005. Is there a
> format statement of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||As I stated and Bob confirmed you would want to look at CONVERT() in
BooksOnLine. You can convert the decimal into an INT and that will loos the
decimal places.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:OI$46h3CFHA.1836@.tk2msftngp13.phx.gbl...
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do
> I convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement
> of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>
Friday, March 9, 2012
How do I fire a sql statement from being seen by a trace sessi
:o) Your tip works great for me.. Thank you very much Aleksandar.
"Aleksandar Grbic" wrote:
> you can add sensitive text like "sp_addlogin" as comment in your sql
> statements and sql profiler will hide this statement
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Exonet Developer" wrote:
>But that was bad advice indeed.
Infact another security enhancement in SP4 suppress every statement that
contains the text 'password'
But these things may change in the future.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Exonet Developer" <ExonetDeveloper@.discussions.microsoft.com> wrote in
message news:9AD6E379-C45C-4480-9125-963C17BD952F@.microsoft.com...
> :o) Your tip works great for me.. Thank you very much Aleksandar.
> "Aleksandar Grbic" wrote:
>
"Aleksandar Grbic" wrote:
> you can add sensitive text like "sp_addlogin" as comment in your sql
> statements and sql profiler will hide this statement
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Exonet Developer" wrote:
>But that was bad advice indeed.
Infact another security enhancement in SP4 suppress every statement that
contains the text 'password'
But these things may change in the future.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Exonet Developer" <ExonetDeveloper@.discussions.microsoft.com> wrote in
message news:9AD6E379-C45C-4480-9125-963C17BD952F@.microsoft.com...
> :o) Your tip works great for me.. Thank you very much Aleksandar.
> "Aleksandar Grbic" wrote:
>
Sunday, February 19, 2012
How do i display a checkbox on a report?
Hi,
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?
Thanks,
Sam.I've managed to fix the error, simple mistake. Why is it there are very few controls in reporting services compared with access? Is it possible to create custom controls for reports?
Thanks.|||I've been trying to show a boolean value in the database on the report with either a checkbox, Yes/No, or something easyfor a non-technical user to read.(definitely not True/False) Can you please tell me how to put together the expression with the dataset valuse? How to display unicode characters?|||
I got my solution finally. Stupid me that didn't know I can open up the expression editor to choose functions from, and didn't know I can right-click the field to open up expression editor.
Thanks anyway.
|||
I"m interested in seeing your solution. Can you post as reply to this thread? I'll bet a lot of us report designers could use this little tidbit.
thanks,
|||please post the code for this issue.
thanks.
|||textbox
set properties
Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif( Fields!....Value = "True", "P", "" )
How do i display a checkbox on a report?
Hi,
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?
Thanks,
Sam.I've managed to fix the error, simple mistake. Why is it there are very few controls in reporting services compared with access? Is it possible to create custom controls for reports?
Thanks.|||I've been trying to show a boolean value in the database on the report with either a checkbox, Yes/No, or something easyfor a non-technical user to read.(definitely not True/False) Can you please tell me how to put together the expression with the dataset valuse? How to display unicode characters?|||
I got my solution finally. Stupid me that didn't know I can open up the expression editor to choose functions from, and didn't know I can right-click the field to open up expression editor.
Thanks anyway.
|||
I"m interested in seeing your solution. Can you post as reply to this thread? I'll bet a lot of us report designers could use this little tidbit.
thanks,
|||please post the code for this issue.
thanks.
|||textbox
set properties
Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif( Fields!....Value = "True", "P", "" )
How do i display a checkbox on a report?
Hi,
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?
I've been trying to display a checkbox on a report using unicode text eg. ? (U+2616) (Arial Unicode MS). I get the error: BC30037: Character is not valid. I have read somewhere about using html formatting to display it, how could this be done?
Is there any thing else i could do?
Thanks,
Sam.I've managed to fix the error, simple mistake. Why is it there are very few controls in reporting services compared with access? Is it possible to create custom controls for reports?
Thanks.|||I've been trying to show a boolean value in the database on the report with either a checkbox, Yes/No, or something easyfor a non-technical user to read.(definitely not True/False) Can you please tell me how to put together the expression with the dataset valuse? How to display unicode characters?|||
I got my solution finally. Stupid me that didn't know I can open up the expression editor to choose functions from, and didn't know I can right-click the field to open up expression editor.
Thanks anyway.
|||
I"m interested in seeing your solution. Can you post as reply to this thread? I'll bet a lot of us report designers could use this little tidbit.
thanks,
|||please post the code for this issue.
thanks.
|||textbox
set properties
Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif( Fields!....Value = "True", "P", "" )
Subscribe to:
Posts (Atom)