Database Discussion Boards

Re: udf issue

Tauseef A25-Jul-09 5:10

Tauseef A

25-Jul-09 5:10

thanks i was just looking for that .

Tauseef A Khan
MCP Dotnet framework 2.0.

Re: udf issue

Blue_Boy25-Jul-09 6:00

Blue_Boy

25-Jul-09 6:00

You are welcome.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: udf issue

Mycroft Holmes25-Jul-09 20:57

Mycroft Holmes

25-Jul-09 20:57

I would use and else to trap the 2, -2 values

Never underestimate the power of human stupidity
RAH

Cross Tab Data

Isaac Gordon24-Jul-09 21:02

Isaac Gordon

24-Jul-09 21:02

Hi experts
I am using SQL Server 2005
I have Table Having Str. like
__________________________
Heading | Tax
__________________________
A | Taxx
A | Taxz
A | TAXN
B | 10
B | 20
B |30
C |5
C |5
C |4
_____________________________
i Want Result
like
--------------------------------
A B C
----------------------------------
TaxX 10 5
TaxZ 20 5
TaxN 30 4
--------------------------------

The No's of Column also dynamic
i.e A B C D .... so on
then help but Query in Using to get this Result
Dinesh

Re: Cross Tab Data

Jerry Hammond25-Jul-09 2:48

Jerry Hammond

25-Jul-09 2:48

I believe you're looking for something like the following:

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: Cross Tab Data

Mycroft Holmes25-Jul-09 20:56

Mycroft Holmes

25-Jul-09 20:56

This may help Pivot article[^]

Never underestimate the power of human stupidity
RAH

Problem inserting values.

Omar Akhtar Sheikh24-Jul-09 8:40

Omar Akhtar Sheikh

24-Jul-09 8:40

I have set the EMP_ID column in my database as the Primary Key. I mean this is the primary key column. And when I try to insert values in my database table from the form in then it gives me an error as:

"Cannot insert explicit value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF."

So, how is the IDENTITY_INSERT set to on. Where it is actually located so that I can modify it and start to insert values into my table.

Moreover, when I remove the primary key from this column(EMP_ID) and uncheck the Allow Nulls checkbox then also my same problem persists.

Kindly help me out. I am currently using SQL Server 2005.

Re: Problem inserting values.

Ebube24-Jul-09 10:58

Ebube

24-Jul-09 10:58

it depends on what you want to do. if you want to enable insertion on identity column
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF } otherwise remove identity from the emp_id column and insert the value from field

Re: Problem inserting values.

Ebube24-Jul-09 11:01

Ebube

24-Jul-09 11:01

sorry typo error i mean form not field

Re: Problem inserting values.

okdeshpande24-Jul-09 12:41

okdeshpande

24-Jul-09 12:41

I think you are trying to insert a value into a identity column. If you are inserting the value into intentionally then you have to set Identity_insert tablename on/off. else ignore this column while inserting, sql server automatically inserts records into this field.

Re: Problem inserting values.

Robin_Roy26-Jul-09 18:24

Robin_Roy

26-Jul-09 18:24

If you have access to the Management Studio of SQL Server, try and check the property for the column of your table in design mode, check the Identity Specification property...

alter or recreate view

Ebube24-Jul-09 5:11

Ebube

24-Jul-09 5:11

There is a table called QCR100 containing the following information

ITEM_CODE   ITEM_DESCRIPTION         DCODE                  AMOUNT
111200            investments                  24000003         1630000000.0000
111200            investments                  126000005   1630000000.0000
111600            Other Assets                  133000009   1700000000.0000
111600            Other Assets                  150000002   1730000000.0000
111701            Leasehold                     190010130   1740000000.0000
111705            Furniture                     190010036   1750000000.0000
111706            Computer Software   190010141   46995799.1800
Then I created a view with this command statement:
SELECT         CASE WHEN LEFT(e.dcode, 1) = '1' THEN isnull(SUM(a.Dr_bal_lcy - a.CR_BAL_LCY), 0) WHEN LEFT(e.dcode, 1)
                                                = '2' THEN isnull(SUM(a.CR_BAL_LCY - a.Dr_bal_lcy), 0) ELSE 0 END AS amount, e.ITEM_DESCRIPTION, e.ITEM_CODE
FROM                  dbo.GLTEMP_CONS AS a RIGHT OUTER JOIN
                                                dbo.QCR100 AS e ON LTRIM(a.GL_code) = e.Dcode
GROUP BY e.ITEM_DESCRIPTION, e.ITEM_CODE, e.Dcode

How I can alter this view to sum the amount for any duplicated item code and group by item code

Example

ITEM_CODE   ITEM_DESCRIPTION         DCODE                  AMOUNT
111200            investments                  24000003         1630000000.0000
111600            Other Assets                  133000009   1700000000.0000
111701            Leasehold                     190010130   1740000000.0000
111705            Furniture                     190010036   1750000000.0000
111706            Computer Software   190010141   46995799.1800

Re: alter or recreate view

Blue_Boy24-Jul-09 6:44

Blue_Boy

24-Jul-09 6:44

Hope you are lloking for this query.

select item_code,ITEM_DESCRIPTION,<br />
(select max(t1.dcode) from qcr100 as t1 where t1.item_code=qcr100.item_code) as dcode,<br />
sum(cast(amount as float)) as amount<br />
from qcr100 <br />
group by  item_code,ITEM_DESCRIPTION

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: alter or recreate view

Ebube24-Jul-09 11:12

Ebube

24-Jul-09 11:12

actually i can write sql statement to do that. in fact i written it as another view call qcr100f because i will use it to update anorher table. but i want to improve on this giving that the gl_temp_cons has schema

idkey db_bal cr_bal dcode

i want to be able to get the result i post above in with one view definition

thanks for your concern

Novell, Btrieve and data access

Mega124-Jul-09 2:35

Mega1

24-Jul-09 2:35

I have a customer who refuses to move from an old version of Great Plains 'pre-microsoft' on a novell server using Btrieve 6.15. I am looking for help/advice to make a small VB app that will let them query and print a listing from a specific DAT file. This is being done on a local copy of the data 'not live' on an XP machine. While not new to Vb, I only use it to play and relax 'just don't have the time any more'. I will be using VB 2008 and I guess I need help with the following:

1. What files will I need for the connection to this type of data?

2. What would the connection string look like.

I think those should be my only problems.

Thanks in advance and all help is greatly appriciated.

Re: Novell, Btrieve and data access

Mycroft Holmes24-Jul-09 16:14

Mycroft Holmes

24-Jul-09 16:14

Try connectionstrings.com

Never underestimate the power of human stupidity
RAH

PageBreak not rendered in Web Archive in SSRS

Member 461901624-Jul-09 1:32

Member 4619016

24-Jul-09 1:32

Hi

I am facing an issue in page break when the report is rendered as web archive in SSRS. I have applied PageBreak for the report on a group. This page break is rendered correctly when I view the report in PDF but the page break doesnt come when rendered in Web Archive.

Is there any way to apply Page Break to web archive?? I cant use InteractiveHeight for the pagebreak.

Please help ...

Thanks.

sanky

Re: PageBreak not rendered in Web Archive in SSRS

Eddy Vluggen24-Jul-09 4:39

Eddy Vluggen

24-Jul-09 4:39

I don't know about SSRS, but a webarchive is in fact just a webpage. It's kinda difficult to put a page-break in a webpage, but this[^] site might help.

Good luck Smile | :)

error 40 : named pipes

saeidfarahi23-Jul-09 22:57

saeidfarahi

23-Jul-09 22:57

i have this connection string in one of my applications but i got the famous
erro 40 . i tried many forums and found many suggestions but none of them could help!

string _connectionstring =
"server=" + Environment.MachineName +";Initial Catalog=myDatabase;"+
"user id=myLogin;password=myPassword;trusted_connection = true;";

please notice that this application works on local computer , not a network. OMG | :OMG:

Re: error 40 : named pipes

Muhammad Mazhar23-Jul-09 23:42

Muhammad Mazhar

23-Jul-09 23:42

Open surface area configurations of SQL server and allow remote connections. It seems that you haven't enabled them up till now that's why network users can't connect to your database.

Share your experience with others
Check my Blog...

Parameters problem

jonhbt23-Jul-09 19:35

jonhbt

23-Jul-09 19:35

I am running the following MS ACCESS select command from a c# method.

SELECT USERINFO.USERID, CHECKINOUT.CHECKTIME, CHECKINOUT.CHECKTYPE, DEPARTMENTS.DEPTNAME, USERINFO.NAME FROM (CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID) INNER JOIN DEPARTMENTS ON USERINFO.DEFAULTDEPTID = DEPARTMENTS.DEPTID WHERE CHECkINOUT.CHECKTIME BETWEEN @fromDate and @toDate " + sensors + "ORDER BY DEPARTMENTS.DEPTNAME;"

The problem is that the parameters that I am passing, which are in date format are throwing an ivalid criteria exception. This is how I am passing the parameters

cmd.Parameters.AddWithValue("fromDate", todayDate);
     cmd.Parameters.AddWithValue("toDate", tomorrowDate);

Can one identify why it is telling me ivalid criteria when the values should be dates?

Re: Parameters problem

Abhijit Jana23-Jul-09 19:49

Abhijit Jana

23-Jul-09 19:49

jonhbt wrote:
The problem is that the parameters that I am passing, which are in date format are throwing an ivalid criteria exception. This is how I am passing the parameters

cmd.Parameters.AddWithValue("fromDate", todayDate);
cmd.Parameters.AddWithValue("toDate", tomorrowDate);

You didn't use @ with the parameter.
Here is you modified code,

cmd.Parameters.AddWithValue("@fromDate", todayDate);
     cmd.Parameters.AddWithValue("@toDate", tomorrowDate);

This will help you Smile | :)

Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.

Re: Parameters problem

jonhbt23-Jul-09 19:58

jonhbt

23-Jul-09 19:58

It didn't work either it is showwing me the following error.

{System.Data.OleDb.OleDbException: Data type mismatch in criteria expression

Re: Parameters problem

DoctorMick24-Jul-09 0:08

DoctorMick

24-Jul-09 0:08

If you step through the code and hover over todayDate and tomorrowDate what are there values?

Re: Parameters problem

Baran M24-Jul-09 0:16

Baran M

24-Jul-09 0:16

Do you find any difficulties to use Add() Function,
cmd.Parameters.Add("@fromDate", SqlDbType.System.Data.SqlDbType.Date)

cmd.Parameters.Item(0).Value = todayDate;

Using Add() function you can avoid datatype conflicts here.

Education is not a way to escape poverty — it is a way of fighting it.

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