assume connection is established and photo cloumn as data type as image in sql table.
str_insert = "INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"' "
cmd = New SqlCommand(str_insert, conn)
cmd.Connection = conn
cmd.ExecuteNonQuery()
Error at insert stmt: Operator '&' is not defined for types 'string' and 'system.drawing.image'
Hi,
I use an excel datasource to populate some simple dimensions, but when i extract the excel file i get alot of blank rows from the excel files...
How can i overcome this issue? Is this normal?
I never had problems like this using DTS in the 2000 version
Best Regards,
Luis Simões
Hi
Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.
SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME,
SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE,
SYSCOLUMNS.LENGTH,
SYSCOLUMNS.XPREC AS PRECISIONS,
SYSCOLUMNS.XSCALE AS SCALE,
SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS
FROM SYSREFERENCES
RIGHT OUTER JOIN
SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE
INNER JOIN
SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON
SYSREFERENCES.CONSTID = SYSOBJECTS.ID
WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P'
ORDER BY TABLE_NAME
thanks
Venu
I have a need to execute a SQL stmt on SQL 6.5 from a 7.0 box and vice
versa. I know that in 7.0, you can create a remote server and execute
statements across 7.0 servers, but can it be done from 6.5 to 7.0 and
7.0 to 6.5?
It's for a conversion project and time is the essence.
TIA,
Mike
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.
I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Please help
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
does anyone have a script that cleans up profiler traces by removing the variables from tsql. thereby giving you the procedure text as written.
I saw this a few months ago and havent had any luck in finding it.
anyone?
hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @tp
declare @tp datetime
declare @tableName varchar(100)
SET @tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @tableName) -- stmt.1
thank you..
I am using the kill statement to terminate a process, and this is done through my VB.net program. But I get the error
User does not have permission to use the KILL statement
This is the store procedure to call for kill statement..Is there any problem to execute it on dinamic sql?
CREATE PROCEDURE Kill_Process
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON;
DECLARE @spid smallint;
DECLARE @spid2 smallint;
DECLARE @loginame nchar(128);
DECLARE @nsql NVARCHAR(4000);
SET @loginame = 'xxxx'
EXEC Find_Lock_Info @DBName, @TableName
DECLARE ProcessCursor CURSOR FOR
SELECT spid FROM master.dbo.sysprocesses
WHERE dbid = db_id(@dbname) AND loginame = rtrim(@loginame) AND spid <> @@spid
AND spid IN (SELECT spid FROM dbo.tbl_Lock_Info where dbid = db_id(@DBName) AND OBJECT_NAME(ObjId) = @TableName)
OPEN ProcessCursor;
FETCH NEXT FROM ProcessCursor INTO @spid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nsql = 'KILL ' + CONVERT(nvarchar,@spid) + '';
EXEC sp_executesql @nsql;
FETCH NEXT FROM ProcessCursor INTO @spid;
END
CLOSE ProcessCursor;
DEALLOCATE ProcessCursor;
GO
CREATE PROCEDURE Find_Lock_Info
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #tmpLockInfo
(
spid SMALLINT,
dbid SMALLINT,
ObjId INT,
IndIdSMALLINT,
Type NCHAR(4),
ResourceNCHAR(32),
ModeNVARCHAR(8),
StatusNVARCHAR(5)
)
INSERT INTO #tmpLockInfo
(
spid, dbid, ObjId, IndId, Type, Resource, Mode, Status
)
EXEC Lock_Info
TRUNCATE TABLE tbl_Lock_Info
INSERT tbl_Lock_Info
SELECT
spid, dbid, ObjId, Type, Status
FROM #tmpLockInfo WHERE dbid = db_id(@DBName) AND ObjId <> 0 AND OBJECT_NAME(ObjId) = @TableName
END
GO
Hi,
I have two tables:
1. RubricReport
2. RubricReportDetail
How can I code this step in my stored procedure:
If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.
Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy
Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf
Kindly help me.
Thanks in advance
Hi experts,
I have a job that has several steps.
One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action.
Else exit from the job reporting failure.
I would like to know what sql stmts should i use to exit from the job (in the ELSE block)
I tried to use EXIT in the else stmt but it is not exiting from the job step.
Thanks in advance
select uid, OrderID, Count(OrderID)As DupCnt
from OrdDetails
group by uid, OrderID
having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
thx,
Kat
I'm trying to execute the following stmt within a Stored Procedure on SQL Server 2005 -
SELECT DISTINCT PkgActions.[PKG ID] AS PKG_ID, [APPR STATUS] AS Status, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment FROM [PkgActions]
JOIN PkgApprovers ON PkgActions.[PKG ID] = PkgApprovers.[PKG ID]
WHERE 1=1 and ([APPR STATUS] = 'approved' OR [APPR STATUS] = 'Denied')
It fails with the message - Incorrect syntax near 'approved'.
However when I run the same stmt within the Query editor it works properly.
I can't see what the SP doesn't like about this. Any thoughts?
Thanks
I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt
thanks in advance
We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.
We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'
this returns immediately.
Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.
There is no index on UDF3 and WOID is the primary key.
Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.
name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB
hey all,
I am writing a sproc:
select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents
I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried
if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end
but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?
How to get recordset from only last SELECT stmt executed???
this is part of my code.. in T-Sql
Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)
select 1
else
begin
select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.
end
set nocount off
GO
here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"
I thought set nocount ON does that, but it doesn't.
How can I do???
I have a relatively simple update statement that runs fine from Query Analyzer and Enterprise Manager (two rows updated in less than 1 sec) but times out when I run it from a VBScript file.
Any ideas?
Thavalai
Hi Everyone,
For my application, i am writing a generalized Sp for the Select Stmt.
I have started with as shown below,
Create procedure proSelect
@TabName varchar(1000),
@ColName varchar(1000),
@ConName varchar(1000)
As
Begin
Declare @str Varchar(8000)
If @ColName = '' Set @ColName = '* '
else Set @ColName = @ColName + ' '
Set @str = 'Select ' +@ColName+ 'From ' +@tabname
If @ConName <> ''
Set @str = @str + ' Where '+ @ConName
exec (@str)
End;
I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...
Can anyone help ???
Thanks in advance...
one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at
satishr@kggroup.com
Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output <br>
Trans Date, total deposits, total withdrawls, closing balance <br>
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik
View 1 Replies View RelatedHi,Don't worry about the vars, they are defined,the following line give me an err of "Incorrect syntax near '.'."Goal: to rename nonstardard column name.EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')','COLUMN';Thanks.
View 6 Replies View RelatedHi all,I am doing the change from having worked in Oracle for a long time toMS SQL server and am frustrated with a couple of simple SQL stmt's. Orat least they have always been easy.The SQL is pretty straightforward. I am updating a field with a Maxeffective dated row criteria. (PepopleSoft app)update PS_JOB as A set BAS_GROUP_ID = ' 'where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)This stmt is not working. I am getting an error on the keyword 'as'. Ihave tried:update PS_JOB A set...update PS_JOB from PS_JOB A set...Same result, error on 'A' or error on 'from'.I also tried to add the table alias to the sub query, whichtechnically worked, but with wrong data result.So my question comes down to: How do I use a table alias in an updatestatement in MS SQL server?I worked around this by creating a temp table. But that does notfulfill my curiosity, nor is it an ideal solution.Thanks a lot,-OK
View 14 Replies View RelatedI've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts -
@RowCount int Output
SELECT @rowcount = @@RowCount
This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied.
I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP?
I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms.
And while I'm at it, how do I get my ASP page to display this @RowCount value?
Many Thanks.
I'd like to alter a table and add a column:
add_date datetime
Is there a way, in the ALTER statement, to have the value default to the current date -- GETDATE() -- anytime a row is inserted w/out an explicit value for the column.
Thx
Hi there,
The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).
I have the following query:
SELECT
odf.mbb_sector sectorid,
SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
odf.mbb_projecttype = 'lkp_val_productionproblem' or
odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj
FROM
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF
LEFT JOIN RIM_RISKS_AND_ISSUES AS RRI ON RRI.pk_id = odf.id
WHERE
prj.prid = srm.id
AND srm.id = odf.id
AND srm.is_active =1
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'
GROUP BY
odf.mbb_sector
which returns me the following result :
.
The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.
Thanks
Friends,
What are the possible usuages of a SELECT query stmt inside a stored procedure ??
How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??
can any one throw some lite on this ..
Thanks,
SqlPgmr
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record. Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName" <InsertParameters><asp:Parameter Name="LoginName" Type="String" /> <asp:Parameter Name="strusername" Type="String" /> <asp:Parameter Name="UFID" Type="String" /> <asp:Parameter Name="DateAwarded" Type="DateTime" /> <asp:Parameter Name="Amount" Type="Decimal" /> <asp:Parameter Name="AwardingAgency" Type="String" /> </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form. When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement. Thanks in advance for your help,Ken
View 3 Replies View RelatedI have a sp that when executed inserts data into two tables(shown below). The sp works fine when the correct information is inserted into the tables but when you try and insert data that breaks the constraints in the table it obviously errors, but it seems to inert a new row in to the tmptimesheet table(error message shown below). When you open the tmptimesheet table there isn’t the row of a data there but if you run this (SELECT IDENT_CURRENT('tmptimmesheets') after the error it will come back with a id one higher than what’s in the table. Then the next time you run the sp a record will be inserted into the tmptimesheet table and not in to the tmptimsheethours table!? I’m at a total lost at what to do can someone please help!?
CREATE TABLE [dbo].[tmptimesheets](
[TimesheetID] [int] IDENTITY(1,1) NOT NULL,
[Placementid] [int] NOT NULL,
[Periodstarting] [datetime] NOT NULL,
[createdon] [datetime] NOT NULL,
[createduserid] [int] NOT NULL,
[Issued] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
[ReadyForBilling] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
[Reject] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
[Comments] [text] COLLATE Latin1_General_CI_AS NULL,
[Rate] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_tmptimesheets] PRIMARY KEY CLUSTERED
(
[TimesheetID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_tmptimesheets_1] UNIQUE NONCLUSTERED
(
[Placementid] ASC,
[Periodstarting] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[tmptimesheethours](
[TmpTimesheethourid] [int] IDENTITY(1,1) NOT NULL,
[Timesheetid] [int] NOT NULL,
[applicantid] [int] NOT NULL,
[Workedon] [datetime] NOT NULL,
[Hoursworked] [numeric](10, 2) NOT NULL,
[performancevalueid] [int] NOT NULL,
[Reject] [ntext] COLLATE Latin1_General_CI_AS NULL,
[Breaks] [numeric](10, 2) NOT NULL,
CONSTRAINT [PK_tmptimesheethours] PRIMARY KEY CLUSTERED
(
[TmpTimesheethourid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [Pronet_TS]
GO
ALTER TABLE [dbo].[tmptimesheethours] WITH NOCHECK ADD CONSTRAINT [FK_TimesheetHours_Timesheets] FOREIGN KEY([Timesheetid])
REFERENCES [dbo].[tmptimesheets] ([TimesheetID])
GO
ALTER TABLE [dbo].[tmptimesheethours] CHECK CONSTRAINT [FK_TimesheetHours_Timesheets]
Error Message
Msg 2627, Level 14, State 1, Procedure sp_tmptimesheet_insert_day, Line 40
Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 65
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 86
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 108
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 130
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 153
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 178
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 200
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 3902, Level 16, State 1, Procedure sp_tmptimesheet_insert_day, Line 223
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.