Adding Static Rows
Mar 5, 2008
Hi,
I have a data; descriptions (types of elements in my system) on rows, and results (numeric values) of different branches for each description on columns. I have one entry (one row) for each individual day (so i have year, month and day columns) I prepared a report for this data (using enable drilldown; year --> month --> day --> description and its numeric values on the row) But I have to add new descriptions using my own descriptions, like formulas... My descriptions and my new descriptions made of with old ones, should be listed one under the other
For ex;
new_description = (descr1 + descr2) / descr7
Can I do this on report design step? If so, how?
View 4 Replies
ADVERTISEMENT
Sep 4, 2007
Hey all-
I put togehter a package that opens a flat file, parses the data based on the semi-colon delimeter, and imports the rows into a database table. Thats the fun easy part.
What I cant figure out is how to add a variable that will hold a constant ID value that will be persisted with the same value to all rows inserted to the DB. Making the problem harder, I would like that this value be defined in a properties file or database table of some sort so that I can do a lookup based on the file name / location to find out what value should be used.
Any suggestions? I hope my explanation makes at least some sense - but basically I want to do a look up in a configuration of some sort, pull out a single value, and add it to a data import.
let the fun begin!!
View 4 Replies
View Related
Jul 17, 2007
Hello,
Anyone know how to create an update trigger where the primary key isn't fixed.
If the primary key change how can I tie together the Deleted and Inserted tables if more than one row is updated?
/Patrik
View 9 Replies
View Related
Feb 22, 2006
I'm using Delphi 2006 to create a DLL which will be integrated into SQL 2005. It's been a long road and I've made a lot of headway, however I am currently having the following problem creating the stored procedure:
My dll name is 'Crystal_Reports_Test_01'
In the DLL, my class is named 'Class01'.
In the DLL, my procedure is named 'TestMe'
I've managed to integrate the DLL into SQL using the following statement:
CREATE ASSEMBLY TEST_ERIC_01
AUTHORIZATION dbo
FROM 'c:mssqlassembliescrystalreports.dll'
WITH PERMISSION_SET = UNSAFE
I am attempting to create the stored procedure which points to the 'TestMe' method inside of the DLL. FYI: 'CrystalReports' is the namespace above my class that I had to add in order to get it to locate the class. The following code is used to create the stored procedure:
create procedure dbo.Crystal_Reports_Test_01(
@Parm1 nvarchar(255)
)
as external name TEST_ERIC_01.[CrystalReports.Class01].TestMe
But I get the following error:
Msg 6573, Level 16, State 1, Procedure Crystal_Reports_Test_01, Line 1Method, property or field 'TestMe' of class 'CrystalReports.Class01' in assembly 'CrystalReports' is not static.
I'm not sure what this means exactly. I think it means the method (the procedure) is not using Static method binding but should be. I have no idea what this really means or how to accomplish this in the DLL - or if I'm even going about this in the right way.
Any help would be appreciated ! I'll post the Delphi code (DLL) below.
Thanks,
Eric Gooden
library CrystalReports;uses System.Reflection, System.Runtime.InteropServices;...................type Class01 = class public procedure TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export; end;procedure Class01.TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export;begin sVarString:= 'Lets change the value and see if the stored proc. gets the change.';end;end.
View 4 Replies
View Related
Jul 20, 2005
Hi folks,I've a sql query problem I was wondering if you all had a quick anddirty solution for. I've a query:Select code, value from table_a where date in(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')This returns a table that looks like:A_CODE B_CODE VALUE------ ------ -----1000 01000 $5001000 02000 $750What I'd like to see is:A_CODE B_CODE VALUE------ ------ -----1000 01000 $5001000 02000 $7502000 01000 $02000 02000 $0Any suggestions on how to rewrite my query so the results show A_CODE2000 with a VALUE of 0 or null?Thank much in advance!Marc
View 1 Replies
View Related
Jun 3, 2006
I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created.
ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar)
1 Elmwood Murdock 7 22 Louisville Manley 4 33 Manley Elmwood 9 8
ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this:
Elmwood: 1 Win 1 Loss. .500
Thanks for your reply.
View 5 Replies
View Related
Jan 6, 2008
Hi dear users,
I have a crisis situation with my simple database project. Let me introduce You to the topic.
I've created table ZAKLADY in my db:
Code Block
CREATE TABLE ZAKLADY
(ZAKL_ID INT IDENTITY(10,10) CONSTRAINT PK_ZAKLADY PRIMARY KEY,
ZAKL_NAZWA VARCHAR(25),
ZAKL_MIASTO VARCHAR(20),
ZAKL_ULICA VARCHAR(30) );
Then, I've made procedure which adds one row:
Code Block
CREATE PROCEDURE Zakl_AddNewRow
@zakl_nazwa VARCHAR(25),
@zakl_miasto VARCHAR(20),
@zakl_ulica VARCHAR(30)
AS
INSERT INTO Zaklady (Zakl_Nazwa, Zakl_Miasto, Zakl_Ulica)
VALUES (@zakl_nazwa, @zakl_miasto, @zakl_ulica)
Also I've made procedure which deletes the row.
Code Block
CREATE PROCEDURE Zakl_DeleteRow
@zakl_id INT
AS
DELETE Zaklady
WHERE Zakl_ID=@zakl_id
So what's the point of my problem?
If I execute the procedure "Zakl_AddNewRow", I will add a new row to ZAKLADY table. Column ZAKL_ID is added automaticly
because, as You can see, it is declared as INT IDENTITY(10,10). I have added 5 rows so the ZAKL_ID values will be adequately 10,20,30,40,50.
Now I delete the row with ZAKL_ID = 30.
After it I add new row once again.
The result is that the ZAKL_ID value will be 60.
So the number "30" of the identity sequence became unsigned to any ZAKL_ID.
I wanted that new row which was added has ZAKL_ID=30 not 60. The second added row should have ZAKL_ID=60.
Is there any possibility to do this.If yes, What should I change in sql code.
Thank You for all replies
Maciej
View 6 Replies
View Related
Mar 6, 2008
Hello Friends,
I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but I want it at the front .
so it will be like this,
First the total should be displayed then the group members value.
Can anyone help me on this issue.
Thanks & Regards
Shivanandan Gupta
View 7 Replies
View Related
Jan 16, 2002
We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?
View 2 Replies
View Related
Nov 25, 2014
I got a table where i need to add certain rows and minus the valu with a row.
IDC1C2C3C4C5C6
1551557775
266201452
345222266
441727582
532556951
6022022
7182344142
8
Result in the 8 Row
Sum(id(2,3,4)-sum(id(6,7))
View 2 Replies
View Related
Apr 28, 2008
Hi there, I have a simple data flow from OLE DB to OLE DB that writes data from a view into a table. Is it possible to delete the table or the rows within the table before wirting the new data???
Thanks in advance!
View 10 Replies
View Related
Apr 16, 2007
Hi , I am using the Integration Services slowly changing dimension to move data from a SQL Server 2000 database table to a SQL Server 2005 table.
The problem is the package is not tracking changes, it is spending a lot of time doing lookups (i'm guessing this cost it's real slow), but ends up creating new records when there has not been a change.
I'm quite sure the business key is set up correctly (I'm using the PK from the source table).
The database I am transferring from has non Unicode data types (ie varchar and char) and the destination database has Unicode data types (ie nvarchar).
Also some of the fields in the dB are NULL - does this have an effect (ie one null doesn't equal another null)? Or shouldn't that matter?
View 4 Replies
View Related
Apr 15, 2007
Hi , I am using the Integration Services slowly changing dimension to move data from a SQL Server 2000 database table to a SQL Server 2005 table.
The other problem is the package is not tracking changes it is spending a lot of time doing lookups (it's slow), but ends up creating new records when there has not been a change.
I'm quite sure the business key is set up correctly (I'm using the PK from the source table).
The database I am transferring from has non Unicode data types (ie varchar and char) and the destination database has Unicode data types (ie nvarchar).
Also some of the fields in the dB are NULL - does this have an effect (ie one null doesn't equal another null)? Or shouldn't that matter?
View 1 Replies
View Related
Aug 12, 2015
I am trying to use the following Query to create the table Agents and add rows to it.
USE REMAXCLASSIC;
IF OBJECT_ID ('dbo.Agents', 'U') IS NOT NULL
DROP TABLE Agents;
GO
CREATE TABLE Agents
[Code] ....
I get the following error messages:
Msg 102, Level 15, State 1, Line 34 Incorrect syntax near '0.25'
Msg 105, Level 15 mark after the character string ');
View 2 Replies
View Related
May 27, 2007
Hi,
I'm a complete novice concerning SQL Server (Express Edition)
I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.
Have i missed an option during installation?
Thx,
Steven
View 5 Replies
View Related
Apr 24, 2013
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
View 4 Replies
View Related
Apr 25, 2007
I have the following stored procedure...
CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO
The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.
Obviously, I need to wrap this in an IF...EXISTS
IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID))
(the SELECT above, since that's what needs to come back if the data exists)
ELSE
Do the same select but put in a static value for BudgetCode, like '0000'
GO
How could I do the part where the IF...EXISTS fails?
I'm... not sure I can use RETURNS, since it feeds into this recordset:
rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")
and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>
View 4 Replies
View Related
May 30, 2008
Hello,
I have a OLE DB on a Data Flow that execute a query and returns 5 columns i want to add a new column with a static value that a have on a Variable.
I use Derived Column in order to create the new column adding the variable value in the expression field, Is this the best way??
Thanks
View 4 Replies
View Related
Nov 10, 2006
I'm attemping to deploy a crypto class that we use on a desktop app to SQL Server. I'll use the encryptstring/decryptstring found in this class as User Defined Functions on SQL Server. When I try to deploy the class I'm getting this error:
Error 1 Method, property or field 'EncryptString' of class 'EncryptingDecryptingOASISPWDS.Crypto' in assembly 'EncryptingDecryptingOASISPWDS' is not static. EncryptingDecryptingOASISPWDS
Here is the code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Security.Cryptography
Imports System.Text 'for UnicodeEncoding
Imports System.IO 'for MemoryStream, FileStream, Stream, Path
Imports System.Threading 'for Thread.Sleep
Partial Public Class Crypto
'Private Const CodeVersion As String = "01.00.00"
'class member variables
Private m_sPassPhrase As String
'class private variables
Private mbytKey() As Byte 'crypto key
Private mbytIV() As Byte 'Initialization Vector
Private mbKeyIsSet As Boolean = False
Private mksKeyNotSetException As String = "Crypto passphrase is not set."
Private mksPassphraseTooSmall As String = "PassPhrase length must be at least {0} characters."
'--- class constructors
Public Sub New()
'no passphrase is useful for GetHashString/ValidPassword methods
End Sub
Public Sub New(ByVal CryptoPassPhrase As String)
PassPhrase = CryptoPassPhrase
End Sub
'--- class public properties
'generate and store encryption key & iv from passphrase
Public Property PassPhrase() As String
Get
Return m_sPassPhrase
End Get
Set(ByVal Value As String)
Const iMinLength As Integer = -1 '-1 disables min length
m_sPassPhrase = Value.Trim
'enforce a rule on minimum length if desired here
If (Value.Length > iMinLength) Or (iMinLength = -1) Then
Dim sha2 As New SHA256Managed()
'256 bits = 32 byte key
mbytKey = sha2.ComputeHash(BytesFromString(m_sPassPhrase))
'convert to Base64 for Initialization Vector, take last 16 chars
Dim sKey As String = Convert.ToBase64String(mbytKey)
mbytIV = Encoding.ASCII.GetBytes(sKey.Remove(0, sKey.Length - 16))
mbKeyIsSet = True
sha2 = Nothing
Else
mbKeyIsSet = False
Throw New Exception(String.Format(mksPassphraseTooSmall, (iMinLength + 1).ToString))
End If
End Set
End Property
'decrypt a stream
Public Function DecryptStream(ByVal EncryptedStream As MemoryStream) As MemoryStream
If mbKeyIsSet Then
Try
'create Crypto Service Provider, set key, transform and crypto stream
Dim oCSP As New RijndaelManaged()
oCSP.Key = mbytKey
oCSP.IV = mbytIV
Dim ct As ICryptoTransform = oCSP.CreateDecryptor()
Dim cs As CryptoStream = New CryptoStream(EncryptedStream, ct, CryptoStreamMode.Read)
'get bytes from encrypted stream
Dim byteArray(EncryptedStream.Length - 1) As Byte
Dim iBytesIn As Integer = cs.Read(byteArray, 0, EncryptedStream.Length)
cs.Close()
'create and write the decrypted output stream
Dim plainStream As New MemoryStream()
plainStream.Write(byteArray, 0, iBytesIn)
Return plainStream
Catch ex As Exception
Return Stream.Null
End Try
Else
Throw New Exception(mksKeyNotSetException)
End If
End Function
'decrypt a string - wrapper without Base64 flag (True by default)
Public Function DecryptString(ByVal EncryptedString As String) As String
Return _DecryptString(EncryptedString, True)
End Function
'decrypt a string - wrapper with Base64 flag
Public Function DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String
Return _DecryptString(EncryptedString, Base64)
End Function
'encrypt a stream
Public Function EncryptStream(ByVal PlainStream As MemoryStream) As MemoryStream
Try
'open stream for encrypted data
Dim encStream As New MemoryStream()
'create Crypto Service Provider, set key, transform and crypto stream
Dim oCSP As New RijndaelManaged()
oCSP.Key = mbytKey
oCSP.IV = mbytIV
Dim ct As ICryptoTransform = oCSP.CreateEncryptor()
Dim cs As CryptoStream = New CryptoStream(encStream, ct, CryptoStreamMode.Write)
'get input stream into byte array
Dim byteArray() As Byte = PlainStream.ToArray()
'write input bytes to crypto stream and close up
cs.Write(byteArray, 0, PlainStream.Length)
cs.FlushFinalBlock()
cs.Close()
Return encStream
Catch ex As Exception
Return Stream.Null
End Try
End Function
'encrypt a string - wrapper without Base64 flag (True by default)
<Microsoft.SqlServer.Server.SqlFunction()> _
Function EncryptString(ByVal PlainText As String) As String
Return _EncryptString(PlainText, True)
End Function
''encrypt a string - wrapper with Base64 flag
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Function EncryptString2(ByVal PlainText As String, ByVal Base64 As Boolean) As String
Return _EncryptString(PlainText, Base64)
End Function
'calculates the hash of InputValue, returns a string
'- SHA1 hash is always 20 bytes (160 bits)
Public Function GetHashString(ByVal InputValue As String) As String
Try
Dim inputBytes() As Byte = BytesFromString(InputValue)
Dim hashValue() As Byte = New SHA1Managed().ComputeHash(inputBytes)
Return BytesToHexString(hashValue)
Catch ex As Exception
Return String.Empty
End Try
End Function
'returns True if hash of Passphrase matches HashValue
Public Function ValidPassword(ByVal Passphrase As String, ByVal HashValue As String) As Boolean
Return (GetHashString(Passphrase) = HashValue)
End Function
'internal string decryption
Private Function _DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String
Try
'put string in byte array depending on Base64 flag
Dim byteArray() As Byte
If Base64 Then
byteArray = Convert.FromBase64String(EncryptedString)
Else
byteArray = BytesFromString(EncryptedString)
End If
'create the streams, decrypt and return a string
Dim msEnc As New MemoryStream(byteArray)
Dim msPlain As MemoryStream = DecryptStream(msEnc)
Return BytesToString(msPlain.GetBuffer)
Catch ex As Exception
Return String.Empty
End Try
End Function
'internal string encryption
Private Function _EncryptString(ByVal PlainText As String, ByVal Base64 As Boolean) As String
Try
'put string in byte array
Dim byteArray() As Byte = BytesFromString(PlainText)
'create streams and encrypt
Dim msPlain As New MemoryStream(byteArray)
Dim msEnc As MemoryStream = EncryptStream(msPlain)
'return string depending on Base64 flag
If Base64 Then
Return Convert.ToBase64String(msEnc.ToArray)
Else
Return BytesToString(msEnc.ToArray)
End If
Catch ex As Exception
Return String.Empty
End Try
End Function
'returns a Unicode byte array from a string
Private Function BytesFromString(ByVal StringValue As String) As Byte()
Return (New UnicodeEncoding()).GetBytes(StringValue)
End Function
'returns a hex string from a byte array
Private Function BytesToHexString(ByVal byteArray() As Byte) As String
Dim sb As New StringBuilder(40)
Dim bValue As Byte
For Each bValue In byteArray
sb.AppendFormat(bValue.ToString("x2").ToUpper)
Next
Return sb.ToString
End Function
'returns a Unicode string from a byte array
Private Function BytesToString(ByVal byteArray() As Byte) As String
Return (New UnicodeEncoding()).GetString(byteArray)
End Function
'Return True when the file is available for writing
'- returns False if output file locked, for example
Private Function CheckWriteAccess(ByVal FileName As String) As Boolean
'2 second delay with 10,200
Dim iCount As Integer = 0 'retry count
Const iLimit As Integer = 10 'retries
Const iDelay As Integer = 200 'msec
While (iCount < iLimit)
Try
Dim fs As FileStream
fs = New FileStream(FileName, FileMode.Append, _
FileAccess.Write, FileShare.None)
fs.Close()
Return True
Catch ex As Exception
Thread.Sleep(iDelay)
Finally
iCount += 1
End Try
End While
Return False
End Function
End Class
View 4 Replies
View Related
May 29, 2008
Should a static connection be used in application. Is it advisable to use a static connection, i mean everytime should i create a new instance of sqlonnection object and then dispose it after use or should i use a static connection plz advise
View 2 Replies
View Related
Jun 12, 2008
Can I use this class in ASP.NET web site with many visitors?SqlLayer.cs: public static class SqlLayer{ public static string ConnectionString = ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString; public static int ExecuteNonQuery(string StoredProcedureName, string[] ParamNames, object[] ParamValues) { if (ParamNames.Length != ParamValues.Length) { throw new Exception("ParamNames.Length != ParamValues.Length"); } using (SqlConnection cSqlConnection = new SqlConnection(ConnectionString) { cSqlConnection.Open(); SqlCommand cSqlCommand = cSqlConnection.CreateCommand(); cSqlCommand.CommandType = CommandType.StoredProcedure; cSqlCommand.CommandText = StoredProcedureName; cSqlCommand.Parameters.Add("@ReturnValue", DbType.Int32); cSqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue; for (int i = 0; i < ParamValues.Length; i++) { cSqlCommand.Parameters.AddWithValue(ParamNames[i], ParamValues[i]); } cSqlCommand.ExecuteNonQuery(); return (int) cSqlCommand.Parameters["@ReturnValue"].Value; } } public delegate void ActionForReader(SqlDataReader Reader); public static void ExecuteReader(string StoredProcedureName, string[] ParamNames, object[] ParamValues, ActionForReader cActionForReader) { using (SqlConnection SqlConnection1 = new SqlConnection(ConnectionString)) { SqlConnection1.Open(); SqlCommand SqlCommand1 = SqlConnection1.CreateCommand(); SqlCommand1.CommandType = CommandType.StoredProcedure; SqlCommand1.CommandText = StoredProcedureName; for (int i = 0; i < ParamValues.Length; i++) { SqlCommand1.Parameters.AddWithValue(ParamNames[i], (ParamValues[i] == null ? DBNull.Value : ParamValues[i])); } using (SqlDataReader Reader = SqlCommand1.ExecuteReader()) { if (cActionForReader != null) { //if (Reader.HasRows == false) throw new Exception("Reader has no rows"); //if (Reader.RecordsAffected == 0) throw new Exception("Reader RecordsAffected = 0"); while (Reader.Read()) { if(Reader!=null) cActionForReader(Reader); } } } } }} Using: SqlLayer.ExecuteNonQuery("SomeStoredProcedure", "ID", ID);---------SqlLayer.ExecuteReader("SomeStoredProcedure", new string[]{"Param1","Param2"}, new object[]{Value1,Value2}, Action);...void ActionForForumCollection(SqlDataReader Reader) {LabelContent.Text += Reader[0].ToString()+" - "+Reader[1].ToString();}
View 1 Replies
View Related
Oct 17, 2006
Let's say I have a simple query to return the results of my "Status" table. The query reads as follows:
Code:
Select statusID, statusName
From Status
Here is the result set that I am returned:
Code:
22 Associate Member
23 Is Not Paying
24 Exempt
25 Fair Share
26 Member
29 Retiree
30 Staff
32 Fair Share - Self Pay
34 Member - Self Pay
Now, I am using this query for reporting purposes and would like to inject some additional sql that will append one additional row to my result set -- this is what I am calling the 'static' row in the thread title.
In other words, without modifying my database I would like to return the above set of data but with one additional row that has an arbitrary ID with the name "Unknown" or something similar.
again, I want to avoid adding an "Unknown" field directly to my database -- is their any way to "hard code" the selection of this row in my sequal?
Thanks,
Zoop
View 1 Replies
View Related
May 26, 2004
:confused: Please forgive this elementary question. I have database which has a view that produces the desired records and fields from multiple tables. As I understand it a view is a Virtual Table. My problem is I need to export these results periodically to deliver to a customer. I am running this database on SQL Server 2000 if I right click on a table I have the option to dts the data to a text file but when I right click on the view I do not have this option. How can I make this virtual table an actual table. PLEASE Help Thank You, Ed
View 1 Replies
View Related
Sep 26, 2006
dear experts,
i heard in a session, that the tables which are modifying continuously are known as dynamic tables.and which are not are known as static tables.
my question is how to find the statics to judge static and dynamic tables?
View 4 Replies
View Related
Oct 12, 2007
I try to declare a variable with un static decimal point in the following statement:-
declare @mpt int;
declare @mpq nvarchar (1)
SELECT @mpq = (SELECT mpq FROM ims.parm)
exec('declare @MM1 decimal (10,'+ @mpq +');')
set @mm1 = 1
print @mpt
when i print the declare statement looks like the following:
declare @MM1 decimal (10,2);
but with exec the statement, I have the following error:-
Msg 137, Level 15, State 1, Line 18
Must declare the scalar variable "@mm1".
Best regards
View 2 Replies
View Related
Mar 30, 2008
can someone tell me how to use static cursor to read the rows applying it in procedure.
i have input and output tables
i will have to use input table to read data and then in procedure update/insert into output table ) insert values from input and dditioanl calulate charges.
ok, there is my problem:
An Internet service provider has three different subscription
packages for its customers:
Package A: For $15 per month with 50 hours of access provided.
Additional hours are $2.00 per hour over 50 hours.
Assume usage is recorded in one-hour increments,
i.e., a 25-minute session is recorded as one hour.
Package B: For $20 per month with 100 hours of access provided.
Additional hours are $1.50 per hour over 100 hours.
Package C: For $25 per month with 150 hours access is provided.
Additional hours are $1.00 per hour over 150 hours
Assume a 30-day billing cycle.
1) Create a table to hold customer input billing data.
2) Populate input table with follwing records:
CustomerID Pkg Hours
---------- --- ------
1000 A 49
1010 A 50
1020 a 90
1030 a 130
1090 B 40
1100 B 99
1110 b 100
1120 b 145
1140 C 45
1150 c 85
1160 c 149
1170 c 150
1180 c 200
3) Create a table to hold customer data used to generate
the statement to be sent to the customer. It should
include CustomerID, Package, HoursUsed, and Charges.
Write an SQL script that reads customer billing data,
calculates a customer’s monthly charges, and populates
the customer statement table.
Use Cursor to process records and Stored Procedures for
ProcessBill and calcCharges.
CREATE TABLE custinput(
cust_id int NULL,
pkg char(1) NULL,
hrs smallint NULL
)
CREATE TABLE custoutput(
cust_id int NULL,
pkg char(1) NULL,
hrsused smallint NULL,
charges money null
)
insert into custinput values (1000,'A',49);
insert into custinput values (1010,'A',50);
insert into custinput values (1020,'a',90);
insert into custinput values (1030,'a',130);
insert into custinput values (1090,'B',40);
insert into custinput values (1100,'B',99);
insert into custinput values (1110,'b',100);
insert into custinput values (1120,'b',145);
insert into custinput values (1140,'C',45);
insert into custinput values (1150,'c',85);
insert into custinput values (1160,'c',149);
insert into custinput values (1170,'c',150);
insert into custinput values (1180,'c',200);
then there is conditions:
if upper (@pkg)= 'A'
begin
if @hrs<= 50 set @charges =15
else set @charges =15 + (@hrs-50)*2
end;
else if upper(@pkg)= 'B'
begin
if @hrs <= 100 set @charges = 20
else set @charges = 20 + (@hrs - 100)*1.5
end;
else
if @hrs <=150 set @charges = 25
else set @charges =25+(@hrs-150)
insert into custoutput values(@cust_id,@pkg,@hrs,@charges)
View 3 Replies
View Related
Jul 21, 2007
What you mean by static group in matrix?
View 2 Replies
View Related
Jul 30, 2007
This seems like it should be extremely obvious. I don't know if I'm just going insane or what...
If I add a matrix to my report and then right click a "data cell", I can add columns and rows. I go ahead and add however many columns and rows I want. Now I want to delete one of the rows I just added, how do I do that?
I have tried highling the entire row, but it doesn't allow me to delete it. If I select just one cell and click delete, it deletes the column and not the row. I can just use the "undo" command right after I create it, but if I have already done numerous other things to the report, it removes all of that first.
I must be missing something very obvious here.
View 19 Replies
View Related
May 31, 2007
Hello,
I have a dll which is developed in C#. I have made a reference to it from Reporting Services under Report Properties.
My problem is, that the methods in this dll are not static. Therefore under the Classes in Report Properties I have written the class-name and the instance-names of the instances that I need to use from this class (as I understand it, this is what you have to do if using non-static methods). In a text-box, I call one of the instances in the class, but I get this error:
[rsInvalidName] 'MyMethod()' is not a valid code class name. Names of objects must be CLS-compliant identifiers.
What exactly have I done wrong here?
Thanks
/Peter
View 3 Replies
View Related
Aug 30, 2006
Hi!
I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?
Thank you!
View 1 Replies
View Related
Feb 14, 2008
Hi All,
We're currently preparing for a project for a bank client of ours where we would be using SQL Server 2008's data mining capabilities.
In the context of the out-of-the-box algorithms of SQL08 does the algorithms Logistics, Clustering and Logistic Regression includes Dynamic Logistics, Dynamic Clustering and Dynamic Logistic Regression?
Regards,
Joseph
View 2 Replies
View Related
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
