I'm trying to populate a table of pending emails. The problem is I need
to populate the email field using a select statement but the message
field with static text. Can this be done or is another approach more
prudent? What I have is below but is kicking errors:
DECLARE @msg varchar(300)
SET @msg = 'New users have applied for accounts. Please review their information.'
IF @Type='CreateUserApply'
INSERT INTO cdds_Email (Address,Message)
VALUES (SELECT
M.Email
FROM
dbo.aspnet_Membership M
INNER JOIN
dbo.aspnet_UsersInRoles U
INNER JOIN
dbo.aspnet_Roles R
ON
U.RoleId = R.RoleId
ON
U.UserId = M.UserId
WHERE
R.RoleName = 'Manager',@msg)
Greetings,A novice needs some advice:I am able to bulk insert data from a text file into a table. Is itpossible to run a query and insert the results into a text file on thelocal or network hard drive?Thanks For Reading,Aaron
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?
I have created an SSIS Package that takes data in an excel file and writes it to an Oracle Database. The excel file has 5 columns, but the database tabls has many additional columns. I would like to default some of these other columns for this job. For instance, I want to set the created and updated times to the time when the job ran, and set some other fields to values that will be consistent for every row in the job.
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.
an example for the pb 1)First i have created a dynamic cursor :
DECLARE authors_cursor CURSOR DYNAMIC FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7 OPEN authors_cursor FETCH first FROM authors_cursor
2)The result for this cursor is for expamle 'USA'.
3) If now i do an update on that location with a new value 'USA1'
update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7
4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).
If i remove DISTINCT from the cursor declaration , the process works fine .
An example for my pb 1) Created a dynamic cursor : DECLARE cursor_teste CURSOR DYNAMIC FOR Select DISTINCT name from table WHERE ID = 1 OPEN cursor_teste FETCH first FROM cursor_teste 2)The result for this cursor is for example 'teste'. 3) If now i do an update on that name with a new value 'teste1' than if i fetch the cursor , i''ll get the old value (teste) .
any idea how to make a select distinct result in a dynamic Cursor?
I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file?
The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid.
ALTER TRIGGER DICastRaw1hrInsertGuid ON Alphanumericdata.dbo.DICastRaw1hr FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092' WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK))
More Questions:
- The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ...
ALTER TRIGGER DICastRaw1hrInsertDate ON Alphanumericdata.dbo.DICastRaw1hr FOR INSERT AS DECLARE @ID as integer SELECT @ID = i.recordid from inserted i UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092' WHERE recordid = @ID
- I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this?
I have a SQL SERVER database which has Articles Table. This table contains "Description" field which is of type "text". I am trying to insert 800- 1000 words of data into this field. This data also contains code snippets. I dont know for some reason it only inserts one or two lines and thats it. No error is being thrown. I am using multiline textbox to enter the data into the database. any ideas
It displays something like this:
test 1
By AzamSharp
Creating XML Men // This is very long text. Actually its the whole article but it only displays three words
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
My sales are broken into two different major categories: retail and internet. Rather than create two identical fact tables (ala AdventureworksDW) I created on fact table with a dimension that slices by retail or interent. I use that dimension as a parameter on nearly every one of my reports. The use can get retail, internet or both. So far, so good.
Now I need to create a report that shows retail sales and internet sales in adjacent columns on the same report. Is there a way to filter individual columns in a table? Or will I have to rethink how I store my data in the cube?
I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...
Take the following example:SELECT * FROM [User] LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).
Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.
The solution I have used in the past is:
SELECT * FROM [User] LEFT OUTER JOIN ( SELECT * FROM [Profile] INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId] ) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]
The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.
I could do this:SELECT * FROM [User] LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.
I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.
Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?
CREATE TABLE [dbo].[instructions]( [site_no] [int] NOT NULL, [instructions] [text] NULL ) Select top 3 * from instructions
Output
Site_noInstructions 20Request PIN then proceed 21Request PIN if wrong request name 22Request PIN allowed to use only numbers
All text instructions start with “Request PIN” but after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN” the text “and codeword” keeping the current rest of text
Desired output
Site_noInstructions 20Request PIN and codeword then proceed 21Request PIN and codeword if wrong request name 22Request PIN and codeword allowed to use only numbers
I want to create a filtered view according to some information provides. for example I want to create in form 2 check boxes male and female and when either both or one of them is unchecked, the query filters out the unchecked content from view according to the other filtering conditions.
the exact thing I need is: a text box for string looking (in a specific column). male & female checkboxes. two datetime textboxes to specify a range to focus in.
it is very important to me, so if you misunderstood my question, please contact me.
Hello, how do you setup a Profiler filter, that has mixed AND/OR operators. Thru the Profiler GUI, I don't even see how you would ask for any statement/proc with READS over 10,000 OR DURATION over 1000ms. How do you do an "OR" thru the GUI?
Forgetting the GUI and doing traces thru scripts, the @logical_operator isn't too logical, and I can't find any description for setting this parameter. If you only have one filter, it doesn't matter if you set it to 0 (AND) or to 1 (OR)... But if you want multiple filters, so one filter is Reads >= 10,000 and the other filter is Duration >= 1000ms., how do you do that? I tried it with the @logical_operator of "1" on both, but then I also got other events with a NULL value in READS.
I have an update command with 7 parameters, but at run time the order to the parameters gets mixed up. I'm using a stored procedure. At first I have the command type set to text, and was calling it using EXEC spName ?,?,?,?,?,?,? I then named each of the parameters and set their sources. The parameters are like this (samepl name, then source, then type): A : QueryString - intB: Control - intC: Control - intD: None - intE: None - decimalF: Control - datetimeG: Control - datetime At run time I was getting an error that an integer couldn't be converted to date time. So I put a breakpoint in the Updating event and then looked at the parameters prior to update. This is how they looked (Parameter index, paramter name): [0] A[1] B[2] D[3] E[4] F[5] G[6] C It didn't maek any sense. Do, I deleted all of the paramters and readded them. That didnt' work. Then I changed the command to StoredProcedure and refreshed the parameters from the stored proc and it brought them in the right order, but the problem remains the same. I looked at the page source, and there are no indexes in the page source, but the parameters are listed in the proper creation order, as follows:<UpdateParameters><asp:QueryStringParameter Type="Int32" Name="PROJ_ID" QueryStringField="pid"></asp:QueryStringParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="TASK_UID" ControlID="fvTask"></asp:ControlParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="ASSN_UID" ControlID="gvResources"></asp:ControlParameter><asp:Parameter Type="Int32" Name="RES_UID"></asp:Parameter><asp:Parameter Type="Double" Name="Work"></asp:Parameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Start" ControlID="TASK_START_DATETextBox"></asp:ControlParameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Finish" ControlID="TASK_FINISH_DATETextBox"></asp:ControlParameter></UpdateParameters> No mater what I do, at run time ASSN_UID is always the last parameter. I've also run a SQL trace to see how it is actually being executed, and sure enough, its passing the value for ASSN_UID as the last parameter, which obviously doesn't work. Any ideas as to why this would happen or how to fix it? (I guess I can reorder the patameters in the stored proc to match how they are being passed, but still, that wouldn't be a very comfortable solution, since it could perhaps revert at some point or something)
As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.
I can do this in two separate Execute SQL tasks as follows:
Task 1 syntax
DECLARE @maxDate datetime SELECT @maxDate = max(dtLastChangedDate) FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging SET PreviousFilterValue = CurrentFilterValue, CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue), DateSourceTableLastRead = GetDate(), RowsReturned= -1 WHERE SourceTableName = 'cancel'
Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0
UPDATE dbo.tblLogging SET RowsReturned= ? WHERE SourceTableName = 'cancel'
However I cannot make this work with a single SQL statement such as
DECLARE @maxDate datetime SELECT @maxDate = max(dtLastChangedDate) FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging SET PreviousFilterValue = CurrentFilterValue, CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue), DateSourceTableLastRead = GetDate(), RowsReturned= ? WHERE SourceTableName = 'cancel'
because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.
Is this behaviour by design, is it a bug, or is there something I've missed?
I have two different roles, each one with a dax filter. One is for filtering users that access by Excel, and other for filtering users that access by Reporting Services, respectively:
To create only one role that serves Excel and Reporting Services users, is it viable to use only the || (OR) operator?, is there any other regard i should take?
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
Hello,I'm trying to update a single field of a record and i want to do it using a standard multi line text box but I'm not sure how to write the c# command to process the sql update. I would also like the entry to be added into the database with line breaks. Thanks for your help
I am trying to SELECT various fields from a table in SQL Server to INSERT INTO a Text file defined using a Schema.ini. I know that this can be done using BCP (not sure if I could specify which fields as not all are required), DTS (which I have done) and with a Linked Server (where I keep getting a bookmark error). The process used in Access is [Sample#csv] IN 'C:' 'TEXT;' but I can't seem to find the format to use in SQL using either a MSDASQL or ODBC connection string. Any ideas would be greatly appreciated, thanks.
How do you insert more than 255 characters into a text field? or am I using READTEXT and DATALENGTH incorrectly?
I am trying to concatenate two 255 varchar strings. Both do have 255 characters of data. I want to insert the concatenated strings into a field define as a text data type. if I try to ' insert into tempTbl(textFieldDataType) values (@text1+@text2) it appears only the @text1 is inserted. I have been told the data is really there, but I can only read the 256 position + with READTEXT.
DECLARE @textptr varbinary(16) select @textptr = (select TEXTPTR(textFieldDataType) from tempTbl where pktempTbl = 33) READTEXT tempTbl.textFieldDataType @textPtr 255 10
This should have read 10 text positions after the 255th position. However, I get msg Msg 7124, Level 16, State 1 The offset and length specified in the READTEXT command is greater than the actual data length of 255.
If I use the datalength function, it appears to confirm that @text2 was never inserted
declare @length int select @length = (select datalength(textFieldDataTypet) from tempTbl where pktempTbl = 33) select @length