Database Discussion Boards

andyharman21-May-07 8:09

21-May-07 8:09

Jobs are meant to be independent of each other. How about using moving your code into a DTS package - with "success" constraints linking each task? The master job would then just need to execute that one DTS package.

SQL 2005 Report Services

Jonathan Snyder21-May-07 4:13

Jonathan Snyder

21-May-07 4:13

SOftware I'm using

Visual Studio 2005
SQL Express 2005
SQL Server Business Intelligence Studio

My Problem:

I created two reports and using the same parameter for each report. I add report2 to report 1 as a subreport and whenever I preview report1, report2 never shows as the subreport. I doubled, tripled check that they are using the same parameter but I read in another article that you need to pass a report parameter and not a query parameter? What's the difference? Help would be greatly appreciated.

Thanks
Jon

index

WhiteGirl2321-May-07 2:50

WhiteGirl23

21-May-07 2:50

Hi,
I need to find all the indexes in a table from access database.
please help me .
thank you.

Re: Finding indexes on an MS-Access table

andyharman21-May-07 3:25

andyharman

21-May-07 3:25

You could try the ADOX or DAO.

How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 2:16

lilnelse

21-May-07 2:16

I want to insert a binary file, say "tt.bin", into a field (image type) of SQL database, I wrote a program as follows

C++, VS2003, ADO, input file: tt.bin，database: moldb,
Create table moltbl (id INT, mol IMAGE);
But I didn't get nothing in the output file: new.bin

where am I wrong, please? thanks very much!

// BLOB.cpp

#import "c:/Program Files/Common Files/System/ADO/msado15.dll" \
no_namespace rename("EOF", "EndOfFile")

#include
#include

using namespace std;

int main()
{
::CoInitialize(NULL);

_RecordsetPtr m_pRecordset("ADODB.Recordset");
_ConnectionPtr m_pConnection("ADODB.Connection");
_bstr_t bstrSQL("select * from moltbl");

ifstream moldata;
moldata.open("tt.bin", ios::in|ios::binary);
ofstream newdata;
newdata.open("new.bin", ios::out|ios::binary);

char buffer[10240];
char *pBuf = buffer;
long nFileLen = 0;

if (!moldata.fail() && !moldata.eof())
moldata.read(buffer,10240);
nFileLen = moldata.gcount();

VARIANT varBLOB;
SAFEARRAY *psa;
SAFEARRAYBOUND rgsabound[1];
int index = 0;
_bstr_t strConnect = "Provider=SQLOLEDB; Server=ELSE-HP;"
"Database=moldb; uid=sa; pwd=sa";
m_pConnection->Open(strConnect, "", "", adModeUnknown);
if(m_pConnection == NULL)
cerr<<"Load data ERROR!\n";
HRESULT hr = m_pRecordset->Open("SELECT * FROM moltbl",
_variant_t((IDispatch *)m_pConnection,true),
adOpenDynamic,
adLockPessimistic,adCmdText);
m_pRecordset->AddNew();

m_pRecordset->PutCollect("id",_variant_t(index++));

if(pBuf)
{
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = nFileLen;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
for (long i = 0; i < (long)nFileLen; i++)

SafeArrayPutElement (psa, &i, pBuf++);
varBLOB.vt = VT_ARRAY | VT_UI1;
varBLOB.parray = psa;
m_pRecordset->GetFields()->GetItem("mol")->AppendChunk(varBLOB);
}

m_pRecordset->Update();

long lDataSize =_pRecordset->GetFields()->GetItem("mol")->ActualSize;

ifstream newfile;
newfile.open("new.bin", ios::out| ios::binary);
if(lDataSize > 0)
{
_variant_t varBLOB;
varBLOB = m_pRecordset->GetFields()->GetItem("mol")->GetChunk(lDataSize);

if(varBLOB.vt == (VT_ARRAY | VT_UI1))
{

SafeArrayAccessData(varBLOB.parray,(void **)&pBuf);

newdata.write(pBuf, lDataSize);
SafeArrayUnaccessData (varBLOB.parray);
}
}

moldata.close();
newdata.close();
return 0;
}

Hi, there. Need your help!

Hi, there.

Re: How to insert a binary file into SQL database, via ADO

Sathesh Sakthivel21-May-07 2:37

Sathesh Sakthivel

21-May-07 2:37

Hey what error it shows?

Regards,

Satips.

Re: How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 2:57

lilnelse

21-May-07 2:57

Thanks for your attention!

No error, everything seems OK, but I couldn't get the right result,
I insert the binary file into the database, and then I retrieve and output it into a binary file, but I got nothing in the new file?

what's the matter? thanks a lot!

Hi, there.

Re: How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 3:05

lilnelse

21-May-07 3:05

Sorry, but it's already 9:00 PM in my time-zone, I have to leave now, I'll try to fix my program tonight, but if you could give me any suggestions, that'll be much appreciated.

PS. I'm just a beginning learner, so please explain in details. Thanks. See you.

Hi, there.

Re: How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 17:02

lilnelse

21-May-07 17:02

I've stepped into it, and find something seems wrong here:

if(pBuf)
{
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = nFileLen;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
for (long i = 0; i < nFileLen; i++)
SafeArrayPutElement (psa, &i, pBuf++);
varBLOB.vt = VT_ARRAY | VT_UI1;
varBLOB.parray = psa;
m_pRecordset->GetFields()->GetItem("mol")->AppendChunk(varBLOB);
}

because when i did as follow:

m_pRecordset->Update();
long lDataSize = m_pRecordset->GetFields()->GetItem("mol")->ActualSize;

I find that the value of lDataSize is alwayse -1
do you know the reason, please?

thanks a lot

regards.
Li

Hi, there.

Optimizing crystal report

Pradip Kishore21-May-07 1:36

Pradip Kishore

21-May-07 1:36

hi all
i am having a stored procedure in my database which will take 2 parameters and return a resultset which i want to display in a crystal report.when i am executing the store proc. alone with the paramaters in the query analyser of sql server 2000,it is taking around 2.5 min.but when i pass the parameters from a vb.net page page,it z showing an error:{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

please help me out to solve this problm..

thanks in advance

i am Pradip Kishore

Re: Optimizing crystal report

kubben21-May-07 2:32

kubben

21-May-07 2:32

The command timeout defaults to like 30 seconds, so you would need to change this so you don't get a timeout error. There is a property off of the sqlCommand object called CommandTimeout. It is in seconds.

Hope that helps.
Ben

Re: Optimizing crystal report

Paul Conrad21-May-07 5:14

Paul Conrad

21-May-07 5:14

Have you looked at optimizing the stored procedure?

Re: Optimizing crystal report

Pradip Kishore21-May-07 22:04

Pradip Kishore

21-May-07 22:04

hi
thanks for your response

IS there any way to optimize a stored procedure for quick data retrival..

please help me

thanks in advance..

i m pradip kishore

Re: Optimizing crystal report

Paul Conrad22-May-07 5:37

Paul Conrad

22-May-07 5:37

Pradip Kishore wrote:
IS there any way to optimize a stored procedure for quick data retrival..

In short, you need to look at where bottlenecks are. Without having any idea how your database is set up, I can't really help.

Any queries you have in the stored proc, analyze it through query analyzer...

Paul

Re: Optimizing crystal report

Dave Kreskowiak21-May-07 6:45

Dave Kreskowiak

21-May-07 6:45

Pradip Kishore wrote:
but when i pass the parameters from a vb.net page page,it z showing an error:{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

The default CommandTimeout for an SqlCommand object is 30 seconds. If your query doesn't return a result set in that time, you get this error.

Either you have to optimize the stored procedure and/or table structures (indexes!), and/or up the timeout in the SqlCommand object.

If it's taking 2.5 minutes to return a set, are you sure that this is as fast as your query can execute?

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Optimizing crystal report

Pradip Kishore21-May-07 22:10

Pradip Kishore

21-May-07 22:10

Hi
thanks for your response..

but can you show me some sample code or any links by which i can be able to do this..?

thanks in advance..

pradip kishore

Re: Optimizing crystal report

Dave Kreskowiak22-May-07 12:04

Dave Kreskowiak

22-May-07 12:04

I already gave it to you. The CommandTimeout property of your SqlCommand objects you created!

As far as optimization goes. That's done with experience and trial and error. There is no code that will just "do it for you". You have to understand precisely how SQL Server works, how Indexes work and why. What are the consequences of using them and overusing them. And on and on and on...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

SQL Server Self referencing looping through

K.P.Kannan20-May-07 22:02

K.P.Kannan

20-May-07 22:02

We have a table which is a self referencing. The childs may go up to any level.

For eg: PID SubPID
1 3
3 4

We are using cursors to get the ProjectIDs and loop through to 'n' level. Is there any optimized way to improve the performance or is there any keyword like connect by prior as in Oracle

Thanks,
K.P.Kannan

Re: SQL Server Self referencing looping through

Harini N K20-May-07 22:24

Harini N K

20-May-07 22:24

Hi
I think this is possible in SQL 2005 (Yukon) but if you are using SQL 2000. Then check this link

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm[^]

Harini

Re: SQL Server Self referencing looping through

K.P.Kannan20-May-07 22:51

K.P.Kannan

20-May-07 22:51

Thanks. The link was quiet useful. I am trying to implement that

SSIS Package trouble

steffw20-May-07 21:28

steffw

20-May-07 21:28

Hello everyone,

Lately i'm working on a SSIS Package that downloads some data from one database to anohter.
Their is one problem, when i run the package their is nothing wrong. When i try to put it in an job it fails. I've checked the logs but they don't give me some usefull information, the only thin that i've got is: Job failed at step 'my package name'.

I've also checked the permissions, their good (i think) and when i do a connection test in visual studio their is nothing wrong with both connections. When i execute the package in visual studio, nothing fails and the data is pumped over.

Does anyone have an idea about this?

Stef

Opening SQL Enterprise manager

tasnim520-May-07 20:49

tasnim5

20-May-07 20:49

Hi,

I want to open SQL Enterprise Manager from RUN command in Start Menu. I can open it by creating short cut and then after giving a name i can put it in system32 folder. But i want to know that is there any command to open it from from Run command.

Can some one tell the command to open it.?

Thank you.

Ahmed

Re: Opening SQL Enterprise manager

blackjack215020-May-07 21:05

blackjack2150

20-May-07 21:05

If you can create a shortcut, then do Right Click -> Properties on the shortcut icon. Inside the target textbox you'll find the path to the exe file.

Re: Opening SQL Enterprise manager

tasnim520-May-07 23:21

tasnim5

20-May-07 23:21

Hi,

Thank you for replying. I can do it. But i want to know is there any shortcut command for that. like we have inetmgr to open IIS.

Please help me.

Thank you,
Regards,

Ahmed

Re: Opening SQL Enterprise manager

sam#21-May-07 0:18

sam#

21-May-07 0:18

type the exe name in run like ssmsee.exe

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.