SQL 2005 Report Services - Database Discussion Boards

Pualee21-May-07 11:41

21-May-07 11:41

Hey,

I am wondering if it is possible, and what would be the best way?

I'm working on a batch process to take care of building our applications, push/pull from source control and update the databases. As far as updating the DB, there will be a few scripts that need to run and I would like to call them from the same batch process that updates our executables (I really don't want to run a batch file AND set up a SQL job).

The goal is to have a single (1) job which will take care of everything to reduce the chance of error.

Can someone point me in the right direction?

Thanks,
Pualee

Re: Calling SQL updates from Command Prompt

mr_lasseter22-May-07 7:18

mr_lasseter

22-May-07 7:18

For oracle you can use sqlplus calls, for Sql Server you can use iSql.

Mike Lasseter

Re: Calling SQL updates from Command Prompt

Pualee23-May-07 2:37

Pualee

23-May-07 2:37

Thanks for the jumping off point, I'm looking into this, more specifically oSQL now Smile | :)

How to add a table to a database of an SQLServer (in C#)

Leia201121-May-07 4:27

Leia2011

21-May-07 4:27

Hey guys,

I need to know how to add a table to a database in SQL Server 2000.
With C# code its possible to change tables, i.e to update or delete rows or whatever, but how can I create a new table? Is this possible? do you have any ideas?

thanks in advance

Re: How to add a table to a database of an SQLServer (in C#)

Colin Angus Mackay21-May-07 5:22

Colin Angus Mackay

21-May-07 5:22

Leia2011 wrote:
how can I create a new table?

You can put pretty much any SQL command you like through an SqlCommand object. If you don't expect a result set back use ExecuteNonQuery()

Leia2011 wrote:
Is this possible?

yes.

Leia2011 wrote:
do you have any ideas?

See above.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Run Sql jobs in sequence

Aaanand21-May-07 4:21

Aaanand

21-May-07 4:21

Hi
I have created one master job in sqlserver 2000 which run several jobs in different steps.

But it excutes all jobs at a time. I want that job run one by one.
I have set property like "Go TO Next in one finish successfully" but it didnt work in this case.

If i run several store procedures in one job then they run one by one as i need but when i put jobs inside job then they run simultaneously.

I also tried by putting each job in seprate store procedure and kept that store procedures in each steps but it also didnt work and run simultaneously.

I can not understand how to do it.
Could anybody give me the solution?

Thanks in advance.

Re: Run Sql jobs in sequence

andyharman21-May-07 8:09

andyharman

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

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