"standard packages" - where to store? Hints needed

Hi,

I would like to implement a kind of standard packages which can be used in all other processes and will be started using the variables.

But I do not know where to store these kind of packages in "best practise", because we

- would like to use them in Dev and in "Real" also without having to change something in the other processes

- we are storing the packages in the folders of the package store

and as far as I understood I would have to share the package store to all developers though that they would be able to do this?

Then I would better choose another folder with defined access rights I think...

Or would it be better to spend some time in developing a custom component?
But this component would work with recordsets rather than the standard data flow elemtents and therefor I would expect a leak of performance...
Or is it possible to do "trasnformation" from a packae to a custom component?

Thanks in advice!

cheers,
Markus

Personally, I think the best way all around is to store the common packages in SQL Server.

Unless you have a specific and valid reason to do otherwise, this is the best way because it handles a lot of the problems nicely like distribution, security and scheduling etc.

I don't understand how the latter questions relate to the common location question, sorry.

K

|||

i agree that sql server is the best place to store packages because it provides better security than the file system.

i believe that developer access should be controlled by a software configuration management system like visual studio team system.

"large value" data type(bcp)

I want to store some binary things(pic and so on), so I create a table which contain a a "varbinary" data-type column.

but 1. I used OPENROWSET to insert the large file in this table. 2. I used master..xp_cmdshell to retrieve data out as a file. One strange thing happened: the size of the input and output is really different(output is 1k bigger than the input file).

and it seems that the file is broken with different file format......

I really don't know why....

Any help would be appriciated.....

kavin

Could you please post a simple repro script? What command or utility are you using with xp_cmdshell to create the file? Is it BCP or OSQL with SELECT or BCP with queryout option and so on?|||

yes, sure.

EXEC master..xp_cmdshell 'bcp " SELECT column1 FROM Products where id =1111111117 " queryout fileName -n -U sa -P -S yourserver.

Is it any problem?

Anyway, thanks.

BR,

kavin

|||and when I compare the two files, I found that it just the 8-bytes at the beginning of file are extra filled. So I'm afraid something wrong with the created file?

<Unsupported Data Type> ?

I have a problem in a store procedure....
the field isn't updated or touched...it's only readed with a nested select...

In the grid pane is showed :<Unsupported Data Type> as this..
http://www.base2.it/img/sql3.JPG

If I force a cast like this..
CAST(derivedtbl_1.Descrizione AS varchar(255)) the value is showed...

tha field in the original table source is definded as varchar(255).
http://www.base2.it/img/sql4.JPG

I don't have any idea... what's the problem ?

Could you share your stored procedure with us? It might help to see what it looks like.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||THIS FIRST RETURN CORRECT DATA...
1)
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = 1040) AND (NOT EXISTS
(SELECT IDMessaggio, Descrizione, IDLingua, ID
FROM ESMessaggi AS t2
WHERE (IDLingua = @.plingua) AND (IDMessaggio = t1.IDMessaggio)))
UNION ALL
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = @.plingua)

2) SELECT IDMessaggio, Descrizione, IDLingua
FROM (SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = 1040) AND (NOT EXISTS
(SELECT IDMessaggio, Descrizione, IDLingua, ID
FROM ESMessaggi AS t2
WHERE (IDLingua = @.plingua) AND (IDMessaggio = t1.IDMessaggio)))
UNION ALL
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = @.plingua)) AS MYTABLE

The only difference as You can see is an extern select 'cause I need to refer my query result with others table...

|||

Since this is really a generic T-SQL question I'm going to move this to the T-SQL forum.

- Mike

|||This is a tools issue (I am moving thread to the Tools forum). It has nothing to do with the query. Both queries are identical and they will produce same results. You have to provide a repro that demonstrates the problem.|||

Hello Bkl,

My team owns the tool and I would like to help. Is it possible for you to send me scripts that generates the base table and the stored procedure? Looks like the SQL statement you added does not really match the screen-shot. I would like to be able to reproduce your issue and see if it's a potential bug on the tool.

Thanks!

<= and comparing strings

Hi,

I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707

What I want to to is to search for log posts that are in between two dates.

I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.

How would I solve this

Best /M

Well, I would suggest you convert that data into a datetime value and you will get a lot more value. But, the problem you are having is that 'A' comes before 'AA' in a sort order. So, 070422 is before any value in your table that has a time portion. So use < 070423 and you will get the values you need:

drop table test

go

create table test

(

timecreated char(16)

)

go

insert into test

select '0704212112538707'

union all

select '0704223112538707'

union all

select '0704212112338707'

union all

select '0704242112338707'

go

select *

from test

where timeCreated >= '070421'

and timeCreated < '070423'

timecreated
-
0704212112538707
0704223112538707
0704212112338707

'0704242112338707' was not returned.

|||You cannot compare strings in this manner and get teh results you are wanting. You will need to convert the field to a datetime for comparisons.
|||

What if you sum 1 day to the final timecreated?. I mean, of you want the '070421' to '070422' you can probe with the '070422' + 1 day -> '070421' to '070423'

>> Can Google web search index the data in my database?

I have content about projects that I store in a MS SQL database.
People access this information through pages which are dynamically
created in PHP. Since my content is stored in the database, how can I
get google web search (at google.com) to included that content when it
indexes my site? Is it a possibility? Thank you so much!I don't think this is the right group for this question (but I'm not
sure which group is right). Here are some links that might help
(especially the first one):
https://www.google.com/webmasters/s...ocs/en/faq.html
http://desktop.google.com/developerguide.html
http://www.google.com/enterprise/mini/index.html

Razvan|||gene.ellis@.gmail.com wrote:
> Since my content is stored in the database, how can I
> get google web search (at google.com) to included that content when it
> indexes my site? Is it a possibility? Thank you so much!

Google will NOT index your database. However, Google will follow all
the links on your website. If your website is designed so that the
dynamic content is only accessible though, for example form fields,
then google will not be able to find it. On the other hand, any
dynamic content that is accessible through a simple hypertext link will
be found and indexed by google.

> I have content about projects that I store in a MS SQL database.

As an example, let's say that you have a page named projectinfo.php
that accepts as input a variable named ProjectID and looks up and
displays info about that project. On some other page, a user selects
the project that they want to view. The user is then directed to
projectinfo.php. If that other page consists of a form with a
drop-down box, so that the user selects a project from the drop down
and then hit's a submit button, then google probably will not be able
to follow it.

On the other hand, if that other page contains anchors with hrefs like:
projectinfo.php?projectID=1
projectinfo.php?projectID=2
projectinfo.php?projectID=3

Then google absolutely will be able to follow those links and index the
pages.

Hope that helps

£ signs etc not being stored in database

Hi,
I'm having a few problems with my forms saving things like ￡ signs.
I'm trying to store all my data in plain text with-in a MS SQL 2000 database, but for some reason certain characters are being automatically removed. I don't really want to use the HTML code for these characters so was wondering if anyone know how I could stop these characters automatically being removed.
Many thanks in advance.
Below is the code I'm using to insert the text to the database:
----
Dim MyConnection9 As New SQLConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim MySQL9 as string
MySQL9 = "INSERT INTO NEWS_ARTICLES " & _
"(NewsTitle, NewsText, NewsDate, NewsShow) " & _
"VALUEs (@.title, @.text, @.date, @.show)"
Dim Cmd9 as New SqlCommand(MySQL9, MyConnection9)

Dim strtitle, strtext, strimage, strdate, strshow
strtitle = txttitle.Text
strtext = txtText.Text
strdate = DateTime.Now
strshow = ddlShow.SelectedItem.Value

cmd9.Parameters.Add(New SqlParameter("@.title", strtitle))
cmd9.Parameters.Add(New SqlParameter("@.text", strtext))
cmd9.Parameters.Add(New SqlParameter("@.date", strdate))
cmd9.Parameters.Add(New SqlParameter("@.show", strshow))

MyConnection9.Open()
cmd9.ExecuteNonQuery
MyConnection9.Close()
----
Regards,
Rich

What data type are you using? See this article from BOL on Unicode characters.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6voh.asp
When I run this, it works.
declare @.test nvarchar(50)

set @.test = '￡'

select @.test

Nick

|||

Hi Nick,

Thanks for your reply. I'm using nvarchar and also ntext.

Do you think it's my code that's wrong? Or something on the SQL setup?

If I edit it via SQL enterprise manager, it works ok.

Regards,

Rich

|||I would recommend you set a break point in your code and run it in debug mode. This sounds like its an issue of your web code not holding the value correctly. How are you getting the value including the￡into your string variable? Does it come from a textbox? If so, how does it get added to your textbox (key pattern, copy and paste, etc)?
Nick|||

Hi Nick,
I'm getting the ￡ signs from within a textbox, either by pasting one in there or by using the ￡ on the keyboard.
I've tried changing the cost and instead of using the textbox text, I replaced it with a ￡ which worked. So I think it's somewhere in the below code I'm using, but I just can't seem to figure it out :(
Dim strtitle, strtext, strimage, strdate, strshow
strtitle = txttitle.Text
strtext = txtText.Text
strdate = DateTime.Now
strshow = ddlShow.SelectedItem.Value
'strimage = ddlImage.SelectedItem.Value

cmd9.Parameters.Add(New SqlParameter("@.title", strtitle))
cmd9.Parameters.Add(New SqlParameter("@.text", strtext))
cmd9.Parameters.Add(New SqlParameter("@.date", strdate))
cmd9.Parameters.Add(New SqlParameter("@.show", strshow))
Thanks again for your help so far.
Regards,
Rich

|||

Hi Nick,

When you say insert a break point? What does this mean? It's unfortunately not a term I'm familiar with :(

Regards,

Rich

|||

I wonder if the issue is the parm construction. I think it might be seeing the ￡ sign, and the datatype is being converted to money (That is the English symbol for money right?) Maybe try:
dim parm as new sqlParameter("@.title", sqldbType.varchar, 50)
parm.Value = strTitle
cmd9.Parameters.add(parm)
Does cmd9.Parameters.Add(New SqlParameter("@.title", "￡")) work?

Nick
Edit: forgot the parm.add

|||

Hi Nick,

Thanks again for your reply.

I tried your suggested code, but unfortunately it didn't make any difference. Although, cmd9.Parameters.Add(New SqlParameter("@.title", "￡")) did work.
I also tried:
dim parm as new sqlParameter("@.title", sqldbType.varchar, 50)
parm.Value = txtTitle.Text
cmd9.Parameters.add(parm)
My textbox code is:
<asp:TextBox ID="txtTitle" runat="server" Width="350" />
Does that look ok?

Cheers,

Rich

|||

Try outputting the value

response.write (me.txtTitle.text) : response.end

If it doesnt have the value, the encoding isnt happening correctly. Try:

response.write (request.form("txtTitle")) : response.end
See if that helps any. Im kind of lost on this one as I tried it myself and everything seemed okay.
Nick

|||

Hi Nick,
Thanks again for your reply. Same thing happens when I do this also :(
$ works, but not ￡, very strange...
Thanks again for all your help.
Cheers,
Rich

|||

Could the problem be with the below bit of code which is also used on the page?
<%@. Page Language="VB" ContentType="text/html" validateRequest=false ResponseEncoding="iso-8859-1" debug="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQLClient" %>
<%@. Import Namespace="System.Web.Mail" %>
<%@. Import Namespace="System.DateTime" %>
Cheers,
Rich